您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server CLR 整合開發語言輔助函數：數字轉中文/英文文字完整實作

編輯：關於SqlServer

背景動機

全球化應用中，數字轉文字 (支票列印、合約金額) 是常見需求。Oracle 以 TO_CHAR(TO_DATE(n,'J'),'Jsp') 實現，但有限制：

Julian 日期上限 5373484 (西元 9999 年)
無法產生「零」
忽略 NLS 語言設定，固定輸出美式英文

解決架構：規則驅動的泛化引擎

建立兩張規則表，支援多語言、可擴展：

1. 基礎詞彙與例外表

CREATE TABLE numwords (
    lang VARCHAR2(2),      -- 語言代碼: EN, ZH, ES...
    num  INTEGER,          -- 數字值
    word VARCHAR2(30),     -- 對應文字
    CONSTRAINT numwords_pk PRIMARY KEY (lang, num)
);

-- 範例資料 (英文)
INSERT INTO numwords VALUES ('EN', 0, 'Zero');
INSERT INTO numwords VALUES ('EN', 1, 'One');
-- ... 1-19, 20,30,...90, 100, 1000, 1000000...

2. 模板規則表

CREATE TABLE numrules (
    lang      VARCHAR2(2),
    seq       INTEGER,       -- 規則優先序
    p1        INTEGER,       -- 高位數位數
    p2        INTEGER,       -- 低位數位數
    temp0     VARCHAR2(30),  -- 特殊模板 (如 11-19)
    temp      VARCHAR2(30),  -- 一般模板
    CONSTRAINT numrules_pk PRIMARY KEY (lang, seq)
);

-- 範例規則 (英文)
-- 1-19 特殊處理
INSERT INTO numrules VALUES ('EN', 0, 1, 1, '~2teen', NULL);   -- 13 → Thirteen
-- 20-99
INSERT INTO numrules VALUES ('EN', 1, 1, 1, '~1ty', '~1ty-~2'); -- 45 → Forty-Five
-- 百位
INSERT INTO numrules VALUES ('EN', 2, 2, 2, '~1 hundred', '~1 hundred ~2');
-- 千位
INSERT INTO numrules VALUES ('EN', 3, 3, 3, '~1 thousand', '~1 thousand ~2');
-- 百萬
INSERT INTO numrules VALUES ('EN', 4, 6, 6, '~1 million', '~1 million ~2');

核心函數套件

CREATE OR REPLACE PACKAGE genword AS
    FUNCTION get_word(n NUMBER) RETURN VARCHAR2;
    FUNCTION cardinal(n NUMBER) RETURN VARCHAR2;
END genword;
/

CREATE OR REPLACE PACKAGE BODY genword AS
    FUNCTION get_word(n NUMBER) RETURN VARCHAR2 IS
        l_word numwords.word%TYPE;
    BEGIN
        SELECT word INTO l_word FROM numwords
        WHERE lang = SYS_CONTEXT('USERENV','LANG') AND num = n;
        RETURN l_word;
    EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END;

    FUNCTION cardinal(n NUMBER) RETURN VARCHAR2 IS
        p NUMBER; t VARCHAR2(30); v NUMBER; l_word numwords.word%TYPE;
    BEGIN
        IF n < 0 THEN
            l_word := get_word(-1);  -- "Minus"
            RETURN l_word || ' ' || cardinal(-n);
        END IF;
        
        l_word := get_word(n);
        IF l_word IS NOT NULL THEN RETURN l_word; END IF;

        FOR r IN (SELECT * FROM numrules 
                  WHERE lang = SYS_CONTEXT('USERENV','LANG') ORDER BY seq) LOOP
            IF LENGTH(TO_CHAR(n)) <= r.p1 + r.p2 THEN
                p := POWER(10, r.p2);
                v := MOD(n, p);
                
                IF r.seq = 0 THEN  -- 特殊規則 (如 11-19)
                    IF n < 20 THEN RETURN REPLACE(r.temp0, '~2', cardinal(v)); END IF;
                ELSE
                    IF v = 0 THEN
                        RETURN REPLACE(r.temp0, '~1', cardinal(n/p));
                    ELSE
                        RETURN REPLACE(REPLACE(NVL(r.temp, '~1 ~2'), 
                            '~1', cardinal(n-v)), '~2', cardinal(v));
                    END IF;
                END IF;
            END IF;
        END LOOP;
        RETURN 'NUMBER TOO LARGE';
    END cardinal;
END genword;
/

SQL Server CLR 移植要點

在 SQL Server 2005+ 中以 C# 實現相同邏輯：

1. 專案類型

Visual Studio → SQL Server Database Project / Class Library
目標框架：.NET Framework 3.5+ (SQL Server 相容)

2. 核心類別結構

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000)]
public struct NumberToWords : INullable, IBinarySerialize
{
    private string _language;  // "EN", "ZH", "ES"
    private readonly Dictionary _words;
    private readonly List _rules;

    public static NumberToWords Null { get { return new NumberToWords { _language = "EN" }; } }
    public bool IsNull { get { return false; } }

    // 實作 INullable, IBinarySerialize, Parse, ToString
    // 核心邏輯移植自 PL/SQL 版本
}

3. 部署腳本

CREATE ASSEMBLY NumberToWordsAssembly
FROM 'C:\Deploy\NumberToWords.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE TYPE dbo.NumberToWords
EXTERNAL NAME NumberToWordsAssembly.NumberToWords;
GO

-- 使用範例
DECLARE @n NumberToWords = 'EN:12345';
SELECT @n.ToString();  -- Twelve Thousand Three Hundred Forty-Five

多語言擴展範例：中文

-- numwords 中文資料
INSERT INTO numwords VALUES ('ZH', 0, '零');
INSERT INTO numwords VALUES ('ZH', 1, '壹'); -- 財務大寫
INSERT INTO numwords VALUES ('ZH', 10, '拾');
INSERT INTO numwords VALUES ('ZH', 100, '佰');
INSERT INTO numwords VALUES ('ZH', 1000, '仟');
INSERT INTO numwords VALUES ('ZH', 10000, '萬');

-- numrules 中文規則 (簡化)
INSERT INTO numrules VALUES ('ZH', 0, 1, 1, '~2', NULL);      -- 11-19
INSERT INTO numrules VALUES ('ZH', 1, 1, 1, '~1拾', '~1拾~2'); -- 20-99
INSERT INTO numrules VALUES ('ZH', 2, 2, 2, '~1佰', '~1佰~2');
INSERT INTO numrules VALUES ('ZH', 3, 3, 3, '~1仟', '~1仟~2');
INSERT INTO numrules VALUES ('ZH', 4, 4, 4, '~1萬', '~1萬~2');