哎..好久没抽空来看看了。想死偶了!现在终于稳定下来了。继续我的BLOG旅程!
正题:今天实验了下 INSERT ALL / INSERT FIRST
重新建表 all_a , all_b 字段 (id ,name)
0 0!CSDN改版了??? 找不到 插入代码的按钮了。T T..
SQL> drop table all_a ;
Table dropped
SQL> drop table all_b purge;
Table dropped
SQL> select object_name,original_name from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$sDAWsbiAP6rgQGQKLQJnBA==$0 SYS_C0067963
BIN$sDAWsbiBP6rgQGQKLQJnBA==$0 ALL_A -- 复习一下..
建表:
SQL> create table all_a (id number(10) primary key ,name varchar2(10));
Table created
SQL> create table all_b (id number(10) primary key ,name varchar2(10));
Table created
SQL> create sequence test_seq;
Sequence created
TEST INSERT ALL:
TEST 1
=========================================================
SQL> insert all
2 into all_a values(test_seq.nextval ,name )
3 into all_b values(test_seq.nextval ,name )
4 select 'Tiger' from dual;
insert all
into all_a values(test_seq.nextval ,name )
into all_b values(test_seq.nextval ,name )
select 'Tiger' from dual
ORA-00904: "NAME": invalid identifier
SQL> insert all
2 into all_a values(test_seq.nextval ,name)
3 into all_b values(test_seq.nextval ,name)
4 select 'Tiger' name from dual;
2 rows inserted
SQL> select * from all_a;
ID NAME
----------- ----------
1 Tiger
SQL> select * from all_b;
ID NAME
----------- ----------
1 Tiger
注意:在INSERT ALL 中使用SEQUENCE ,位置应该在 into ...values(seq.nextval)中。而不是在select seq.nextval from dual;中。
insert all
into all_a values(seq ,name )
into all_b values(seq ,name )
select test_seq.nextval seq,'Tiger test seq' name from dual
ORA-02287: sequence number not allowed here !!!!!!!!!!!!!!!!!
=========================================================
TEST 2:(含有WHEN判断)
=========================================================
SQL> insert all 2 when 1=1 then into all_a values (test_seq.nextval ,name) 3 when 1=2 then into all_b values (test_seq.nextval ,name) 4 select 'Test When' name from dual; 1 row inserted
SQL> select * from all_a; ID NAME ----------- ---------- 1 Tiger 2 Test When SQL> select * from all_b; ID NAME ----------- ---------- 1 Tiger
=========================================================
TEST INSERT ALL:
TEST
=========================================================
SQL> insert first
2 when 1=2 then into all_a values (test_seq.nextval ,name)
3 when 1=1 then into all_b values (test_seq.nextval ,name)
4 select 'first x1' name from dual;
1 row inserted
SQL> select * from all_a;
ID NAME
----------- ----------
SQL> select * from all_b;
ID NAME
----------- ----------
10 first x1
SQL> commit;
Commit complete
SQL> insert first
2 when 1=1 then into all_a values (test_seq.nextval ,name)
3 when 1=1 then into all_b values (test_seq.nextval ,name)
4 select 'first x2' name from dual;
1 row inserted
SQL> select * from all_a;
ID NAME
----------- ----------
11 first x2
SQL> select * from all_b;
ID NAME
----------- ----------
10 first x1=========================================================
OK!总结一下:
INSERT ALL :
1.首先确定可以使用sequence ,但位置要注意,不是在SELECT 字句中。
2.WHEN 当满足条件 就匹配 插入
INSERT FIRST:
相当于一个筛子,当WHEN 1 满足 ,就不在往下执行 WHEN 2。
若WHEN 1不满足 ,则往下执行WHEN 2,直到 匹配到符合条件的WHEN,然后才INSERT。
如此说来,只INSERT 一次了 0 -。
如上说述,如有差错,请指教!
TEST INSERT ALL:
TEST 1
=========================================================