SQL開發

1.0 BINARY_FLOAT和BINARY_DOUBLE
    BINARY_FLOAT可以儲存一個單精度的32位浮點數；
    BINARY_DOUBLE可以儲存一個雙精度的64位浮點數；
    f和d分別用於表明一個數字是BINARY_FLOAT和BINARY_DOUBLE型別的。
    eg.35.5f,65.8d
2.0 DATE關鍵字
    DATE關鍵字向資料庫提供一個日期文字字串，此日期字串格式為YYYY-MM-DD。可以將該文字字串轉換為日期型，相當於TO_DATE(文字字串,'YYYY-MM-DD');

3.0 ANY和ALL
    ANY和ALL是比較操作符。
    ANY操作符將一個值與列表中的任何值進行比較，此時必須在ANY前新增一個= 、<>、>、=、<=操作符。
    ALL操作符將一個值與列表中的所有值進行比較，此時必須在ANY前新增一個= 、<>、>、=、<=操作符。  

4.0 ESCAPE
    ESCAPE關鍵字說明其指定的字元是一個轉義字元，其指定字元後的萬用字元在進行匹配時，作為普通字元進行文字匹配，而不當作萬用字元。
    eg. SELECT first_name FROM cus_inf WHERE first_name LIKE '%a\_product%' ESCAPE '\';

5.0 連線條件和連線型別
    連線條件可以分為：等連線和不等連線。
    等連線在連線中使用等於操作符；不等連線在連線中使用除等號之外的其他操作符，如：BETWEEN AND；

    連線型別可分為內連線，外連線和自連線。自連線是一種特殊的內連線，笛卡爾積是一種不帶條件的內連線；

    左外連線和右外連線
    左外連線中，外連線符在等於操作符的右邊，右外連線中，外連線符在等於操作符的左邊。
    在進行外連線時，先將符合條件的記錄列出，然後再列出外連線符相反一邊的連線鍵欄位的值，如果在外連線符一邊的連線表中沒有匹配記錄，則可對應空值；

6.0 ANSI SQL/92連線語法。
    內連線：FROM table1 INNER JOIN table2 ON condition;
    外連線：FROM table1 LEFT|RIGHT|FULL OUTER JOIN table2 ON condition;
    笛卡爾積：FROM table1 CROSS JOIN table2 ;
    用USING簡化連線：如果table1和table2中連線鍵欄位名相同，而且連線是等連線，就可以使用USING來取代ON 。
    USING(連線關鍵字);
    eg.ON table1.column1 = table2.column1 AND table1.column2=table2.column2可以簡寫為：
       USING(column1,column2);
    注意：USING中的連線關鍵字，不得使用別名和表名。

7.0 MEDIAN(X):取中間值

8.0 NOT IN 和 NOT EXISTS
    當一個值列表包含一個空值時，NOT EXISTS返回TRUE，而NOT IN 則返回FALSE。

9.0 集合操作符(文章來源 www.iocblog.net)
    UNION　ALL:返回各個查詢檢索出的所有行，包括重複行；(並)
    UNION：    返回各個查詢檢索出的所有行，不包括重複行；(並)
    INTERSECT：返回各個查詢檢索出的共有行；(交)
    MINUS    ：返回將第二個查詢檢索出的行從第一個查詢檢索出的行中減去之後剩餘的記錄(差)

10.0 TRANSLATE(x,from_string,to_string)函式
     在X中查詢from_string字元，並將其轉換成to_string中對應的字元。

11.0 CONNECT BY 和START WITH
    SELECT [LEVEL],column,expression,.... FROM table [WHERE where_clause] [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
    LEVEL是偽列，代表位於樹的第幾層。對於根節點而言，LEVEL為1；
    start_condition定義了層次化查詢的起點。編寫層次化查詢時必須指定START WITH；
    prior_condition定義了父行和子行之間的關係。編寫層次化查詢時必須定義CONNECT BY PRIOR；   
    START WITH子句可以使用子查詢。
    通過交換你節點與子節點在CONNECT BY PRIOR中的順序可以實現從下向上遍歷；
    通過START WITH 子句，可以從任何節點開始遍歷；
    可以通過WHERE 子句從查詢樹中除去某個特點的節點；
    如果要將準備除去節點的分支也去掉，則在CONNECT BY PRIOR子句中增加相應條件；
   
12.0 GROUP BY擴充套件
     ROLLUP的用法：GROUP BY ROLLUP(column1,column2,....columnN)
     為每一個分組返回一條小計記錄，併為全部分組返回總計。
     先列出按column1,column2,....columnN進行分組的結果，並在每組後列出column1分組的結果，最後列出所有分組的結果；
    
     CUBE的用法：GROUP BY CUBE(column1,column2,....columnN)
     返回所列組合的小計資訊，同時在最後顯示總計資訊。    
     先列出按column1,column2,....columnN進行分組的結果，並在每組後列出column1分組的結果，然後分別列出按column2,....columnN分組的結果，最後列出所有分組的結果；
    
     GROUPING()函式可以接受一列，返回0或1。如果列值為空，則返回1，否則返回0；GROUPING()函式只能在CUBE或ROLLUP中使用。

     GROUPING SETS子句：可以只返回小計記錄。
     GROUP BY GROUPING SETS(column1,column2,...columnN);
     分別列出按column1,column2,......columnN進行分組的小計值。
    
     GROUPING_ID()函式:藉助HAVING子句對記錄進行過濾，將不包含小計或者總計的記錄去掉。
     GROUPING_ID()函式可以接受一列或多列，返回GROUPING位向量的十進位制值。GROUPING位向量的計算方法是將按照順序對每一列呼叫GROUPING()函式的結果組合起來,然後轉換成十進位制數。
     HAVING GROUPING_ID(column1,column2,....columnN)>0就可以將不包含小計或總計的記錄除去。

     GROUP_ID()函式：用於消除GROUP BY子句返回的重複記錄。GROUP_ID函式不接受任何引數。某個某個特定分組重複出現n次，則該函式返回從0至n-1之間的一個整數。因此，可以在 HAVING子句中消除重複記錄，只返回GROUP_ID()的值為0的記錄。
    
    
13.0 分析函式：
     A.評級函式
     RANK():返回資料項在分組中的排名。在排名相等的情況下會在名次中留下空位；
     DENSE_RANK():返回資料項在分組中的排名。在排名相等的情況下不會在名次中留下空位；
     CUME_DIST():返回特定值相對於一組值的位置；
     PERCENT_RANK()：返回某個值對於一組值的百分比排名；
     NTILE()：返回n分片後的值；
     ROW_NUMBER():為每一條分組記錄返回一個數字,從1開始
     用法：分析函式 OVER  (ORDER BY  DESC|ASC)
     OVER子句中，使用DESC時將空值指定為最高排名，使用ASC時將空值指定為最低排名；在OVER子句中可使用NULLS FIRST和NULLS LAST子句來顯示的控制將空值是作為最高排名還是最低排名。
     eg.RANK() OVER (ORDER BY SUM(account_sum) DESC NULLS FIRST);
     使用PARTITION BY子句將分組劃分為子分組：
     RANK() OVER (PARTITION BY add_date ORDER BY SUM(account_sum) DESC)
     與ROLLUP，CUBE，GROUPING SETS的結合使用。
     反百分點函式：
     PERCENTILE_DISC(X):在每一個分組中檢查累積分佈的數值，直到找到大於或等於X的值；
     PERCENTILE_CONT(X):在每一個分組中檢查百分比排名的值，直到找到大於或等於X的值；
     用法：
     eg.SELECT PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY sum(account_sum) DESC) FROM all_sales GROUP BY month;

     B.視窗函式
     1.計算累計和：
     SELECT month,SUM(account_sum),
            SUM(SUM(account_sum)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
     FROM all_sales
     WHERE year=2003
     GROUP BY month;
     查詢2003年各月銷量及各月的累計銷量。
     SUM(SUM(account_sum)):計算累計銷量。
     ORDER BY month：按照月份對查詢的記錄進行排序
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：定義視窗的起點和終點。起點由UNBOUNDED PRECEDING 指定查詢所讀取的所有行，CURRENT ROW表示終點是當前行，這是預設值，可省略.
 
     2.計算移動本均值
     SELECT month,SUM(account_sum),
            AVG(SUM(account_sum)) OVER (ORDER BY month ROWS BETWEEN n PRECEDING AND CURRENT ROW)
     FROM all_sales
     WHERE year=2003
     GROUP BY month;
     查詢2003年每月與其前n個月之間銷量的移動平均值;

     3.計算中心平均值
     SELECT month,SUM(account_sum),
            AVG(SUM(account_sum)) OVER (ORDER BY month ROWS BETWEEN n PRECEDING AND m FOLLOWING)
     FROM all_sales
     WHERE year=2003
     GROUP BY month;
     查詢2003年每月與其前n個月、後m個月之間銷量的移動平均值;
    
     4.FIRST_VALUE()和LAST_VALUE()獲取當前視窗函式中的第一條和最後一條記錄
     eg.
     SELECT month,SUM(account_sum),
            FIRST_VALUE(SUM(account_sum)) OVER (ORDER BY month ROWS BETWEEN n PRECEDING AND m FOLLOWING) as pre_month_account_sum,
            LAST_VALUE(SUM(account_sum)) OVER (ORDER BY month ROWS BETWEEN n PRECEDING AND m FOLLOWING) as next_month_account_sum
     FROM all_sales
     WHERE year=2003
     GROUP BY month;
     查詢2003年每月前一月和後月的銷量。

    C.報表函式
     1.總計報表
     SELECT month,prd_type_id,SUM(SUM(account_sum)) OVER (PARTITION BY month),SUM(SUM(account_sum)) OVER (PARTITION BY prd_type_id)
     FROM all_sales
     WHERE year =2003
     AND month <=3 GROUP BY month,prd_type_id;
     計算2003年前三個月每個月的銷量總和和所有產品型別銷量的總和
     先用SUM(account_sum)求出每個月每類產品的銷量，再用OVER (PARTITION BY month)對所有產品銷量總計按月分組求每個月的總計；
     先用SUM(account_sum)求出每個月每類產品的銷量，再用OVER (PARTITION BY prd_type_id)對所有產品銷量總計按產品型別分組求每種型別的總計；(文章來源 www.iocblog.net)

     2.RATIO_TO_REPORT()函式：用來計算某個值在一組值的總和中所佔的比率。
     SELECT month,prd_type_id,SUM(account_sum),RATIO_TO_REPORT(SUM(account_sum)) OVER (PARTITION BY month)
     WHERE year =2003
     AND month <=3 GROUP BY month,prd_type_id;
     查詢2003年前三個月每種型別產品每個月的銷量及該類產品銷量佔整月銷量的比率。 

     D.LAG()和LEAD()函式
     用來獲得位於距當前記錄指定距離處的那條記錄。
    
     SELECT month,SUM(account_sum),
            LAG(SUM(account_sum),1) OVER (ORDER BY month),
            LEAD(SUM(account_sum),1) OVER (ORDER BY month)
     FROM all_sales
     WHERE year =2003
     GROUP BY month;
     LAG(SUM(account_sum),1) OVER (ORDER BY month):獲得前一月的銷量；按month欄位進行排序，並返回當前記錄之前一條記錄的sum(account_sum)值
     LEAD(SUM(account_sum),1) OVER (ORDER BY month):獲得後一月的銷量；按month欄位進行排序，並返回當前記錄之後一條記錄的sum(account_sum)值    

14.0 MODEL子句的用法：用於行間計算。允許像訪問陣列中的元素那樣訪問記錄中的某個列。
     SELECT prd_type_id,year,month,sales_amount
     FROM all_sales
     WHERE prd_type_id BETWEEN 1 AND 2
     AND   emp_id = 21
     MODEL
     PARTITION BY(prd_type_id)
     DIMENSION BY(month,year)
     MEASURES (account_sum sales_amount)(
       sales_amount[1,2004] = sales_amount[1,2003],
       sales_amount[2,2004] = sales_amount[2,2003] + sales_amount[3,2003],
       sales_amount[3,2004] = ROUND(sales_amount[3,2003] * 1.5,2)
      )
     ORDER BY prd_type_id,year,month;
     根據2003年工號為21的員工完成的產品型別為1，2的銷量預測2004年1，2，3月份的銷量。
     解釋：
     PARTITION BY(prd_type_id) 指定結果是根據prd_type_id進行分割槽；
     DIMENSION BY(month,year)定義陣列的維數是month和year；
     MEASURES (account_sum sales_amount)用來指定包含數量的陣列中的任何一個單元，陣列名為sales_amount,account_sum為指定的列；
       sales_amount[1,2004] = sales_amount[1,2003],
       sales_amount[2,2004] = sales_amount[2,2003] + sales_amount[3,2003],
       sales_amount[3,2004] = ROUND(sales_amount[3,2003] * 1.5,2)
     用於計算陣列單元具體的值。以上是使用位置標記，也可用符號標記訪問資料單元。
       sales_amount[month=1,year=2004] = sales_amount[month=1,year=2003],
       sales_amount[month=2,year=2004] = sales_amount[month=2,year=2003] + sales_amount[month=3,year=2003],
       sales_amount[month=3,year=2004] = ROUND(sales_amount[month=3,year=2003] * 1.5,2)
      用位置標記和用符號標記訪問資料單元的區別：
      sales_amount[null,2003]表示訪問年份為2003年，月份為空的資料單元；
      sales_amount[month=null,year=2003]不能訪問任何有效資料單元。
      可以在陣列的訪問方法中加上BETWEEN AND ，用於返回特定範圍內的資料單元
      eg:sales_amount[1,2004] = ROUND(AVG((sales_amount)[month BETWEEN 1 AND 3,2003],2)
      可以用ANY和IS ANY訪問所有的資料單元,ANY和位置標記合用，IS ANY和符號標記合用;
      eg:sales_amount[1,2004] = ROUND(SUM((sales_amount)[ANY ,year IS ANY],2)
      將2004年1月的銷量設定為所有年份月份銷量之和取整。
     用CURRENTV()獲取某個維度的當前值
      eg:sales_amount[1,2004] = ROUND(sales_amount[CURRENTV ,2003] * 1.5,2)
      表示將2004年1月的銷量設定為2003年同月份銷量的1.5倍取整。
     用FOR迴圈來訪問資料單元
      eg:sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004] = ROUND(sales_amount[CURRENTV ,2003] * 1.5,2)
     表示將2004年1-3月銷量設定為2003年同月份銷量的1.5倍取整
     INCREMENT用來表示迴圈遞增量。
     處理空值和缺失值
     IS PRESENT：當資料單元指定的記錄在MODEL執行之前就存在，則返回TRUE。
     sales_amount[CURRENTV(),2003] IS PRESENT
     PRESENTV(cell,expr1,expr2)：如果cell引用的記錄在MODEL子句執行之前就存在，則返回expr1，否則返回expr2。
     PRESENTNNV(cell,expr1,expr2)：如果cell引用的單元在MODEL執行之前就存在，並且該單元的值不為空，則返回expr1，如果單元不存在或單元值為空值，則返回expr2.
     IGNORE NAV和KEEP NAV
     IGNORE NAV返回值如下：
     空值或缺失數字值時返回0；
     空值或缺失字串值時返回空字串；
     空值或缺失日期值時返回01-JAN-2000；
     KEEP NAV對空值或缺失數字值返回空值。此為預設條件
     eg:MODEL IGNORE NAV

     預設情況下，如果表示式左端的引用單元存在，則更新該單元。不存在，就在陣列中建立一條新的記錄。可用RULES UPDATE改變這種預設行為即在單元不存在的情況下不建立新記錄。
     eg:
     SELECT prd_type_id,year,month,sales_amount
     FROM all_sales
     WHERE prd_type_id BETWEEN 1 AND 2
     AND   emp_id = 21
     MODEL IGNORE NAV
     PARTITION BY(prd_type_id)
     DIMENSION BY(month,year)
     MEASURES (account_sum sales_amount)
     RULES UPDATE
     (
       sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004] = ROUND(sales_amount[CURRENTV ,2003] * 1.5,2)     
     )
     ORDER BY prd_type_id,year,month;

15.0 UPDATE用法補充
     在ORACLE10G中可以使用RETURNING子句返回使用聚合函式計算的結果。
     eg:
        VARIABLE average_product_prices;

        UPDATE products
        SET price = price * 1.25
        RETURNING AVG(price) INTO :average_product_prices;
      1)宣告變數average_product_prices
      2)增加price列25%,並將平均值存在average_product_prices中；(更新前的平均值還是更新後的平均值?)

16.0 MERGE用法
     MERGE INTO table1
     USING table2 ON  ( conditition )
     WHEN MATCHED THEN
       UPDATE
       SET table1.column1 = table2.column1,.....table1.columnN = table2.columnN
     WHEN NOT MATCHED THEN
       INSERT (
               table1.column1,table1.column2,.....table1.columnN
              )
       VALUES (
               table2.column1,table2.column2,.....table2.columnN
               );

17.0 設定事務隔離性級別
     1)幻讀：事務1讀取記錄時事務2增加了記錄並提交，事務1再次讀取時可以看到事務2新增的記錄；
     2)不可重複讀取：事務1讀取記錄時，事務2更新了記錄並提交，事務1再次讀取時可以看到事務2修改後的記錄；
     3)髒讀：事務1更新了記錄，但沒有提交，事務2讀取了更新後的行，然後事務T1回滾，現在T2讀取無效。
     事務隔離級別描述：
     READ UNCOMMITTED：幻讀，不可重複讀和髒讀均允許；
     READ COMMITTED：允許幻讀和不可重複讀，但不允許髒讀；
     REPEATABLE READ：允許幻讀，但不允許不可重複讀和髒讀；
     SERIALIZABLE:幻讀，不可重複讀和髒讀都不允許
     ORACLE預設的是 READ COMMITTED
     設定語法：
      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE|READ COMMITTED|READ UNCOMMITTED|REPEATABLE READ；

18.0 查詢閃回
     查詢閃回的授權
     GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO user1;
     查詢閃回的使用
     將資料庫閃回到一個特定的時間。下例中是10分鐘前
     EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE - 10/1440);
     禁用閃回
     EXECUTE DBMS_FLASHBACK.DISABLE();
     在再次啟用閃回操作之前，必須先將其禁用。
     通過系統變更號(SCN)進行查詢閃回
     取得當前的SCN
     VARIABLE current_scn number;
     EXECUTE :current_scn :=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
     通過SCN閃回
     EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:current_scn);
     禁用閃回
     EXECUTE DBMS_FLASHBACK.DISABLE();