【開發篇sql】 條件和表示式（十） 特定的dml

yellowlee發表於2010-05-18
SQL

10，特定的dml

With as select

先來看個例子，

SQL> with temp as

  2  (select a.deptno,a.job,a.sal,sum(a.sal)over(partition by a.deptno) max_sal from scott.emp a

  3  )

  4  select * from temp

  5  ;

 

    DEPTNO JOB              SAL    MAX_SAL

---------- --------- ---------- ----------

        10 MANAGER         2450       8750

        10 PRESIDENT       5000       8750

        10 CLERK           1300       8750

        20 MANAGER         2000       9900

        20 ANALYST         3000       9900

        20 CLERK           1100       9900

        20 CLERK            800       9900

        20 ANALYST         3000       9900

        30 SALESMAN        1250       9400

        30 SALESMAN        1500       9400

        30 SALESMAN        1600       9400

 

    DEPTNO JOB              SAL    MAX_SAL

---------- --------- ---------- ----------

        30 CLERK            950       9400

        30 MANAGER         2850       9400

        30 SALESMAN        1250       9400

 

14 rows selected.

可以看出with的一般用法，with子句只能用於select，一個比較好的用處是使得程式碼的邏輯比較清楚。

另外with語句使用系統臨時表，一定程度上可以提升效能，因為oracle執行一次WITH子查詢，會將結果放到臨時表中，如果隨後有對子查詢的多次訪問，那麼會從臨時表中直接讀取資料。有關效能的分析具體給出yangtingkun老師的文章：http://yangtingkun.itpub.net/post/468/202694

 

Merge into

SQL> select * from test.cust;

 

   CUST_ID CUST_CITY

---------- --------------------------------------------------

        10 a

        15 b

        20 c

        25 d

        30 e

        35 f

        40 g

 

7 rows selected

SQL> merge into test.cust a

  2  using (select 10 cust_id from dual ) b

  3  on (a.cust_id = b.cust_id)

  4  when matched then

  5       update set a.cust_city = a.cust_city||'1'

  6  when not matched then

  7       insert (cust_id) values (b.cust_id);

 

1 row merged.

 

Insert all:

可以使用insert all select 同時插入不同的表中，也可以使用帶條件的insert all語句，增加條件判斷語句：when ..then .into。

使用insert first 則下一個條件將自動不考慮上一個條件被選中的行。

 

 

帶條件的update:

update t_test_bill a

   set a.date =

   (case when a.b_no = '000025661847852' then date '2010-4-13'

         when a.b_no = '000044155978850' then date '2010-3-25'

         else date '2010-4-2' end)

 where a.b_no in ('000025661847852', '000028634036852', '000028634143852',

        '000028634250852', '000044155978850');

 

更新檢視：

SQL> update (select a.empno, a.comm,b.dname

  2            from scott.emp a, scott.dept b

  3           where a.deptno = b.deptno

  4           and b.loc in ('NEW YORK', 'DALLAS'))

  5     set comm = 0.1;

 

8 rows updated

但要注意，不能同時update多個關聯的基表：

SQL> update (select a.empno, a.comm,b.dname

  2            from scott.emp a, scott.dept b

  3           where a.deptno = b.deptno

  4           and b.loc in ('NEW YORK', 'DALLAS'))

  5     set comm = 0.1,dname = dname||'';

 

update (select a.empno, a.comm,b.dname

          from scott.emp a, scott.dept b

         where a.deptno = b.deptno

         and b.loc in ('NEW YORK', 'DALLAS'))

   set comm = 0.1,dname = dname||''

 

ORA-01776: cannot modify more than one base table through a join view

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-663044/，如需轉載，請註明出處，否則將追究法律責任。

相關文章