如何在PL/SQL中实现全球通用的电话号码格式化？

原存储过程通过硬编码字符串拼接实现了固定格式的号码格式化，仅适用于北美地区的(XXX)XXX-XXXX格式。要适配全球不同国家的号码格式，核心思路是引入国家代码参数，并建立对应格式规则的映射逻辑，以下是两种可行的改造方案：

方案一：基于CASE语句的轻量实现 #

这种方案无需额外数据库对象，直接通过CASE分支匹配不同国家的格式规则，适合国家数量较少、格式相对固定的场景。

改造后代码 #

CREATE OR REPLACE PROCEDURE format_phone
(
    phone_no IN OUT VARCHAR2,
    country_code IN VARCHAR2 DEFAULT 'US' -- 默认使用美国格式
) IS
    cleaned_phone VARCHAR2(20); -- 存储清理后的纯数字号码
BEGIN
    -- 第一步：预处理，移除号码中的所有非数字字符（空格、括号、连字符等）
    cleaned_phone := REGEXP_REPLACE(phone_no, '[^0-9]', '');
    
    -- 根据国家代码匹配对应格式规则
    CASE UPPER(country_code)
        WHEN 'US' THEN -- 美国：(XXX) XXX-XXXX
            phone_no := '(' || SUBSTR(cleaned_phone, 1, 3) || ') ' || SUBSTR(cleaned_phone, 4, 3) || '-' || SUBSTR(cleaned_phone, 7);
        WHEN 'GB' THEN -- 英国：区分带国际区号和本地号码
            IF LENGTH(cleaned_phone) = 11 THEN -- 带+44的国际号码
                phone_no := '+44 ' || SUBSTR(cleaned_phone, 3, 5) || ' ' || SUBSTR(cleaned_phone, 8);
            ELSE -- 本地号码（示例为XX XXX XXXX格式）
                phone_no := SUBSTR(cleaned_phone, 1, 2) || ' ' || SUBSTR(cleaned_phone, 3, 3) || ' ' || SUBSTR(cleaned_phone, 6);
            END IF;
        WHEN 'CN' THEN -- 中国：XXX-XXXX-XXXX（11位手机号）
            IF LENGTH(cleaned_phone) = 11 THEN
                phone_no := SUBSTR(cleaned_phone, 1, 3) || '-' || SUBSTR(cleaned_phone, 4, 4) || '-' || SUBSTR(cleaned_phone, 8);
            ELSE -- 固定电话：XXXX-XXXXXXX
                phone_no := SUBSTR(cleaned_phone, 1, 4) || '-' || SUBSTR(cleaned_phone, 5);
            END IF;
        WHEN 'DE' THEN -- 德国：XXX XXX XXX
            phone_no := SUBSTR(cleaned_phone, 1, 3) || ' ' || SUBSTR(cleaned_phone, 4, 3) || ' ' || SUBSTR(cleaned_phone, 7);
        ELSE -- 不支持的国家代码抛出异常
            RAISE_APPLICATION_ERROR(-20001, '不支持的国家代码：' || country_code);
    END CASE;
END format_phone;
/

方案二：基于配置表的可扩展实现 #

如果需要支持大量国家，或者格式规则可能频繁变动，建议使用配置表存储格式规则，避免每次修改都要变更存储过程代码。

步骤1：创建格式规则配置表 #

CREATE TABLE phone_format_rules (
    country_code VARCHAR2(3) PRIMARY KEY, -- 国家代码（如US、CN）
    format_mask VARCHAR2(50) NOT NULL, -- 格式掩码，用X代表数字位
    min_length NUMBER(3) NOT NULL, -- 号码最小长度（纯数字）
    max_length NUMBER(3) NOT NULL -- 号码最大长度（纯数字）
);

-- 插入初始规则数据
INSERT INTO phone_format_rules VALUES ('US', '(XXX) XXX-XXXX', 10, 10);
INSERT INTO phone_format_rules VALUES ('GB', '+44 XXX XXXXX', 11, 11);
INSERT INTO phone_format_rules VALUES ('CN', 'XXX-XXXX-XXXX', 11, 11);
INSERT INTO phone_format_rules VALUES ('DE', 'XXX XXX XXX', 9, 9);
COMMIT;

步骤2：改造存储过程读取配置表 #

CREATE OR REPLACE PROCEDURE format_phone
(
    phone_no IN OUT VARCHAR2,
    country_code IN VARCHAR2 DEFAULT 'US'
) IS
    cleaned_phone VARCHAR2(20);
    v_format_mask VARCHAR2(50);
    v_min_length NUMBER(3);
    v_max_length NUMBER(3);
    formatted_phone VARCHAR2(50);
    digit_pos NUMBER := 1; -- 跟踪纯数字号码的当前位置
BEGIN
    -- 预处理：清理非数字字符
    cleaned_phone := REGEXP_REPLACE(phone_no, '[^0-9]', '');
    
    -- 从配置表获取对应国家的格式规则
    SELECT format_mask, min_length, max_length
    INTO v_format_mask, v_min_length, v_max_length
    FROM phone_format_rules
    WHERE UPPER(country_code) = UPPER(country_code);
    
    -- 验证号码长度是否符合规则
    IF LENGTH(cleaned_phone) NOT BETWEEN v_min_length AND v_max_length THEN
        RAISE_APPLICATION_ERROR(-20002, '号码长度不符合' || country_code || '的格式要求');
    END IF;
    
    -- 根据掩码替换生成格式化号码
    formatted_phone := v_format_mask;
    FOR i IN 1..LENGTH(formatted_phone) LOOP
        IF SUBSTR(formatted_phone, i, 1) = 'X' THEN
            -- 逐个替换掩码中的X为对应数字
            formatted_phone := REPLACE(formatted_phone, 'X', SUBSTR(cleaned_phone, digit_pos, 1), 1, 1);
            digit_pos := digit_pos + 1;
        END IF;
    END LOOP;
    
    phone_no := formatted_phone;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, '不支持的国家代码：' || country_code);
END format_phone;
/

关键注意事项 #

1. 号码预处理：必须先清理掉输入号码中的非数字字符，避免因输入格式杂乱导致拼接错误。
2. 格式规则覆盖：可以根据实际需求扩展更多国家的格式规则，比如法国、日本等，两种方案都能轻松适配。
3. 异常处理：针对不支持的国家代码、不符合长度要求的号码，抛出明确的自定义异常，方便调用方排查问题。

内容的提问来源于stack exchange，提问作者Abrham IT