sql - 如何在匿名 PL/SQL block 中使用重复占位符名称？

Question

我正在尝试运行 UPSERT在表 Person 上像这样:

BEGIN
    EXECUTE IMMEDIATE q'[
        MERGE INTO Person p
        USING (SELECT :x id, :y first_name, :z last_name FROM Dual) data

        ON (p.id = :x) -- note the repeated placeholder :x here

        WHEN MATCHED THEN
              UPDATE SET p.first_name = data.first_name, p.last_name = data.last_name
        WHEN NOT MATCHED THEN
              INSERT (id, first_name, last_name) VALUES (data.id, data.first_name, data.last_name)
    ]' USING 123, 'Foo', 'Bar';
END;
/

如果我运行上面给出的代码，Oracle 会抛出一个ORA-01008: not all variables bound ORA-06512: at line 2

根据docs这不应该在此时发生(在匿名 block 内)。我做错了什么？

<子>我知道如果我提供四个参数 ... USING 123, 'Foo', 'Bar', 123; 就可以解决这个问题，而且效果很好，但显然我不想重复自己.编辑:正如答案中所指出的，ON (p.id = data.id) 是另一种可能的解决方法。

问题不在于如何解决这个问题，而在于了解在这种情况下出现此错误的原因。

Answer 1

您似乎误解了文档:

If the dynamic SQL statement represents an anonymous PL/SQL block or a CALL statement, repetition of placeholder names is significant.

这表明如果正在动态执行的语句 是一个 PL/SQL block ，则不必重复绑定(bind)变量。你的动态语句是SQL语句，不合格。您可以通过将 begin 和 end; 添加到动态语句来解决此问题:

BEGIN
    EXECUTE IMMEDIATE q'[
        BEGIN
           MERGE INTO Person p
           USING (SELECT :x id, :y first_name, :z last_name FROM Dual) data    
           ON (p.id = :x) -- note the repeated placeholder :x here    
           WHEN MATCHED THEN
              UPDATE SET p.first_name = data.first_name, p.last_name = data.last_name
           WHEN NOT MATCHED THEN
              INSERT (id, first_name, last_name) VALUES (data.id, data.first_name, data.last_name)
        END;
    ]' USING 123, 'Foo', 'Bar';
END;
/

---

在某种情况下，无需重复绑定(bind)变量即可修复此语句很简单:

BEGIN
    EXECUTE IMMEDIATE q'[
        MERGE INTO Person p
        USING (SELECT :x id, :y first_name, :z last_name FROM Dual) data
        ON (p.id = data.id) 
        WHEN MATCHED THEN
              UPDATE SET p.first_name = data.first_name, p.last_name = data.last_name
        WHEN NOT MATCHED THEN
              INSERT (id, first_name, last_name) VALUES (data.id, data.first_name, data.last_name)
    ]' USING 123, 'Foo', 'Bar';
END;
/

---

最后，我觉得我必须指出在这种情况下没有理由使用动态 SQL。除非有更多影响正在运行的 SQL 的事情发生，否则这应该只是一个静态 SQL 语句。