Oracle SQL and PLSQL exclusive table insertion

For an online e-commerce system, we now need to simply divide the customer into three types based on the consumption amount in the order table and insert them into three tables.

Order table order (order_id number, cust_id number, amount number); small customer table small_cust (cust_id number, tot_amt number); medium customer table med_cust (cust_id number, tot_amt number ); big account table big_cust (cust_id number, tot_amt number); if the total consumption amount is less than 10000, it is classified as a small customer; if the total consumption amount is greater than 10000 and less than 50000, it is classified as a medium customer; if the total consumption amount is greater than 50000, it is classified into major customers. To meet this requirement, if we do not know the use of insert all/FIRST, we may use a PL/SQL statement to traverse the cursor returned by the query order table, then, record the data one by one to determine the table to be inserted by the customer's total consumption. You need to write three INSERT statements separately. This can also be achieved, but INSERT is far from used. FIRST is concise and efficient. The following example is implemented using insert first. Is it clear at a glance?

 
 

  
  
 insert first  
  
  
    when tot_amount < 10000 then  
  
  
    into small_cust_test  
  
  
    when tot_amount >=10000 and tot_amount <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 first when condition is met, the next record is skipped and other WHEN conditions are no longer judged.
  
  
ALL: No matter whether or not the preceding WHEN condition has been met, subsequent conditions will be judged. Multiple tables may be inserted at the same time.
 
 

  Complete sample 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       2000 
  
  
         8       1001       2050 
  
  
  
  
  
8 rows selected 
  
  
  
  
  
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 
  
  
      1004      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 first 
  
  
  2  when tot_amount < 10000 then 
  
  
  3  into small_cust_test 
  
  
  4  when tot_amount >=10000 and tot_amount <50000 then 
  
  
  5  into med_cust_test 
  
  
  6  else 
  
  
  7  into big_cust_test 
  
  
  8  select cust_id,sum(amount) as tot_amount  
  
  
  9  from order_test 
  
  
 10  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 is from "the power comes from the sincere love !" Blog, please be sure to keep this source http://stevex.blog.51cto.com/4300375/1042856