使用 EXECUTE IMMEDIATE 的动态 SQL 语法

Question

动态 SQL 更新语句如下：

EXECUTE IMMEDIATE 'UPDATE '||l_prefix||'CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = '||i.CUSTOMER_REF_ID||' WHERE CUSTOMER_ID = '||i.CUSTOMER_ID;

l_prefix参数保存表名的前缀，则分配的值T_ i.CUSTOMER_REF_ID和i.CUSTOMER_ID是从光标读取的字段。

动态语句在获取数据时遇到错误ORA-00904: invalid identifier。

以静态方式重写时，查询工作正常：

UPDATE T_CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = i.CUSTOMER_REF_ID WHERE CUSTOMER_ID = i.CUSTOMER_ID;

我知道动态SQL的串联一定有问题，只是无法确定任何问题，因为编译很好。

Answer 1

警告：像这样的动态SQL容易受到SQL注入攻击。 尽可能重写动态 SQL 以改用绑定变量。

而不是像这样构造你的动态 SQL：

L_SQL := 'UPDATE '||l_prefix||'CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = '||i.CUSTOMER_REF_ID||' WHERE CUSTOMER_ID = '||i.CUSTOMER_ID;
EXECUTE IMMEDIATE L_SQL;

使用这个：

L_SQL := 'UPDATE '||l_prefix||'CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = :REF_ID WHERE CUSTOMER_ID = :CUST_ID';
EXECUTE IMMEDIATE L_SQL USING i.CUSTOMER_REF_ID, i.CUSTOMER_ID;

这仍然受制于l_prefix的SQL注入，但是如果您以编程方式控制该值，则可能没问题。 此外，将 SQL 的构造和 SQL 的执行拆分为两个步骤，可以更轻松地将EXECUTE IMMEDIATE替换为DBMS_OUTPUT.PUT_LINE(SQL);以检查查询是否存在语法错误。 您还可以希望DBMS_OUTPUT.PUT_LINE参数i.CUSTOMER_REF_ID并i.CUSTOMER_ID检查其值。