PLSQL中使用BULK INSERT批量插入随机手机号与CNIC数据方案问询

前置说明 #

以下实现基于Oracle PLSQL环境，假设你需要插入的表基础结构、索引已经创建完成，1000条数据量级较小，不需要额外做索引禁用重建操作，插入过程中Oracle会自动维护索引。

实现步骤 #

1. 参考表和索引结构 #

如果还未创建表和索引，可参考以下语句创建：

-- 示例业务表
CREATE TABLE user_info (
    id NUMBER PRIMARY KEY,
    mobile VARCHAR2(11),
    CNIC VARCHAR2(18)
);
-- 按需创建业务查询需要的索引
CREATE INDEX idx_user_mobile ON user_info(mobile);
CREATE INDEX idx_user_cnic ON user_info(CNIC);

注意：如果mobile或CNIC字段建的是唯一索引，需要在随机生成逻辑中增加去重判断，避免触发唯一约束报错。

2. 批量插入PLSQL代码 #

采用PLSQL的FORALL批量DML特性（也就是你说的BULK INSERT实现），相比逐行循环插入可减少PLSQL和SQL引擎的上下文切换，性能提升数倍：

DECLARE
    -- 定义存储批量数据的集合类型
    TYPE t_user_arr IS TABLE OF user_info%ROWTYPE INDEX BY PLS_INTEGER;
    v_user_arr t_user_arr;
    v_insert_count NUMBER := 1000; -- 自定义插入条数
BEGIN
    -- 循环生成随机数据存入集合
    FOR i IN 1..v_insert_count LOOP
        -- id字段如果用自增序列，可替换为 序列名.NEXTVAL
        v_user_arr(i).id := i;
        -- 生成11位中国大陆格式随机手机号，号段取常见的13/15/17/18/19开头
        v_user_arr(i).mobile := '1' || TRUNC(DBMS_RANDOM.VALUE(3,9)) || LPAD(TRUNC(DBMS_RANDOM.VALUE(0,999999999)),9,'0');
        -- 生成18位中国大陆身份证格式随机CNIC，可根据你需要的格式调整规则
        v_user_arr(i).CNIC := LPAD(TRUNC(DBMS_RANDOM.VALUE(100000,999999)),6,'0') -- 前6位地址码
                            || TO_CHAR(ADD_MONTHS(SYSDATE, -TRUNC(DBMS_RANDOM.VALUE(18*12,70*12))), 'YYYYMMDD') -- 8位生日，年龄范围18-70岁
                            || LPAD(TRUNC(DBMS_RANDOM.VALUE(0,999)),3,'0') -- 3位顺序码
                            || DBMS_RANDOM.STRING('X',1); -- 最后1位校验位，如需符合国标校验规则可额外加校验逻辑
    END LOOP;

    -- 批量执行插入
    FORALL i IN 1..v_user_arr.COUNT
        INSERT INTO user_info VALUES v_user_arr(i);
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('插入成功，总条数：' || v_user_arr.COUNT);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('插入失败，错误信息：' || SQLERRM);
        RAISE;
END;
/

3. 结果验证 #

插入完成后可执行以下语句确认数据：

-- 统计总条数
SELECT COUNT(*) FROM user_info;
-- 查看前10条样例数据
SELECT * FROM user_info WHERE ROWNUM <= 10;

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