如何使用具有可变表名和条件的立即执行 [英] How to use execute immediate with variable table name and condition

问题描述

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

I want to create a PL/SQL function which is passed a table name and a condition, and which returns the number of rows than the condition meets on that table.

我创建了函数:

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;

该函数创建成功，但是当我尝试使用此代码使用它时:

The function is created successfully, but when I try to use it using this code:

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

我得到一个例外:ORA-00903:无效的表名.

I get the exception: ORA-00903: invalid table name.

推荐答案

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

You cannot use bind variables for table or column names, or a complete condition. Those have to be known when the statement is parsed, which is before the bind variables are assigned - otherwise you'd lose one of he benefits of the bind variables. You can only bind the values of variables.

解析字符串时，表名从字面上解释为:TABLE_NAME，冒号使该值成为表名的无效值. 不使用您传递给函数的值.

When your string is parsed the table name is interpreted literally as :TABLE_NAME, and the colon makes that an invalid value for a table name. It is not using the value you passed in to the function.

因此，您需要连接名称和条件；您的INTO子句也放在错误的位置:

So you need to concatenate the name and condition instead; your INTO clause is also in the wrong place:

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注入的所有建议，这似乎有些奇怪.仍然适用，您不能在此处为表名使用它.

This might seem odd given all the advice you will see about using bind variables to avoid SQL injection. That still applies, you just cannot do it for the table name here.

但这确实意味着您可能会接受SQL注入，因此您应该清理所获得的输入，这对于表名来说非常简单-例如，您可以查看它是否存在于all_tables中-但是变量条件将更难检查.幸运的是，您只能使用动态SQL执行一条语句，因此很难做任何令人讨厌的事情，除非会产生一些使您处于有效状态的副作用.

But that does mean you are potentially open to SQL injection, so you should sanitise the inputs you get, which is reasonably straightforward for the table name - you can see if it exists in all_tables, for example - but the variable condition will be harder to check. Luckily you can only execute a single statement with dynamic SQL so it would be hard to do anything too nasty, except as side-effects from something you can put in a valid condition.

如果出于某些原因确实想要在execute immediate调用中使用绑定变量，则可以进行一些复杂的操作，例如:

If for some reason you really want to use bind variables for your execute immediate call you could do something convoluted like:

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.我不确定这样做有什么好处.

... which pushes the concatenation into an anonymous block; the execution then uses an OUT bind variable for the count result instead of an INTO. I'm not sure there's much benefit in doing that though.

正如@AvrajitRoy提到的那样，由于您正在执行汇总count()，因此查询将始终返回结果(除非它是来自不存在的表的错误或当然是格式错误的条件，您想知道) ，因此永远无法访问您拥有的异常处理程序.虽然它实际上并没有造成任何危害，但是可以将其删除:

As @AvrajitRoy mentioned, since you're doing an aggregate count() the query will always return a result (unless it errors from a non-existent table or malformed condition of course, which you'd want to know about), so the exception handler you have can never be reached. While it isn't really doing any harm, it can be removed:

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;

这篇关于如何使用具有可变表名和条件的立即执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！