行列转换 行转列
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;
