如何实现首位非0的10位唯一手机号生成函数并查重插入？

问题分析 #

你现有的代码能生成10位随机数字串，但存在两个核心问题：一是可能生成0开头的无效号码，二是没有查重机制，也未封装为可复用的函数。以下是完善后的解决方案：

完整实现代码 #

1. 确认表结构（你已创建，此处仅作校验）

CREATE TABLE PHONE_NUMBERS(PHONE_NUMBER NUMBER);

2. 创建生成唯一手机号的函数

CREATE OR REPLACE FUNCTION generate_unique_phone RETURN NUMBER IS
    v_phone NUMBER;
    v_is_exists NUMBER;
    MAX_ATTEMPTS CONSTANT NUMBER := 10; -- 避免无限循环的最大尝试次数
    v_attempt_count NUMBER := 0;
BEGIN
    LOOP
        v_attempt_count := v_attempt_count + 1;
        
        -- 生成10位手机号：首位1-9，后9位0-9随机
        SELECT TO_NUMBER(
            -- 首位：生成1-9的整数，确保非0
            TRUNC(DBMS_RANDOM.VALUE(1, 10)) ||
            -- 后9位：生成9个0-9的随机数字并拼接
            LISTAGG(TRUNC(DBMS_RANDOM.VALUE(0, 10)), '') WITHIN GROUP (ORDER BY DBMS_RANDOM.RANDOM)
        ) INTO v_phone
        FROM DUAL
        CONNECT BY LEVEL <= 9;
        
        -- 检查号码是否已存在（NOT EXISTS比COUNT(*)更高效）
        SELECT CASE 
                WHEN NOT EXISTS (SELECT 1 FROM PHONE_NUMBERS WHERE PHONE_NUMBER = v_phone)
                THEN 0 
                ELSE 1 
               END INTO v_is_exists
        FROM DUAL;
        
        -- 号码不存在或尝试次数达上限时退出循环
        EXIT WHEN v_is_exists = 0 OR v_attempt_count >= MAX_ATTEMPTS;
    END LOOP;
    
    -- 返回有效号码，若尝试失败则返回NULL
    RETURN CASE WHEN v_is_exists = 0 THEN v_phone ELSE NULL END;
END;
/

3. 调用函数插入唯一手机号

通过PL/SQL块安全插入，避免重复调用函数导致的不一致：

DECLARE
    v_new_phone NUMBER;
BEGIN
    v_new_phone := generate_unique_phone();
    
    IF v_new_phone IS NOT NULL THEN
        INSERT INTO PHONE_NUMBERS (PHONE_NUMBER) VALUES (v_new_phone);
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('成功插入手机号：' || v_new_phone);
    ELSE
        DBMS_OUTPUT.PUT_LINE('生成失败：已达最大尝试次数，未找到可用的唯一手机号');
    END IF;
END;
/

关键细节说明 #

• 首位非0保障：用TRUNC(DBMS_RANDOM.VALUE(1, 10))生成1-9的整数作为手机号首位，彻底规避0开头的无效格式。
• 高效查重：使用NOT EXISTS语句检查号码是否已存在，找到匹配记录后立即终止查询，比COUNT(*)性能更优。
• 循环重试机制：设置最大尝试次数，避免因表中号码接近饱和时出现无限循环的情况。

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