oracle 分析函式(轉)

個人覺得這篇文章寫的不錯，講的比較明白，初學很容易理解分析函式的作用和用法。

轉自：http://www.blogjava.net/liuyz2006/articles/370737.html

Oracle開發專題之：分析函式(OVER)

Oracle開發專題之：分析函式2(Rank, Dense_rank, row_number)

Oracle開發專題之：分析函式3(Top/Bottom N、First/Last、NTile)

Oracle開發專題之：視窗函式

Oracle開發專題之：報表函式

Oracle開發專題之：分析函式總結

Oracle開發專題之：26個分析函式

PLSQL開發筆記和小結

分析函式簡述

說明: 1)Oracle開發專題99%收集自: http://www.blogjava.net/pengpenglin/(偶補充了一點點1%);

2) PLSQL開發筆記和小結收集自http://www.blogjava.net/cheneyfree/

3)分析函式簡述收集自http://space.itpub.net/7607759/

昆明小蟲http://ynlxc.cnblogs.com/ 收集,並補充了一點點1%

Oracle開發專題之：分析函式(OVER)

目錄：
===============================================
1.Oracle分析函式簡介
2. Oracle分析函式簡單例項
3.分析函式OVER解析

一、Oracle分析函式簡介：

在日常的生產環境中，我們接觸得比較多的是OLTP系統(即Online Transaction Process)，這些系統的特點是具備實時要求，或者至少說對響應的時間多長有一定的要求；其次這些系統的業務邏輯一般比較複雜，可能需要經過多次的運算。比如我們經常接觸到的電子商城。

在這些系統之外，還有一種稱之為OLAP的系統(即Online Aanalyse Process)，這些系統一般用於系統決策使用。通常和資料倉儲、資料分析、資料探勘等概念聯絡在一起。這些系統的特點是資料量大，對實時響應的要求不高或者根本不關注這方面的要求，以查詢、統計操作為主。

我們來看看下面的幾個典型例子：
①查詢上一年度各個銷售區域排名前10的員工
②按區域查詢上一年度訂單總額佔區域訂單總額20%以上的客戶
③查詢上一年度銷售最差的部門所在的區域
④查詢上一年度銷售最好和最差的產品

我們看看上面的幾個例子就可以感覺到這幾個查詢和我們日常遇到的查詢有些不同，具體有：

①需要對同樣的資料進行不同級別的聚合操作
②需要在表內將多條資料和同一條資料進行多次的比較
③需要在排序完的結果集上進行額外的過濾操作

分析函式語法:
FUNCTION_NAME(,...)
OVER
()

例:
sum(sal) over (partition by deptno order by ename) new_alias
sum就是函式名
(sal)是分析函式的引數,每個函式有0~3個引數,引數可以是表示式,例如:sum(sal+comm)
over 是一個關鍵字,用於標識分析函式,否則查詢分析器不能區別sum()聚集函式和sum()分析函式
partition by deptno 是可選的分割槽子句,如果不存在任何分割槽子句,則全部的結果集可看作一個單一的大區
order by ename 是可選的order by 子句,有些函式需要它,有些則不需要.依靠已排序資料的那些函式,如:用於訪問結果集中前一行和後一行的LAG和LEAD,必須使用,其它函式,如AVG,則不需要.在使用了任何排序的開窗函式時,該子句是強制性的,它指定了在計算分析函式時一組內的資料是如何排序的.

1)FUNCTION子句
ORACLE提供了26個分析函式,按功能分5類
分析函式分類
等級(ranking)函式:用於尋找前N種查詢
開窗(windowing)函式:用於計算不同的累計,如SUM,COUNT,AVG,MIN,MAX等,作用於資料的一個視窗上
例:
sum(t.sal) over (order by t.deptno,t.ename) running_total,
sum(t.sal) over (partition by t.deptno order by t.ename) department_total
製表(reporting)函式:與開窗函式同名,作用於一個分割槽或一組上的所有列
例:
sum(t.sal) over () running_total2,
sum(t.sal) over (partition by t.deptno ) department_total2
製表函式與開窗函式的關鍵不同之處在於OVER語句上缺少一個ORDER BY子句!
LAG,LEAD函式:這類函式允許在結果集中向前或向後檢索值,為了避免資料的自連線,它們是非常用用的.
VAR_POP,VAR_SAMP,STDEV_POPE及線性的衰減函式:計算任何未排序分割槽的統計值

2)PARTITION子句
按照表示式分割槽(就是分組),如果省略了分割槽子句,則全部的結果集被看作是一個單一的組

3)ORDER BY子句
分析函式中ORDER BY的存在將新增一個預設的開窗子句,這意味著計算中所使用的行的集合是當前分割槽中當前行和前面所有行,沒有ORDER BY時,預設的視窗是全部的分割槽 在Order by 子句後可以新增nulls last,如:order by comm desc nulls last   表示排序時忽略comm列為空的行.   

4)WINDOWING子句
用於定義分析函式將在其上操作的行的集合
Windowing子句給出了一個定義變化或固定的資料視窗的方法,分析函式將對這些資料進行操作
預設的視窗是一個固定的視窗,僅僅在一組的第一行開始,一直繼續到當前行,要使用視窗,必須使用ORDER BY子句
根據2個標準可以建立視窗:資料值的範圍(RANGES)或與當前行的行偏移量.

5)Rang視窗
Range 5 preceding:將產生一個滑動視窗,他在組中擁有當前行以前5行的集合
ANGE視窗僅對NUMBERS和DATES起作用,因為不可能從VARCHAR2中增加或減去N個單元
另外的限制是ORDER BY中只能有一列,因而範圍實際上是一維的,不能在N維空間中
例:
avg(t.sal) over(order by t.hiredate asc range 100 preceding) 統計前100天平均工資

6)Row視窗
利用ROW分割槽,就沒有RANGE分割槽那樣的限制了,資料可以是任何型別,且ORDER BY 可以包括很多列

7)Specifying視窗
UNBOUNDED PRECEDING:這個視窗從當前分割槽的每一行開始,並結束於正在處理的當前行
CURRENT ROW:該視窗從當前行開始(並結束)
Numeric Expression PRECEDING:對該視窗從當前行之前的數字表示式(Numeric Expression)的行開始,對RANGE來說,從從行序值小於數字表示式的當前行的值開始.
Numeric Expression FOLLOWING:該視窗在當前行Numeric Expression行之後的行終止(或開始),且從行序值大於當前行Numeric Expression行的範圍開始(或終止)
range between 100 preceding and 100 following:當前行100前,當前後100後

注意:分析函式允許你對一個資料集進排序和篩選,這是SQL從來不能實現的.除了最後的Order by子句之外,分析函式是在查詢中執行的最後的操作集,這樣的話,就不能直接在謂詞中使用分析函式,即不能在上面使用where或having子句!!!

二、Oracle分析函式簡單例項：

下面我們透過一個實際的例子：按區域查詢上一年度訂單總額佔區域訂單總額20%以上的客戶，來看看分析函式的應用。

【1】測試環境：

SQL> desc orders_tmp;
 Name                           Null?    Type
 ----------------------- -------- ----------------
 CUST_NBR                    NOT NULL NUMBER(5)
 REGION_ID                   NOT NULL NUMBER(5)
 SALESPERSON_ID      NOT NULL NUMBER(5)
 YEAR                              NOT NULL NUMBER(4)
 MONTH                         NOT NULL NUMBER(2)
 TOT_ORDERS              NOT NULL NUMBER(7)
 TOT_SALES                 NOT NULL NUMBER(11,2)

【2】測試資料：

SQL> select * from orders_tmp;

  CUST_NBR  REGION_ID SALESPERSON_ID       YEAR      MONTH TOT_ORDERS  TOT_SALES
---------- ---------- -------------- ---------- ---------- ---------- ----------
        11          7             11                       2001          7          2      12204
         4          5              4                         2001         10         2      37802
         7          6              7                         2001          2          3       3750
        10          6              8                        2001          1          2      21691
        10          6              7                        2001          2          3      42624
        15          7             12                       2000          5          6         24
        12          7              9                        2000          6          2      50658
         1          5              2                         2000          3          2      44494
         1          5              1                         2000          9          2      74864
         2          5              4                         2000          3          2      35060
         2          5              4                         2000          4          4       6454
         2          5              1                         2000         10          4      35580
         4          5              4                         2000         12          2      39190

13 rows selected.

【3】測試語句：

SQL> select o.cust_nbr customer,
  2         o.region_id region,
  3         sum(o.tot_sales) cust_sales,
  4         sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
  5    from orders_tmp o
  6   where o.year = 2001
  7   group by o.region_id, o.cust_nbr;

  CUSTOMER     REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
         4              5      37802        37802
         7              6       3750         68065
        10             6      64315        68065
        11             7      12204        12204

三、分析函式OVER解析：

請注意上面的綠色高亮部分，group by的意圖很明顯：將資料按區域ID，客戶進行分組，那麼Over這一部分有什麼用呢？假如我們只需要統計每個區域每個客戶的訂單總額，那麼我們只需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶所在區域的訂單總額，這一點和前面的不同：需要在前面分組的基礎上按區域累加。很顯然group by和sum是無法做到這一點的(因為聚集操作的級別不一樣，前者是對一個客戶，後者是對一批客戶)。

這就是over函式的作用了！它的作用是告訴SQL引擎：按區域對資料進行分割槽，然後累積每個區域每個客戶的訂單總額(sum(sum(o.tot_sales)))。

現在我們已經知道2001年度每個客戶及其對應區域的訂單總額，那麼下面就是篩選那些個人訂單總額佔到區域訂單總額20%以上的大客戶了

SQL> select *
  2    from (select o.cust_nbr customer,
  3                 o.region_id region,
  4                 sum(o.tot_sales) cust_sales,
  5                 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
  6            from orders_tmp o
  7           where o.year = 2001
  8           group by o.region_id, o.cust_nbr) all_sales
  9   where all_sales.cust_sales > all_sales.region_sales * 0.2;

  CUSTOMER     REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
         4          5      37802        37802
        10          6      64315        68065
        11          7      12204        12204

SQL> 

現在我們已經知道這些大客戶是誰了！哦，不過這還不夠，如果我們想要知道每個大客戶所佔的訂單比例呢？看看下面的SQL語句，只需要一個簡單的Round函式就搞定了。

SQL> select all_sales.*,
  2         100 * round(cust_sales / region_sales, 2) || '%' Percent
  3    from (select o.cust_nbr customer,
  4                 o.region_id region,
  5                 sum(o.tot_sales) cust_sales,
  6                 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
  7            from orders_tmp o
  8           where o.year = 2001
  9           group by o.region_id, o.cust_nbr) all_sales
 10   where all_sales.cust_sales > all_sales.region_sales * 0.2;

  CUSTOMER     REGION CUST_SALES REGION_SALES PERCENT
---------- ---------- ---------- ------------ ----------------------------------------
         4            5                  37802        37802    100%
        10           6                  64315        68065      94%
        11           7                  12204        12204    100%

SQL> 

總結：

①Over函式指明在那些欄位上做分析，其內跟Partition by表示對資料進行分組。注意Partition by可以有多個欄位。

②Over函式可以和其它聚集函式、分析函式搭配，起到不同的作用。例如這裡的SUM，還有諸如Rank，Dense_rank等。

Oracle開發專題之：分析函式2(Rank, Dense_rank, row_number)

目錄
===============================================
1.使用rownum為記錄排名
2.使用分析函式來為記錄排名
3.使用分析函式為記錄進行分組排名

一、使用rownum為記錄排名：

在前面一篇《Oracle開發專題之：分析函式》，我們認識了分析函式的基本應用，現在我們再來考慮下面幾個問題：

①對所有客戶按訂單總額進行排名
②按區域和客戶訂單總額進行排名
③找出訂單總額排名前13位的客戶
④找出訂單總額最高、最低的客戶
⑤找出訂單總額排名前25%的客戶

按照前面第一篇文章的思路，我們只能做到對各個分組的資料進行統計，如果需要排名的話那麼只需要簡單地加上rownum不就行了嗎？事實情況是否如此想象般簡單，我們來實踐一下。

【1】測試環境：

SQL> desc user_order;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REGION_ID                                          NUMBER(2)
 CUSTOMER_ID                                  NUMBER(2)
 CUSTOMER_SALES                          NUMBER

【2】測試資料：

SQL> select * from user_order order by customer_sales;

 REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ----------- --------------
         5           1              151162
        10          29             903383
         6           7              971585
        10          28            986964
         9          21           1020541
         9          22           1036146
         8          16           1068467
         6           8            1141638
         5           3            1161286
         5           5            1169926
         8          19           1174421
         7          12           1182275
         7          11           1190421
         6          10           1196748
         6           9            1208959
        10          30          1216858
         5             2                1224992
           9             24              1224992
           9             23              1224992
           8          18           1253840
         7          15           1255591
         7          13           1310434
        10          27          1322747
         8          20           1413722
         6           6            1788836
        10          26          1808949
         5           4            1878275
         7          14           1929774
         8          17           1944281
         9          25           2232703

30 rows selected.

注意這裡有3條記錄的訂單總額是一樣的。假如我們現在需要篩選排名前12位的客戶，如果使用rownum會有什麼樣的後果呢？

SQL> select rownum, t.*
  2    from (select * 
  3            from user_order
  4           order by customer_sales desc) t
  5   where rownum <= 12
  6   order by customer_sales desc;

    ROWNUM  REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ---------- ----------- --------------
         1          9                 25        2232703
         2          8                 17        1944281
         3          7                 14        1929774
         4          5                   4        1878275
         5         10                26        1808949
         6          6                   6        1788836
         7          8                 20        1413722
         8         10                27        1322747
         9          7                13        1310434
        10          7               15        1255591
        11          8               18        1253840
          12             5                     2          1224992

12 rows selected.

很明顯假如只是簡單地按rownum進行排序的話，我們漏掉了另外兩條記錄(參考上面的結果)。

二、使用分析函式來為記錄排名：

針對上面的情況，Oracle從8i開始就提供了3個分析函式：rand，dense_rank，row_number來解決諸如此類的問題，下面我們來看看這3個分析函式的作用以及彼此之間的區別：

Rank，Dense_rank，Row_number函式為每條記錄產生一個從1開始至N的自然數，N的值可能小於等於記錄的總數。這3個函式的唯一區別在於當碰到相同資料時的排名策略。

①ROW_NUMBER：

Row_number函式返回一個唯一的值，當碰到相同資料時，排名按照記錄集中記錄的順序依次遞增。 

②DENSE_RANK：
Dense_rank函式返回一個唯一的值，除非當碰到相同資料時，此時所有相同資料的排名都是一樣的。 

③RANK：
Rank函式返回一個唯一的值，除非遇到相同的資料時，此時所有相同資料的排名是一樣的，同時會在最後一條相同記錄和下一條不同記錄的排名之間空出排名。

這樣的介紹有點難懂，我們還是透過例項來說明吧，下面的例子演示了3個不同函式在遇到相同資料時不同排名策略：

SQL> select region_id, customer_id, sum(customer_sales) total,
  2         rank() over(order by sum(customer_sales) desc) rank,
  3         dense_rank() over(order by sum(customer_sales) desc) dense_rank,
  4         row_number() over(order by sum(customer_sales) desc) row_number
  5    from user_order
  6   group by region_id, customer_id;

 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
            
         8          18                1253840         11         11         11
         5           2                 1224992         12         12         12
         9          23                1224992         12         12         13
         9          24                1224992         12         12         14
        10          30               1216858         15           13            15
    

30 rows selected.

請注意上面的綠色高亮部分，這裡生動的演示了3種不同的排名策略：

①對於第一條相同的記錄，3種函式的排名都是一樣的：12

②當出現第二條相同的記錄時，Rank和Dense_rank依然給出同樣的排名12；而row_number則順延遞增為13，依次類推至第三條相同的記錄

③當排名進行到下一條不同的記錄時，可以看到Rank函式在12和15之間空出了13,14的排名，因為這2個排名實際上已經被第二、三條相同的記錄佔了。而Dense_rank則順序遞增。row_number函式也是順序遞增

比較上面3種不同的策略，我們在選擇的時候就要根據客戶的需求來定奪了：

①假如客戶就只需要指定數目的記錄，那麼採用row_number是最簡單的，但有漏掉的記錄的危險

②假如客戶需要所有達到排名水平的記錄，那麼採用rank或dense_rank是不錯的選擇。至於選擇哪一種則看客戶的需要，選擇dense_rank或得到最大的記錄

三、使用分析函式為記錄進行分組排名：

上面的排名是按訂單總額來進行排列的，現在跟進一步：假如是為各個地區的訂單總額進行排名呢？這意味著又多了一次分組操作：對記錄按地區分組然後進行排名。幸虧Oracle也提供了這樣的支援，我們所要做的僅僅是在over函式中order by的前面增加一個分組子句：partition by region_id。

SQL> select region_id, customer_id, 
               sum(customer_sales) total,
  2         rank() over(partition by region_id
                        order by sum(customer_sales) desc) rank,
  3         dense_rank() over(partition by region_id
                        order by sum(customer_sales) desc) dense_rank,
  4         row_number() over(partition by region_id
                        order by sum(customer_sales) desc) row_number

  5    from user_order
  6   group by region_id, customer_id;

 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
         5           4                1878275          1          1          1
         5           2                1224992          2          2          2
         5           5                1169926          3          3          3
         6           6                1788836          1          1          1
         6           9                1208959          2          2          2
         6          10               1196748          3          3          3       
  

30 rows selected.

現在我們看到的排名將是基於各個地區的，而非所有區域的了！Partition by 子句在排列函式中的作用是將一個結果集劃分成幾個部分，這樣排列函式就能夠應用於這各個子集。

前面我們提到的5個問題已經解決了2個了(第1,2)，剩下的3個問題(Top/Bottom N，First/Last, NTile)會在下一篇講解。

Oracle開發專題之：分析函式3(Top/Bottom N、First/Last、NTile)

目錄
===============================================
1.帶空值的排列
2.Top/Bottom N查詢
3.First/Last排名查詢
4.按層次查詢

一、帶空值的排列：

在前面《Oracle開發專題之：分析函式2(Rank、Dense_rank、row_number)》一文中，我們已經知道了如何為一批記錄進行全排列、分組排列。假如被排列的資料中含有空值呢？

SQL> select region_id, customer_id,
  2         sum(customer_sales) cust_sales,
  3         sum(sum(customer_sales)) over(partition by region_id) ran_total,
  4         rank() over(partition by region_id
  5                  order by sum(customer_sales) desc) rank
  6    from user_order
  7   group by region_id, customer_id;

 REGION_ID CUSTOMER_ID CUST_SALES  RAN_TOTAL       RANK
---------- ----------- ---------- ---------- ---------- 
        10          31                    6238901          1
        10          26    1808949    6238901          2
        10          27    1322747    6238901          3
        10          30    1216858    6238901          4
        10          28     986964    6238901          5
        10          29     903383    6238901          6

我們看到這裡有一條記錄的CUST_TOTAL欄位值為NULL，但居然排在第一名了！顯然這不符合情理。所以我們重新調整完善一下我們的排名策略，看看下面的語句：

SQL> select region_id, customer_id,
  2         sum(customer_sales) cust_total,
  3         sum(sum(customer_sales)) over(partition by region_id) reg_total,
  4         rank() over(partition by region_id 
                        order by sum(customer_sales) desc NULLS LAST) rank
  5        from user_order
  6       group by region_id, customer_id;

 REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK
---------- ----------- ---------- ---------- ----------
        10          26    1808949     6238901           1
        10          27    1322747    6238901           2
        10          30    1216858    6238901           3
        10          28     986964     6238901           4
        10          29     903383     6238901           5
        10          31     6238901                           6

綠色高亮處，NULLS LAST/FIRST告訴Oracle讓空值排名最後後第一。

注意是NULLS，不是NULL。

二、Top/Bottom N查詢：

在日常的工作生產中，我們經常碰到這樣的查詢：找出排名前5位的訂單客戶、找出排名前10位的銷售人員等等。現在這個對我們來說已經是很簡單的問題了。下面我們用一個實際的例子來演示：

【1】找出所有訂單總額排名前3的大客戶：

SQL> select *
SQL>   from (select region_id,
SQL>                customer_id,
SQL>                sum(customer_sales) cust_total,
SQL>                rank() over(order by sum(customer_sales) desc NULLS LAST) rank
SQL>           from user_order
SQL>          group by region_id, customer_id)
SQL>  where rank <= 3;

 REGION_ID CUSTOMER_ID CUST_TOTAL       RANK
---------- ----------- ---------- ----------
         9          25    2232703          1
         8          17    1944281          2
         7          14    1929774          3

SQL> 

【2】找出每個區域訂單總額排名前3的大客戶：

SQL> select *
  2    from (select region_id,
  3                 customer_id,
  4                 sum(customer_sales) cust_total,
  5                 sum(sum(customer_sales)) over(partition by region_id) reg_total,
  6                 rank() over(partition by region_id
                                order by sum(customer_sales) desc NULLS LAST) rank
  7            from user_order
  8           group by region_id, customer_id)
  9   where rank <= 3;

 REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK
---------- ----------- ---------- ---------- ----------
         5           4    1878275    5585641          1
         5           2    1224992    5585641          2
         5           5    1169926    5585641          3
         6           6    1788836    6307766          1
         6           9    1208959    6307766          2
         6          10    1196748    6307766          3
         7          14    1929774    6868495          1
         7          13    1310434    6868495          2
         7          15    1255591    6868495          3
         8          17    1944281    6854731          1
         8          20    1413722    6854731          2
         8          18    1253840    6854731          3
         9          25    2232703    6739374          1
         9          23    1224992    6739374          2
         9          24    1224992    6739374          2
        10          26    1808949    6238901          1
        10          27    1322747    6238901          2
        10          30    1216858    6238901          3

18 rows selected.

三、First/Last排名查詢：

想象一下下面的情形：找出訂單總額最多、最少的客戶。按照前面我們學到的知識，這個至少需要2個查詢。第一個查詢按照訂單總額降序排列以期拿到第一名，第二個查詢按照訂單總額升序排列以期拿到最後一名。是不是很煩？因為Rank函式只告訴我們排名的結果，卻無法自動替我們從中篩選結果。

幸好Oracle為我們在排列函式之外提供了兩個額外的函式：first、last函式，專門用來解決這種問題。還是用例項說話：

SQL> select min(customer_id)
  2         keep (dense_rank first order by sum(customer_sales) desc) first,
  3         min(customer_id)
  4         keep (dense_rank last order by sum(customer_sales) desc) last
  5    from user_order
  6   group by customer_id;

     FIRST       LAST
---------- ----------
        31          1

這裡有幾個看起來比較疑惑的地方：

①為什麼這裡要用min函式
②Keep這個東西是幹什麼的
③fist/last是幹什麼的
④dense_rank和dense_rank()有什麼不同，能換成rank嗎？

首先解答一下第一個問題：min函式的作用是用於當存在多個First/Last情況下保證返回唯一的記錄。假如我們去掉會有什麼樣的後果呢？

SQL> select keep (dense_rank first order by sum(customer_sales) desc) first, 
  2             keep (dense_rank last order by sum(customer_sales) desc) last
  3    from user_order
  4   group by customer_id;
select keep (dense_rank first order by sum(customer_sales) desc) first,
                        *
ERROR at line 1:
ORA-00907: missing right parenthesis

接下來看看第2個問題：keep是幹什麼用的？從上面的結果我們已經知道Oracle對排名的結果只“保留”2條資料，這就是keep的作用。告訴Oracle只保留符合keep條件的記錄。

那麼什麼才是符合條件的記錄呢？這就是第3個問題了。dense_rank是告訴Oracle排列的策略，first/last則告訴最終篩選的條件。

第4個問題：如果我們把dense_rank換成rank呢？

SQL> select min(region_id)
  2          keep(rank first order by sum(customer_sales) desc) first,
  3         min(region_id)
  4          keep(rank last order by sum(customer_sales) desc) last
  5    from user_order
  6   group by region_id;
select min(region_id)
*
ERROR at line 1:
ORA-02000: missing DENSE_RANK

四、按層次查詢：

現在我們已經見識瞭如何透過Oracle的分析函式來獲取Top/Bottom N，第一個，最後一個記錄。有時我們會收到類似下面這樣的需求：找出訂單總額排名前1/5的客戶。

很熟悉是不？我們馬上會想到第二點中提到的方法，可是rank函式只為我們做好了排名，並不知道每個排名在總排名中的相對位置，這時候就引入了另外一個分析函式NTile，下面我們就以上面的需求為例來講解一下：

SQL> select region_id,
  2         customer_id,
  3         ntile(5) over(order by sum(customer_sales) desc) til
  4    from user_order
  5   group by region_id, customer_id;

 REGION_ID CUSTOMER_ID       TILE
---------- ----------- ----------
        10          31          1
         9          25           1
        10          26          1
         6           6            1         
         8          18           2
         5           2            2
         9          23           3
         6           9            3
         7          11           3
         5           3            4
         6           8            4
         8          16           4
         6           7            5
        10          29          5
         5           1            5

Ntil函式為各個記錄在記錄集中的排名計算比例，我們看到所有的記錄被分成5個等級，那麼假如我們只需要前1/5的記錄則只需要擷取TILE的值為1的記錄就可以了。假如我們需要排名前25%的記錄(也就是1/4)那麼我們只需要設定ntile(4)就可以了。

Oracle開發專題之：視窗函式

1.視窗函式簡介
2.視窗函式示例-全統計
3.視窗函式進階-滾動統計(累積/均值)
4.視窗函式進階-根據時間範圍統計
5.視窗函式進階-first_value/last_value
6.視窗函式進階-比較相鄰記錄

一、視窗函式簡介：

到目前為止，我們所學習的分析函式在計算/統計一段時間內的資料時特別有用，但是假如計算/統計需要隨著遍歷記錄集的每一條記錄而進行呢？舉些例子來說：

①列出每月的訂單總額以及全年的訂單總額
②列出每月的訂單總額以及截至到當前月的訂單總額
③列出上個月、當月、下一月的訂單總額以及全年的訂單總額
④列出每天的營業額及一週來的總營業額
⑤列出每天的營業額及一週來每天的平均營業額

仔細回顧一下前面我們介紹到的分析函式，我們會發現這些需求和前面有一些不同：前面我們介紹的分析函式用於計算/統計一個明確的階段/記錄集，而這裡有部分需求例如2，需要隨著遍歷記錄集的每一條記錄的同時進行統計。

也即是說：統計不止發生一次，而是發生多次。統計不至發生在記錄集形成後，而是發生在記錄集形成的過程中。

這就是我們這次要介紹的視窗函式的應用了。它適用於以下幾個場合：

①透過指定一批記錄：例如從當前記錄開始直至某個部分的最後一條記錄結束
②透過指定一個時間間隔：例如在交易日之前的前30天
③透過指定一個範圍值：例如所有佔到當前交易量總額5%的記錄

二、視窗函式示例－全統計：

下面我們以需求：列出每月的訂單總額以及全年的訂單總額為例，來看看視窗函式的應用。

【1】測試環境：

SQL> desc orders;
 名稱                    是否為空? 型別
 ----------------------- -------- ----------------
 MONTH                            NUMBER(2)
 TOT_SALES                    NUMBER

SQL> 

【2】測試資料：

SQL> select * from orders;

     MONTH  TOT_SALES
---------- ----------
         1     610697
         2     428676
         3     637031
         4     541146
         5     592935
         6     501485
         7     606914
         8     460520
         9     392898
        10     510117
        11     532889
        12     492458

已選擇12行。

【3】測試語句：

回憶一下前面《Oracle開發專題之：分析函式(OVER)》一文中，我們使用了sum(sum(tot_sales)) over (partition by region_id) 來統計每個分割槽的訂單總額。現在我們要統計的不單是每個分割槽，而是所有分割槽，partition by region_id在這裡不起作用了。

Oracle為這種情況提供了一個子句：rows between ... preceding and ... following。從字面上猜測它的意思是：在XXX之前和XXX之後的所有記錄，實際情況如何讓我們透過示例來驗證：

SQL> select month,
  2         sum(tot_sales) month_sales,
  3         sum(sum(tot_sales)) over (order by month
  4            rows between unbounded preceding and unbounded following) total_sales
  5    from orders
  6   group by month;

     MONTH MONTH_SALES TOTAL_SALES
---------- ----------- -----------
         1      610697     6307766
         2      428676     6307766
         3      637031     6307766
         4      541146     6307766
         5      592935     6307766
         6      501485     6307766
         7      606914     6307766
         8      460520     6307766
         9      392898     6307766
        10      510117     6307766
        11      532889     6307766
        12      492458     6307766

已選擇12行。

綠色高亮處的程式碼在這裡發揮了關鍵作用，它告訴oracle統計從第一條記錄開始至最後一條記錄的每月銷售額。這個統計在記錄集形成的過程中執行了12次，這時相當費時的！但至少我們解決了問題。

unbounded preceding and unbouned following的意思針對當前所有記錄的前一條、後一條記錄，也就是表中的所有記錄。那麼假如我們直接指定從第一條記錄開始直至末尾呢？看看下面的結果：

SQL> select month,
  2         sum(tot_sales) month_sales,
  3         sum(sum(tot_sales)) over (order by month
  4            rows between 1 preceding and unbounded following) all_sales
  5    from orders
  6   group by month;

     MONTH MONTH_SALES  ALL_SALES
---------- ----------- ----------
         1      610697    6307766
         2      428676    6307766
         3      637031    5697069
         4      541146    5268393
         5      592935    4631362
         6      501485    4090216
         7      606914    3497281
         8      460520    2995796
         9      392898    2388882
        10      510117    1928362
        11      532889    1535464
        12      492458    1025347

已選擇12行。

很明顯這個語句錯了。實際1在這裡不是從第1條記錄開始的意思，而是指當前記錄的前一條記錄。preceding前面的修飾符是告訴視窗函式執行時參考的記錄數，如同unbounded就是告訴oracle不管當前記錄是第幾條，只要前面有多少條記錄，都列入統計的範圍。

三、視窗函式進階－滾動統計(累積/均值)：

考慮前面提到的第2個需求：列出每月的訂單總額以及截至到當前月的訂單總額。也就是說2月份的記錄要顯示當月的訂單總額和1,2月份訂單總額的和。3月份要顯示當月的訂單總額和1,2,3月份訂單總額的和，依此類推。

很明顯這個需求需要在統計第N月的訂單總額時，還要再統計這N個月來的訂單總額之和。想想上面的語句，假如我們能夠把and unbounded following換成代表當前月份的邏輯多好啊！很幸運的是Oracle考慮到了我們這個需求，為此我們只需要將語句稍微改成: curreent row就可以了。

SQL> select month,
  2         sum(tot_sales) month_sales,
  3         sum(sum(tot_sales)) over(order by month
  4           rows between unbounded preceding and current row) current_total_sales
  5    from orders
  6   group by month;

     MONTH MONTH_SALES CURRENT_TOTAL_SALES
---------- ----------- -------------------
         1      610697              610697
         2      428676             1039373
         3      637031             1676404
         4      541146             2217550
         5      592935             2810485
         6      501485             3311970
         7      606914             3918884
         8      460520             4379404
         9      392898             4772302
        10      510117             5282419
        11      532889             5815308
        12      492458             6307766

已選擇12行。

現在我們能得到滾動的銷售總額了！下面這個統計結果看起來更加完美，它展現了所有我們需要的資料：

SQL> select month,
  2         sum(tot_sales) month_sales,
  3         sum(sum(tot_sales)) over(order by month
  4         rows between unbounded preceding and current row) current_total_sales,
  5         sum(sum(tot_sales)) over(order by month
  6         rows between unbounded preceding and unbounded following) total_sales
  7    from orders
  8   group by month;

     MONTH MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES
---------- ----------- ------------------- -----------
         1      610697              610697     6307766
         2      428676             1039373     6307766
         3      637031             1676404     6307766
         4      541146             2217550     6307766
         5      592935             2810485     6307766
         6      501485             3311970     6307766
         7      606914             3918884     6307766
         8      460520             4379404     6307766
         9      392898             4772302     6307766
        10      510117             5282419     6307766
        11      532889             5815308     6307766
        12      492458             6307766     6307766

已選擇12行。

在一些銷售報表中我們會時常看到求平均值的需求，有時可能是針對全年的資料求平均值，有時會是針對截至到當前的所有資料求平均值。很簡單，只需要將：
sum(sum(tot_sales))換成avg(sum(tot_sales))即可。

四、視窗函式進階－根據時間範圍統計：

前面我們說過，視窗函式不單適用於指定記錄集進行統計，而且也能適用於指定範圍進行統計的情況，例如下面這個SQL語句就統計了當天銷售額和五天內的評價銷售額：

 select trunc(order_dt) day,
             sum(sale_price) daily_sales,
             avg(sum(sale_price)) over (order by trunc(order_dt)
                      range between interval '2' day preceding 
                                     and interval '2' day following) five_day_avg
   from cust_order
 where sale_price is not null 
     and order_dt between to_date('01-jul-2001','dd-mon-yyyy')
     and to_date('31-jul-2001','dd-mon-yyyy')

為了對指定範圍進行統計，Oracle使用關鍵字range、interval來指定一個範圍。上面的例子告訴Oracle查詢當前日期的前2天，後2天範圍內的記錄，並統計其銷售平均值。

五、視窗函式進階－first_value/last_value：

Oracle提供了2個額外的函式：first_value、last_value，用於在視窗記錄集中查詢第一條記錄和最後一條記錄。假設我們的報表需要顯示當前月、上一個月、後一個月的銷售情況，以及每3個月的銷售平均值，這兩個函式就可以派上用場了。

select month,
             first_value(sum(tot_sales)) over (order by month 
                                    rows between 1 preceding and 1 following) prev_month,
 
             sum(tot_sales) monthly_sales,
 
             last_value(sum(tot_sales)) over (order by month 
                                  rows between 1 preceding and 1 following) next_month,
 
             avg(sum(tot_sales)) over (order by month 
                                 rows between 1 preceding and 1 following) rolling_avg
    from orders
 where year = 2001 
      and region_id = 6
  group by month
 order by month;

首先我們來看:rows between 1 preceding and 1 following告訴Oracle在當前記錄的前一條、後一條範圍內查詢並統計，而first_value和last_value在這3條記錄中至分別找出第一條、第三條記錄，這樣我們就輕鬆地得到相鄰三個月的銷售記錄及平均值了！

六、視窗函式進階－比較相鄰記錄：

透過第五部分的學習，我們知道了如何利用視窗函式來顯示相鄰的記錄，現在假如我們想每次顯示當月的銷售額和上個月的銷售額，應該怎麼做呢？

從第五部分的介紹我們可以知道，利用first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following))就可以做到了，其實Oracle還有一個更簡單的方式讓我們來比較2條記錄，它就是lag函式。

leg函式類似於preceding和following子句，它能夠透過和當前記錄的相對位置而被應用，在比較同一個相鄰的記錄集內兩條相鄰記錄的時候特別有用。

select month,            
          sum(tot_sales) monthly_sales,
          lag(sum(tot_sales), 1) over (order by month) prev_month_sales
   from orders
 where year = 2001
      and region_id = 6
  group by month
 order by month;

lag(sum(tot_sales),1)中的1表示以1月為基準。

Oracle開發專題之：報表函式

1.報表函式簡介
2.RATIO_TO_REPORT函式

一、報表函式簡介：

回顧一下前面《Oracle開發專題之：視窗函式》中關於全統計一節，我們使用了Oracle提供的：

sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following)

來統計全年的訂單總額，這個函式會在記錄集形成的過程中，每檢索一條記錄就執行一次，它總共執行了12次。這是非常費時的。實際上我們還有更簡便的方法：

SQL> select month,
  2         sum(tot_sales) month_sales,
  3         sum(sum(tot_sales)) over(order by month
  4         rows between unbounded preceding and unbounded following) win_sales,
  5         sum(sum(tot_sales)) over() rpt_sales
  6    from orders
  7   group by month;

     MONTH MONTH_SALES WINDOW_SALES REPORT_SALES
---------- ----------- ------------ ------------
         1      610697      6307766      6307766
         2      428676      6307766      6307766
         3      637031      6307766      6307766
         4      541146      6307766      6307766
         5      592935      6307766      6307766
         6      501485      6307766      6307766
         7      606914      6307766      6307766
         8      460520      6307766      6307766
         9      392898      6307766      6307766
        10      510117      6307766      6307766
        11      532889      6307766      6307766
        12      492458      6307766      6307766

已選擇12行。

over函式的空括號表示該記錄集的所有記錄都應該被列入統計的範圍，如果使用了partition by則先分割槽，再依次統計各個分割槽。

二、RATIO_TO_REPORT函式：

報表函式特(視窗函式)特別適合於報表中需要同時顯示詳細資料和統計資料的情況。例如在銷售報告中經常會出現這樣的需求：列出上一年度每個月的銷售總額、年底銷售額以及每個月的銷售額佔全年總銷售額的比例：

方法①:

select all_sales.*,
           100 * round(cust_sales / region_sales, 2) || '%' Percent
 from (select o.cust_nbr customer,
                        o.region_id region,
                       sum(o.tot_sales) cust_sales,
                       sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
               from orders_tmp o
            where o.year = 2001
             group by o.region_id, o.cust_nbr) all_sales
 where all_sales.cust_sales > all_sales.region_sales * 0.2;

這是一種笨方法也是最易懂的方法。

方法②：

select region_id, salesperson_id, 
           sum(tot_sales) sp_sales,
           round(sum(tot_sales) / sum(sum(tot_sales)) 
                      over (partition by region_id), 2) percent_of_region
  from orders
where year = 2001
 group by region_id, salesperson_id
 order by region_id, salesperson_id;

方法③

select region_id, salesperson_id, 
            sum(tot_sales) sp_sales,
            round(ratio_to_report(sum(tot_sales)) 
                          over (partition by region_id), 2) sp_ratio
   from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;

Oracle提供的Ratio_to_report函式允許我們計算每條記錄在其對應記錄集或其子集中所佔的比例。

Oracle開發專題之：分析函式總結

這一篇是對前面所有關於分析函式的文章的總結：

一、統計方面：

Sum( ) Over ([Partition by  ] [Order by  ])

Sum( ) Over ([Partition by  ] [Order by  ]  
     Rows Between   Preceding And   Following)
       
Sum( ) Over ([Partition by  ] [Order by  ]
     Rows Between   Preceding And Current Row)

Sum( ) Over ([Partition by  ] [Order by  ]
     Range Between Interval ' ' 'Day' Preceding
                    And Interval ' ' 'Day' Following )

具體請參考《Oracle開發專題之：分析函式(OVER)》和《Oracle開發專題之：視窗函式》


二、排列方面：

Rank() Over ([Partition by  ] [Order by  ] [Nulls First/Last])

  Dense_rank() Over ([Patition by  ] [Order by  ] [Nulls First/Last])
   
Row_number() Over ([Partitionby  ] [Order by  ] [Nulls First/Last])
   
Ntile( ) Over ([Partition by  ] [Order by  ])

具體請參考《Oracle開發專題之：分析函式2》

三、最大值/最小值查詢方面：

Min( )/Max( ) Keep (Dense_rank First/Last [Partition by  ] [Order by  ])

具體請參考《Oracle開發專題之：分析函式3》

四、首記錄/末記錄查詢方面：

First_value / Last_value(Sum( ) Over ([Patition by  ] [Order by  ]
       Rows Between   Preceding And   Following  ))

具體請參考《Oracle開發專題之：視窗函式》

五、相鄰記錄之間比較方面：

Lag(Sum( ), 1) Over([Patition by  ] [Order by  ])

具體請參考《Oracle開發專題之：報表函式》

Oracle開發專題之：26個分析函式

參考資料：《Mastering Oracle SQL》(By ,  O'Reilly June 2004  0-596-00632-2) 

PLSQL開發筆記和小結

*****************************************
  PLSQL基本結構
*****************************************
基本資料型別變數

    1. 基本資料型別
    Number 數字型 
    Int 整數型 
    Pls_integer 整數型，產生溢位時出現錯誤 
    Binary_integer 整數型，表示帶符號的整數 
    Char 定長字元型，最大255個字元 
    Varchar2 變長字元型，最大2000個字元 
    Long 變長字元型，最長2GB 
    Date 日期型 
    Boolean 布林型（TRUE、FALSE、NULL三者取一） 
    在PL/SQL中使用的資料型別和Oracle資料庫中使用的資料型別，有的含義是完全一致的，有的是有不同的含義的。

    2. 基本資料型別變數的定義方法
    變數名 型別識別符號 [not null]:=值;
    declare
        age number(3):=26; --長度為3，初始值為26
    begin
        commit;
    end;
    其中，定義常量的語法格式：
    常量名 constant 型別識別符號 [not null]:=值;
    declare
        pi constant number(9):=3.1415926;--為pi的數字型常量，長度為9，初始值為3.1415926
    begin
        commit;
    end;

表示式

    變數、常量經常需要組成各種表示式來進行運算，下面介紹在PL/SQL中常見表示式的運算規則。
    
    1. 數值表示式
    PL/SQL程式中的數值表示式是由數值型常數、變數、函式和算術運算子組成的，可以使用的算術運算子包括+（加法）、-（減法）、*（乘法）、/（除法

）和**（乘方）等。
    命令視窗中執行下列PL/SQL程式，該程式定義了名為result的整數型變數，計算的是10+3*4-20+5**2的值，理論結果應該是27。
    ―――――――――――――――――――――――――――――――――――――
    set serveroutput on
    Declare
       result integer;
    begin
       result:=10+3*4-20+5**2;
       dbms_output.put_line('運算結果是：'||to_char(result));
    end;
    ―――――――――――――――――――――――――――――――――――――
    dbms_output.put_line函式輸出只能是字串，因此利用to_char函式將數值型結果轉換為字元型。
    
    2. 字元表示式
    字元表示式由字元型常數、變數、函式和字元運算子組成，唯一可以使用的字元運算子就是連線運算子“||”。
    
    3. 關係表示式
    關係表示式由字元表示式或數值表示式與關係運算子組成，可以使用的關係運算子包括以下9種。
    小於
    > 大於
    = 等於（不是賦值運算子:=）
    like 類似於
    in 在……之中
    <= 小於等於
    >= 大於等於
    != 不等於 或<>
    between 在……之間
    關係型表示式運算子兩邊的表示式的資料型別必須一致。
 
    4. 邏輯表示式
    邏輯表示式由邏輯常數、變數、函式和邏輯運算子組成，常見的邏輯運算子包括以下3種。
    NOT：邏輯非
    OR：邏輯或
    AND：邏輯與
    運算的優先次序為NOT、AND和OR。

PLSQL函式

    PL/SQL程式中提供了很多函式供擴充套件功能，除了標準SQL語言的函式可以使用外，最常見的資料型別轉換函式有以下3個。
    To_char：將其他型別資料轉換為字元型。
    To_date：將其他型別資料轉換為日期型。
    To_number：將其他型別資料轉換為數值型。
    繼續追加中..

系統輸出列印

    利用pl/sql在資料庫伺服器端列印一句話：
    set serveroutput on--設定資料庫輸出，預設為關閉，每次重新開啟視窗需要重新設定。
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Hello PL/SQL');
    END;

pl/sql程式中對大小寫不敏感（列印宣告的變數）
   ―――――――――――――――――――――――――――――――――――――
    set serveroutput on
    DECLARE
      v_char varchar2(20):='a';
      v_char1 varchar2(20):='b';
    BEGIN
      DBMS_OUTPUT.PUT_LINE(v_char);
      DBMS_OUTPUT.PUT_LINE(v_char1);
    END;

pl語句塊是pl/sql裡最小的程式設計塊，其中可以再巢狀begin end
    begin
     dbms_output.put_line('Hello World');
     dbms_output.put_line('2*3='||(2*3));
     dbms_output.put_line('what''s');
    end;
   ―――――――――――――――――――――――――――――――――――――

PL/SQL中的變數宣告

 所有變數必須在declare中宣告，程式中不允許宣告。
  沒有初始化的變數預設值為null，螢幕上null是看不見的，命名習慣：PL/SQL中變數一般以v_開頭（等同於儲存過程中as和begin區域的變數定義習慣）。
  注意number也能存小數，最長38位，所以以後建議整數都用binary_integer存。
  long是字元型別，boolean型別不能列印。
  標準變數型別：數字，字元，時間，布林。
   ―――――――――――――――――――――――――――――――――――――
    declare
 v_number1 number;
 v_number2 number(3,2) ;
 v_number3 binary_integer :=1;
 v_name varchar2(20) :='kettas';
 v_date date :=sysdate;
 v_long long :='ni hao';
 v_b boolean := true;
    begin
 if (v_number1 is null) then
  dbms_output.put_line( 'hello');
 end if;
 dbms_output.put_line(v_number1);
 dbms_output.put_line(v_number2);
 dbms_output.put_line(v_number3);
 dbms_output.put_line(v_name);
 dbms_output.put_line(v_date);
 dbms_output.put_line(v_long);
      --dbms_output.put_line(v_b); --執行該句ORACLE提示“呼叫 'PUT_LINE' 時引數個數或型別錯誤”
    end;
   ―――――――――――――――――――――――――――――――――――――

   備註：
   關於宣告number(4,3)中括號中的兩個數字的意義，前面的數字叫精度，後面的叫刻度。
   刻度：
     當刻度為正數的時候，表示四捨五入到小數點後面的位數
     當刻度為負數的時候，表示四捨五入到小數點前面的位數
   精度：
     從數字的最前面不為零開始到刻度精確到的位置
   v_Number number(4,3):=123.12312
   1、按刻度進行四捨五入得到123.123
   2、確定刻度精確到的位置123123處，精度為6位（.符號不算）
   2、根據精度進行判斷6位（>4）精度上限值  --報錯不能儲存
   number(3,-3):=44445
   1、根據刻度－3進行四捨五入得到44000
   2、小數點向前移動3位44.此位置為刻度精確到的位置
   3、根據精度進行判斷2位（<3）精度上限值  --不報錯可儲存結果為44000
   
   DECLARE
     v_Number number(4,3):=123.12312;--實際精度6位大於上限精度值4位，提示“ORA-06502: PL/SQL: 數字或值錯誤 :  數值精度太高”
   BEGIN
     DBMS_OUTPUT.PUT_LINE(v_Number);
   END
   ;
   
   DECLARE
     v_Number number(7,3):=4555; --實際精度7位等於上限精度值，可以儲存
   BEGIN
     DBMS_OUTPUT.PUT_LINE(v_Number);
   END
   ;
   
*****************************************
  變數賦值方式
*****************************************
oracle中變數賦值方式是值複製而非引用
   
   declare
       v_number1 number:=100;
       v_number2 number;
   begin
       v_number2:=v_number1;
       v_number1:=200;
       dbms_output.put_line(v_number1); --200
       dbms_output.put_line(v_number2); --100
 
   end;
  
*****************************************
  PLSQL複合型別
*****************************************
記錄型別record

record型別最常用，宣告的時候可以加not null，但必須給初始值，如果record型別一致可以相互賦值，如果型別不同，裡面的欄位恰好相同，不能互相賦值。引用記錄型變數的方法是“記錄變數名.基本型別變數名”。
  ―――――――――――――――――――――――――――――――――――――
  declare
       type t_first is record(
            id number(3),
            name varchar2(20)
       );
       v_first t_first;
  begin
     v_first.id:=1;
     v_first.name:='cheng';
     dbms_output.put_line(v_first.id);
     dbms_output.put_line(v_first.name);
  end;

  record型別變數間賦值
  declare
       type t_first is record(
         id number,
         name varchar2(20)   
       );
       v_first t_first;
       v_second t_first;
  begin
       v_first.id:=1;
       v_first.name:='susu';
       
       v_second:=v_first;--相互賦值
       
       v_first.id:=2;
       v_first.name:='kettas';
       dbms_output.put_line(v_first.id);
       dbms_output.put_line(v_first.name);
       dbms_output.put_line(v_second.id);
       dbms_output.put_line(v_second.name);
   end;
   ―――――――――――――――――――――――――――――――――――――
表型別變數table
語法如下：
    type 表型別 is table of 型別 index by binary_integer;
    表變數名 表型別;
型別可以是前面的型別定義，index by binary_integer子句代表以符號整數為索引，這樣訪問表型別變數中的資料方法就是“表變數名(索引符號整數)”。table型別，相當於java中的Map容器，就是一個可變長的陣列，key（符號整數索引）必須是整數，可以是負數，value（型別）可以是標量，也可以是record型別。可以不按順序賦值，但必須先賦值後使用。

1. 定義一維表型別變數
   ―――――――――――――――――――――――――――――――――――――
   declare
        type t_tb is table of varchar2(20) index by binary_integer;
        v_tb t_tb;
   begin
      v_tb(100):='hello';
      v_tb(98):='world';
      dbms_output.put_line(v_tb(100));
      dbms_output.put_line(v_tb(98));
   end;    

   型別為record的表型別變數 
   declare
        type t_rd is record(id number,name varchar2(20));
        type t_tb is table of t_rd index by binary_integer;
        v_tb2 t_tb;
   begin
        v_tb2(100).id:=1;
        v_tb2(100).name:='hello';
        --dbms_output.put_line(v_tb2(100).id);
        --dbms_output.put_line(v_tb2(100).name);
        dbms_output.put_line(v_tb2(100).id||'    '||v_tb2(100).name);
   end;
   ―――――――――――――――――――――――――――――――――――――

2. 定義多維表型別變數
該程式定義了名為tabletype1的多維表型別，相當於多維陣列，table1是多維表型別變數，將資料表tempuser.testtable中recordnumber為60的記錄提取出來

存放在table1中並顯示。
   ―――――――――――――――――――――――――――――――――――――
   declare
      type tabletype1 is table of testtable%rowtype index by binary_integer;
      table1 tabletype1;
   begin
       select * into table1(60) from tempuser.testtable where recordnumber=60;
       dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);
   end;
   
   備註：在定義好的表型別變數裡，可以使用count、delete、first、last、next、exists和prior等屬性進行操作，使用方法為“表變數名.屬性”，返回的是數字。
    
   set serveroutput on
   declare
        type tabletype1 is table of varchar2(9) index by binary_integer;
        table1 tabletype1;
   begin
        table1(1):='成都市';
        table1(2):='北京市';
        table1(3):='青島市';
        dbms_output.put_line('總記錄數：'||to_char(table1.count));
        dbms_output.put_line('第一條記錄：'||table1.first);
        dbms_output.put_line('最後條記錄：'||table1.last);
        dbms_output.put_line('第二條的前一條記錄：'||table1.prior(2));
        dbms_output.put_line('第二條的後一條記錄：'||table1.next(2));
    end;
    ―――――――――――――――――――――――――――――――――――――

*****************************************
     %type和%rowtype
*****************************************
使用%type定義變數，為了讓PL/SQL中變數的型別和資料表中的欄位的資料型別一致，Oracle 9i提供了%type定義方法。這樣當資料表的欄位型別修改後，PL/SQL程式中相應變數的型別也自動修改。
    ―――――――――――――――――――――――――――――――――――――
    create table student(
       id number,
       name varchar2(20),
       age number(3,0)
    );

    insert into student(id,name,age) values(1,'susu',23);
    --查詢一個欄位的變數

    declare
       v_name varchar2(20);
       v_name2 student.name%type;
    begin
       select name into v_name2 from student where rownum=1;
       dbms_output.put_line(v_name2);
    end;

    --查詢多個欄位的變數
    declare
        v_id student.id%type;
        v_name student.name%type;
        v_age student.age%type;
    begin
      select id,name,age into v_id,v_name,v_age from student where rownum=1;
      dbms_output.put_line(v_id||'  '||v_name||'  '||v_age);
    end;

    --查詢一個型別的變數，推薦用*

    declare
       v_student student%rowtype;
    begin
       select * into v_student from student where rownum=1;
       dbms_output.put_line(v_student.id||'  '||v_student.name||'  '||v_student.age);
    end;

    --也可以按欄位查詢，但是欄位順序必須一樣，不推薦這樣做
    declare
       v_student student%rowtype;
    begin
     select id,name,age into v_student from student where rownum=1;
     dbms_output.put_line(v_student.id||'  '||v_student.name||'  '||v_student.age);
    end;

    declare
       v_student student%rowtype;
    begin
     select id,name,age into v_student.id,v_student.name,v_student.age from student where id=1;
     --select * into v_student.id,v_student.name,v_student.age from student where id=1;
     dbms_output.put_line();
    end;
    ―――――――――――――――――――――――――――――――――――――
    備註：insert，update，delete，select都可以，create table，drop table不行。DPL，DML，和流程控制語句可以在pl/sql裡用，但DDL語句不行。
    
    declare
       v_name student.name%type:='wang';
    begin
       insert into student(id,name,age) values(2,v_name,26);
    end;
    
    begin
       insert into student(id,name,age) values(5,'hehe',25);
    end;

    declare
       v_name student.name%type:='hexian';
    begin
       update student set name=v_name where id=1;
    end;

    begin
       update student set name='qinaide' where id=2;
    end;
    ―――――――――――――――――――――――――――――――――――――
*****************************************
   PLSQL變數的可見空間
*****************************************
變數的作用域和可見性，變數的作用域為變數申明開始到當前語句塊結束。當外部過程和內嵌過程定義了相同名字的變數的時候，在內嵌過程中如果直接寫這個變數名是沒有辦法訪問外部過程的變數的，可以透過給外部過程定義一個名字<>,透過outername變數名來訪問外部過程的變數（待測試..）。
    ―――――――――――――――――――――――――――――――――――――
    declare
            v_i1 binary_integer:=1;
    begin
         declare
            v_i2 binary_integer:=2;
         begin
            dbms_output.put_line(v_i1);
            dbms_output.put_line(v_i2);
         end;
      dbms_output.put_line(v_i1);
    --dbms_output.put_line(v_i2);  解開後執行Oracle會提示“必須說明識別符號 'V_I2'”
    end;
    ―――――――――――――――――――――――――――――――――――――

*****************************************
   PLSQL流程控制
*****************************************
if判斷
declare
       v_b boolean:=true;
begin if v_b then
         dbms_output.put_line('ok');
      end if;
end;

if else判斷
declare
        v_b boolean:=true;
begin
     if v_b then
        dbms_output.put_line('ok');
     else
        dbms_output.put_line('false');
     end if;
end;

if elsif else判斷
declare
        v_name varchar2(20):='cheng';
begin
     if v_name='0701' then
        dbms_output.put_line('0701');
     elsif v_name='cheng' then
        dbms_output.put_line('cheng');
     else
        dbms_output.put_line('false');
     end if;
end;

loop迴圈，注意推出exit是推出迴圈，而不是推出整個程式碼塊
declare
       v_i binary_integer:=0;
begin
     loop
         if v_i>10 then
            exit;
         end if;
         v_i:=v_i+1;
         dbms_output.put_line('hehe');
     end loop;
         dbms_output.put_line('over');
end;

loop簡化寫法
declare
       v_i binary_integer :=0;
begin
     loop
         exit when v_i>10;
         v_i :=v_i+1;
         dbms_output.put_line('hehe');
     end loop;
         dbms_output.put_line('over');
end;

while迴圈
declare
       v_i binary_integer:=0;
begin
       while v_i<10 loop
             dbms_output.put_line('hello'||v_i );
             v_i:=v_i+1;
       end loop;
       dbms_output.put_line('over');
end;

for迴圈，注意不需要宣告變數
begin
     for v_i in 0..10 loop
         dbms_output.put_line('hello'||v_i);
     end loop;
         dbms_output.put_line('over');
end;

*****************************************
     PLSQL異常處理
*****************************************
1、宣告異常
 異常名 EXCEPTION；
2、丟擲異常
 RAISE 異常名
3、處理異常
 丟擲異常後的邏輯程式碼不會被繼續執行

異常的定義使用
    ―――――――――――――――――――――――――――――――――――――
    begin
          dbms_output.put_line(1/0);
    exception
            when others then
                dbms_output.put_line('error');
    end;

    declare
            e_myException exception;
    begin
            dbms_output.put_line('hello');
            raise e_myException; --raise丟擲異常,用此關鍵字，丟擲後轉到自定義的e_myException ，執行其裡面的putline函式後，再跳到end處，結束PL/SQL塊，raise接下面的2句不會繼續執行。
            dbms_output.put_line('world');
            dbms_output.put_line(1/0);
    exception
            when e_myException then
                dbms_output.put_line(sqlcode); --當前會話執行狀態，錯誤編碼
                dbms_output.put_line(sqlerrm); --當前錯誤資訊
                dbms_output.put_line('my error');
            when others then
                dbms_output.put_line('error');
    end;
    ―――――――――――――――――――――――――――――――――――――
*****************************************
  PLSQL遊標和goto語句
*****************************************
備註：下面提到的遊標為靜態cursor，包括顯示和隱式。
遊標，從declare、open、fetch、close是一個完整的生命旅程。當然了一個這樣的遊標是可以被多次open進行使用的，顯式cursor是靜態cursor，她的作用域是全域性的，但也必須明白，靜態cursor也只有pl/sql程式碼才可以使用它。靜態遊標變數是在定義時就必須指定SQL語句。

cursor 遊標(結果集)用於提取多行資料，定義後不會有資料，使用後才有。一旦遊標被開啟，就無法再次開啟(可以先關閉，再開啟)。
    declare
          cursor c_student is  select * from book;
    begin
          open c_student;
          close c_student;
    end;

第二種遊標的定義方式，用變數控制結果集的數量。
    declare
          v_id binary_integer;
          cursor c_student is select * from book where id>v_id;
    begin
          v_id:=10;
          open c_student;
          close c_student;
    end;

第三種遊標的定義方式，帶引數的遊標，用的最多。
    declare
          cursor c_student(v_id binary_integer) is select * from book where id>v_id;
    begin
          open c_student(10);
          close c_student;
    end;

遊標的使用，一定別忘了關遊標。
    declare
          v_student book%rowtype;
          cursor c_student(v_id binary_integer) is select * from book where id>v_id;
    begin
          open c_student(10);
          fetch c_student into v_student;
          close c_student;
          dbms_output.put_line(v_student.name);
    end;

如何遍歷遊標fetch
  遊標的屬性 %found，%notfound,%isopen,%rowcount。
  %found:若前面的fetch語句返回一行資料，則%found返回true，如果對未開啟的遊標使用則報ORA-1001異常。
  %notfound，與%found行為相反。
  %isopen，判斷遊標是否開啟。
  %rowcount:當前遊標的指標位移量，到目前位置遊標所檢索的資料行的個數，若未開啟就引用，返回ORA-1001。

注：
no_data_found和%notfound的用法是有區別的，小結如下
1）SELECT . . . INTO 語句觸發 no_data_found；
2）當一個顯式游標(靜態和動態)的 where 子句未找到時觸發 %notfound；
3）當UPDATE或DELETE 語句的where 子句未找到時觸發 sql%notfound；
4）在游標的提取(Fetch)迴圈中要用 %notfound 或%found 來確定迴圈的退出條件，不要用no_data_found。

下面是幾個例項：
create table BOOK
(
  ID       VARCHAR2(10) not null,
  BOOKNAME VARCHAR2(10) not null,
  PRICE    VARCHAR2(10) not null,
  CID      VARCHAR2(10) not null
);

--insert
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
     as
     v_price varchar2(100); 
     e_myException exception;

     begin
        insert into book(id,bookname,price) values (1,2,3);
        o_result_msg := 'success';
     exception
        when others then
             rollback;
             o_result_msg := substr(sqlerrm, 1, 200);
     end;

--update or delete
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
     as
     v_price varchar2(100); 
     e_myException exception;

     begin
        update book set price = '55' where bookname = i_name;
        delete from book where bookname = i_name;
        if sql%notfound then
           raise e_myException; 
        end if;
        /*
 if sql%rowcount = 0 then--寫法2
           raise e_myException; 
        end if;
 */
        o_result_msg := 'success';
     exception
        when e_myException then
             rollback;
             o_result_msg := 'update or delete dail';
     end;

--select
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
     as
     v_price varchar2(100); 
     e_myException exception;

     begin
        select price into v_price from book where bookname = i_name;
        o_result_msg := 'success';
     exception
        when no_data_found then
             rollback;
             o_result_msg := 'select into dail';
     end;

loop方式遍歷遊標
    declare
          v_bookname  varchar2(100);
          cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
        Open  c_book(i_id);
        Loop
            Fetch c_book into v_bookname;
            exit when c_student%notfound;
              update book set price = '33' where bookname = v_bookname;
        End Loop;
        Close c_book;
    end;
    或
    declare
          v_bookname  varchar2(100);
          cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
        Open  c_book(i_id);
          Fetch c_book into v_bookname;
          While c_book%Found
          Loop
              update book set price = '33' where bookname = v_bookname;
          Fetch  c_book into v_bookname;
          End Loop;
        Close c_book;
    end;
       

while迴圈遍歷遊標，注意，第一次遊標剛開啟就fetch，%found為null，進不去迴圈
解決方法：while nvl(c_student%found,true) loop
    declare
         v_bookname  varchar2(100);
         cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
         Open  c_book(i_id);
         while nvl(c_book%found,true) --或這種寫法：while c_book%found is null or c_book%found loop    
             Fetch c_book into v_bookname;
             update book set price = '33' where bookname = v_bookname;
         End Loop;
         Close c_book;
    end;

for迴圈遍歷，最簡單，用的最多，不需要 宣告v_student,Open和Close遊標和fetch操作(不用開啟遊標和關閉遊標，實現遍歷遊標最高效方式)
    declare
         cursor c_book(i_id number) is select bookname from book where id = i_id;
    begin
         for cur in c_book(i_id) --直接將入參i_id傳入cursor即可
         loop
             update book set price = '53' where bookname = cur.bookname;
         end loop;
    end;

goto例子，一般不推薦使用goto，會使程式結構變亂
    declare
         i number:=0;
    begin
         if i=0 then 
             goto hello;
         end if;
         <>
         begin
             dbms_output.put_line('hello');
             goto over;
         end;
         <>
         begin
             dbms_output.put_line('world');
             goto over;
         end;
         <>
             dbms_output.put_line('over');
     end;
*****************************************
   Oracle儲存過程
*****************************************
在談儲存過程書寫中的一些規則時，先看一下執行它的規則，在命令視窗執行儲存過程sp_get_product_prompt 
     set serveroutput on
     var ret1 varchar2(200);
     var ret2 varchar2(200);
     exec sp_get_product_prompt(83,:ret1,:ret2); --或execute
     print ret1;
     print ret2;
     或
     set serveroutput on
     declare
        ret1 varchar2(200);
        ret2 varchar2(200);
     begin
        sp_get_product_prompt(83,ret1,ret2);
        dbms_output.put_line(ret1);
        dbms_output.put_line(ret2);
     end;

儲存過程入參，不論型別，預設情況下值都為null,入參和出參不能有長度,其中關鍵字as可以替換成is,儲存過程中變數宣告在as和begin之間，同時，儲存過程中可以再呼叫其它的儲存過程，如果要保證儲存過程之間的事務處理不受影響，可以定義為自治事務。
     create or replace procedure say_hello(
       v_name in varchar2,
       v_flag number,
       o_ret out number
     )
     as
     begin
       if v_name is null and v_flag is null then --v_name和v_flag都等於null
           o_ret := 10;
       else
           o_ret := 100; 
       end if;
     end;

對於入參為null情況下給予預設值
     create or replace procedure say_hello(
       i_name in varchar2,
       i_flag number,
       o_ret out number
     )
     as
       v_name  varchar2(100);
     begin
       if i_name is null then 
          v_name := '0';
       else
          v_name := i_name;
       end if;
       insert into phone(..,wname..,) values(..,v_name,..);  
     
     end;
或直接在insert語句中呼叫nvl函式賦預設值
     insert into phone(..,wname..,) values(..,nvl(v_name,' '),..); ----如果將' '寫成'',則insert進來的v_name值還是為''等價於null值

帶一個引數的儲存過程
   輸入引數in，輸入引數不能進行:=賦值，但可以將它賦給as後面定義的變數；
   輸入引數in，可以作為變數進行條件判斷；
   預設不寫就是in；
   儲存過程沒有過載，這個有參的say_hello會替代已經存在的無參say_hello。

     create or replace procedure say_hello(v_name in varchar2)
     as
     begin
        --v_name:='a'; --儲存過程入參v_name不能做為賦值目標
        dbms_output.put_line('hello '||v_name); 
     end;

儲存過程輸入引數作為變數進行條件判斷
     create or replace procedure say_hello(
        i_opFlag in number
     )
     as
        v_name varchar2(100);
     begin
        if i_opFlag = 1 then
    v_name :='0'; 
        else
    v_name :='haha';
        end if; 
        dbms_output.put_line('hello '||v_name); 
     end;

利用儲存過程中定義的變數對入參的空值處理：
     create or replace procedure say_hello(
        i_name in varchar2
     )
     as
        v_name varchar2(100);
     begin
        if i_name is null then
    v_name :='0'; 
        else
    v_name :=i_name;--將入賦值給定義變數
        end if; 
        dbms_output.put_line('hello '||v_name); 
     end;

多個引數的儲存過程
     create or replace procedure say_hello(
        v_first_name in varchar2,
        v_last_name in varchar2)
     as
     begin
        dbms_output.put_line('hello '||v_first_name||'.'||v_last_name);
     end;

out輸出引數，用於利用儲存過程給一個或多個變數賦值，類似於返回值
     create or replace procedure say_hello(
        v_name in varchar2,
        v_content out varchar2
     )
     begin
        v_content:='hello'||v_name;
     end;

     呼叫：
     declare
        v_con varchar2(200);
        v_in varchar2(20):='wang';
     begin
        say_hello(v_in,v_con);
        dbms_output.put_line(v_con);
     end;

in out引數，既賦值又取值
     create or replace procedure say_hello(v_name in out varchar2)
     as
     begin
        v_name:='hi '||v_name;
     end;

     呼叫：
     declare
        v_inout varchar2(20):='wangsu';
     begin
        say_hello(v_inout);
        dbms_output.put_line(v_inout);
     end;

對儲存過程入參賦預設值
     create or replace procedure say_hello(
        v_name varchar2 default 'susu',
        v_content varchar2 default 'hello'
     )
     as
     begin
        dbms_output.put_line(v_name||' '||v_content);
     end;

     呼叫：(用指明形參名的方式呼叫更好)
     begin
        say_hello();
     end;
     或
     begin
        say_hello('cheng');
     end;
     或
     begin
     say_hello(v_name=>'cheng');
    end;

*****************************************
 PLSQL中的function
*****************************************
FUNCTION和PROCEDURE的區別
1、函式有返回值，過程沒有
2、函式呼叫在一個表示式中，過程則是作為pl/sql程式的一個語句
  過程和函式都以編譯後的形式存放在資料庫中，函式可以沒有引數也可以有多個引數並有一個返回值。過程
  有零個或多個引數，沒有返回值。函式和過程都可以透過引數列表接收或返回零個或多個值，函式和過程的
  主要區別不在於返回值，而在於他們的呼叫方式，過程是作為一個獨立執行語句呼叫的，函式以合法的表達
  式的方式呼叫
    create or replace function func(v_name in varchar2)
    return varchar2
    is
    begin
       return(v_name||' hello');
    end;

    呼叫：
    declare
       v_name varchar2(20);
    begin
       v_name:=func('cheng');
       dbms_output.put_line(v_name);
    end;

帶out引數的函式
    create or replace function func(
       v_name in varchar2,
       v_content out varchar2
    )
    return varchar2
    is
    begin
       v_content:=v_name||' hello';
       return v_content;
    end;

    呼叫：
    declare
       v_name varchar2(20);
       v_name1 varchar2(20);
    begin
       v_name1:=func('susu',v_name);--返回v_name值
       dbms_output.put_line(v_name1);--列印func結果
       dbms_output.put_line(v_name);--列印v_name結果
    end;

帶in out 引數的函式
    create or replace function func(
       v_name in out varchar2)
    return varchar2
    is
    begin
       v_name:=v_name||' hello';
       return 'cheng';
    end;

    呼叫：
    declare
       v_inout varchar2(20):='world';
       v_ret varchar2(20);
    begin
       v_ret:=func(v_inout);--返回撥用v_inout值(作為出參)
       dbms_output.put_line(v_ret);--列印func結果     
       dbms_output.put_line(v_inout);--返回v_name結果
    end;

    部分內容參考至CSDN：
    文章內容繼續更新中..歡迎大家指點。
     

分析函式簡述  

注：N表示數字型，C表示字元型，D表示日期型，[]表示內中引數可被忽略，fmt表示格式。

　　分析函式計算基於group by的列，分組查詢出的行被稱為"比照(window)"，在根據over()執行過程中，針對每一行都會重新定義比照。比照為"當前行(current row)"確定執行計算的行的範圍。這點一定要理解清楚。它是分析函式生成資料的原理。如果此處模糊，那麼你在應用分析函式時恐就不會那麼得心應手了。

　　分析函式與前面章節中講到的聚合函式非常相似，不同於聚合函式的地方在於它們每個分組序列均返回多行。在本節示例中會同時應用兩種函式做對比，以更好體現二者的差異。透過本章節練習相信大家就會注意到，部分聚合函式和分析函式是同一個命令，事實確實如此。如果從語法格式上區分的話，沒加over()的即是聚合函式，加了over()即是分析函式：）

　　有一點需要注意喲，除了order by子句的運算外，分析函式在SQL語句中將會最後執行。因此，分析函式只能應用於select的列或order by子句中(記住嘍，千萬別扔到什麼where、group by、having之類的地方了)。也正因此，同名的函式在做為聚合函式和分析函式時得出的結果可能不相同，就是因為此處運算邏輯不同造成的。

　　同時，部分分析函式在選擇列時支援distinct，如果你指定了該引數，則over條件中就只能指定partition子句，而不能再指定order by 子句了。

　　分析函式的語法結構比較複雜，但多數函式都具有相同的語法結構，所以先在之前進行統一介紹，後續單個函式介紹時就不過多說明函式語法結構了。

　　基本上所有的分析函式均是這種格式：

　　函式名稱 ([引數]) OVER (analytic_clause)

analytic_clause包含：[partition 子句][ order 子句 [window子句]]

l         Partition 子句：Partition by exp1[ ,exp2]...

Partition沒啥說的，功能強大引數少，主要用於分組，可以理解成select中的group by。不過它跟select語句後跟的group by 子句並不衝突。

l         Order子句：Order by exp1[asc|desc] [ ,exp2 [asc|desc]]... [nulls first|last]。部分函式支援window子句。

Order by的引數基本與select中的order by相同。大家按那個理解就是了。Nulls first|last是用來限定nulls在分組序列中的所在位置的，我們知道oracle中對於null的定義是未知，所以預設ordery的時候nulls總會被排在最前面。如果想控制值為null的列的話呢，nulls first|last引數就能派上用場了。

l         Window子句：En,貼個圖吧

　　       看起來複雜其實簡單，而且應用的機率相當的低，不詳細介紹了。

l         AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 計算平均值。

例如：

--聚合函式

SELECT col, AVG(value) FROM tmp1 GROUP BY col ORDER BY col;

--分析函式

SELECT col, AVG(value) OVER(PARTITION BY col ORDER BY col)

 FROM tmp1

 ORDER BY col;

l         SUM ( [ DISTINCT | ALL ] expr ) OVER ( analytic_clause )

例如：

--聚合函式

SELECT col, sum(value) FROM tmp1 GROUP BY col ORDER BY col;

--分析函式

SELECT col, sum(value) OVER(PARTITION BY col ORDER BY col)

 FROM tmp1

 ORDER BY col;

l         COUNT({* | [DISTINCT | ALL] expr}) OVER (analytic_clause) 查詢分組序列中各組行數。

例如：

--分組查詢col的數量

SELECT col,count(0) over(partition by col order by col) ct FROM tmp1;

l         FIRST() 從DENSE_RANK返回的集合中取出排在第一的行。

例如：

--聚合函式

SELECT col,

       MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) "Min Value",

       MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) "Max Value"

 FROM tmp1

 GROUP BY col;

--分析函式

SELECT col,

       MIN(value) KEEP(DENSE_RANK FIRST ORDER BY col) OVER(PARTITION BY col),

       MAX(value) KEEP(DENSE_RANK LAST ORDER BY col) OVER(PARTITION BY col)