ITPub博客

首页 > 数据库 > Oracle > pivoting insert等

pivoting insert等

Oracle 作者:season0891 时间:2014-03-10 12:50:38 0 删除 编辑
http://hi.baidu.com/noyes_dk/item/55ffa05259b1a8a2acc857a9

Use the following types of multitable inserts
?Unconditional INSERT
?Pivoting INSERT
?Conditional ALL INSERT
?Conditional FIRST INSERT
:) Create and use external tables
:) Name the index at the time of creating a primary key constraint

unconditional insert: all into_clause
The oracle server executes each insert_into_clause once for each row returned by the subquery.
在使用all关键字时,oracle将执行每个insert,不进行排除
SQL> insert all
2 into sal_history values(empid,hiredate,sal)
3 into mgr_history values(empid,mgr,sal)
4 select employee_id empid,hire_date hiredate,salary sal,manager_id mgr
5 from employees
6 where employee_id>200;

8 rows created.
SQL> select employee_id from sal_history
2 union all
3 select employee_id from mgr_history
EMPLOYEE_ID
-----------
201
202
205
206
201
202
205
206

-- 有条件的insert all
insert all
when sal>10000 then
into sal_history values (empid,hiredate,sal)
when mgr>200 then
into mgr_history values (empid,mgr,sal)
select employee_id empid,hire_date hiredate,salary sal,manager_id mgr
from employees
where employee_id>200;

-- insert first是符合第一个的记录不再应用到下面的条件判断
insert first
when sal >25000 then
into special_sal values(deptid,sal)
when hiredate like ('%00%') then
into hiredate_history_00 values (deptid,hiredate)
when hiredate like ('%99%') then
into hiredate_history_99 values(deptid,hiredate)
else
into hiredate_history values(deptid,hiredate)
select department_id deptid,sum(salary) sal,
max(hire_date) hiredate
from employees
group by department_id;

-- 什么是pivoting insert
create table sales_source_data (
employee_id number(6),
week_id number(2),
sales_mon number(8,2),
sales_tue number(8,2),
sales_wed number(8,2),
sales_thur number(8,2),
sales_fri number(8,2)
);
insert into sales_source_data values (176,6,2000,3000,4000,5000,6000);

create table sales_info (
employee_id number(6),
week number(2),
sales number(8,2)
);
-- 现在要将上表的数据转换到下表中,
insert all
into sales_info values(employee_id,week_id,sales_mon)
into sales_info values(employee_id,week_id,sales_tue)
into sales_info values(employee_id,week_id,sales_wed)
into sales_info values(employee_id,week_id,sales_thur)
into sales_info values(employee_id,week_id,sales_fri)
select employee_id,week_id,sales_mon,sales_tue,
sales_wed,sales_thur,sales_fri
from sales_source_data;

 

  1.一般的insert 操作。

  

 使用语法insert into table_name[(column[,column...])] values (value[,value…])的insert语句,每条insert只能插入到目标表中一条指定的数据。如果有很多行需要插入,而且这些数据来源于别 的表或多个表之间提取的数据,那么这就不能满足要求了。因此oracle提供了一个扩展,见下面描述。

  2.insert into table_name[(column[,column…])] select_statement

  

 Oracle提供了一个扩展,将通过select语句查询的记录插入到指定表的所有列或者指定列中。每次只能插入到一个表中,但是性能已经比写很多条 insert语句要高了。另外oracle还提供了直接create table table_name as select….(as不可以少!),实现直接创建表并且插入数据,依赖于后面的select语句的结果,比如只复制表结构,约束不赋值,那么可以使用 Create table new_table as select * from source_table where 1=0; --1=0不查询记录,只复制基本结构。

  比如有个需求:表mxt 新增了一个列time,那么这个新增的列在oracle 中会被放在所有列的后面,现在想把这个time列放在第2 个位置,那么可以使用这个操作:

  下面具体说明oracle 对insert 操作的其他扩展,实现目标,一条insert 操作,插入到多个表中,每个表插入多条记录。

  3向多个目标insert

  这里有四种类型:

  

  1. 无条件的insert

  2. 带条件的insert all

  3. 带条件的insert first

  4. pivoting insert

  多表insert使用限制:

  

  1. 只能对table使用多表insert,不能对视图或物化视图使用。

  2. 不能对远程表进行这个插入操作。

  3. 在做多表insert操作,不能指定一个表的集合表达式。

  4. 多表insert中的的into目标表加在一起的列数不能超过999 个。

  语法:

  注:如果没有conditional_insert_clause,必须有all,即insert all 开头。如果有conditional_insert_clause,则all和first只能取其一。

  3.1 无条件的insert操作

  

  这里所说的无条件的insert操作有两种,一种就是单表insert into dest_table select..,一种是向多个目标中insert,那么使用insert all into dest_table1,dest table2…select…

  目标表只有一个:insert into dest_table[colum(,column…)] select …

  目标表有多个: insert all --可以替代上面的操作

  Into dest_table1[column(,column…)] [values(…)]

  Into dest_table2[column(,column…)] [values(…)]

  …

  Select_statement—values中指定的是select结果中的列

  目标表只有一个的,那么插入目标表中后面可以跟指定的列,依赖于查询出来的数目以及类型,没有values指定。如果不指定列,那么表示目标表的列类型和顺序与select语句查询的结果完全一致。

 对于目标表有多个的必须使用insert all,all 不能省略。与带条件的不同,带条件的all 可以省略,后面讲解。这里的每个目标表可以不指定列,依赖于values 中指定的select语句查询出来的结果中的列。如果目标表不指定列,那么表示目标表的列的顺序类型和values 中指定的一致,而values 中的列则依赖于select 语句查询出的结果。当然也可以省略values,那么表示目标表中的列顺序和类型和select语句查询出来的结果完全一致,否则报错。Insert all可以替代目标表只有一个的操作。

  建议不要省略目标表中的列和values,增强可读性。

  例如:

  这里总共插入了75行记录,select语句查询的记录为25 行,因为是无条件的插入,那么对每个目标表都插入了25行记录,所以总共插入75 行记录。

  3.2 带条件的insert all

  带条件的insert all与不带条件的insert all相比可以省略all,但是不建议省略。另外的区别就是插入到目标表之前,有条件判断,从查询的结果行中筛选出满足条件的记录,然后向目标表中插入,而不在是无条件的insert all中的全量插入。

  语法是:

  Insert all

  When 条件 then

  Into dest[指定列] values(查询中的列)

  When 条件 then

  …

  [else]

  Into dest[指定列] values(查询中的列)

  Select语句.

 是否可以省略目标表中的指定列和values 那么与无条件的insert all 一致。条件中的列那么必须与查询的结果列名保持一致,如果select中使用别名,那么条件中也必须使用别名,和values中的规则一样。Else可以 指定,如果不指定else,则按照条件筛选,如果指定else,则表示与前面全不匹配的插入到else后面的目标表中。带条件的insert all与insert first的区别在于只按条件对查询的结果进行筛选,不会不考虑前面已经匹配过的记录,每次都是全量匹配,而insert first是后面的when条件insert操作会对前面已经匹配过的记录不予考虑。

  例如:

  总共插入26行,而查询结果为25行,emp_2中插入了一条与emp_1中重复的记录。

  3.3 带条件的insert first

  

  Insert first只有带条件的,没有不带条件的。

  语法只要将insert all中的all改为first就可以了。这里的first不可以省略。省略那么默认就是all。

  它的插入规则是如果前面的when 匹配过的,下一个when 会自动不考虑上面已经匹配过的行记录。比如将上面16.3.2中的例子改为:

  插入25行,因为when s_last_name like M%不考虑前面已经匹配的行,所以emp_2中就没有那条与插入emp_1中的重复行。

  3.4 pivoting insert(旋转插入)

  

  这节主要讨论使用pivoting insert 实现将非关系性表记录转换为关系型表中存储。Pivot 旋转是OLAP 中的一个基本改变,提供多维度数据分析。比如一个表为sales_source_data,里面的列为

 employee_id number,week_id number,sales_mon number,sales_tue number,sales_wed number,sales_thur number,sales_fri number 员工id,周id,其他的是工作日的每天的销售记录。

  现在要把数据转到sales_info表中,这个表的列为employee_id,week_id,sales。

  要实现这个转换可以使用pivoting insert 操作。和insert all类似,只不过这里的多表都是同一个表。

  insert all

  into sales_info values(employee_id,week_id,sales_mon) --分别按每个工作日插入

  into sales_info values(employee_id,week_id,sales_tue)

  into sales_info values(employee_id,week_id,sales_wed)

  into sales_info values(employee_id,week_id,sales_thur)

  into sales_info values(employee_id,week_id,sales_fri)

  select employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri

  from sales_source_data;

  比如原来的sales_source_data 中只有一条记录,那么插入到sales_info 中就变成了5 条记录。

  select * from sales_source_data;

  select * from sales_info;

  4 external tables(外部表)



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/90618/viewspace-1104484/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-06-10

  • 博文量
    791
  • 访问量
    1946560