OracleSQL精妙SQL语句讲解.docx
- 文档编号:4877766
- 上传时间:2022-12-11
- 格式:DOCX
- 页数:9
- 大小:19.83KB
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;
-
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- OracleSQL 精妙 SQL 语句 讲解