INSERT高階應用

智慧先行者發表於2014-12-20
INSERT INTO departments 
   VALUES  (departments_seq.nextval, 'Entertainment', 162, 1400); 

INSERT INTO employees 
      (employee_id, last_name, email, hire_date, job_id, salary)
   VALUES 
   (employees_seq.nextval, 'Doe', 'john.doe@example.com', 
       SYSDATE, 'SH_CLERK', 2400) 
   RETURNING salary*12, job_id INTO :bnd1, :bnd2;

ALL

If you specify ALL, the default value, then the database evaluates each WHEN clause regardless of the results of the evaluation of any other WHEN clause. For each WHEN clause whose condition evaluates to true, the database executes the corresponding INTO clause list.

FIRST

If you specify FIRST, then the database evaluates each WHEN clause in the order in which it appears in the statement. For the first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row.

CREATE TABLE small_orders 
   (order_id       NUMBER(12)   NOT NULL,
    customer_id    NUMBER(6)    NOT NULL,
    order_total    NUMBER(8,2),
    sales_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),
    sales_rep_id   NUMBER(6),
    credit_limit   NUMBER(9,2),
    cust_email     VARCHAR2(30)
   );

Puts orders greater than 290,000 into thespecial_orders table.
INSERT ALL
   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 > 200000 THEN
      into large_orders  --不僅存放大於200000的資料，而且存放大於290000的資料
          VALUES(oid, ottl, sid, cid)
   WHEN ottl > 290000 THEN
      INTO special_orders  --僅存放大於290000的資料
   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;

put orders greater than 200,000 into the large_orders table andspecial_orders table  is null:
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 > 200000 THEN
      into large_orders  --不僅存放大於200000的資料，而且存放大於290000的資料
          VALUES(oid, ottl, sid, cid)
   WHEN ottl > 290000 THEN
      INTO special_orders  --沒有大於290000的資料，此表為空
   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;

 

相關文章