近期開發專案需要使用 SQL 產生西元日期搭配流水號作為單號，例如 P221027001，若當天已經存在第一筆單號，則找到當日最大的單號，將流水號遞增產生下一筆單號，例如 P221027002。
需求很簡單，但考慮到開發的專案是 Web，如果將當日最大單號取出再由後端程式碼產生新單號，當 User 確定存檔後再將單號與資料送至資料庫執行，過程中一來一往，假設當兩個 User 幾乎同時操作時，有極小的機率拿到相同的單號傳回資料庫作業。礙於可能產生重複單號，最後決定當資料送回資料庫時，執行預存再產生新單號，縮小產生新單號與儲存資料之間的時間。
原先預期是由預存程序來執行儲存資料以及產生單號，但在撰寫預存時覺得這樣不太好，該預存命名的定義為儲存資料，裡面竟然偷偷再做產生單號的動作，於是決定將產生單號的動作改為由純量函數執行，下方是純量函數預設新增的 Script：
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
-- Add the T-SQL statements to compute the return value here
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>
END
GO
在 BEGIN 和 END 之間加入產生單號的邏輯。
DECLARE @RESULT VARCHAR(10) --最終返回的值
DECLARE @MAX AS VARCHAR(10) --從資料表中找到的最大單號
DECLARE @HEADER VARCHAR(8) --扣除流水號的單號頭部
-- 取出當天日期作為單號的一部分: 20221027
SET @HEADER = CONVERT(VARCHAR, GETDATE(), 112)
-- 去除日期前兩碼: 221027
SET @HEADER = SUBSTRING(@HEADER, 3, LEN(@HEADER) - 2)
-- 組裝單號的頭部: P221027
SET @HEADER = 'P' + @HEADER
-- 從資料表欄位取回當天最大單號
SET @MAX = (SELECT MAX(ORDERNO) FROM DBO.ORDERS WHERE ORDERNO LIKE @HEADER + '%')
-- 測試假設存在單號
-- SET @MAX = 'P221027002'
IF @MAX IS NULL -- 如果沒找到 @MAX 會是 NULL
BEGIN
-- 當天還沒有單號, 直接將 @HEADER 和流水號組裝: P221027001
SET @RESULT = @HEADER + '001'
END
ELSE -- 資料表內已經有當天單號
BEGIN
DECLARE @VAL VARCHAR(3)
-- 取最大單號後三位流水號
SET @VAL = SUBSTRING(@MAX, LEN(@HEADER) + 1, 3)
-- 轉成 INT 後加一, 再轉回 VARCHAR
SET @VAL = CONVERT(VARCHAR, CONVERT(INT, @VAL) + 1)
-- 使用 REPLICATE 函數將流水號前面補零至為三位
SET @VAL = (REPLICATE('0', 3 - LEN(@VAL)) + @VAL)
-- 組裝 @HEADER 和流水號: P221027005
SET @RESULT = @HEADER + @VAL
END
-- 返回值
RETURN @RESULT
完成後，將函數命名為 dbo.fnGetNewOrderNo ，執行函數新增，呼叫函數用 SELECT <SCHEMA.FUNCTIONNAME>() 。
INSERT INTO [DBO].[ORDERS]
(
[ORDERNO]
)
OUTPUT inserted.ORDERNO
VALUES
(
(SELECT dbo.fnGetNewOrderNo()) --呼叫產生單號函數
)
利用 OUTPUT inserted.<column> 查看指定欄位的執行結果。
如果單號需要西元四碼，例如 20221027，將下面這行移除以及調整 VARCHAR 大小。
-- 去除日期前兩碼: 221027
SET @HEADER = SUBSTRING(@HEADER, 3, LEN(@HEADER) - 2)
收工！
Reference
Thanks for reading the article 🌷 🌻 🌼
If you like it, please don't hesitate to click heart button ❤️
or follow my GitHub ⭐
or buy me a coffee ⬇️ I'd appreciate it.
Top comments (0)