insert语句

1.单表单行INSERT

INSERT INTO table[(column1,column2,...)] VALUE(value1,value2,...)

 

2.无条件多表INSERT

INSERT[ALL][condition_insert_clause]

[insert_into_clause values_clause] (subquery)

 

例子：

insert all
into e_sal(empno,sal) values(empno,sal)
into e_job(no,job) values(empno,job)
select empno,sal,job from emp
/

3.有条件的多表INSERT

INSERT [ALL | FIRST]

WHEN condition THEN into_clause values_clause

[WHEN condition THEN] into_clause values_clause]

......

[ELSE] [insert_into_clause values_clause] 

Subquery;

 

例子：

insert all
when salary<=1000 then into sal1000 values(emp_id,hire_date,salary)
when salary<=2000 then into sal2000 values(emp_id,hire_date,salary)
else
into sal_else values(emp_id,hire_date,salary)
select empno emp_id,hiredate hire_date,sal salary from emp
/

insert first
when salary<=1000 then into sal1000 values(emp_id,hire_date,salary)
when salary<=2000 then into sal2000 values(emp_id,hire_date,salary)
else
into sal_else values(emp_id,hire_date,salary)
select empno emp_id,hiredate hire_date,sal salary from emp
/

当使用all时，从上到下检查每个条件，满足就执行后面的into。

当使用first时，从上到下，一旦满足就执行后面的into，之后终止判断，开始下个row的检查。

 

4.pivoting insert，实际上就是无条件的insert all