Multitable INSERT 的用法

最新推荐文章于 2020-10-23 10:52:21 发布

原创最新推荐文章于 2020-10-23 10:52:21 发布 · 2.8k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#insert #date #sun #input #email #table

oracle 专栏收录该内容

91 篇文章

订阅专栏

本文深入探讨了SQL中的INSERT语句，从基础用法到高级应用，包括如何进行条件插入、批量插入以及插入特定顺序的数据。通过具体的代码示例，详细介绍了如何使用INSERT INTO、INSERT ALL、UNION和特定条件插入，帮助读者掌握在不同场景下灵活运用INSERT语句的技术。

1、insert 基础用法

INSERT INTO departments 
   VALUES  (departments_seq.nextval, 'Entertainment', 162, 1400);

2、conditional insert all

INSERT ALL
   WHEN order_total < 1000000 THEN
      INTO small_orders
   WHEN order_total > 1000000 AND order_total < 2000000 THEN
      INTO medium_orders
   WHEN order_total > 2000000 THEN
      INTO large_orders
   SELECT order_id, order_total, sales_rep_id, customer_id
      FROM orders;

3、 unconditional insert all

INSERT ALL
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date, sales_sun)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
   SELECT product_id, customer_id, weekly_start_date, sales_sun,
      sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
      FROM sales_input_table;

4、insert first

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;