前置補0至固定長度

由於資料擴增而必需原代碼進行升碼處理，以下將說明如何將原代碼（長度3~5碼）前置補『0』為固定為5碼。

ORACLE可直接使用LPAD左側字串填補函數，將代碼左方以『0』補足至5碼。MSSQL無對應函數，但可先將所有代碼前（左）方補上兩碼『0』，再以取出字串右側子字串的RIGHT函數，取出右側5碼，即可捨去超過5碼之左側『0』字元，SQL如下：

資料庫	SQL	說明
MSSQL	SELECT Emp_Id , '00' + Emp_Id             New_Id     　, RIGHT('00' + Emp_Id, 5) Emp_No FROM (VALUES('298') , ('1298') , ('51298') ) AS Emp(Emp_Id)	1.  代碼左方補上兩碼『0』 2.  由右側取出5碼
ORACLE	SELECT COLUMN_VALUE Emp_Id       , LPAD(COLUMN_VALUE, 5, '0') Emp_No FROM TABLE(SYS.DBMS_DEBUG_VC2COLL('298' 　　　　　　　, '1298' 　　　　　　　, '51298') 　　　　　　)	LPAD左側字串填補函數，將代碼左方以『0』補足至5碼

以上範例代碼為字元型態，如為數值型態，ORACLE仍可正常運行；但前述SQL中，MSSQL是使用數學運算符號加號『+』將字串連結，但系統判定轉型為數值，而發生非預期結果，因此需先進行轉型處理，如2012以上版本即可使用CONCAT字串連結函數，如下:

SELECT Emp_Id

    　, RIGHT(CONCAT('00', Emp_Id), 5)             "Emp_No(CONCAT)"

        , RIGHT('00'+ CAST(Emp_Id AS VARCHAR(5)), 5) "Emp_No(轉型)"

FROM (VALUES(298)

, (1298)

, (51298)

) AS Emp(Emp_Id) 

如欲進行逆向處理將前置『0』剔除，可參考《剔除前置0（左側）字元》文章所述。本範中所使用SYS.DBMS_DEBUG_VC2COLL函數為ORACLE未公開指令，使用方法或其他作法可參考另一篇文章《CSV String 2 Row(Table)》。

[ORACLE] LNNVL函數應用-以查詢離職人員為例

LNNVL為應用於WHERE子句中的篩選邏輯函數，如資料符合篩選條件（true）即傳回false；如資料為UNKNOWN（NULL）或不符合（false）則傳回true，如說明應改用負向條件，且不可使用複合運算式，如AND, OR, or BETWEEN。語法如下:

LNNVL(<condition>)

本範例將查詢2016-02-29後仍在職人員，即離職日期需大於此查詢日期或為NULL值，如LNNVL函數定義將離職日期改為小於等於2016-02-29。

SELECT A.*

     , CASE WHEN Leave_Date IS NULL          THEN 1 ELSE 0 END JUDGE1

     , CASE WHEN Leave_Date <= '20160229'   THEN 0 ELSE 1 END JUDGE2

     , CASE WHEN Leave_Date IS NULL          THEN 1 ELSE 0 END

       + CASE WHEN Leave_Date <= '20160229' THEN 0 ELSE 1 END JUDGE

FROM

    (

    SELECT 'Sam'   Emp_Name, '20160229' Leave_Date

    FROM DUAL

    UNION ALL

    SELECT 'Tom'   Emp_Name, NULL         Leave_Date

    FROM DUAL

    UNION ALL

    SELECT 'Kevin' Emp_Name, NULL        Leave_Date

    FROM DUAL

    UNION ALL

    SELECT 'Simon' Emp_Name, '20170126' Leave_Date

    FROM DUAL

    UNION ALL

    SELECT 'Alex'  Emp_Name, '20150630' Leave_Date

    FROM DUAL

    ) A

WHERE 1=1

      --AND (Leave_Date IS NULL OR Leave_Date > '20160229') --方法1

--AND LNNVL(Leave_Date<='20160229')                      --方法2

      --AND COALESCE(Leave_Date, '99991231') >'20160229'    --方法3     

為說明函數運行概念，於查詢中增加二個運算式欄位（JUDGE1、JUDGE2）及前兩者OR運算欄位（JUDGE）並整理如下表，並列出三種方法，請自行測試。

:

#	判斷式	說明
1	Leave_Date IS NULL	離職日期為未知
2	Leave_Date <= '20160229'	離職日期小於等於20160229

雖官方文件說明此函數為WHERE子句中，但仍嘗試應用於SELECT子句中，SQL如下。由於此函數傳回值為boolean值(true/false)，因此無法直接顯示及應用而轉換為NULL，但仍可搭配CASE WHEN子句使用。

SELECT CASE WHEN LNNVL(1=0) THEN 0 ELSE 1 END "1=0"

     , CASE WHEN LNNVL(1=1) THEN 0 ELSE 1 END "1=1"

     , LNNVL(1=0) "1=0"

     , LNNVL(1=1) "1=0"

FROM DUAL

註: 本範例為常見違反設計原則的案例

1.   以日期資料以字串方式儲存

日期合法性已無法由資料庫進行檢核，且無法直接使用日期運算函數。

2.   未使用預設值

雖現職人員並無離職日期，建議可設定預設值為9999-12-31，應避免為NULL，否則查詢/應用時常需進行額外轉換。

參考: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/LNNVL.html#GUID-FBCCE9B1-614E-45FA-8EE1-DFAA4F936867

次序函數

ROW_NUMBER、RANK及DENSE_RANK函數將依指定資料值（欄位）為每筆資料產生從1至N的整數值（integer）流水序號，N的值可能小於或等於記錄筆數，三者唯一區別在於同值（同名）排名策略。

函數	並列名次	排名連續	說明
ROW_NUMBER()	無	連續	對結果集的每一筆資料均賦予一個遞增的整數值
RANK()	有	不一定連續	每一筆資料均賦予一個遞增的整數值。 但資料值相同時，則賦予相同名次(跳號)
DENSE_RANK()	有	連續	與RANK()類似。差別在於DENSE_RANK()排名不會略過資料值相同的名次(不跳號)

指令將依據資料排序定義產生次序編號，因此也稱為次序函數，排序為重要且必要之參考項目（ORDER BY子句）。語法如下:

ROW_NUMBER

ROW_NUMBER() OVER ([PARTITION BY ] <ORDER BY clause>)

RANK

RANK() OVER ([PARTITION BY ] <ORDER BY clause>)

DENSE_RANK

DENSE_RANK() OVER ([PARTITION BY ] <ORDER BY clause>)

範例SQL語法及結果如下：

SELECT No

     , Name

     , Score

     , ROW_NUMBER() OVER (ORDER BY Score　DESC) "ROW_NUMBER"

     , RANK() OVER (ORDER BY Score DESC)        "RANK"

     , DENSE_RANK() OVER (ORDER BY Score DESC)  "DENSE_RANK"

FROM

    (

    SELECT  1 No, '小夫' Name, 90 Score

    FROM DUAL

    UNION ALL

    SELECT 2, '大雄', 30

    FROM DUAL

    UNION ALL

    SELECT 3, '靜香', 90

    FROM DUAL

    UNION ALL

    SELECT 4, '小杉', 100

    FROM DUAL

    UNION ALL    

    SELECT 5, '胖虎', 50

    FROM DUAL

  )

--ORDER BY No  

成績資料中，小夫與靜香為同分（名），以比較次序函數使用上之差異，及對查詢結果影響。

1.  ROW_NUMBER

將按分數產生1~5不重複之流水序號。但由於小夫與靜香為同分，此情況下則排名不具決定性。

2.  RANK函數

對同分二人配發同一序號，依定義將產生跳號。

3.  DENSE_RANK函數

同分處理與RANK函數相同，但不跳號。

4.  資料排序改變

資料使用不會更動順序的UNION ALL集合（SET）運算指令創建，如未指定排序條件情況下，查詢結果將被分析函數的排序設定所影響。

SELECT No

     , Name

     , Score

     , ROW_NUMBER() OVER (ORDER BY Score DESC) "ROW_NUMBER"

     , RANK() OVER (ORDER BY Score DESC)        "RANK"

     , DENSE_RANK() OVER (ORDER BY Score DESC)  "DENSE_RANK"

FROM

    (VALUES(1, '小夫',  90)

         , (2, '大雄',  30)

         , (3, '靜香',  90)

         , (4, '小杉', 100)

         , (5, '胖虎',  50)

       ) Math (No, Name, Score)

--ORDER BY No  

排序定義為次序函數必要參考項目（ORDER BY子句），但些許情況下，並無任何欄位可供或無法（如：依資料位置）指定，可參考《如何在無任何適當ORDER BY欄位情況下，使用ROW_NUMBER函數》文章所探討。