如何使用带有变量表名称和条件的立即执行

我想创建一个 PL/SQL 函数，该函数传递一个表名和一个条件，并返回该表上满足条件的行数。

我创建了这个函数：

CREATE OR REPLACE FUNCTION CHECK_EXISTS 
      (TABLE_NAME VARCHAR2, CONDITION VARCHAR2) RETURN NUMBER AS 
      VAL NUMBER;
      SQL_CODE VARCHAR2(200):='SELECT COUNT (*) INTO VAL FROM :TABLE_NAME WHERE
      :CONDITION';
BEGIN 
      EXECUTE IMMEDIATE SQL_CODE USING TABLE_NAME ,CONDITION;
      RETURN VAL;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
      RETURN 0;
END;

该函数已成功创建，但是当我尝试使用以下代码使用它时：

BEGIN DBMS_OUTPUT.PUT_LINE(CHECK_EXISTS('EMPLOYEES' ,'DEPARTMENT_ID=50')); END;

我收到异常：ORA-00903：无效的表名。

您不能将绑定变量用于表名或列名或完整条件。这些必须在解析语句时知道，即在分配绑定变量之前 - 否则您将失去绑定变量的好处之一。您只能绑定变量的值。

当你的字符串被解析时，表名被字面解释为:TABLE_NAME，冒号使其成为表名的无效值。这是not使用您传递给函数的值。

所以你需要连接名称和条件；你的INTO子句也放错了地方：

CREATE OR REPLACE FUNCTION CHECK_EXISTS 
      (TABLE_NAME VARCHAR2, CONDITION VARCHAR2) RETURN NUMBER AS 
      VAL NUMBER;
      SQL_CODE VARCHAR2(200):='SELECT COUNT (*) FROM '
        || TABLE_NAME || ' WHERE ' || CONDITION;
BEGIN 
      EXECUTE IMMEDIATE SQL_CODE INTO VAL;
      RETURN VAL;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
      RETURN 0;
END;

考虑到您将看到的有关使用绑定变量来避免 SQL 注入的所有建议，这可能看起来很奇怪。这仍然适用，只是不能在这里对表名执行此操作。

但这确实意味着您可能对 SQL 注入持开放态度，因此您应该清理获得的输入，这对于表名来说相当简单 - 您可以查看它是否存在于all_tables，例如 - 但可变条件将更难检查。幸运的是，您只能使用动态 SQL 执行单个语句，因此很难做任何太讨厌的事情，除非您可以放入有效条件中产生副作用。

如果由于某种原因你really想要使用绑定变量execute immediate打电话你可以做一些复杂的事情，比如：

CREATE OR REPLACE FUNCTION CHECK_EXISTS 
      (TABLE_NAME VARCHAR2, CONDITION VARCHAR2) RETURN NUMBER AS 
      VAL NUMBER;
      SQL_CODE VARCHAR2(200):=q'[BEGIN EXECUTE IMMEDIATE 'SELECT COUNT (*) FROM ' || :TABLE_NAME || ' WHERE ' || :CONDITION INTO :VAL; END;]';
BEGIN 
      EXECUTE IMMEDIATE SQL_CODE USING TABLE_NAME, CONDITION, OUT VAL;
      RETURN VAL;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
      RETURN 0;
END;
/

...将串联推入匿名块；然后执行使用 OUT 绑定变量来代替计数结果INTO。但我不确定这样做有多大好处。

正如 @AvrajitRoy 提到的，因为你正在做一个聚合count()查询将始终返回结果（除非它因不存在的表或格式错误的条件而出错，当然，您想了解这一点），因此永远无法到达您拥有的异常处理程序。虽然它并没有真正造成任何伤害，但可以将其删除：

CREATE OR REPLACE FUNCTION CHECK_EXISTS (TABLE_NAME VARCHAR2, CONDITION VARCHAR2)
RETURN NUMBER AS 
    VAL NUMBER;
    SQL_CODE VARCHAR2(200):='SELECT COUNT (*) FROM '
        || TABLE_NAME || ' WHERE ' || CONDITION;
BEGIN 
    EXECUTE IMMEDIATE SQL_CODE INTO VAL;
    RETURN VAL;
END;