oracle sql應用

1.集合操作
學習oracle中集合操作的有關語句，掌握union,union all,minus,interest的使用,能夠描述結合運算，並且能夠將多個查詢組合到一個查詢中去，能夠控制行返回的順序。
包含集合運算的查詢稱為複合查詢。見表格1-1
表1-1
Operator         Returns         content
UNION         由每個查詢選擇的所有不同的行          並集不包含重複值
UNION ALL         由每個查詢選擇的所有的行，包括所有重複的行         完全並集包含重複值
INTERSECT         由兩個查詢選擇的所有不同的行          交集
MINUS         由第一個查詢選擇的所有不同的行          差集

所有的集合運算與等號的優先順序相同，如果SQL語句包含多個集合運算並且沒有圓括號明確地指定另一個順序，Oracle伺服器將以從左到右的順序計算。你應該使用圓括號來明確地指定帶另外的集合運算的INTERSECT (相交) 運算查詢中的賦值順序。
Union all 效率一般比union高。
1.1.union和union all
UNION(聯合)運算
UNION運算返回所有由任一查詢選擇的行。用UNION運算從多表返回所有行，但除去任何重複的行。
原則 :

?被選擇的列數和列的資料型別必須是與所有用在查詢中的SELECT語句一致。列的名字不必相同。
?聯合運算在所有被選擇的列上進行。
?在做重複檢查的時候不忽略空(NULL)值。
?IN運算有比UNION運算高的優先順序。
?在預設情況下，輸出以SELECT子句的第一列的升序排序。

全聯合(UNION ALL)運算
用全聯合運算從多個查詢中返回所有行。
原則

?和聯合不同，重複的行不被過濾，並且預設情況下輸出不排序。
?不能使用DISTINCT關鍵字。
使用：
Select statement union | union all Select statement;

1.2.intersect交集操作
相交運算
用相交運算返回多個查詢中所有的公共行。 無重複行。
原則

?在查詢中被 SELECT 語句選擇的列數和資料型別必須與在查詢中所使用的所有的 SELTCT 語句中的一樣，但列的名字不必一樣。
?相交的表的倒序排序不改變結果。
?相交不忽略空值。
使用：
Select statement intersect all Select statement;

1.3. minus差集操作
相減運算
用相減運算返回由第一個查詢返回的行，那些行不出現在第二個查詢中 (第一個SELECT語句減第二個SELECT語句)。
原則

?在查詢中被SELECT語句選擇的列數和資料型別必須與在查詢中所使用的所有的SELTCT語句中的一樣，但列的名字不必一樣。
?對於MINUS運算，在WHERE子句中所有的列都必須在SELECT子句中。


集合運算的原則
•在兩個SELECT列表中的表示式必須在數目上和資料型別上相匹配
•可以用圓括號改變執行的順序
•ORDER BY子句：–只能出現在語句的最後–從第一個SELECT語句接收列名、別名，或者位置記號

注：•除了UNION ALL，重複行自動被清除
•在結果中的列名是第一個查詢中出現的列名
•除了UNION ALL，預設情況下按升序順序輸出
2.exists和not exists的使用
2.1. exists的使用
Exists用於只能用於子查詢，可以替代in，若匹配到結果，則退出內部查詢，並將條件標誌為true，傳回全部結果資料,in不管匹配到匹配不到都全部匹配完畢，使用exists可以將子查詢結果定為常量，不影響查詢效果，而且效率高。如查詢所有銷售部門員工的姓名，對比如下：
IN is often better if the results of the subquery are very small
When you write a query using the IN clause, you're telling the rule-based optimizer that you want the inner query to drive the outer query.
When you write EXISTS in a where clause, you're telling the optimizer that you want the outer query to be run first, using each value to fetch a value from the inner query.
In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.


In和exists對比：
若子查詢結果集比較小，優先使用in，若外層查詢比子查詢小，優先使用exists。因為若用in，則oracle會優先查詢子查詢，然後匹配外層查詢，若使用exists，則oracle會優先查詢外層表，然後再與內層表匹配。最優化匹配原則，拿最小記錄匹配大記錄。
使用in
select last_name, title
        from s_emp
        where dept_id in
                (select id
                from s_dept
                where name='Sales');        

使用exists
select last_name,title
       from s_emp e
       where  exists
       (select 'x' --把查詢結果定為constant，提高效率
        from s_dept s where s.id=e.dept_id and s.name='Sales');
2.2 not exists的使用
        與exists 含義相反，也在子查詢中使用，用於替代not in。其他一樣。如查詢不在銷售部的員工姓名
select last_name,title
       from s_emp e
       where  not exists
       (select 'x' --把查詢結果定為constant，提高效率
        from s_dept s where s.id=e.dept_id and s.name='Sales');
3.with子句
1.使用with子句可以重複使用相同的子查詢塊,通過select呼叫，一般在子查詢用到多次情況下。
2.with子句的返回結果存到使用者的臨時表空間中
3.with子句可以提高查詢效率
4.有多個with的時候，用逗號隔開
5.最後一個with子句與下面的查詢之間不能有逗號，只通過右括號分割,查詢必須用括號括起來
目的是為了重用查詢。
語法：
With alias_name as (select1),
With alias_name2 as (select2),
…
With alias_namen as (select n)
Select ….
如查詢銷售部門員工的姓名
  --with clause
with a as
     (select id from s_dept where name='Sales' order by id)
  select last_name,title
         from s_emp where dept_id in (select * from a);--使用select查詢別名
4.merge into合併資料
語法：(其中as可以省略)
MERGE INTO table_name AS table_alias
USING (table|view|sub_query) AS alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)—多個列以逗號分割                      //可以不指定列
VALUES (column_values);

作用：將源資料(來源於實際的表,檢視,子查詢)更新或插入到指定的表中(必須實際存在),依賴於on條件,好處是避免了多個insert和update操作。Merge是一個目標性明確的操作符,不允許在一個merge語句中對相同的行insert或update操作。這個語法僅需要一次全表掃描就完成了全部工作，執行效率要高於INSERT＋UPDATE。例子如下：

drop table t;
CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

drop table t1;
CREATE TABLE T1 AS
SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE
FROM DBA_TABLES;


select *  from dba_objects;
select *  from dba_tables;

MERGE INTO T1 USING T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);--insert後面不寫表示插入全部列


MERGE INTO T1 USING T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);--常見錯誤，連線條件不能獲得穩定的行，可以使用下面的用子查詢


MERGE INTO T1
USING (SELECT OWNER, OBJECT_NAME, MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
  WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);


SELECT ID, OWNER, OBJECT_NAME, OBJECT_TYPE FROM T
MINUS
SELECT * FROM T1;

drop table subs;
create table subs(msid number(9),
                    ms_type char(1),
                  areacode number(3)
                    );
                    
     drop table acct;               
                     create table acct(msid number(9),
                     bill_month number(6),
                    areacode   number(3),
                    fee        number(8,2) default 0.00);
                    
insert into subs values(905310001,0,531);
insert into subs values(905320001,1,532);
insert into subs values(905330001,2,533);                 
commit;

merge into acct a --操作的表
      using subs b on (a.msid=b.msid)--使用原始資料來源的表，並且制定條件，條件必須有括號
     when matched then
          update set a.areacode=b.areacode--當匹配的時候，執行update操作，和直接update的語法不一樣，不需要制定表名
     when not matched then--當不匹配的時候，執行insert操作，也不需要制定表名，若指定欄位插入，則在insert後用括號標明，不指定是全部插入
          insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);
         
   
   
另外，MERGE語句的UPDATE不能修改用於連線的列，否則會報錯
select *  from acct;
select * from subs;
--10g新特性，單個操作
merge into acct a
      using subs b on(a.msid=b.msid)
    when not matched then--只有單個not matched的時候，只做插入，不做更新，只有單個matched的時候，只做更新操作
         insert(a.msid,a.bill_month,a.areacode) values(b.msid,'200702',b.areacode);
         
update acct set areacode=800 where msid=905320001;

delete from acct where areacode=533 or areacode=531;

insert into acct values(905320001,'200702',800,0.00);


--刪除重複行
delete from subs b where b.rowidselect max(a.rowid) from subs a where a.msid=b.msid and a.ms_type=b.ms_type and a.areacode=b.areacode);

--10g新特性，merge操作之後，只有匹配的update操作才可以，用delete where子句刪除目標表中滿足條件的行。
  merge into acct a
     using subs b on (a.msid=b.msid)
   when MATCHED then
        update set a.areacode=b.areacode        
        delete where (b.ms_type!=0)
        when NOT MATCHED then
        insert(msid,bill_month,areacode)
        values(b.msid,'200702',b.areacode)
        where b.ms_type=0;
  --10g新特性，滿足條件的插入和更新         
merge into acct a
     using subs b on (a.msid=b.msid)     
   when MATCHED then
        update set a.areacode=b.areacode
        where b.ms_type=0
   when NOT MATCHED then
        insert(msid,bill_month,areacode)
        values(b.msid,'200702',b.areacode)
        where b.ms_type=0;

select *  from subs where ms_type=0;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/219982/viewspace-501438/，如需轉載，請註明出處，否則將追究法律責任。