OracleSQL精妙SQL語句講解

　　行列轉換 行轉列

　　DROP TABLE t_change_lc;

　　CREATE TABLE t_change_lc (card_code VARCHAR() q NUMBER bal NUMBER);

　　INSERT INTO t_change_lc

　　SELECT card_code ROWNUM q trunc(dbms_randomVALUE * ) bal FROM dual CONNECT BY ROWNUM <=

　　UNION

　　SELECT card_code ROWNUM q trunc(dbms_randomVALUE * ) bal FROM dual CONNECT BY ROWNUM <= ;

　　SELECT * FROM t_change_lc;

　　SELECT acard_code

　　SUM(decode(aq abal )) q

　　SUM(decode(aq abal )) q

　　SUM(decode(aq abal )) q

　　SUM(decode(aq abal )) q

　　FROM t_change_lc a

　　GROUP BY acard_code

　　ORDER BY ;

　　行列轉換 列轉行

　　DROP TABLE t_change_cl;

　　CREATE TABLE t_change_cl AS

　　SELECT acard_code

　　SUM(decode(aq abal )) q

　　SUM(decode(aq abal )) q

　　SUM(decode(aq abal )) q

　　SUM(decode(aq abal )) q

　　FROM t_change_lc a

　　GROUP BY acard_code

　　ORDER BY ;

　　SELECT * FROM t_change_cl;

　　SELECT tcard_code

　　trn q

　　decode(trn tq tq tq tq) bal

　　FROM (SELECT a* brn

　　FROM t_change_cl a

　　(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= ) b) t

　　ORDER BY ;

　　行列轉換 行轉列 合並

　　DROP TABLE t_change_lc_comma;

　　CREATE TABLE t_change_lc_comma AS SELECT card_codequarter_||q AS q FROM t_change_lc;

　　SELECT * FROM t_change_lc_comma;

　　SELECT tcard_code substr(MAX(sys_connect_by_path(tq ;)) ) q

　　FROM (SELECT acard_code

　　aq

　　row_number() over(PARTITION BY acard_code ORDER BY aq) rn

　　FROM t_change_lc_comma a) t

　　START WITH trn =

　　CONNECT BY tcard_code = PRIOR tcard_code

　　AND trn = PRIOR trn

　　GROUP BY tcard_code;

　　行列轉換 列轉行 分割

　　DROP TABLE t_change_cl_comma;

　　CREATE TABLE t_change_cl_comma AS

　　SELECT tcard_code substr(MAX(sys_connect_by_path(tq ;)) ) q

　　FROM (SELECT acard_code

　　aq

　　row_number() over(PARTITION BY acard_code ORDER BY aq) rn

　　FROM t_change_lc_comma a) t

　　START WITH trn =

　　CONNECT BY tcard_code = PRIOR tcard_code

　　AND trn = PRIOR trn

　　GROUP BY tcard_code;

　　SELECT * FROM t_change_cl_comma;

　　SELECT tcard_code

　　substr(tq

　　instr(; || tq ; rn)

　　instr(tq || ; ; rn) instr(; || tq ; rn)) q

　　FROM (SELECT acard_code aq brn

　　FROM t_change_cl_comma a

　　(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= ) b

　　WHERE instr(; || aq ; rn) > ) t

　　ORDER BY ;

　　 實現一條記錄根據條件多表插入

　　DROP TABLE t_ia_src;

　　CREATE TABLE t_ia_src AS SELECT a||ROWNUM c b||ROWNUM c FROM dual CONNECT BY ROWNUM<=;

　　DROP TABLE t_ia_dest_;

　　CREATE TABLE t_ia_dest_(flag VARCHAR() c VARCHAR());

　　DROP TABLE t_ia_dest_;

　　CREATE TABLE t_ia_dest_(flag VARCHAR() c VARCHAR());

　　DROP TABLE t_ia_dest_;

　　CREATE TABLE t_ia_dest_(flag VARCHAR() c VARCHAR());

　　SELECT * FROM t_ia_src;

　　SELECT * FROM t_ia_dest_;

　　SELECT * FROM t_ia_dest_;

　　SELECT * FROM t_ia_dest_;

　　INSERT ALL

　　WHEN (c IN (aa)) THEN

　　INTO t_ia_dest_(flagc) VALUES(flagc)

　　WHEN (c IN (aa)) THEN

　　INTO t_ia_dest_(flagc) VALUES(flagc)

　　ELSE

　　INTO t_ia_dest_(flagc) VALUES(flag||flagc||c)

　　SELECT cc f flag f flag FROM t_ia_src;

　　 如果存在就更新不存在就插入用一個語句實現

　　DROP TABLE t_mg;

　　CREATE TABLE t_mg(code VARCHAR() NAME VARCHAR());

　　SELECT * FROM t_mg;

　　MERGE INTO t_mg a

　　USING (SELECT the code code the name NAME FROM dual) b

　　ON (de = de)

　　WHEN MATCHED THEN

　　UPDATE SET aNAME = bNAME

　　WHEN NOT MATCHED THEN

　　INSERT (code NAME) VALUES (de bNAME);

　　 抽取/刪除重復記錄

　　DROP TABLE t_dup;

　　CREATE TABLE t_dup AS SELECT code_||ROWNUM code dbms_randomstring(z) NAME FROM dual CONNECT BY ROWNUM<=;

　　INSERT INTO t_dup SELECT code_||ROWNUM code dbms_randomstring(z) NAME FROM dual CONNECT BY ROWNUM<=;

　　SELECT * FROM t_dup;

　　SELECT * FROM t_dup a WHERE aROWID <> (SELECT MIN(bROWID) FROM t_dup b WHERE de=de);

　　SELECT de bNAME

　　FROM (SELECT de

　　aNAME

　　row_number() over(PARTITION BY de ORDER BY aROWID) rn

　　FROM t_dup a) b

　　WHERE brn > ;

　　 IN/EXISTS的不同適用環境

　　 t_orderscustomer_id有索引

　　SELECT a*

　　FROM t_employees a

　　WHERE aemployee_id IN

　　(SELECT bsales_rep_id FROM t_orders b WHERE bcustomer_id = );

　　SELECT a*

　　FROM t_employees a

　　WHERE EXISTS (SELECT

　　FROM t_orders b

　　WHERE bcustomer_id =

　　AND aemployee_id = bsales_rep_id);

　　 t_employeesdepartment_id有索引

　　SELECT a*

　　FROM t_employees a

　　WHERE adepartment_id =

　　AND EXISTS

　　(SELECT FROM t_orders b WHERE aemployee_id = bsales_rep_id);

　　SELECT a*

　　FROM t_employees a

　　WHERE adepartment_id =

　　AND aemployee_id IN (SELECT bsales_rep_id FROM t_orders b);

　　 FBI

　　DROP TABLE t_fbi;

　　CREATE TABLE t_fbi AS

　　SELECT ROWNUM rn dbms_randomSTRING(z) NAME SYSDATE + dbms_randomVALUE * dt FROM dual

　　CONNECT BY ROWNUM <=;

　　CREATE INDEX idx_nonfbi ON t_fbi(dt);

　　DROP INDEX idx_fbi_;

　　CREATE INDEX idx_fbi_ ON t_fbi(trunc(dt));

　　SELECT * FROM t_fbi WHERE trunc(dt) = to_date(yyyymmdd) ;

　　 不建議使用

　　SELECT * FROM t_fbi WHERE to_char(dt yyyymmdd) = ;

　　 LOOP中的COMMIT/ROLLBACK

　　DROP TABLE t_loop PURGE;

　　create TABLE t_loop AS SELECT * FROM user_objects WHERE =;

　　SELECT * FROM t_loop;

　　 逐行提交

　　DECLARE

　　BEGIN

　　FOR cur IN (SELECT * FROM user_objects) LOOP

　　INSERT INTO t_loop VALUES cur;

　　COMMIT;

　　END LOOP;

　　END;

　　 模擬批量提交

　　DECLARE

　　v_count NUMBER;

　　BEGIN

　　FOR cur IN (SELECT * FROM user_objects) LOOP

　　INSERT INTO t_loop VALUES cur;

　　v_count := v_count + ;

　　IF v_count >= THEN

　　COMMIT;

　　END IF;

　　END LOOP;

　　COMMIT;

　　END;

　　 真正的批量提交

　　DECLARE

　　CURSOR cur IS

　　SELECT * FROM user_objects;

　　TYPE rec IS TABLE OF user_objects%ROWTYPE;

　　recs rec;

　　BEGIN

　　OPEN cur;

　　WHILE (TRUE) LOOP

　　FETCH cur BULK COLLECT

　　INTO recs LIMIT ;

　　 forall 實現批量

　　FORALL i IN recsCOUNT

　　INSERT INTO t_loop VALUES recs (i);

　　COMMIT;

　　EXIT WHEN cur%NOTFOUND;

　　END LOOP;

　　CLOSE cur;

　　END;

　　 悲觀鎖定/樂觀鎖定

　　DROP TABLE t_lock PURGE;

　　CREATE TABLE t_lock AS SELECT ID FROM dual;

　　SELECT * FROM t_lock;

　　 常見的實現邏輯隱含bug

　　DECLARE

　　v_cnt NUMBER;

　　BEGIN

　　 這裡有並發性的bug

　　SELECT MAX(ID) INTO v_cnt FROM t_lock;

　　 here for other operation

　　v_cnt := v_cnt + ;

　　INSERT INTO t_lock (ID) VALUES (v_cnt);

　　COMMIT;

　　END;

　　 高並發環境下安全的實現邏輯

　　DECLARE

　　v_cnt NUMBER;

　　BEGIN

　　 對指定的行取得lock

　　SELECT ID INTO v_cnt FROM t_lock WHERE ID= FOR UPDATE;

　　 在有lock的情況下繼續下面的操作

　　SELECT MAX(ID) INTO v_cnt FROM t_lock;

　　 here for other operation

　　v_cnt := v_cnt + ;

　　INSERT INTO t_lock (ID) VALUES (v_cnt);

　　COMMIT; 提交並且釋放lock

　　END;

　　 硬解析/軟解析

　　DROP TABLE t_hard PURGE;

　　CREATE TABLE t_hard (ID INT);

　　SELECT * FROM t_hard;

　　DECLARE

　　sql_ VARCHAR();

　　BEGIN

　　 hard parse

　　 java中的同等語句是 Statementexecute()

　　FOR i IN LOOP

　　sql_ := insert into t_hard(id) values( || i || );

　　EXECUTE IMMEDIATE sql_;

　　END LOOP;

　　COMMIT;

　　 soft parse

　　java中的同等語句是 PreparedStatementexecute()

　　sql_ := insert into t_hard(id) values(:id);

　　FOR i IN LOOP

　　EXECUTE IMMEDIATE sql_

　　USING i;

　　END LOOP;

　　COMMIT;

　　END;

　　 正確的分頁算法

　　SELECT *

　　FROM (SELECT a* ROWNUM rn

　　FROM (SELECT * FROM t_employees ORDER BY first_name) a

　　WHERE ROWNUM <= )

　　WHERE rn > ;

　　 分頁算法(why not this one)

　　SELECT a* ROWNUM rn

　　FROM (SELECT * FROM t_employees ORDER BY first_name) a

　　WHERE ROWNUM <= AND ROWNUM > ;

　　 分頁算法(why not this one)

　　SELECT b*

　　FROM (SELECT a* ROWNUM rn

　　FROM t_employees a

　　WHERE ROWNUM < =

　　ORDER BY first_name) b

　　WHERE brn > ;

　　 OLAP

　　 小計合計

　　SELECT CASE

　　WHEN adeptno IS NULL THEN

　　合計

　　WHEN adeptno IS NOT NULL AND aempno IS NULL THEN

　　小計

　　ELSE

　　 || adeptno

　　END deptno

　　aempno

　　aename

　　SUM(asal) total_sal

　　FROM scottemp a

　　GROUP BY GROUPING SETS((adeptno)(adeptno aempno aename)());

　　 分組排序

　　SELECT adeptno

　　aempno

　　aename

　　asal

　　 可跳躍的rank

　　rank() over(PARTITION BY adeptno ORDER BY asal DESC) r

　　 密集型rank

　　dense_rank() over(PARTITION BY adeptno ORDER BY asal DESC) r

　　 不分組排序

　　rank() over(ORDER BY sal DESC) r

　　FROM scottemp a

　　ORDER BY adeptnoasal DESC;

　　 當前行數據和前/後n行的數據比較

　　SELECT aempno

　　aename

　　asal

　　 上面一行

　　lag(asal) over(ORDER BY asal DESC) lag_

　　 下面三行

　　lead(asal ) over(ORDER BY asal DESC) lead_

　　FROM scottemp a

　　ORDER BY asal DESC;

From:http://tw.wingwit.com/Article/program/Oracle/201311/16728.html