【网学网提醒】：网学会员为广大网友收集整理了，Oracle中的SQL语言技巧，希望对大家有所帮助！
1、CASE的用法.....................................................................................................................22、存在就更新，不存在就插入.............................................................................................33、行列的转换.........................................................................................................................44、怎么实现一条记录根据条件多表插入.............................................................................75、查询从多少行到多少行的记录.........................................................................................86、利用groupbyrollup和cube进行小计和合计。.............................................................9<1>下面的语句可以进行总计........................................................................................9<2>对第1个字段小计，最后合计..............................................................................9<3>对第1个字段小计，再对第2个字段小计，最后合计.......................................9<5>复合cube表达式,只做总计..................................................................................10<6>下面的语句可以按照rollup不同的字段进行小计...............................................107、删除重复行.......................................................................................................................118、少用“%”.........................................................................................................................129、树形查询...........................................................................................................................12
1、CASE的用法、
在sql语句中CASEtest_valueWHENexpression1THENvalue1[[WHENexpression2THENvalue2][...]]WHEN[ELSEdefault_value]ELSEEND比如1SELECTlast_name,job_id,salary,CASEjob_idWHEN'IT_PROG'THEN1.10*salaryWHEN'ST_CLERK'THEN1.15*salaryWHEN'SA_REP'THEN1.20*salaryELSEsalaryENDasdfFROMemployees比如2SELECTCASEWHENreal_charge>=20000andreal_charge<30000THEN5000WHENreal_charge>=30000andreal_charge<40000THEN9000WHENreal_charge>=40000andreal_charge<50000THEN10000WHENreal_charge>=50000andreal_charge<60000THEN14000WHENreal_charge>=60000andreal_charge<70000THEN18000WHENreal_charge>=70000andreal_charge<80000THEN19000WHENreal_charge>=80000andreal_charge<90000THEN24000WHENreal_charge>=90000andreal_charge<100000THEN27000WHENreal_charge>=100000andreal_charge<110000THEN27000WHENreal_charge>=110000andreal_charge<120000THEN29000WHENreal_charge>=120000THEN36000ELSE0ENDasdfs,acc_id,user_id,real_chargeFROMokcai_jh_charge_200505
2、存在就更新，不存在就插入、存在就更新，
语法：MERGEINTOtableUSIN
Gdata_sourceON(condition)WHENMATCHEDTHENupdate_clauseWHENNOTMATCHEDTHENinsert_clause;例：MERGEINTOcm_user_creditUSING(select*fromdual)ON(user_id=1302514690)WHENMATCHEDTHENupdatesetcredit_value=1000WHENNOTMATCHEDTHENinsert(user_id,acc_id,bill_id,plan_id,region_code,credit_value)values(1302514690,1305032158,'13857141218',10070247,'571',1000);values
3、行列的转换、
<1>、固定列数的行列转换<1>、固定列数的行列转换表tablestudentIDstudentIDsubjectgradestudent1语文80student1数学70student1英语60student2语文90student2数学80student2英语100...转换为语文数学英语student1807060student29080100...语句如下：sum(decodeselectstudentID,sumdecodesumdecode(subject,'语文',grade,0))as语文,sum(decodedecode(subject,'数学',grade,0))as数学,sumdecodesum(decodedecode(subject,'英语',grade,0))as英语sumdecodefromtablegroupbystudentID<2>、<2>、不定列行列转换表tablec1c21我1是1谁2知2道3不转换为1我是谁2知道3不这一类型的转换必须借助于PL/SQL来完成，这里给一个例子CREATEORREPLACEFUNCTIONget_c2(tmp_c1NUMBER)RETURNVARCHAR2ISCol_c2VARCHAR2(4000);
BEGINFORcurIN(SELECTc2FROMtWHEREc1=tmp_c1)LOOPCol_c2:=Col_c2||cur.c2;ENDLOOP;Col_c2:=rtrim(Col_c2,1);RETURNCol_c2;END;SQL>selectdistinctc1,get_c2(c1)cc2fromtable;即可--例子：createtableokcai_1(user_idvarchar2(10),user_numbervarchar2(10),user_numnumber(8))user_iduser_numberuser_num--------------------1123214565178962112222323342445255626672778312341356782方式一：方式一：createorreplacefunctionget_col(p_userIdnumber,p_colnumber)returnvarcharasv_tmpvarchar2(255);beginselectuser_number||chr(9)||user_numintov_tmpfrom(selectuser_number,user_num,rownumrow_idfromokcai_1whereuser_id=p_userId)awhererow_id=p_col;
returnltrim(v_tmp);--returnv_tmp;end;然后selectdistinctuser_id,get_col(user_id,1),get_col(user_id,2),get_col(user_id,3)....fromokcai_1方式二：方式二：createorreplacefunctionget_col(p_userIdnumber,p_colnumber)returnvarcharasv_tmpvarchar2(255);beginselectuser_number||chr(9)||user_numintov_tmpfrom(selectuser_number,user_num,rownumrow_idfromokcai_1whereuser_id=p_userId)awhererow_id=p_col;returnltrim(v_tmp);--returnv_tmp;end;selectdistinctuser_id,get_col_new(user_id)fromokcai_1;
4、怎么实现一条记录根据条件多表插入、
可以通过Insertall语句完成，仅仅是一个语句，如：INSERTALLWHEN(id=1)THENINTOtable_1(id,name)values(‘id’,’name’)valuesWHEN(id=2)THENINTOtable_2(id,name)values(id,name)valuesELSEINTOtable_other(id,name)values(id,name)valuesSELECTid,nameFROMa;如果没有条件的话，则完成每个表的插入，如INSER
TALLINTOtable_1(id,name)values(id,name)valuesINTOtable_2(id2,name2)values(id,name)valuesINTOtable_other(id,name)values(id,name)valuesSELECTid,nameFROMa;
5、查询从多少行到多少行的记录、
可以应用到取排名最前，最后或是正中的记录，select*from(selectrownumrow_id,b.*from(selecta.*fromsys_opera)b)selectwhererow_idbetween15and20;
6、利用groupbyrollup和cube进行小计和合计。、进行小计和合计。
groupbyrollup和cube的操作。
<1>下面的语句可以进行总计下面的语句可以进行总计
count(*)fromaicbs.acc_woff_notifyselectregion_code,countcountrollup(region_code);groupbyrollup(
selectkhbh,sum(qrsl)fromxgs_dh_dhmxbtwherefxsj>=to_date('2007-8-10','yyyy-mm-ddhh:mi:ss')groupbyrollup(khbh);
<2>对第1个字段小计，最后合计个字段小计，
count(*)fromaicbs.acc_woff_notifyselectregion_code,write_status,countcountrollup(region_code,write_status);groupbyrollup---------------------57003570125705--此处小计了570的记录5710105711257112--此处小计了571的记录.....100--此处有总计
selectkhbh,ppbm,sum(qrsl)fromxgs_dh_dhmxbtwherefxsj>=to_date('2007-8-10','yyyy-mm-ddhh:mi:ss')groupbyrollup(khbh,ppbm);
<3>对第1个字段小计，再对第2个字段小计，最后合计个字段小计，个字段小计，
count(*)fromaicbs.acc_woff_notifyselectregion_code,write_status,countcountcube(region_code,write_status);groupbycube100--此处有总计016039--对write_status＝0的小计--对write_status＝1的小
3570570570571571571
153212102
计--对write_status＝3的小计--此处小计了570的记录
0101
--此处小计了571的记录
selectkhbh,ppbm,sum(qrsl)fromxgs_dh_dhmxbtwherefxsj>=to_date('2007-8-10','yyyy-mm-ddhh:mi:ss')groupbycube(khbh,ppbm);
<5>复合cube表达式只做总计表达式,只做总计
count(*)fromaicbs.acc_woff_notifyselectregion_code,countcountcube(region_code);groupbycube
selectkhbh,sum(qrsl)fromxgs_dh_dhmxbtwherefxsj>=to_date('2007-8-10','yyyy-mm-ddhh:mi:ss')groupbycube(khbh);
<6>下面的语句可以按照rollup不同的字段进行小计下面的语句可以按照
count(*)fromaicbs.acc_woff_notifyselectregion_code,write_status,countcountrollup(write_status);groupbyregion_code,rolluprollup
selectkhbh,ppbm,sum(qrsl)fromxgs_dh_dhmxbtwherefxsj>=to_date('2007-8-10','yyyy-mm-ddhh:mi:ss')groupbykhbh,rollup(ppbm);
没有合计
7、删除重复行、
假设表名为Tbl，表中有三列col1，col2，col3，1、通过创建临时表可以把数据先导入到一个临时表中，然后删除原表的数据，再把数据导回原表，SQL语句如下：creattabletbl_tmpasselectdistinct*fromtbl;truncatetabletbl;//清空表记录insertintotblselect*fromtbl_tmp;//将临时表中的数据插回来。
这种方法可以实现需求，但是很明显，对于一个千万级记录的表，这种方法很慢，在生产系统中，这会给系统带来很大的开销，不可行。2、利用rowid在oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同。SQL语句如下：deletefromtblwhererowidin(selecta.rowidfromtbla,tblbwhereselecta.rowid>b.rowidanda.col1=b.col1anda.col2=b.col2)如果已经知道每条记录只有一条重复的，这个sql语句适用。但是如果每条记录的重复记录有N条，这个N是未知的，就要考虑适用下面这种方法了。3、利用max或min函数这里也要使用rowid，与上面不同的是结合max或min函数来实现。SQL语句如下deletefromtblawhererowidnotin(selectmaxselectmax(b.rowid)fromtblbwherea.col1=b.col1anda.col2=b.col2);//这里max使用min也可以或者用下面的语句deletefromtblawhererowid<(selectmaxselectmax(b.rowid)fromtblbwherea.col1=b.col1anda.col2=b.col2);//这里如果把max换成min的话，前面的where子句中需要把"<"改为">"跟上面的方法思路基本是一样的，不过使用了groupby，减少了显性的比较条件，提高效率。SQL语句如下：wheredeletefromtblwhererowidnotin(selectmaxselectmax(rowid)fromtbltgroupbyt.col1,t.col2);
8、少用“%”、少用“”
like'03%';selectxqbh,zybh,zyxmfromsys_zyxxtwherexqbhlikeselectxqbh,zybh,zyxmfromsys_zyxxtwheresubstr(xqbh,1,2)='03';
9、树形查询、
createtabletest_zj(bmnumber(8),bmmcvarchar2(20),sjbmnumber(8));insertintotest_zjvalues(1,'aaa',0);insertintotest_zjvalues(11,'aaa1',1);insertintotest_zjvalues(121,'aaa21',12);insertintotest_zjvalues(111,'aaa11',11);insertintotest_zjvalues(112,'aaa12',11);insertintotest_zjvalues(122,'aaa22',12);insertintotest_zjvalues(123,'aaa23',12);insertintotest_zjvalues(12,'aaa2',1);insertintotest_zjvalues(113,'aaa13',11);select*fromtest_zj;selectbm,bmmc,sjbm,levelfromtest_zjstartwithsjbm=0connectbypriorbm=sjbm;selectbm,bmmc,sjbm,levelfromtest_zjstartwithsjbm=0connectbysjbm=priorbm;
10、对CLOB字段进行全文检索、
SELECT*FROMAWHEREdbms_lob.instr(a.a,'K',1,1)>0;
