[SQL] SQL Server 批次產生單號：流水號與驗證碼實作

- 11月 18, 2025

📦 什麼是「產單號」？

在資訊系統開發中，我們經常會聽到「產單號」這個術語。簡單來說，就是批次產生序號的過程。當企業與物流公司、金融機構或其他合作夥伴對接時，對方通常會配置一段序號區間，例如從 A 號到 B 號，而我們系統需要依照特定規則將這段區間內的所有序號都產生出來並存入資料庫，以供後續業務使用。

這些序號通常不是單純的連續數字，而是包含了驗證機制（如檢查碼、確認碼），以確保序號的正確性和防偽性。

🎯 實際業務場景

假設我們與某知名宅配公司合作，對方提供了以下序號配置規則：

📮 已為您配置單號區間：5,000 組

🟢 起始單號：922117866191

🔴 終止單號：922117916182

🔐 單號規則解析

這個單號系統採用了 12 碼的結構設計：

▸ 前 11 碼：主流水號，使用數字遞增方式產生
▸ 第 12 碼：確認碼（檢查碼），用於驗證單號正確性

💡 確認碼計算公式

確認碼 = 前11碼流水號 % 3

例如：92211786619 % 3 = 1，所以完整單號為 922117866191

這種設計方式很常見於物流、金融等需要高度資料正確性的產業。透過簡單的數學運算（取餘數），可以在資料傳輸或人工輸入時快速驗證單號是否正確，降低錯誤率。

💾 資料表結構設計

首先，我們需要建立一個資料表來儲存這些單號。以下是 SQL Server 的資料表定義：

CREATE TABLE [dbo].[MAIL_SERIAL_NO](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [SERIAL_NO] [nvarchar](25) NOT NULL,      -- 流水號（11碼）
    [CHECK_NO] [nvarchar](2) NULL,             -- 確認碼（1碼）
    [IS_USE] [nvarchar](1) NULL,                -- 是否已使用（Y/N）
    [CREATE_DATE] [datetime] NULL,            -- 建立日期
) ON [PRIMARY]
GO

📋 欄位說明：

ID：自動遞增的主鍵
SERIAL_NO：儲存11碼的流水號
CHECK_NO：儲存計算出的確認碼
IS_USE：標記該單號是否已被使用
CREATE_DATE：記錄單號產生的時間

⚙️ 批次產生單號的 SQL 實作

接下來是核心邏輯：使用 SQL Server 的 WHILE 迴圈來批次產生單號。這個方法的優點是可以一次性產生大量序號，並且確保資料的一致性。

-- ⚠️ 重要！請根據實際配置修改起訖值
DECLARE @as_start_no bigint = 92211786619;  -- 起始單號
DECLARE @as_end_no bigint   = 92211791618;  -- 終止單號（這裡示範產生5000組）

-- 建立暫存資料表，用於預先檢查資料
IF OBJECT_ID('tempdb..#temp_MAIL_SERIAL_NO') IS NOT NULL 
    DROP TABLE #temp_MAIL_SERIAL_NO;

-- 複製資料表結構
SELECT TOP 0 * 
INTO #temp_MAIL_SERIAL_NO 
FROM [dbo].[MAIL_SERIAL_NO];

-- 初始化當前序號
DECLARE @curr_no bigint = @as_start_no;

-- 開始迴圈產生單號
WHILE (@curr_no <= @as_end_no)
BEGIN
    INSERT INTO #temp_MAIL_SERIAL_NO
        ([SERIAL_NO], [CHECK_NO], [IS_USE], [CREATE_DATE])
    VALUES
        (
            -- 產生11碼流水號（左補0）
            RIGHT('00000000000' + LTRIM(STR(@curr_no, 11)), 11),
            -- 計算確認碼（取餘數3）
            @curr_no % 3,
            -- 預設為未使用
            'N',
            -- 記錄產生時間
            GETDATE()
        );
    
    -- 序號遞增
    SET @curr_no += 1;
END;

-- 檢查產生的資料是否正確
SELECT * FROM #temp_MAIL_SERIAL_NO;

-- 確認無誤後，再插入正式資料表
-- INSERT INTO [dbo].[MAIL_SERIAL_NO] SELECT [SERIAL_NO], [CHECK_NO], [IS_USE], [CREATE_DATE] FROM #temp_MAIL_SERIAL_NO;

🔍 程式碼重點說明

🎯 使用暫存表

先將資料寫入暫存表 #temp_MAIL_SERIAL_NO，確認無誤後再寫入正式表，這是一個好習慣，可以避免直接污染正式資料。

🔢 補零處理

使用 RIGHT('00000000000' + ..., 11) 確保流水號永遠是11碼，不足的部分會在左邊補0。

✅ 確認碼計算

使用模數運算 @curr_no % 3 計算確認碼，結果會是 0、1 或 2。

🔄 效能考量

若需產生超大量資料（如百萬筆），建議分批執行，或考慮使用數字輔助表（Tally Table）來提升效能。

⚠️ 重要提醒

務必檢查起訖值：執行前請再三確認 @as_start_no 和 @as_end_no 是否正確，避免產生錯誤的序號區間。
先用暫存表測試：正式執行前，請先檢查暫存表的資料是否符合預期，確認無誤再寫入正式表。
避免重複產生：執行前請先確認資料庫中是否已存在相同區間的單號，避免重複產生造成資料混亂。
備份資料：若是在正式環境執行，建議先備份資料表，以防萬一。

💡 驗證範例

讓我們驗證一下產生的單號是否正確：

流水號（11碼）	計算式	確認碼	完整單號（12碼）
92211786619	92211786619 % 3	1	922117866191
92211786620	92211786620 % 3	2	922117866202
92211786621	92211786621 % 3	0	922117866210

🎓 總結

透過這個實作範例,我們學會了如何在 SQL Server 中批次產生帶有驗證機制的序號。這個方法不僅適用於物流單號,也可以應用在發票號碼、會員編號、交易序號等各種需要序號管理的場景。

✨ 關鍵技術點回顧：

使用 WHILE 迴圈實現批次產生
透過暫存表確保資料安全
運用模數運算產生檢查碼
字串處理技巧（補零、格式化）

希望這篇文章能幫助到需要處理序號產生的開發者們。如果有任何問題或建議,歡迎在下方留言討論！

搜尋此網誌

Mike軟體工程雜記