如何创建可插入多参数组合数据的Oracle存储过程？

原存储过程仅支持单条数据插入，代码如下：

CREATE OR REPLACE PROCEDURE INSERT_HOSPITAL_PROGRAMS 
    (HOSPITAL_ID IN INT, 
     SAMPLE_ID IN INT,
     PROGRAM_ID IN INT) 
IS
BEGIN
    INSERT INTO HAJJ_HOSPITAL_PROGRAMS (ID, HOSPITAL_ID, SAMPLE_ID, PROGRAM_ID)
    VALUES (seq.nextval , HOSPITAL_ID, SAMPLE_ID, PROGRAM_ID);
END;

需求是插入HOSPITAL_ID从1到20、SAMPLE_ID从1到10、PROGRAM_ID从1到6的所有组合数据，可通过以下两种方式修改存储过程：

方式一：基于笛卡尔积批量插入（高效无循环） #

直接利用Oracle层级查询生成所有组合，一次性完成插入，避免多次单条插入的性能损耗：

CREATE OR REPLACE PROCEDURE INSERT_HOSPITAL_PROGRAMS 
IS
BEGIN
    INSERT INTO HAJJ_HOSPITAL_PROGRAMS (ID, HOSPITAL_ID, SAMPLE_ID, PROGRAM_ID)
    SELECT seq.nextval, h.hospital_id, s.sample_id, p.program_id
    FROM 
        (SELECT LEVEL AS hospital_id FROM DUAL CONNECT BY LEVEL <= 20) h,
        (SELECT LEVEL AS sample_id FROM DUAL CONNECT BY LEVEL <= 10) s,
        (SELECT LEVEL AS program_id FROM DUAL CONNECT BY LEVEL <= 6) p;
    COMMIT; -- 根据业务需求决定是否在过程内提交事务
END;

说明： #

• 三个子查询分别生成对应范围的序列，通过笛卡尔积得到所有维度的组合
• 用seq.nextval自动生成ID列的自增值
• 若需灵活配置范围，可给存储过程添加参数（如p_max_hospital INT DEFAULT 20）替换硬编码数值

方式二：嵌套循环实现（逻辑直观） #

如果需要显式的循环逻辑，可使用三层嵌套循环遍历所有组合：

CREATE OR REPLACE PROCEDURE INSERT_HOSPITAL_PROGRAMS 
IS
    v_hospital_id INT;
    v_sample_id INT;
    v_program_id INT;
BEGIN
    FOR v_hospital_id IN 1..20 LOOP
        FOR v_sample_id IN 1..10 LOOP
            FOR v_program_id IN 1..6 LOOP
                INSERT INTO HAJJ_HOSPITAL_PROGRAMS (ID, HOSPITAL_ID, SAMPLE_ID, PROGRAM_ID)
                VALUES (seq.nextval, v_hospital_id, v_sample_id, v_program_id);
            END LOOP;
        END LOOP;
    END LOOP;
    COMMIT; -- 根据业务需求决定是否提交事务
END;

说明： #

• 外层循环遍历HOSPITAL_ID，中层遍历SAMPLE_ID，内层遍历PROGRAM_ID
• 每次循环插入单条数据，适合小批量场景，数据量大时优先选择方式一

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