OracleSQL精妙SQL语句讲解.docx
《OracleSQL精妙SQL语句讲解.docx》由会员分享，可在线阅读，更多相关《OracleSQL精妙SQL语句讲解.docx（9页珍藏版）》请在冰豆网上搜索。
OracleSQL精妙SQL语句讲解
Oracle:
SQL精妙SQL语句讲解
一、重复操作查询
--where条件得distinctsystemdicid作为唯一标识
select*
fromdmis_zd_systemdict
WHEREtypeid='06012'
andt.systemdicidin（selectmin（systemdicid）
fromdmis_zd_systemdic
wheretypeid='06012'
groupbyname）
orderbyorderno;
二、检查表是否存在
selectcount（tname）fromtabwheretname=upper（'表名'）;
三、日期函数
--返回当前日期年度的第一天
selecttrunc（sysdate,'year'）fromdual;
--返回当前日期月份的第一天
selecttrunc（sysdate,'month'）fromdual;
--上月最后一天
selectlast_day（add_months（sysdate,-1））fromdual;
--给定日期后最近星期几得日期
selectnext_day（to_date（'2009-12-01','yyyy-mm-dd'）,'星期一'）next_day
fromdual;
四、同一张表中，根据一个字段更新另一个字段
update（selectt.fgenerationtimeasftime,t.fgeneratedateallasstr
fromdmis_fs_approvebookt
wheret.fgenerationtimeisnotnull）
setstr=TO_CHAR（ftime,'yyyy-mm-dd'）
wherestrisnull;
五、重复数据查询
select*FROMEMPE
WHEREE.ROWID>（SELECTMIN（X.ROWID）
FROMEMPX
WHEREX.EMP_NO=E.EMP_NO）;
六、合并不同表的数据（mergeinto）
mergeintostudents
using（selectid,name,telfromtest001）x
on（s.s_id=x.id）
whenmatchedthen
updatesets_name=x.name
whennotmatchedthen
insert（s_id,s_name,s_age）values（x.id,x.name,x.tel）;
commit;
七、查询执行sql（v$sql）
selectt.module,t.first_load_time,t.sql_text
fromv$sqlt
orderbyfirst_load_timedesc;
2、数据库精度修改处理
--Createtable
/*droptabletemp_data;*/
createtabletemp_data
（
FIDVARCHAR2（40）notnull,
USEHOURSNUMBER（10）default0,
FVOLTAGENUMBER（10）default0,
INVOLTAGENUMBER（10）default0
）
;
altertableTEMP_DATA
addconstrainttempfidprimarykey（FID）;
insertintotemp_data
selecta.fid,a.usehours,a.fvoltage,a.involtage
fromdmis_fs_factorymonthdetaila;
updatedmis_fs_factorymonthdetailt
sett.usehours='',t.fvoltage='',t.involtage='';
altertableDMIS_FS_FACTORYMONTHDETAILmodifyUSEHOURSNUMBER（10,1）;
altertableDMIS_FS_FACTORYMONTHDETAILmodifyFVOLTAGENUMBER（10,1）;
altertableDMIS_FS_FACTORYMONTHDETAILmodifyINVOLTAGENUMBER（10,1）;
update（selecta.usehoursastusehours,
b.usehoursasfusehours,
a.fvoltageastfvoltage,
b.fvoltageasffvoltage,
a.involtageastinvoltage,
b.involtageasfinvoltage,
a.fidasffid,
b.fidastfid
fromdmis_fs_factorymonthdetaila,temp_datab
wherea.fid=b.fid）tt
settt.tusehours=tt.fusehours,
tt.tfvoltage=tt.ffvoltage,
tt.tinvoltage=tt.finvoltage
whereffid=tfid;
droptabletemp_data;
commit;
3、恢复drop掉的存储过程
用sys用户登陆，执行如下的查询：
SQL>selecttextfromdba_sourceasoftimestampto_timestamp（'2009-03-0609:
45:
00','YYYY-MM-DDHH24:
MI:
SS'）whereowner='IPRA'andname='P_IPACCHECK_NC'orderbyline;
4、删除某个用户下的对象
--删除某个用户下的对象
setheadingoff;
setfeedbackoff;
spoolc:
\dropobj.sql;
prompt--Dropconstraint
select'altertable'||table_name||'dropconstraint'||constraint_name||';'fromuser_constraintswhereconstraint_type='R';
prompt--Droptables
select'droptable'||table_name||';'fromuser_tables;
prompt--Dropview
select'dropview'||view_name||';'fromuser_views;
prompt--Dropsequence
select
--行列转换行转列
DROPTABLEt_change_lc;
CREATETABLEt_change_lc（card_codeVARCHAR2（3）,qNUMBER,balNUMBER）;
INSERTINTOt_change_lc
SELECT'001'card_code,ROWNUMq,trunc（dbms_random.VALUE*100）balFROMdualCONNECTBYROWNUM<=4
UNION
SELECT'002'card_code,ROWNUMq,trunc（dbms_random.VALUE*100）balFROMdualCONNECTBYROWNUM<=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<=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<=100）b
WHEREinstr（';'||a.q,';',1,rn）>0）t
ORDERBY1,2;
--实现一条记录根据条件多表插入
DROPTABLEt_ia_src;
CREATETABLEt_ia_srcASSELECT'a'||ROWNUMc1,'b'||ROWNUMc2FROMdualCONNECTBYROWNUM<=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<=10;
INSERTINTOt_dupSELECT'code_'||ROWNUMcode,dbms_random.string（'z',5）NAMEFROMdualCONNECTBYROWNUM<=2;
SELECT*FROMt_dup;
SELECT*FROMt_dupaWHEREa.ROWID<>（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>1;
--IN/EXISTS的不同适用环境
--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<=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_objectsWHERE1=2;
SELECT*FROMt_loop;
--逐行提交
DECLARE
BEGIN
FORcurIN（SELECT*FROMuser_objects）LOOP
INSERTINTOt_loopVALUEScur;
COMMIT;
ENDLOOP;
END;
--模拟批量提交
DECLARE
v_countNUMBER;
BEGIN
FORcurIN（SELECT*FROMuser_objects）LOOP
INSERTINTOt_loopVALUEScur;
v_count:
=v_count+1;
IFv_count>=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;
--常见的实现逻辑，隐含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;
--高并发环境下，安全的实现逻辑
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;
-
升级会员 