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但是没有用到它,此时也是允许的,不会报错。