Oracle 精妙SQL语句集锦

﹏ヽ暗。殇╰゛Y 2022-08-26 14:10 173阅读 0赞

\--行列转换 行转列  
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;  
\-- 模拟批量提交[http://blog.knowsky.com/][http_blog.knowsky.com]  
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;

[http_blog.knowsky.com]: http://blog.knowsky.com/