如何在PL/SQL存储过程的动态SQL中插入变量NULL值

现有表Foobars，建表语句如下：

CREATE TABLE Foobars(
   foo NUMBER,
   bar NUMBER
);

需要在动态PL/SQL存储过程中通过变量插入NULL值，原始代码执行时报ORA-00936错误：missing expression（缺失表达式）。

报错的根源是动态SQL字符串拼接时对NULL值的处理逻辑错误。当变量var_bar为NULL时，PL/SQL的字符串拼接操作会把NULL值直接转为空占位，最终拼接出来的执行语句为：

INSERT INTO Foobars VALUES (69, );

逗号后没有合法的表达式，不符合SQL语法规范，因此触发缺失表达式的报错。

方案1：使用绑定变量（推荐） #

动态SQL优先选择绑定变量的传参方式，不需要额外处理NULL值，同时能避免SQL注入风险，执行效率也更高。修改后的代码如下：

DECLARE
   var_foo  NUMBER := 69;
   var_bar  NUMBER := NULL;
   l_insert_statement VARCHAR2(128);
BEGIN
   -- 构造带占位符的插入语句
   l_insert_statement := 'INSERT INTO Foobars VALUES (:1, :2)';
   -- 执行动态SQL时传入参数
   EXECUTE immediate l_insert_statement USING var_foo, var_bar;
END;
/

方案2：拼接时显式处理NULL值 #

如果业务场景必须要直接拼接SQL字符串，可以用NVL函数将NULL值转换为SQL关键字'NULL'的字符串形式，保证拼接后的语法正确：

DECLARE
   var_foo  NUMBER := 69;
   var_bar  NUMBER := NULL;
   l_insert_statement VARCHAR2(128);
BEGIN
   -- 拼接时对NULL值做转换处理
   l_insert_statement :=
      'INSERT INTO Foobars VALUES (' ||
         var_foo || ', ' ||
         NVL(TO_CHAR(var_bar), 'NULL') ||
      ');';
   EXECUTE immediate (l_insert_statement);
END;
/

这种方式拼接出来的最终执行语句为INSERT INTO Foobars VALUES (69, NULL);，符合SQL语法要求，可以正常执行。

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