Oracle带输入参数的存储过程：用SUBSTR批量插入逗号分隔的员工姓名

没问题，我给你写一个完全符合需求的Oracle存储过程，用SUBSTR配合INSTR来拆分逗号分隔的姓名字符串，然后逐个插入到Employee表中。下面是完整实现和详细说明：

完整存储过程代码 #

CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEES_BY_NAMES(p_names IN VARCHAR2)
IS
    v_start_pos NUMBER := 1;
    v_comma_pos NUMBER;
    v_employee_name VARCHAR2(100); -- 可根据Employee表的employee_name字段长度调整
BEGIN
    -- 循环拆分输入的姓名字符串
    LOOP
        -- 定位下一个逗号的位置
        v_comma_pos := INSTR(p_names, ',', v_start_pos);
        
        IF v_comma_pos = 0 THEN
            -- 处理最后一个姓名（没有后续逗号的情况）
            v_employee_name := TRIM(SUBSTR(p_names, v_start_pos));
            -- 插入数据，这里用序列生成employee_id，按需替换
            INSERT INTO Employee (employee_id, employee_name)
            VALUES (EMPLOYEE_ID_SEQ.NEXTVAL, v_employee_name);
            EXIT; -- 退出循环
        ELSE
            -- 提取当前逗号前的姓名
            v_employee_name := TRIM(SUBSTR(p_names, v_start_pos, v_comma_pos - v_start_pos));
            INSERT INTO Employee (employee_id, employee_name)
            VALUES (EMPLOYEE_ID_SEQ.NEXTVAL, v_employee_name);
            -- 更新起始位置到下一个逗号之后
            v_start_pos := v_comma_pos + 1;
        END IF;
    END LOOP;
    
    COMMIT; -- 提交所有插入操作
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- 出错时回滚所有操作
        RAISE; -- 抛出异常，让调用者感知错误
END INSERT_EMPLOYEES_BY_NAMES;
/

代码说明 #

• 输入参数：p_names IN VARCHAR2 用来接收逗号分隔的姓名字符串，比如'张三,李四,王五'。
• 变量作用：
  • v_start_pos：记录每次截取姓名的起始索引，初始值为1（Oracle字符串索引从1开始）。
  • v_comma_pos：存储每次找到的逗号位置，用来确定当前姓名的结束位置。
  • v_employee_name：临时存储提取出的单个姓名，用TRIM去除前后空格，避免插入无效的空白姓名。
• 循环逻辑：
  • 用INSTR函数查找下一个逗号的位置，如果返回0，说明已经到了最后一个姓名，截取后插入并退出循环。
  • 如果找到逗号，就从起始位置到逗号前截取字符串，得到单个姓名，插入后更新起始位置到逗号的下一个字符，继续循环处理下一个姓名。
• 主键处理：这里假设你用序列EMPLOYEE_ID_SEQ来生成自增的employee_id。如果你的表是Oracle 12c+版本，用了IDENTITY列定义主键，可以把插入语句改成：

  INSERT INTO Employee (employee_name) VALUES (v_employee_name);
  

  让数据库自动生成主键值。

使用示例 #

你可以这样调用这个存储过程：

-- 直接调用，插入三个员工姓名
EXEC INSERT_EMPLOYEES_BY_NAMES('张三,李四,王五');

或者在PL/SQL块中调用：

BEGIN
    INSERT_EMPLOYEES_BY_NAMES('Alice,Bob,Charlie');
END;
/

注意事项 #

• 如果输入字符串存在连续逗号（比如'张三,,李四'），会提取出空字符串并插入。如果要避免这种情况，可以在插入前加一个判断：

  IF v_employee_name IS NOT NULL AND v_employee_name <> '' THEN
      INSERT INTO Employee (employee_id, employee_name)
      VALUES (EMPLOYEE_ID_SEQ.NEXTVAL, v_employee_name);
  END IF;
  

• 请根据Employee表中employee_name字段的实际长度，调整v_employee_name的定义长度（比如字段是VARCHAR2(50)，就改成v_employee_name VARCHAR2(50)），避免字符串截断。

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