Oracle SQL精妙SQL语句讲解 电脑资料

Oracle SQL精妙SQL语句讲解 电脑资料 -行列转换 行转列 DROP TABLE t_change_lc; CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER); INSERT INTO t_change_lc SELECT 001 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM = 4 UNION SELECT 002 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM = 4; SELECT * FROM t_change_lc; SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0) q1, SUM(decode(a.q, 2, a.bal, 0) q2, SUM(decode(a.q, 3, a.bal, 0) q3, SUM(decode(a.q, 4, a.bal, 0) q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1; -行列转换 列转行 DROP TABLE t_change_cl; CREATE TABLE t_change_cl AS SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0) q1, SUM(decode(a.q, 2, a.bal, 0) q2, SUM(decode(a.q, 3, a.bal, 0) q3, SUM(decode(a.q, 4, a.bal, 0) q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1; SELECT * FROM t_change_cl; SELECT t.card_code, t.rn q, decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal FROM (SELECT a.*, b.rn FROM t_change_cl a, (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM = 4) b) t ORDER BY 1, 2; -行列转换 行转列 合并 DROP TABLE t_change_lc_ma; CREATE TABLE t_change_lc_ma AS SELECT card_code,quarter_|q AS q FROM t_change_lc; SELECT * FROM t_change_lc_ma; SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;), 2) q FROM (SELECT a.card_code, a.q, row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn FROM t_change_lc_ma a) t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.card_code; -行列转换 列转行 分割 DROP TABLE t_change_cl_ma; CREATE TABLE t_change_cl_ma AS SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;), 2) q FROM (SELECT a.card_code, a.q, row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn FROM t_change_lc_ma a) t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.card_code; SELECT * FROM t_change_cl_ma; SELECT t.card_code, substr(t.q, instr(; | t.q, ;, 1, rn), instr(t.q | ;, ;, 1, rn) - instr(; | t.q, ;, 1, rn) q FROM (SELECT a.card_code, a.q, b.rn FROM t_change_cl_ma a, (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM 0) t ORDER BY 1, 2; - 实现一条记录根据条件多表插入 DROP TABLE t_ia_src; CREATE TABLE t_ia_src AS SELECT a|ROWNUM c1, b|ROWNUM c2 FROM dual CONNECT BY ROWNUM=5; DROP TABLE t_ia_dest_1; CREATE TABLE t_ia_dest_1(flag VARCHAR2(10) , c VARCHAR2(10); DROP TABLE t_ia_dest_2; CREATE TABLE t_ia_dest_2(flag VARCHAR2(10) , c VARCHAR2(10); DROP TABLE t_ia_dest_3; CREATE TABLE t_ia_dest_3(flag VARCHAR2(10) , c VARCHAR2(10); SELECT * FROM t_ia_src; SELECT * FROM t_ia_dest_1; SELECT * FROM t_ia_dest_2; SELECT * FROM t_ia_dest_3; INSERT ALL WHEN (c1 IN (a1,a3) THEN INTO t_ia_dest_1(flag,c) VALUES(flag1,c2) WHEN (c1 IN (a2,a4) THEN INTO t_ia_dest_2(flag,c) VALUES(flag2,c2) ELSE INTO t_ia_dest_3(flag,c) VALUES(flag1|flag2,c1|c2) SELECT c1,c2, f1 flag1, f2 flag2 FROM t_ia_src; - 如果存在就更新，不存在就插入用一个语句实现 DROP TABLE t_mg; CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10); SELECT * FROM t_mg; MERGE INTO t_mg a USING (SELECT the code code, the name NAME FROM dual) b ON (a.code = b.code) WHEN MATCHED THEN UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN INSERT (code, NAME) VALUES (b.code, b.NAME); - 抽取/删除重复记录 DROP TABLE t_dup; CREATE TABLE t_dup AS SELECT code_|ROWNUM code, dbms_random.string(z,5) NAME FROM dual CONNECT BY ROWNUM=10; INSERT INTO t_dup SELECT code_|ROWNUM code, dbms_random.string(z,5) NAME FROM dual CONNECT BY ROWNUM=2; SELECT * FROM t_dup; SELECT * FROM t_dup a WHERE a.ROWID (SELECT MIN(b.ROWID) FROM t_dup b WHERE a.code=b.code); SELECT b.code, b.NAME FROM (SELECT a.code, a.NAME, row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rn FROM t_dup a) b WHERE b.rn 1; - IN/EXISTS的不同适用环境 - t_orders.customer_id有索引 SELECT a.* FROM t_employees a WHERE a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12); SELECT a.* FROM t_employees a WHERE EXISTS (SELECT 1 FROM t_orders b WHERE b.customer_id = 12 AND a.employee_id = b.sales_rep_id); - t_employees.department_id有索引 SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND EXISTS (SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id); SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b); - FBI DROP TABLE t_fbi; CREATE TABLE t_fbi AS SELECT ROWNUM rn, dbms_random.STRING(z,10) NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual CONNECT BY ROWNUM = 100 THEN MIT; END IF; END LOOP; MIT; 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 100; - forall 实现批量 FORALL i IN 1 . recs.COUNT INSERT INTO t_loop VALUES recs (i); MIT; EXIT WHEN cur%NOTFOUND; END LOOP; CLOSE cur; END; - 悲观锁定/乐观锁定 DROP TABLE t_lock PURGE; CREATE TABLE t_lock AS SELECT 1 ID FROM dual; SELECT * FROM t_lock; - 常见的实现逻辑，隐含bug DECLARE v_t NUMBER; BEGIN - 这里有并发性的bug SELECT MAX(ID) INTO v_t FROM t_lock; - here for other operation v_t := v_t + 1; INSERT INTO t_lock (ID) VALUES (v_t); MIT; END; - 高并发环境下，安全的实现逻辑 DECLARE v_t NUMBER; BEGIN - 对指定的行取得lock SELECT ID INTO v_t FROM t_lock WHERE ID=1 FOR UPDATE; - 在有lock的情况下继续下面的操作 SELECT MAX(ID) INTO v_t FROM t_lock; - here for other operation v_t := v_t + 1; INSERT INTO t_lock (ID) VALUES (v_t); MIT; -提交并且释放lock END; - 硬解析/软解析 DROP TABLE t_hard PURGE; CREATE TABLE t_hard (ID INT); SELECT * FROM t_hard; DECLARE sql_1 VARCHAR2(200); BEGIN - hard parse - java中的同等语句是 Statement.execute() FOR i IN 1 . 1000 LOOP sql_1 := insert into t_hard(id) values( | i | ); EXECUTE IMMEDIATE sql_1; END LOOP; MIT; - soft parse -java中的同等语句是 PreparedStatement.execute() sql_1 := insert into t_hard(id) values(:id); FOR i IN 1 . 1000 LOOP EXECUTE IMMEDIATE sql_1 USING i; END LOOP; MIT; END; - 正确的分页算法 SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM t_employees ORDER BY first_name) a WHERE ROWNUM 480 ; - 分页算法(why not this one) SELECT a.*, ROWNUM rn FROM (SELECT * FROM t_employees ORDER BY first_name) a WHERE ROWNUM 480; - 分页算法(why not this one) SELECT b.* FROM (SELECT a.*, ROWNUM rn FROM t_employees a WHERE ROWNUM 480; - OLAP - 小计合计 SELECT CASE WHEN a.deptno IS NULL THE