PL/SQL MERGE INTO语句报错，求助编写停用产品转移存储过程

问题背景 #

需创建存储过程，将PRODUCTS表中isdiscontinued=1的产品迁移至结构完全一致的Product_discontinued_[NTID]表，但编写的存储过程执行报错。

原存储过程代码：

PROCEDURE move_table AS
 begin
     merge into PRODUCTS_DISCONTINUTED b
     using  PRODUCTS a
     on (a.ID = b.ID)
     when matched then
       update set b.id = a.id,b.productname = a.productname,b.supplierid=a.supplierid,b.unitprice=a.unitprice,
                  b.package=a.package, b.isdiscontinuted=a.isdiscontinuted
        where a.isdiscontinuted = 1
     when not matched then
      insert (id,productname,supplierid,unitprice,package,isdiscontinuted)
      values( a.id,a.productname,a.supplierid,a.unitprice,a.package,a.isdiscontinuted)
      where a.isdiscontinuted = 1; END move_table;

报错原因及修复点 #

• MERGE语法不符合规范
  PL/SQL中，WHEN MATCHED THEN UPDATE后不能直接追加WHERE子句；WHEN NOT MATCHED THEN INSERT也不允许添加WHERE条件。正确做法是将isdiscontinued=1的筛选逻辑移到USING子查询中，提前过滤数据。
• 表名与需求不一致
  需求目标表为Product_discontinued_[NTID]，但代码中写的是PRODUCTS_DISCONTINUTED，需修正为目标表名（若[NTID]是动态变量，需用动态SQL处理）。
• 字段拼写错误
  代码中多处将isdiscontinued拼写成isdiscontinuted，需统一修正为正确字段名。
• 冗余更新操作
  UPDATE中设置b.id = a.id完全多余，因为ON条件已经保证a.ID = b.ID，无需重复赋值。

修复后的存储过程 #

固定表名版本（若[NTID]是固定值） #

PROCEDURE move_table AS
BEGIN
    MERGE INTO Product_discontinued_[NTID] b
    USING (
        SELECT id, productname, supplierid, unitprice, package, isdiscontinued
        FROM PRODUCTS
        WHERE isdiscontinued = 1
    ) a
    ON (a.ID = b.ID)
    WHEN MATCHED THEN
        UPDATE SET 
            b.productname = a.productname,
            b.supplierid = a.supplierid,
            b.unitprice = a.unitprice,
            b.package = a.package,
            b.isdiscontinued = a.isdiscontinued
    WHEN NOT MATCHED THEN
        INSERT (id, productname, supplierid, unitprice, package, isdiscontinued)
        VALUES (a.id, a.productname, a.supplierid, a.unitprice, a.package, a.isdiscontinued);
END move_table;

动态表名版本（若[NTID]是传入变量） #

PROCEDURE move_table(p_ntid VARCHAR2) AS
    v_dynamic_sql VARCHAR2(2000);
BEGIN
    v_dynamic_sql := '
        MERGE INTO Product_discontinued_' || p_ntid || ' b
        USING (
            SELECT id, productname, supplierid, unitprice, package, isdiscontinued
            FROM PRODUCTS
            WHERE isdiscontinued = 1
        ) a
        ON (a.ID = b.ID)
        WHEN MATCHED THEN
            UPDATE SET 
                b.productname = a.productname,
                b.supplierid = a.supplierid,
                b.unitprice = a.unitprice,
                b.package = a.package,
                b.isdiscontinued = a.isdiscontinued
        WHEN NOT MATCHED THEN
            INSERT (id, productname, supplierid, unitprice, package, isdiscontinued)
            VALUES (a.id, a.productname, a.supplierid, a.unitprice, a.package, a.isdiscontinued)';
    
    EXECUTE IMMEDIATE v_dynamic_sql;
END move_table;

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