Oracle SQL and Pl/sql multiple table insertion tips

If an online e-commerce system, we now need to be based on the order form reflected in the consumption of the customer simply divided into three categories of large and small and inserted into three tables.

Order Form orders (order_id number, cust_id number, amount number);

Small Customer table Small_cust (cust_id number, tot_amt number);

Customer table Med_cust (cust_id number, tot_amt number);

Large Customer table Big_cust (cust_id number, tot_amt number);

If the total consumption amount is less than 10000, it is classified into small customers;

If the total consumption amount is greater than 10000 and less than 50000, it is classified into the customer;

If the total consumption amount is greater than 50000, it is classified into a major customer;

To implement this requirement, if we do not know the use of Insert All/first, we may use a PL/SQL traversal query order table returned by the cursor, and then one by one record to determine the total customer consumption to decide which table to insert, you need to write three INSERT statements, which can also achieve the purpose, But the use of insert-I is far from simple and efficient.

Here is an example of how to use insert one to make it clear.

Insert the  
   Tot_amount < 10000 then into  
   small_cust_test when  
   tot_amount >=10000 and Tot_amount & lt;50000 then into  
   med_cust_test  
   else into
   big_cust_test  
   select Cust_id,sum (amount) as Tot_amount From   
   order_test  
   Group by cust_id;

First: Indicates that when the condition is met, it jumps to the next record and no longer judges the other when condition.  All: Regardless of whether the previous when condition is satisfied, subsequent conditions are judged, and multiple tables may be inserted at once.

Sample complete code:

Sql> CREATE TABLE Order_test (order_id number, cust_id number, amount number); 
      
Table created sql> CREATE TABLE small_cust_test (cust_id number, tot_amt number); 
      
Table created sql> CREATE TABLE med_cust_test (cust_id number, tot_amt number); 
      
Table created sql> CREATE TABLE big_cust_test (cust_id number, tot_amt number); 
      
  Table created sql> select * from Order_test order by order_id;      order_id cust_id AMOUNT------------------------------1 1001 2060 2 1002 
         20060 3 1003 30060 4 1004 50060 5 1004 10060 6 1005 100060 7 1001 8 1001 2050 8 rows selected view this column More Highlights:http://www.bianceng.cnhttp://www.bianceng.cn/database/oracle/sql> Select cust_id, sum (amount) as Tot_amt 
      
 From Order_test GROUP BY cust_id;  cust_id Tot_amt--------------------1003 30060 1001 6110 1002 20060 10 
      
   60120 1005 100060 sql> select * from Small_cust_test; 
      
   cust_id tot_amt--------------------sql> select * from Med_cust_test; 
      
   cust_id tot_amt--------------------sql> select * from Big_cust_test; cust_id Tot_amt--------------------sql> Insert 2 when Tot_amount < 10000 then 3 into smal L_cust_test 4 tot_amount >=10000 and Tot_amount <50000 then 5 into Med_cust_test 6 else 7 into BI 
      
G_cust_test 8 Select Cust_id,sum (amount) as Tot_amount 9 from Order_test Group by cust_id; 
      
   5 Rows inserted sql> select * from Small_cust_test; 
      
   cust_id Tot_amt--------------------1001 6110 sql> SELECT * from Med_cust_test; cust_id Tot_amt--------------------1003 30060 1002 20060 sql> select * from Big_cust_test; cust_id Tot_amt--------------------1004 60120 1005 100060 sql>

This article comes from "the power from absolute sincerity love!" "Blog, please be sure to keep this source http://stevex.blog.51cto.com/4300375/1042856