Oracle中编写通过字符串输入向Z_FNAME表插入数据的存储过程

现有表结构 #

1. z_names表 #

CREATE TABLE z_names (
    ID number,
    NAME VARCHAR2(200)
);

2. Z_FNAME表 #

CREATE TABLE Z_FNAME (
    "FAMILY" VARCHAR2(200 BYTE), 
    "ID" NUMBER, 
    "NAME" VARCHAR2(200 BYTE), 
    "NAME_ID" NUMBER
);

需求梳理 #

• 存储过程接收字符串格式输入，输入格式示例：nam1;fam1,nam2;fam2
  • 逗号分隔不同待插入记录
  • 分号分隔单条记录的NAME和FAMILY字段值
• Z_FNAME的ID字段由已存在的触发器自动生成，存储过程无需处理
• Z_FNAME的NAME_ID字段取值匹配z_names表中对应NAME的ID值

存储过程实现代码 #

CREATE OR REPLACE PROCEDURE INSERT_Z_FNAME(p_input_str IN VARCHAR2)
IS
    -- 定义存储单条记录的结构体和集合
    TYPE t_single_rec IS RECORD(
        v_name VARCHAR2(200),
        v_family VARCHAR2(200)
    );
    TYPE t_rec_list IS TABLE OF t_single_rec INDEX BY PLS_INTEGER;
    v_rec_list t_rec_list;

    v_temp_str VARCHAR2(4000) := p_input_str;
    v_current_rec VARCHAR2(4000);
    v_sep_index NUMBER;
    v_match_nameid z_names.ID%TYPE;
BEGIN
    -- 第一层循环拆分所有逗号分隔的记录
    WHILE LENGTH(v_temp_str) > 0 LOOP
        v_sep_index := INSTR(v_temp_str, ',');
        -- 处理最后一条无逗号后缀的记录
        IF v_sep_index = 0 THEN
            v_current_rec := v_temp_str;
            v_temp_str := '';
        ELSE
            v_current_rec := SUBSTR(v_temp_str, 1, v_sep_index - 1);
            v_temp_str := SUBSTR(v_temp_str, v_sep_index + 1);
        END IF;

        -- 拆分单条记录的name和family
        v_sep_index := INSTR(v_current_rec, ';');
        IF v_sep_index > 0 THEN
            v_rec_list(v_rec_list.COUNT + 1).v_name := TRIM(SUBSTR(v_current_rec, 1, v_sep_index - 1));
            v_rec_list(v_rec_list.COUNT).v_family := TRIM(SUBSTR(v_current_rec, v_sep_index + 1));
        END IF;
    END LOOP;

    -- 循环插入数据
    FOR i IN 1..v_rec_list.COUNT LOOP
        -- 匹配z_names表获取name对应的ID
        SELECT ID INTO v_match_nameid FROM z_names WHERE NAME = v_rec_list(i).v_name;
        -- 插入目标表
        INSERT INTO Z_FNAME(FAMILY, NAME, NAME_ID)
        VALUES (v_rec_list(i).v_family, v_rec_list(i).v_name, v_match_nameid);
    END LOOP;

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001, '输入内容中存在z_names表未收录的NAME值，插入终止');
    WHEN TOO_MANY_ROWS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20002, 'z_names表存在重复NAME值，无法匹配唯一ID');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20003, '执行出错：' || SQLERRM);
END INSERT_Z_FNAME;
/

调用示例 #

-- 传入示例格式字符串即可完成批量插入
CALL INSERT_Z_FNAME('nam1;fam1,nam2;fam2');

注意事项 #

• 输入字符串总长度不能超过Oracle VARCHAR2默认最大长度4000字节，如需支持更长输入可调整参数为CLOB类型
• 若需支持未匹配到的NAME自动跳过而非终止全部插入，可将查询逻辑改为关联查询，去掉SELECT INTO的异常抛出逻辑即可

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