Oracle中如何对拼接生成的虚拟列执行INSERT/UPDATE操作

核心逻辑 #

通过字符串定位和截取函数，将.分隔的产品编号拆分为三段，分别赋值给三个原始字段即可完成插入、更新操作。

1. 基础拆分方案（兼容所有Oracle版本） #

使用INSTR定位分隔符位置，配合SUBSTR截取对应分段，性能最优。

INSERT 示例

INSERT INTO your_table (id_part1, id_part2, id_part3)
VALUES (
    -- 截取第一个分隔符前的内容作为id_part1
    SUBSTR(:input_product_num, 1, INSTR(:input_product_num, '.', 1, 1) - 1),
    -- 截取两个分隔符中间的内容作为id_part2
    SUBSTR(:input_product_num, INSTR(:input_product_num, '.', 1, 1) + 1, INSTR(:input_product_num, '.', 1, 2) - INSTR(:input_product_num, '.', 1, 1) - 1),
    -- 截取第二个分隔符后的内容作为id_part3
    SUBSTR(:input_product_num, INSTR(:input_product_num, '.', 1, 2) + 1)
);

UPDATE 示例

UPDATE your_table
SET 
    id_part1 = SUBSTR(:new_product_num, 1, INSTR(:new_product_num, '.', 1, 1) - 1),
    id_part2 = SUBSTR(:new_product_num, INSTR(:new_product_num, '.', 1, 1) + 1, INSTR(:new_product_num, '.', 1, 2) - INSTR(:new_product_num, '.', 1, 1) - 1),
    id_part3 = SUBSTR(:new_product_num, INSTR(:new_product_num, '.', 1, 2) + 1)
WHERE 你的过滤条件;

2. 简洁拆分方案（Oracle 12c及以上版本可用） #

使用正则函数REGEXP_SUBSTR拆分，代码可读性更高：

-- 三段拆分语法
id_part1 = REGEXP_SUBSTR(:input_product_num, '[^.]+', 1, 1),
id_part2 = REGEXP_SUBSTR(:input_product_num, '[^.]+', 1, 2),
id_part3 = REGEXP_SUBSTR(:input_product_num, '[^.]+', 1, 3)

注意事项 #

• 可添加格式校验逻辑避免非法输入报错，例如通过REGEXP_COUNT(:input_product_num, '\.') = 2判断输入的产品编号是否恰好有2个分隔符，不符合规则时可抛出错误或赋值默认值。
• 代码中:input_product_num、:new_product_num为绑定变量，替换为实际传入的产品编号值即可。

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