oracle - 如何使用带有可变表名和条件的立即执行

Question

我想创建一个 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:无效的表名。

Answer 1

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

当您的字符串被解析时，表名按字面解释为 :TABLE_NAME，并且冒号使它成为表名的无效值。它不使用您传递给函数的值。

因此您需要连接名称和条件；您的 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;

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

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

---

如果出于某种原因你真的想为你的execute immediate调用使用绑定(bind)变量，你可以做一些复杂的事情，比如:

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;
/

... 将连接插入匿名 block ；然后执行使用 OUT 绑定(bind)变量而不是 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;