Oracle SQL精妙SQL语句讲解（材料）由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“oracle常用sql语句”。
好东西，大家赶紧收藏吧~~~ 转自junsansi
--行列转换 行转列
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
SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)bal FROM dual CONNECT BY ROWNUM
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
--行列转换 行转列 合并
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.rn1 = 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
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
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
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
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 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
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;
