multitable insert command
来源:互联网 发布:甩狗头的软件 编辑:程序博客网 时间:2024/05/17 01:30
conn hr/hr
1,创建测试表
SQL> create table small_orders(order_id int, order_total number, sales_rep_id varchar2(4), customer_id varchar2(10));
Table created.
SQL> create table medium_orders(order_id int, order_total number, sales_rep_id varchar2(4), customer_id varchar2(10));
Table created.
SQL> create table large_orders(order_id int, order_total number, sales_rep_id varchar2(4), customer_id varchar2(10));
Table created.
SQL> create table orders(order_id int, order_total number, sales_rep_id varchar2(4), customer_id varchar2(10));
Table created.
2,插入测试数据:
insert into orders(order_id,order_total,sales_rep_id,customer_id) values(1,1000,'0001','0000000001');
insert into orders(order_id,order_total,sales_rep_id,customer_id) values(2,2000,'0002','0000000002');
insert into orders(order_id,order_total,sales_rep_id,customer_id) values(3,10000,'0003','0000000003');
insert into orders(order_id,order_total,sales_rep_id,customer_id) values(4,20000,'0004','0000000004');
insert into orders(order_id,order_total,sales_rep_id,customer_id) values(5,100000,'0005','0000000005');
insert into orders(order_id,order_total,sales_rep_id,customer_id) values(6,200000,'0006','0000000006');
3,有条件的多表插入(conditional insert all):
INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total >= 10000 AND order_total < 100000 THEN
INTO medium_orders
WHEN order_total >=100000 THEN
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id
FROM orders;
4,无条件多表插入
INSERT ALL
INTO small_orders
INTO medium_orders
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id
FROM orders;
,5,Insert First
SQL> delete from small_orders;
2 rows deleted.
SQL> delete from medium_orders;
2 rows deleted.
SQL> delete from large_orders;
2 rows deleted.
INSERT FIRST
WHEN ottl < 10000 THEN
INTO small_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl >= 10000 and ottl < 100000 THEN
INTO medium_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl >= 100000 THEN
INTO large_orders
VALUES(oid, ottl, sid, cid)
SELECT order_id oid, order_total ottl, sales_rep_id sid, customer_id cid
FROM orders;
6,The difference between insert all and insert first
INSERT ALL
WHEN order_total < 1000000 THEN
INTO small_orders
WHEN order_total < 1000000 THEN
INTO medium_orders
WHEN order_total >=10 THEN
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id
FROM orders;
18 rows created.
三个表都将插入6条记录
INSERT FIRST
WHEN order_total < 1000000 THEN
INTO small_orders
WHEN order_total < 1000000 THEN
INTO medium_orders
WHEN order_total >=10 THEN
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id
FROM orders;
6 rows created.
只有第一个表small_orders插入了6条记录,其他的两个表没有被插入记录。
从上面的两个例子可以看出INSERT FIRST与INSERT ALL的区别:
INSERT FIRST只检查第一个条件,如果第一个条件满足,虽然第二个条件也满足也不会去检查,除非第一个条件不满足的情况,才会检查第二个条件;
INSERT ALL则检查所有条件;
- multitable insert command
- Oracle-Multitable Insert Command 操作
- Multitable Insert
- Multitable INSERT 的用法
- Multitable INSERT 的用法
- Multitable INSERT 的用法
- Oracle Multitable INSERT 的用法
- Oracle Sql技巧 - Upsert, Multitable Insert, Undrop
- Oracle笔记-Multitable INSERT 的用法
- Oracle笔记-Multitable INSERT 的用法
- Oracle笔记-Multitable INSERT 的用法
- Oracle笔记-Multitable INSERT 的用法
- ADO.NET Insert command function
- INSERT command denied to user 上线后
- mysql INSERT command denied to user
- MVCPaging MultiTable 多个Table
- - INSERT command denied to user 'XXX'@'XXX.internal' for table
- android Sqlite insert command after,data is empty
- Android中防止Toast重复弹出相同的信息
- 读取properties文件的工具类
- Managing NuGet Packages Using the Dialog
- Extjs日期的定义及使用
- 命令行式下java package的编译与运行
- multitable insert command
- 为什么后缀++只能作右值
- Android编程14个很有用的代码片段
- Java GC(垃圾回收)
- android color
- 黑马程序员_Java基础知识
- tcpdump抓包分析详解
- python2.4升级2.7.2
- Android Sensor 架构深入剖析