--行列转换 行转列　

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_comma;　

CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;　

SELECT * FROM t_change_lc_comma;

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_comma 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_comma;

CREATE TABLE t_change_cl_comma 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_comma 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_comma;

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_comma a,

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

WHERE instr(';' || a.q, ';', 1, rn) > 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 <=10;　

CREATE INDEX idx_nonfbi ON t_fbi(dt);

DROP INDEX idx_fbi_1;

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

SELECT * FROM t_fbi WHERE trunc(dt) = to_date('2006-09-21','yyyy-mm-dd') ;

-- 不建议使用　

SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd') = '2006-09-21';　　

-- LOOP中的COMMIT/ROLLBACK

DROP TABLE t_loop PURGE;　

create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;　

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 + 1;

IF v_count >= 100 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 100;　

-- forall 实现批量　　

FORALL i IN 1 .. recs.COUNT　

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 1 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 + 1;　

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=1 FOR UPDATE;

-- 在有lock的情况下继续下面的操作

SELECT MAX(ID) INTO v_cnt FROM t_lock;

-- here for other operation　

v_cnt := v_cnt + 1;　　

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_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;　

COMMIT;

-- 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;　

COMMIT;　

END;　

-- 正确的分页算法　

SELECT *

FROM (SELECT a.*, ROWNUM rn

FROM (SELECT * FROM t_employees ORDER BY first_name) a　

WHERE ROWNUM <= 500)

WHERE rn > 480 ;

-- 分页算法(why not this one)　

SELECT a.*, ROWNUM rn　

FROM (SELECT * FROM t_employees ORDER BY first_name) a　

WHERE ROWNUM <= 500 AND ROWNUM > 480;

-- 分页算法(why not this one)　

SELECT b.*

FROM (SELECT a.*, ROWNUM rn　

FROM t_employees a　

WHERE ROWNUM < = 500

ORDER BY first_name) b　

WHERE b.rn > 480;

-- OLAP　　

-- 小计合计　　

SELECT CASE

WHEN a.deptno IS NULL THEN　

'合计'　　

WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN　　

'小计'

ELSE　

'' || a.deptno　　

END deptno,　

a.empno,　

a.ename,　　

SUM(a.sal) total_sal　　

FROM scott.emp a　

GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

-- 分组排序　　

SELECT a.deptno,　

a.empno,　

a.ename,　　

a.sal,　

-- 可跳跃的rank　

rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1,　

-- 密集型rank

dense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2,　　

-- 不分组排序　　

rank() over(ORDER BY sal DESC) r3　

FROM scott.emp a　

ORDER BY a.deptno,a.sal DESC;

-- 当前行数据和前/后n行的数据比较　　

SELECT a.empno,　

a.ename,　

a.sal,　　

-- 上面一行　　

lag(a.sal) over(ORDER BY a.sal DESC) lag_1,　　

-- 下面三行　　

lead(a.sal, 3) over(ORDER BY a.sal DESC) lead_3　　

FROM scott.emp a

ORDER BY a.sal DESC;