查看完整版本: Oracle SQL精妙SQL语句讲解

Oracle SQL精妙SQL语句讲解

[size=3][/size]--行列转换行转列
DROPTABLEt_change_lc;
CREATETABLEt_change_lc(card_codeVARCHAR2(3),qNUMBER,balNUMBER);
INSERTINTOt_change_lc
SELECT'001'card_code,ROWNUMq,trunc(dbms_random.VAL[wiki]UE[/wiki]*100)balFROMdualCONNECTBYROWNUM%26lt;=4
UNION
SELECT'002'card_code,ROWNUMq,trunc(dbms_random.VALUE*100)balFROMdualCONNECTBYROWNUM%26lt;=4;
SELECT*FROMt_change_lc;
SELECTa.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
FROMt_change_lca
GROUPBYa.card_code
ORDERBY1;
--行列转换列转行
DROPTABLEt_change_cl;
CREATETABLEt_change_clAS
SELECTa.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
FROMt_change_lca
GROUPBYa.card_code
ORDERBY1;
SELECT*FROMt_change_cl;
SELECTt.card_code,
t.rnq,
decode(t.rn,1,t.q1,2,t.q2,3,t.q3,4,t.q4)bal
FROM(SELECTa.*,b.rn
FROMt_change_cla,
(SELECTROWNUMrnFROMdualCONNECTBYROWNUM%26lt;=4)b)t
ORDERBY1,2;
--行列转换行转列合并
DROPTABLEt_change_lc_comma;
CREATETABLEt_change_lc_commaASSELECTcard_code,'quarter_'││qASqFROMt_change_lc;
SELECT*FROMt_change_lc_comma;
SELECTt1.card_code,substr(MAX(sys_connect_by_path(t1.q,';')),2)q
FROM(SELECTa.card_code,
a.q,
row_number()over(PARTITIONBYa.card_codeORDERBYa.q)rn
FROMt_change_lc_commaa)t1
STARTWITHt1.rn=1
CONNECTBYt1.card_code=PRIORt1.card_code
ANDt1.rn-1=PRIORt1.rn
GROUPBYt1.card_code;
--行列转换列转行分割
DROPTABLEt_change_cl_comma;
CREATETABLEt_change_cl_commaAS
SELECTt1.card_code,substr(MAX(sys_connect_by_path(t1.q,';')),2)q
FROM(SELECTa.card_code,
a.q,
row_number()over(PARTITIONBYa.card_codeORDERBYa.q)rn
FROMt_change_lc_commaa)t1
STARTWITHt1.rn=1
CONNECTBYt1.card_code=PRIORt1.card_code
ANDt1.rn-1=PRIORt1.rn
GROUPBYt1.card_code;
SELECT*FROMt_change_cl_comma;
SELECTt.card_code,
substr(t.q,
instr(';'││t.q,';',1,rn),
instr(t.q││';',';',1,rn)-instr(';'││t.q,';',1,rn))q
FROM(SELECTa.card_code,a.q,b.rn
FROMt_change_cl_commaa,
(SELECTROWNUMrnFROMdualCONNECTBYROWNUM%26lt;=100)b
WHEREinstr(';'││a.q,';',1,rn)%26gt;0)t
ORDERBY1,2;

--实现一条记录根据条件多表插入
DROPTABLEt_ia_src;
CREATETABLEt_ia_srcASSELECT'a'││ROWNUMc1,'b'││ROWNUMc2FROMdualCONNECTBYROWNUM%26lt;=5;
DROPTABLEt_ia_dest_1;
CREATETABLEt_ia_dest_1(flagVARCHAR2(10),cVARCHAR2(10));
DROPTABLEt_ia_dest_2;
CREATETABLEt_ia_dest_2(flagVARCHAR2(10),cVARCHAR2(10));
DROPTABLEt_ia_dest_3;
CREATETABLEt_ia_dest_3(flagVARCHAR2(10),cVARCHAR2(10));
SELECT*FROMt_ia_src;
SELECT*FROMt_ia_dest_1;
SELECT*FROMt_ia_dest_2;
SELECT*FROMt_ia_dest_3;
INSERTALL
WHEN(c1IN('a1','a3'))THEN
INTOt_ia_dest_1(flag,c)VALUES(flag1,c2)
WHEN(c1IN('a2','a4'))THEN
INTOt_ia_dest_2(flag,c)VALUES(flag2,c2)
ELSE
INTOt_ia_dest_3(flag,c)VALUES(flag1││flag2,c1││c2)
SELECTc1,c2,'f1'flag1,'f2'flag2FROMt_ia_src;
--如果存在就更新，不存在就插入用一个语句实现
DROPTABLEt_mg;
CREATETABLEt_mg(codeVARCHAR2(10),NAMEVARCHAR2(10));
SELECT*FROMt_mg;
MERGEINTOt_mga
USING(SELECT'thecode'code,'thename'NAMEFROMdual)b
ON(a.code=b.code)
WHENMATCHEDTHEN
UPDATESETa.NAME=b.NAME
WHENNOTMATCHEDTHEN
INSERT(code,NAME)VALUES(b.code,b.NAME);
--抽取/删除重复记录
DROPTABLEt_dup;
CREATETABLEt_dupASSELECT'code_'││ROWNUMcode,dbms_random.string('z',5)NAMEFROMdualCONNECTBYROWNUM%26lt;=10;
INSERTINTOt_dupSELECT'code_'││ROWNUMcode,dbms_random.string('z',5)NAMEFROMdualCONNECTBYROWNUM%26lt;=2;
SELECT*FROMt_dup;
SELECT*FROMt_dupaWHEREa.ROWID%26lt;%26gt;(SELECTMIN(b.ROWID)FROMt_dupbWHEREa.code=b.code);
SELECTb.code,b.NAME
FROM(SELECTa.code,
a.NAME,
row_number()over(PARTITIONBYa.codeORDERBYa.ROWID)rn
FROMt_dupa)b
WHEREb.rn%26gt;1;
--IN/EXISTS的不同适用[wiki]环境[/wiki]
--t_orders.customer_id有索引
SELECTa.*
FROMt_employeesa
WHEREa.employee_idIN
(SELECTb.sales_rep_idFROMt_ordersbWHEREb.customer_id=12);
SELECTa.*
FROMt_employeesa
WHEREEXISTS(SELECT1
FROMt_ordersb
WHEREb.customer_id=12
ANDa.employee_id=b.sales_rep_id);
--t_employees.department_id有索引
SELECTa.*
FROMt_employeesa
WHEREa.department_id=10
ANDEXISTS
(SELECT1FROMt_ordersbWHEREa.employee_id=b.sales_rep_id);
SELECTa.*
FROMt_employeesa
WHEREa.department_id=10
ANDa.employee_idIN(SELECTb.sales_rep_idFROMt_ordersb);
--FBI
DROPTABLEt_fbi;
CREATETABLEt_fbiAS
SELECTROWNUMrn,dbms_random.STRING('z',10)NAME,SYSDATE+dbms_random.VALUE*10dtFROMdual
CONNECTBYROWNUM%26lt;=10;
CREATEINDEXidx_nonfbiONt_fbi(dt);
DROPINDEXidx_fbi_1;
CREATEINDEXidx_fbi_1ONt_fbi(trunc(dt));
SELECT*FROMt_fbiWHEREtrunc(dt)=to_date('2006-09-21','yyyy-mm-dd');
--不建议使用
SELECT*FROMt_fbiWHEREto_char(dt,'yyyy-mm-dd')='2006-09-21';
--LOOP中的COMMIT/ROLLBACK
DROPTABLEt_loopPURGE;
createTABLEt_loopASSELECT*FROMuser_[wiki]object[/wiki]sWHERE1=2;
SELECT*FROMt_loop;
--逐行提交
DECLARE
BEGIN
FORcurIN(SELECT*FROMuser_objects)LOOP
INSERTINTOt_loopVALUEScur;
COMMIT;
ENDLOOP;
END;
--模拟批量提交http://blog.knowsky.com/
DECLARE
v_countNUMBER;
BEGIN
FORcurIN(SELECT*FROMuser_objects)LOOP
INSERTINTOt_loopVALUEScur;
v_count:=v_count+1;
IFv_count%26gt;=100THEN
COMMIT;
ENDIF;
ENDLOOP;
COMMIT;
END;
--真正的批量提交
DECLARE
CURSORcurIS
SELECT*FROMuser_objects;
TYPErecISTABLEOFuser_objects%ROWTYPE;
recsrec;
BEGIN
OPENcur;
WHILE(TRUE)LOOP
FETCHcurBULKCOLLECT
INTOrecsLIMIT100;
--forall实现批量
FORALLiIN1..recs.COUNT
INSERTINTOt_loopVALUESrecs(i);
COMMIT;
EXITWHENcur%NOTFOUND;
ENDLOOP;
CLOSEcur;
END;
--悲观锁定/乐观锁定
DROPTABLEt_lockPURGE;
CREATETABLEt_lockASSELECT1IDFROMdual;
SELECT*FROMt_lock;
--常见的实现[wiki]逻辑[/wiki]，隐含bug
DECLARE
v_cntNUMBER;
BEGIN
--这里有并发性的bug
SELECTMAX(ID)INTOv_cntFROMt_lock;
--hereforotheroperation
v_cnt:=v_cnt+1;
INSERTINTOt_lock(ID)VALUES(v_cnt);
COMMIT;
END;
--高并发环境下，[wiki]安全[/wiki]的实现逻辑
DECLARE
v_cntNUMBER;
BEGIN
--对指定的行取得lock
SELECTIDINTOv_cntFROMt_lockWHEREID=1FORUPDATE;
--在有lock的情况下继续下面的操作
SELECTMAX(ID)INTOv_cntFROMt_lock;
--hereforotheroperation
v_cnt:=v_cnt+1;
INSERTINTOt_lock(ID)VALUES(v_cnt);
COMMIT;--提交并且释放lock
END;
--硬解析/软解析
DROPTABLEt_hardPURGE;
CREATETABLEt_hard(IDINT);
SELECT*FROMt_hard;
DECLARE
sql_1VARCHAR2(200);
BEGIN
--hardparse
--java中的同等语句是Statement.execute()
FORiIN1..1000LOOP
sql_1:='insertintot_hard(id)values('││i││')';
EXECUTEIMMEDIATEsql_1;
ENDLOOP;
COMMIT;
--softparse
--java中的同等语句是PreparedStatement.execute()
sql_1:='insertintot_hard(id)values(:id)';
FORiIN1..1000LOOP
EXECUTEIMMEDIATEsql_1
USINGi;
ENDLOOP;
COMMIT;
END;

--正确的分页[wiki]算法[/wiki]
SELECT*
FROM(SELECTa.*,ROWNUMrn
FROM(SELECT*FROMt_employeesORDERBYfirst_name)a
WHEREROWNUM%26lt;=500)
WHERErn%26gt;480;
--分页算法(whynotthisone)
SELECTa.*,ROWNUMrn
FROM(SELECT*FROMt_employeesORDERBYfirst_name)a
WHEREROWNUM%26lt;=500ANDROWNUM%26gt;480;
--分页算法(whynotthisone)
SELECTb.*
FROM(SELECTa.*,ROWNUMrn
FROMt_employeesa
WHEREROWNUM%26lt;=500
ORDERBYfirst_name)b
WHEREb.rn%26gt;480;
--OLAP
--小计合计
SELECTCASE
WHENa.deptnoISNULLTHEN
'合计'
WHENa.deptnoISNOTNULLANDa.empnoISNULLTHEN
'小计'
ELSE
''││a.deptno
ENDdeptno,
a.empno,
a.ename,
SUM(a.sal)total_sal
FROMscott.empa
GROUPBYGROU[wiki]PING[/wiki]SETS((a.deptno),(a.deptno,a.empno,a.ename),());
--分组排序
SELECTa.deptno,
a.empno,
a.ename,
a.sal,
--可跳跃的rank
rank()over(PARTITIONBYa.deptnoORDERBYa.salDESC)r1,
--密集型rank
dense_rank()over(PARTITIONBYa.deptnoORDERBYa.salDESC)r2,
--不分组排序
rank()over(ORDERBYsalDESC)r3
FROMscott.empa
ORDERBYa.deptno,a.salDESC;
--当前行数据和前/后n行的数据比较
SELECTa.empno,
a.ename,
a.sal,
--上面一行
lag(a.sal)over(ORDERBYa.salDESC)lag_1,
--下面三行
lead(a.sal,3)over(ORDERBYa.salDESC)lead_3
FROMscott.empa
ORDERBYa.salDESC;