Oracle中编写字符串分割后批量插入z_names表的存储过程问题

前置环境确认 #

你的环境已提前配置好以下对象，无需额外创建：

• 数据表 z_names：存储拆分后的名称数据，建表语句为 CREATE TABLE z_names (ID number, NAME VARCHAR2(200))
• 序列 z_names_seq：用于生成ID主键，创建语句为 CREATE SEQUENCE z_names_seq MINVALUE 1 START WITH 1 INCREMENT BY 1;
• 前置触发器 z_names_on_insert：插入数据时自动调用序列填充ID字段，无需手动赋值ID

存储过程实现代码 #

CREATE OR REPLACE PROCEDURE sp_split_str_insert(p_input_str IN VARCHAR2)
IS
BEGIN
  -- 拆分逗号分隔字符串，过滤前后空格与空值后批量插入
  INSERT INTO z_names(NAME)
  SELECT TRIM(REGEXP_SUBSTR(p_input_str, '[^,]+', 1, LEVEL)) AS split_name
  FROM DUAL
  CONNECT BY LEVEL <= REGEXP_COUNT(p_input_str, ',') + 1
  AND TRIM(REGEXP_SUBSTR(p_input_str, '[^,]+', 1, LEVEL)) IS NOT NULL;
  
  COMMIT;
EXCEPTION
  -- 异常处理：出错回滚事务并抛出异常
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

代码说明 #

• 输入参数 p_input_str 为传入的逗号分隔字符串，支持逗号前后带空格的格式，TRIM 函数会自动清除多余空格
• 拆分逻辑用 Oracle 原生正则函数实现，无需额外自定义拆分函数
• 自动过滤空值：如果输入字符串存在连续逗号、首尾逗号的情况，不会插入空的NAME记录
• 插入时仅需指定NAME字段，ID由已配置的触发器自动生成

调用&验证示例 #

1. 调用存储过程传入测试字符串：

CALL sp_split_str_insert('john, jim, jack');

2. 验证插入结果：

SELECT * FROM z_names;

返回结果如下：

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