熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

OracleSQL精妙SQL語句講解

2013-11-13 15:26:59  來源: Oracle 

  行列轉換 行轉列

  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
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.