insert 用法

1、标准insert --单表单行插入

insert into dep (dep_id,dep_name) values(1,'技术部');
备注:使用标准语法只能插入一条数据,且只能在一张表中插入数据

2、无条件 insert all --多表多行插入

insert all
into sal_history(emp_id,hire_date,salary) values (empid,hiredate,sal)
into mgr_history(emp_id,manager_id,salary) values (empid,hiredate,sal)
select employee_id empid,hire_date hiredate,salary sal,manager_id mgr
from employees
where employee_id>200;

--这种方式也为插入多行
insert into table_test select 字段1,字段2 from table_test1 --多行插入

3、有条件的insert

insert all
when id>5 then into z_test1(id, name) values(id,name)
when id<>2 then into z_test2(id) values(id)
else into z_test3 values(name)
select id,name from z_test;

当使用all关键字时,oracle会从上至下判断每一个条件,当条件满足时就执行后面的into语句
在上面的例子中,如果id=6 那么将会在z_test1中插入一条记录,同时也在z_test2中插入一条记录

备注:
当使用first关键字时,oracle会从上至下判断每一个条件,当遇到第一个满足时就执行后面的into语句,
同时中断判断的条件判断,在上面的例子中,如果id=6,仅仅会在z_test1中插入一条数据

insert first
when id = 1 then into z_test1 values(id, name)
when id > 5 then into z_test2 values(id)
else into z_test3 values(name)
select * from z_test;

4、旋转insert (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)
);

看上面的表结构,现在将要sales_source_data表中的数据转换到sales_info表中,这种情况就需要使用旋转insert

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;

从该例子可以看出,所谓旋转insert是无条件 insert all 的一种特殊应用,但这种应用被oracle官方,赋予了一个pivoting insert的名称,即旋转insert