首页 > 代码库 > Oracle insert all语句介绍
Oracle insert all语句介绍
Oracle 中insert语句的高级用法,INSERT ALL 语句介绍:
1、无条件insert all 全部插入
CREATE TABLE t1(product_id NUMBER, product_name VARCHAR2(80),MONTH NUMBER); INSERT INTO t1 VALUES(111, '苹果',1); INSERT INTO t1 VALUES(222, '橘子',1); INSERT INTO t1 VALUES(333, '香蕉',1); COMMIT; CREATE TABLE t2 AS SELECT * FROM t1 WHERE 1=2; INSERT ALL INTO t2 VALUES (product_id, product_name,MONTH) INTO t2 VALUES (product_id, product_name,MONTH+1) INTO t2 VALUES (product_id, product_name,MONTH+2) INTO t2 VALUES (product_id, product_name,MONTH+3) SELECT product_id, product_name, MONTH FROM t1; COMMIT; SELECT * FROM t2 ORDER BY product_id, product_name, MONTH;
---------- ---------- ----------
111 苹果 1
111 苹果 2
111 苹果 3
111 苹果 4
222 橘子 1
222 橘子 2
222 橘子 3
222 橘子 4
333 香蕉 1
333 香蕉 2
333 香蕉 3
333 香蕉 4
已选择12行。
2、有条件insert all
CREATE TABLE small_orders (order_id NUMBER(12) NOT NULL, customer_id NUMBER(6) NOT NULL, order_total NUMBER(8,2), sale_rep_id NUMBER(6) ); CREATE TABLE medium_orders AS SELECT * FROM small_orders; CREATE TABLE large_orders AS SELECT * FROM small_orders; CREATE TABLE special_orders (order_id NUMBER(12) NOT NULL, customer_id NUMBER(6) NOT NULL, order_total NUMBER(8,2), sale_rep_id NUMBER(6), credit_limit NUMBER(9,2), cust_email VARCHAR2(30) ); INSERT ALL WHEN order_total < 100000 THEN INTO small_orders WHEN order_total > 100000 AND order_total < 200000 THEN INTO medium_orders ELSE INTO large_orders SELECT order_id, customer_id, order_total, sales_rep_id FROM orders;
3、有条件insert first
如果第一个 WHEN 子句的值为 true,Oracle 服务器对于给定的行执行相应的 INTO 子句,
并且跳过后面的 WHEN 子句(后面的when语句都不再考虑满足第一个When子句的记录,即使该记录满足when语句中的条件)。
INSERT FIRST WHEN ottl < 100000 THEN INTO small_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 100000 and ottl < 200000 THEN INTO medium_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 290000 THEN INTO special_orders WHEN ottl > 200000 THEN INTO large_orders VALUES(oid, ottl, sid, cid) SELECT o.order_id oid, o.customer_id cid, o.order_total ottl, o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem FROM orders o, customers c WHERE o.customer_id = c.customer_id; SELECT * FROM small_orders; SELECT * FROM medium_orders; SELECT * FROM large_orders; SELECT * FROM special_orders;
---------------------------------
By Dylan.
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。