查詢股票最近之庫存數量，存在，則傳回最近之庫存數量，若無，仍需回傳0

當查詢特定股票最近之庫存數量時，若此檔股票尚有庫存數量，則傳回最近之庫存數量，若無，仍需回傳0，以供Stored Procedure或程式後續使用。本範例重點在於如何解決以下兩項問題:

1. 最近庫存資料值如何取得（重點1）

取得最近庫存資料值，可使用ROW_NUMBER函數，根據庫存日期進行反向排序以產生序號，並擷取序號為1的資料列（Row）。

2. 若無符合資料，仍需傳回0（重點2）

利用純量彚總（Scalar Aggregate）運算只傳回1筆資料列之特性，即SQL中無GROUP BY子句但使用彚總函數之語法，當無任何資料符合查詢條件時，仍將傳回1筆值為NULL之資料列；再配合ANSI SQL的NULL置換函數-COALESCE即可解決必要傳回的問題，另外，ORACLE也可使用NVL函數，MSSQL則為ISNULL函數，不過仍建議應優先採用ANSI語法，可減少未來可能之移轉及教育訓練成本。

SQL	測試1：2330（有庫存）	測試2：2340（無庫存）
SELECT COALESCE(MAX(Stk_Qty), 0) Stk_Qty--重點(2) FROM ( SELECT S.* , ROW_NUMBER() OVER(ORDER BY DT Desc) Seq --重點(1) FROM Stocks S WHERE 1=1 AND DT < '20130906' AND Stk_No = '2330' --測試1: 存在 AND Stk_No = '2340' --測試2: 無 ) A WHERE 1=1      AND Seq = 1 --重點(1)		使用彚總函數所產生1筆NULL資料，以COALESCE- NULL置換函數變更為0。

可使用以下SQL產生資料

MSSQL	ORACEL
SELECT '20130905' DT       , '2330' Stk_No       , 120 Stk_Qty      INTO Stocks UNION ALL SELECT '20130904', '2330', 100 UNION ALL SELECT '20130903', '2330', 86	CREATE TABLE Stocks AS SELECT '20130905' DT   , '2330' Stk_No   , 120 Stk_Qty FROM DUAL UNION ALL SELECT '20130904', '2330', 100 FROM DUAL UNION ALL SELECT '20130903', '2330', 86 FROM DUAL

利用數值序列產生當月日曆資料

許多應用案例中，如銷售、生產日報表，希望呈現整個月完整日期之報表，但因偶發狀況造成當日並未生產或銷售，若公司也未定義日曆資料表，則可能要在AP端或以其他方式解決，在此說明如何應用數值序列以產生當月日曆資料，SQL如下：

功能	SQL (當月日曆)	說明
MSSQL	SELECT number +1  N   , DATEADD(DD, number, CONVERT(CHAR(8), GETDATE(), 120)+'01') DT --INTO #Calendar FROM master.dbo.spt_values WHERE name IS NULL AND number<DAY( DATEADD(MM , 1 , CONVERT(CHAR(8),GETDATE(), 120) +'01' )-1 ) -------- 月初: CONVERT(CHAR(8),GETDATE(), 120) +'01' 月底: DATEADD(MM, 1, CONVERT(CHAR(8),GETDATE(), 120) +'01')-1	月初:利用CONVERT函數以120型式，取得局部日期字串(如2013-09-)，再補上01即為所求。 月底：利用前述月初日期，以DATEADD函數加上一個月可得次月月初日期，減1天即為本月月底。
ORACLE	SELECT LEVEL N       , TRUNC(SYSDATE, 'MM') + LEVEL-1 DT FROM DUAL CONNECT BY LEVEL <=TO_CHAR(LAST_DAY(SYSDATE),'DD') -------- 月底：LAST_DAY(SYSDATE) 另外，TO_CHAR(,'DD') 可得到當日日期數值文字，此處預期應用數值才正確，此為少數允許自動轉型之情況。	LAST_DAY為取得月底日期函數

SQL是一種非程序語言，優點在於集合（SET）及大量等運算處理，循序處理或複雜邏輯則否，且程式常用之迴圈（Loop）功能(T-SQL、PL/SQL非一般SQL)，而一般SQL指令並無支援，可參考本範例之概念，搭配數值序列產生額外資料空間以解決此問題。

另外，將應用前述日曆資料，將日期序列轉換為數值序列。

功能	SQL (當月日曆)	說明
MSSQL	SELECT DT   , DATEDIFF(DD               , CONVERT(CHAR(8),GETDATE(), 120) +'01'               ,  DT               ) +1  N   , DT-CAST(CONVERT(CHAR(8),GETDATE(), 120) +'01'               AS datetime) DF FROM #Calendar WHERE 1=1      AND DT >= CONVERT(CHAR(8),GETDATE(), 120) +'01'
ORACLE	SELECT DT      , TRUNC(SYSDATE, 'MM') BOM --月初      , DT - TRUNC(SYSDATE, 'MM') +1 N FROM    (     SELECT LEVEL N           , TRUNC(SYSDATE, 'MM') + LEVEL-1 DT     FROM DUAL     CONNECT BY LEVEL <=TO_CHAR(LAST_DAY(SYSDATE),'DD')    )

ORACLE直接使用日期相減，即可得到數值序列。但MSSQL的日期相減卻仍為日期型態，需使用DATEDIFF函數才正確。另外，ORACLE的月初日期可用TRUNC(SYSDATE,'MM')取得，月底日期則為LAST_DAY(SYSDATE)。

產生數值序列(1~N)

對於數值序列的取得，MSSQL可由master.dbo.spt_values 系統資料表直接取得，不同版本有些許差異，MSSQL 2000值域為0~255，以上版本則為0~2047；ORACLE並未提供類似的系統資料表，不過可使用CONNECT BY指令的方式產生，為避免耗用不必要資源建議建立1~10000的數值序列資料表，以供各系統使用。指令及說明如下:

功能	SQL (1~N)	說明
MSSQL	SELECT number FROM master.dbo.spt_values WHERE name IS NULL ORDER BY 1	2000為0~255 2005、2008為0~2047
ORACLE	SELECT LEVEL N FROM DUAL CONNECT BY LEVEL<=100	利用CONNECT BY遞迴運算式產生。

以下建立Tally數值序列資料表並儲存10,000筆資料， MSSQL雖可直接使用master.dbo.spt_values系系資料表，但以CROSS JOIN語法方式建立，兩種SQL語法及說明如下：

	MSSQL	ORACEL
建立資料表	CREATE TABLE Tally ( N INT NOT NULL, CONSTRAINT PK_Tally PRIMARY KEY(N) )	CREATE TABLE TALLY ( N NUMBER(10) NOT NULL, CONSTRAINT PK_TALLY PRIMARY KEY (N) ) ORGANIZATION INDEX
儲存序列	INSERT INTO Tally SELECT K.number * 1000  + N.number +1 --FROM master.dbo.spt_values K --   , master.dbo.spt_values N FROM master.dbo.spt_values K      CROSS JOIN master.dbo.spt_values N WHERE 1=1      AND K.name IS NULL      AND N.name is NULL      AND K.number <10      AND N.number <1000 ORDER BY 1	INSERT INTO TALLY SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=10000

數值序列資料表僅有一個整數型態的欄位，欄位名稱為N。MSSQL是以一般資料表方式建立；ORACLE則建議採用索引組織資料表（Index-organized Table；IOT）方式，一般資料表的搜尋模式，索引空間中只儲存索引欄位資料，先由索引中搜尋定位資訊後，再藉此於實體資料表中快速找出（定位）、取得資料，而索引組織資料表（IOT）是將整個資料表的資料儲存於索引中，因此可大幅減少I/O次數並提昇效能。