insert all用法

INSERT ALL是9i新增的语法，它扩充了原有的INSERT语句，

使得INSERT语句从原来的只能插入到一张表发展到可以同时插入多张表，

还可以根据判断条件来决定每条记录插入到哪张或哪几张表中。

example：

CREATE TABLE mxm01
(
ID NUMBER,
NAME VARCHAR2(10)
)

CREATE TABLE mxm02
(
ID NUMBER,
NAME VARCHAR2(10)
)         

CREATE TABLE mxm03
(
ID NUMBER,
NAME VARCHAR2(10),
address VARCHAR2(10)
)         
INSERT INTO mxm03 VALUES(1,'m','add') ;  
INSERT INTO mxm03 VALUES(2,'x','addx') ;
INSERT INTO mxm03 VALUES(3,'mm','addmm') ;


INSERT ALL
WHEN ID = 1 THEN INTO mxm01(ID,NAME) VALUES (ID,NAME)
WHEN ID = 2 THEN INTO mxm02(ID,NAME)VALUES (ID,NAME)
SELECT ID,NAME FROM mxm03;

SELECT * FROM mxm01;

ID           NAME

1             m

SELECT * FROM mxm02;

ID           NAME

2             x

 

-------------------------------------------------------------------------------------------------------

INSERT ALL
WHEN ID = 1 THEN INTO mxm01(ID,NAME) VALUES (ID,NAME)
WHEN ID = 2 THEN INTO mxm02(ID,NAME)VALUES (ID,NAME)
SELECT ID,NAME,address FROM mxm03;

SELECT * FROM mxm01;

ID           NAME

1             m

1             m
SELECT * FROM mxm02;

ID           NAME

2             x

2             x

 

ps:在上面的sql语句中，我查询出了address但是没有用到它，此时也是允许的，不会报错。