sql - 使用 Oracle 表类型执行动态 native SQL 会出现无效标识符错误

Question

我们已经创建了 Oracle 表类型。我们已经创建了一个数组。这样做是因为我们不知道可以有多少值，这对于 sql 查询中的 IN 子句来说可能太多了。

--代码片段-----

create or replace 
TYPE "INPUTCODE" 
as object
(pc varchar2(100) )

create or replace 
TYPE "INPUTCODEARR" 
    IS TABLE OF inputcode;

create or replace 
PROCEDURE "TEST_PROC" (testCodes IN inputcodeArr, timeHorizon IN NUMBER, p_recordset OUT SYS_REFCURSOR)
AS
var_sqlStmt VARCHAR2(4096);
BEGIN
var_sqlStmt := 'select t.a,t.b, t.c';
var_sqlStmt := var_sqlStmt || 'from test t';

if testCodes is not null then 
    var_sqlStmt := var_sqlStmt || ', table(testCodes) tc';
        var_sqlStmt := var_sqlStmt || 'where tc.pc = t.name';
end if; 
dbms_output.put_line('Final SQL Statement::' || var_sqlStmt);
open p_recordset for var_sqlStmt;
END TEST_PROC;

上面所有的都是编译的，当你运行带有少量 testCode 值的 TEST_PROC 过程时，它将失败并显示错误 Invalid identifier for testCodes。

在该过程中，在我的控制台中打印的最终 sql 语句是正确的，当您在过程中将其作为静态 sql 语句运行时，它运行时没有任何错误。但是在动态 sql 内部它失败了。我尝试使用 DYNAMIC_SQL 包执行，但它导致了同样的错误。此外，我尝试将其作为“table(testCodes)”的绑定(bind)变量。那也失败了。

请提出建议。

Answer 1

您正在使用 dynamic SQL ，所以您必须告诉 Oracle 哪个词是标识符，哪个词是变量。

考虑以下直接在 SQLPlus 中运行的语句:

select t.a,t.b, t.c from test t, table(testCodes) tc

它将失败，因为在您的数据库中没有对象被命名为 testCodes。您必须告诉 SQL 引擎 testCodes 实际上是一个变量。您必须这样做，因为您选择使用动态 SQL，而变量绑定(bind)在静态 SQL 中是自动的。

在大多数情况下，您可以像绑定(bind)标准变量一样绑定(bind)“对象”变量。在 PL/SQL 中有几种方法可以做到这一点，例如使用游标可以使用 USING:

SQL> DECLARE
  2     l_cur SYS_REFCURSOR;
  3     l_tab inputcodeArr := inputcodeArr(INPUTCODE('A'), INPUTCODE('B'));
  4     l_obj varchar2(100);
  5  BEGIN
  6     OPEN l_cur FOR 'SELECT pc FROM TABLE(:my_variable)' -- notice the ":"
  7        USING l_tab; -- binding by position
  8     LOOP
  9        FETCH l_cur
 10           INTO l_obj;
 11        EXIT WHEN l_cur%NOTFOUND;
 12        dbms_output.put_line(l_obj);
 13     END LOOP;
 14     CLOSE l_cur;
 15  END;
 16  /

A
B

PL/SQL procedure successfully completed

但是在您的情况下，我不会为动态 SQL 而烦恼，因为您可以有条件地打开游标:

CREATE OR REPLACE PROCEDURE "TEST_PROC"(testCodes   IN inputcodeArr,
                                        timeHorizon IN NUMBER,
                                        p_recordset OUT SYS_REFCURSOR) AS
BEGIN
   IF testCodes IS NOT NULL THEN
      OPEN p_recordset FOR
         SELECT t.a, t.b, t.c FROM test t, TABLE(testCodes) tc 
          WHERE tc.pc = t.NAME;
   ELSE
      OPEN p_recordset FOR
         SELECT t.a, t.b, t.c FROM test t;
   END IF;
END TEST_PROC;

我的建议是尽可能长时间地坚持使用静态 SQL，因为使用动态 SQL 更容易出错。

---

更新以下评论:

如果你输入的个数不是常量，因为有很多过滤器的组合而不得不使用动态SQL，你可以使用如下策略:

CREATE OR REPLACE PROCEDURE "TEST_PROC"(testCodes   IN inputcodeArr,
                                        timeHorizon IN NUMBER,
                                        p_recordset OUT SYS_REFCURSOR) AS
   l_sql LONG := 'SELECT t.a, t.b, t.c FROM test t WHERE';
BEGIN
   -- filter #1
   IF testCodes IS NOT NULL THEN
      l_sql := l_sql || ' t.name IN (SELECT pc FROM TABLE(:filter1))';
   ELSE
      l_sql := l_sql || ' :filter1 IS NULL';
   END IF;
   -- filter #2
   IF timeHorizon IS NOT NULL THEN
      l_sql := l_sql || ' AND t.horizon = :filter2';
   ELSE
      l_sql := l_sql || ' AND :filter2 IS NULL';
   END IF;
   -- open cursor
   OPEN p_recordset FOR l_sql USING testCodes, timeHorizon;
END TEST_PROC;
/

我确保最终的 SQL 将始终以相同的顺序具有相同数量的变量，但是过滤器为 NULL 的每个条件都将是重言式 (NULL IS NULL)。