【网学网提醒】：网学会员鉴于大家对十分关注，论文会员在此为大家搜集整理了“oracle高级sql的参考资料”一文，供大家参考学习
1.集合操作
学习oracle中集合操作的有关语句，掌握union,unionall,minus,interest的使用,能够描述结合运算，并且能够将多个查询组合到一个查询中去，能够控制行返回的顺序。
包含集合运算的查询称为复合查询。见表格1-1
表1-1
OperatorReturnscontent
UNION由每个查询选择的所有不重复的行并集不包含重复值
UNIONALL由每个查询选择的所有的行，包括所有重复的行完全并集包含重复值
INTERSECT由每个查询选择的所有不重复的相交行交集
MINUS在第一个查询中，不在后面查询中，并且结果行不重复差集
所有的集合运算与等号的优先级相同，如果SQL语句包含多个集合运算并且没有圆括号明确地指定另一个顺序，Oracle服务器将以从左到右的顺序计算。你应该使用圆括号来明确地指定带另外的集合运算的INTERSECT(相交)运算查询中的赋值顺序。
Unionall效率一般比union高。
1.1.union和unionall
UNION(联合)运算
UNION运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行，但除去任何重复的行。
原则:
&;#1048707;?被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。
&;#1048707;?联合运算在所有被选择的列上进行。
&;#1048707;?在做重复检查的时候不忽略空(NULL)值。
&;#1048707;?IN运算有比UNION运算高的优先级。
&;#1048707;?在默认情况下，输出以SELECT子句的第一列的升序排序。
全联合(UNIONALL)运算
用全联合运算从多个查询中返回所有行。
原则
&;#1048707;?和联合不同，重复的行不被过滤，并且默认情况下输出不排序。
&;#1048707;?不能使用DISTINCT关键字。
使用：
Selectstatementunion|unionallSelectstatement;
1.2.intersect交集操作
相交运算
用相交运算返回多个查询中所有的公共行。无重复行。
原则
&;#1048707;?在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样，但列的名字不必一样。
&;#1048707;?相交的表的倒序排序不改变结果。
&;#1048707;?相交不忽略空值。
使用：
SelectstatementintersectallSelectstatement;
1.3.minus差集操作
相减运算
用相减运算返回由第一个查询返回的行，那些行不出现在第二个查询中(第一个SELECT语句减第二个SELECT语句)。
原则
&;#1048707;?在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样，但列的名字不必一样。
&;#1048707;?对于MINUS运算，在WHERE子句中所有的列都必须在SELECT子句中。
集合运算的原则
?在两个SELECT列表中的表达式必须在
数目上和数据类型上相匹配
?可以用圆括号改变执行的顺序
?ORDERBY子句：–只能出现在语句的最后–从第一个SELECT语句接收列名、别名，或者位置记号
注：?除了UNIONALL，重复行自动被清除
?在结果中的列名是第一个查询中出现的列名
?除了UNIONALL，默认情况下按升序顺序输出
2.exists和notexists的使用
2.1.exists的使用
Exists用于只能用于子查询，可以替代in，若匹配到结果，则退出内部查询，并将条件标志为true，传回全部结果资料,in不管匹配到匹配不到都全部匹配完毕，使用exists可以将子查询结果定为常量，不影响查询效果，而且效率高。如查询所有销售部门员工的，对比如下：
INisoftenbetteriftheresultsofthesubqueryareverysmall
WhenyouwriteaqueryusingtheINclause,you'retellingtherule-basedoptimizerthatyouwanttheinnerquerytodrivetheouterquery.
WhenyouwriteEXISTSinawhereclause,you'retellingtheoptimizerthatyouwanttheouterquerytoberunfirst,usingeachvaluetofetchavaluefromtheinnerquery.
Inmanycases,EXISTSisbetterbecauseitrequiresyoutospecifyajoincondition,whichcaninvokeanINDEXscan.However,INisoftenbetteriftheresultsofthesubqueryareverysmall.Youusuallywanttorunthequerythatreturnsthesmallersetofresultsfirst.
In和exists对比：
若子查询结果集比较小，优先使用in，若外层查询比子查询小，优先使用exists。因为若用in，则oracle会优先查询子查询，然后匹配外层查询，若使用exists，则oracle会优先查询外层表，然后再与内层表匹配。最优化匹配原则，拿最小记录匹配大记录。
使用in
selectlast_name,title
froms_emp
wheredept_idin
(selectid
froms_dept
wherename='Sales');
使用exists
selectlast_name,title
froms_empe
whereexists
(select'x'--把查询结果定为constant，提高效率
froms_deptswheres.id=e.dept_idands.name='Sales');
2.2notexists的使用
与exists含义相反，也在子查询中使用，用于替代notin。其他一样。如查询不在销售部的员工
selectlast_name,title
froms_empe
wherenotexists
(select'x'--把查询结果定为constant，提高效率
froms_deptswheres.id=e.dept_idands.name='Sales');
3.with子句
9i新增语法
1.使用with子句可以让子查询重用相同的with查询块,通过select调用，一般在with查询用到多次情况下。
2.with子句的返回结果存到用户的临时表空间中，只做一次查询，提高效率。
3.有多个查询的时候，第1个用with，后面的不用with，并且用逗号隔开。
5.最后一个with子句与下面的查询之间不能有逗号，只通过右括
号分割,查询必须用括号括起来
6.如果定义了with子句，而在查询中不使用，那么会报ora-32035错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询）
7.前面的with子句定义的查询在后面的with子句中可以使用。
With子句目的是为了重用查询。
语法：
Withalias_nameas(select1),--as和select中的括号都不能省略
alias_name2as(select2),--后面的没有with，逗号分割
…
alias_namenas(selectn)–与下面的查询之间没有逗号
Select….
如查询销售部门员工的：
--withclause
withaas
(selectidfroms_deptwherename='Sales'orderbyid)
selectlast_name,title
froms_empwheredept_idin(select*froma);--使用select查询别名
使用with子句，可以在复杂的查询中预先定义好一个结果集，然后在查询中反复使用，不使用会报错。而且with子句获得的是一个临时表，如果在查询中使用，必须采用selectfromwith查询名，比如
Withcntas(selectcount(*)fromtable)
Selectcnt+1fromdual;
是错误的。必须是
Withcntas(selectcount(*)shumufromuser_tables)
Selectshumu+1fromcnt;
--直接引用with子查询中的列别名。
一个with查询的实例：
查询出部门的总薪水大于所有部门平均总薪水的部门。部门表s_dept，员工表s_emp。
分析：做这个查询，首先必须计算出所有部门的总薪水，然后计算出总薪水的平均薪水，再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。那么第1步with查询查出所有部门的总薪水，第2步用with从第1步获得的结果表中查询出平均薪水，最后利用这两次的with查询比较总薪水大于平均薪水的结果，如下：
with
--step1:查询出部门名和部门的总薪水
dept_costsas(
selecta.name,sum(b.salary)dept_total
from
s_depta,s_empb
wherea.id=b.dept_id
groupbya.name
),
--step2:利用上一个with查询的结果，计算部门的平均总薪水
avg_costsas(
selectsum(dept_total)/count(*)dept_avg
fromdept_costs
)
--step3:从两个with查询中比较并且输出查询结果
selectname,dept_total
fromdept_costs
where
dept_total>
(
selectdept_avg
from
avg_costs
)
orderbyname;
从上面的查询可以看出，前面的with查询的结果可以被后面的with查询重用，并且对with查询的结果列支持别名的使用，在最终查询中必须要引用所有with查询，否则会报错ora-32035错误。
再如有这样一个需求：一个查询，如果查询的结果行不满足是10的倍数，则补空行，直到是查询出的行数是10的倍数。例如：se
lect*fromtrademark这个查询。
withcntas(select10-mod(count(*),10)shumufromtrademark)–查询比10的倍数差几个空行
selectid,name
fromtrademark
unionall--空行加进去
selectnull,null--补空行
fromdualconnectbyrownum<=(selectshumufromcnt);--10个中connectby可以使用子查询
10g之前的写法
withcntas(select10-mod(count(*),10)shumufromtrademark)–查询比10的倍数差几个空行
selectid,name
fromtrademark
unionall--空行加进去
selectnull,null--补空行
fromall_objectswhererownum<=(selectshumufromcnt);--使用all_objects行比较多
4.mergeinto合并资料
语法：(其中as可以省略)
MERGEINTOtable_nameAStable_alias
USING(table|view|sub_query)ASalias
ON(joincondition)
WHENMATCHEDTHEN
UPDATESET
col1=col_val1,
col2=col2_val
WHENNOTMATCHEDTHEN
INSERT(column_list)—多个列以逗号分割//可以不指定列
VALUES(column_values);
作用：将源数据(来源于实际的表,视图,子查询)更新或插入到指定的表中(必须实际存在),依赖于on条件,好处是避免了多个insert和update操作。Merge是一个目标性明确的操作符,不允许在一个merge语句中对相同的行insert或update操作。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。例子如下：
droptablet;
CREATETABLETASSELECTROWNUMID,A.*fromDBA_OBJECTSA;
droptablet1;
CREATETABLET1AS
SELECTROWNUMID,OWNER,TABLE_NAME,CAST('TABLE'ASVARCHAR2(100))OBJECT_TYPE
fromDBA_TABLES;
select*fromdba_objects;
select*fromdba_tables;
MERGEINTOT1USINGT
ON(T.OWNER=T1.OWNERANDT.OBJECT_NAME=T1.TABLE_NAMEANDT.OBJECT_TYPE=T1.OBJECT_TYPE)
WHENMATCHEDTHENUPDATESETT1.ID=T.ID
WHENNOTMATCHEDTHENINSERTVALUES(T.ID,T.OWNER,T.OBJECT_NAME,T.OBJECT_TYPE);--insert后面不写表示插入全部列
MERGEINTOT1USINGT
ON(T.OWNER=T1.OWNERANDT.OBJECT_NAME=T1.TABLE_NAME)
WHENMATCHEDTHENUPDATESETT1.ID=T.ID
WHENNOTMATCHEDTHENINSERTVALUES(T.ID,T.OWNER,T.OBJECT_NAME,T.OBJECT_TYPE);--常见错误，连接条件不能获得稳定的行，可以使用下面的用子查询
MERGEINTOT1
USING(SELECTOWNER,OBJECT_NAME,MAX(ID)IDfromTGROUPBYOWNER,OBJECT_NAME)T
ON(T.OWNER=T1.OWNERANDT.OBJECT_NAME=T1.TABLE_NAME)
WHENMATCHEDTHENUPDATESETT1.ID=T.ID
WHENNOTMATCHEDTHENINSERTVALUES(T.ID,T.OWNER,T.OBJECT_NAME);
SELECTID,OWNER,OBJECT_NAME,OBJECT_TYPEfromT
MINUS
SELECT*fromT1;
droptablesubs;
createtablesubs(msidnumber(9),
ms_typechar(1),
areacodenumber(3)
);
droptableacct;
createtableacct(msidnumber(9),
bill_monthnumber(6),
ar
eacodenumber(3),
feenumber(8,2)default0.00);
insertintosubsvalues(905310001,0,531);
insertintosubsvalues(905320001,1,532);
insertintosubsvalues(905330001,2,533);
commit;
mergeintoaccta--操作的表
usingsubsbon(a.msid=b.msid)--使用原始数据来源的表，并且制定条件，条件必须有括号
whenmatchedthen
updateseta.areacode=b.areacode--当匹配的时候，执行update操作，和直接update的语法不一样，不需要制定表名
whennotmatchedthen--当不匹配的时候，执行insert操作，也不需要制定表名，若指定字段插入，则在insert后用括号标明，不指定是全部插入
insert(msid,bill_month,areacode)values(b.msid,'200702',b.areacode);
另外，MERGE语句的UPDATE不能修改用于连接的列，否则会报错
select*fromacct;
select*fromsubs;
--10g新特性，单个操作
mergeintoaccta
usingsubsbon(a.msid=b.msid)
whennotmatchedthen--只有单个notmatched的时候，只做插入，不做更新，只有单个matched的时候，只做更新操作
insert(a.msid,a.bill_month,a.areacode)values(b.msid,'200702',b.areacode);
updateacctsetareacode=800wheremsid=905320001;
deletefromacctwhereareacode=533orareacode=531;
insertintoacctvalues(905320001,'200702',800,0.00);
--删除重复行
deletefromsubsbwhereb.rowid<(
selectmax(a.rowid)fromsubsawherea.msid=b.msidanda.ms_type=b.ms_typeanda.areacode=b.areacode);
--10g新特性，merge操作之后，只有匹配的update操作才可以，用deletewhere子句删除目标表中满足条件的行。
mergeintoaccta
usingsubsbon(a.msid=b.msid)
whenMATCHEDthen
updateseta.areacode=b.areacode
deletewhere(b.ms_type!=0)
whenNOTMATCHEDthen
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
whereb.ms_type=0;
--10g新特性，满足条件的插入和更新
mergeintoaccta
usingsubsbon(a.msid=b.msid)
whenMATCHEDthen
updateseta.areacode=b.areacode
whereb.ms_type=0
whenNOTMATCHEDthen
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
whereb.ms_type=0;
select*fromsubswherems_type=0;
