Oracle 實現漢字拼音首字母查詢：自訂函數完整實作

需求場景

業務系統常需支援「輸入拼音首字母即可模糊查詢中文姓名/地址」的功能。Oracle 原生無內建拼音轉換函數，但可利用 NLSSORT 搭配 SCHINESE_PINYIN_M 排序規則，依據漢字在拼音排序中的區間，推導出首字母。

核心原理

1. NLSSORT(char, 'NLS_SORT=SCHINESE_PINYIN_M') 會依拼音順序產生可比較的二進位排序鍵。
2. 所有拼音以 a 開頭的漢字，其排序鍵落在 '吖' 到 '驁' 之間；以 b 開頭落在 '八' 到 '簿' 之間……以此類推。
3. 將輸入字串逐字拆解，比對各字元排序鍵所在區間，即可拼湊出首字母字串。

完整函數實作

CREATE OR REPLACE FUNCTION F_TRANS_PINYIN_CAPITAL(P_NAME IN VARCHAR2)
RETURN VARCHAR2 AS
  V_COMPARE VARCHAR2(100);
  V_RETURN  VARCHAR2(4000);

  FUNCTION F_NLSSORT(P_WORD IN VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    RETURN NLSSORT(P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
  END;
BEGIN
  FOR I IN 1..LENGTH(P_NAME) LOOP
    V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, I, 1));
    IF V_COMPARE >= F_NLSSORT('吖') AND V_COMPARE <= F_NLSSORT('驁') THEN V_RETURN := V_RETURN || 'a';
    ELSIF V_COMPARE >= F_NLSSORT('八') AND V_COMPARE <= F_NLSSORT('簿') THEN V_RETURN := V_RETURN || 'b';
    ELSIF V_COMPARE >= F_NLSSORT('嚓') AND V_COMPARE <= F_NLSSORT('錯') THEN V_RETURN := V_RETURN || 'c';
    ELSIF V_COMPARE >= F_NLSSORT('咑') AND V_COMPARE <= F_NLSSORT('鵽') THEN V_RETURN := V_RETURN || 'd';
    ELSIF V_COMPARE >= F_NLSSORT('妸') AND V_COMPARE <= F_NLSSORT('樲') THEN V_RETURN := V_RETURN || 'e';
    ELSIF V_COMPARE >= F_NLSSORT('發') AND V_COMPARE <= F_NLSSORT('猤') THEN V_RETURN := V_RETURN || 'f';
    ELSIF V_COMPARE >= F_NLSSORT('旮') AND V_COMPARE <= F_NLSSORT('腂') THEN V_RETURN := V_RETURN || 'g';
    ELSIF V_COMPARE >= F_NLSSORT('妎') AND V_COMPARE <= F_NLSSORT('夻') THEN V_RETURN := V_RETURN || 'h';
    ELSIF V_COMPARE >= F_NLSSORT('丌') AND V_COMPARE <= F_NLSSORT('攈') THEN V_RETURN := V_RETURN || 'j';
    ELSIF V_COMPARE >= F_NLSSORT('咔') AND V_COMPARE <= F_NLSSORT('穒') THEN V_RETURN := V_RETURN || 'k';
    ELSIF V_COMPARE >= F_NLSSORT('垃') AND V_COMPARE <= F_NLSSORT('擽') THEN V_RETURN := V_RETURN || 'l';
    ELSIF V_COMPARE >= F_NLSSORT('嘸') AND V_COMPARE <= F_NLSSORT('椧') THEN V_RETURN := V_RETURN || 'm';
    ELSIF V_COMPARE >= F_NLSSORT('拏') AND V_COMPARE <= F_NLSSORT('瘧') THEN V_RETURN := V_RETURN || 'n';
    ELSIF V_COMPARE >= F_NLSSORT('筽') AND V_COMPARE <= F_NLSSORT('漚') THEN V_RETURN := V_RETURN || 'o';
    ELSIF V_COMPARE >= F_NLSSORT('妑') AND V_COMPARE <= F_NLSSORT('曝') THEN V_RETURN := V_RETURN || 'p';
    ELSIF V_COMPARE >= F_NLSSORT('七') AND V_COMPARE <= F_NLSSORT('裠') THEN V_RETURN := V_RETURN || 'q';
    ELSIF V_COMPARE >= F_NLSSORT('亽') AND V_COMPARE <= F_NLSSORT('鶸') THEN V_RETURN := V_RETURN || 'r';
    ELSIF V_COMPARE >= F_NLSSORT('仨') AND V_COMPARE <= F_NLSSORT('蜶') THEN V_RETURN := V_RETURN || 's';
    ELSIF V_COMPARE >= F_NLSSORT('侤') AND V_COMPARE <= F_NLSSORT('籜') THEN V_RETURN := V_RETURN || 't';
    ELSIF V_COMPARE >= F_NLSSORT('屲') AND V_COMPARE <= F_NLSSORT('鶩') THEN V_RETURN := V_RETURN || 'w';
    ELSIF V_COMPARE >= F_NLSSORT('夕') AND V_COMPARE <= F_NLSSORT('鑂') THEN V_RETURN := V_RETURN || 'x';
    ELSIF V_COMPARE >= F_NLSSORT('丫') AND V_COMPARE <= F_NLSSORT('韻') THEN V_RETURN := V_RETURN || 'y';
    ELSIF V_COMPARE >= F_NLSSORT('帀') AND V_COMPARE <= F_NLSSORT('咗') THEN V_RETURN := V_RETURN || 'z';
    END IF;
  END LOOP;
  RETURN V_RETURN;
END;
/

使用範例

-- 測試
SELECT F_TRANS_PINYIN_CAPITAL('羅華') FROM DUAL;  -- 回傳 'lh'

-- 實際查詢：支援中文或拼音首字母雙模式
SELECT name FROM users
WHERE name = :input
   OR F_TRANS_PINYIN_CAPITAL(name) = :input;

傳入「羅華」查中文全匹配；傳入「lh」查拼音首字母匹配。

效能優化建議

• 若查詢量大，建議在表中新增 PY_INITIALS VARCHAR2(100) 欄位，寫入時由 Trigger 自動填充，並建立索引，避免查詢時逐行呼叫函數導致全表掃描。
• 12c 起可將函數標記 DETERMINISTIC 並建立函數索引：
  CREATE INDEX idx_users_py ON users (F_TRANS_PINYIN_CAPITAL(name));

注意事項

• 區間邊界字元需與資料庫字元集（如 AL32UTF8、ZHS16GBK）一致，跨字元集遷移時需重新驗證邊界值。
• 生僻字、多音字、非漢字字元（英文、數字、符號）會直接略過，不產出字母。