无法在Oracle函数中动态创建的SYS_REFCURSOR中进行迭代。

huangapple
go评论99阅读模式
英文:

Cannot Iterate Dynamically Created SYS_REFCURSOR in Oracle Function

问题

我正在面对一个我正在处理的函数的问题。该函数旨在根据输入参数和所请求的表的列名检索列值。然而，当我尝试编译其代码时，出现以下错误：

  1. [警告] ORA-24344：编译错误成功 19/27 PLS-00221：'C_TEST_TABLE' 不是过程或未定义 19/5 PL/SQL：语句被忽略（1: 0）：警告：已编译但存在编译错误
  3. 这表明在声明块之后不能使用C_TEST_TABLE。
英文:

I am facing an issue with a function that I'm working on. The function aims to retrieve a column value based on the input parameters and a requested column name of that table. However, when I try to compile its code, which is the following:

  1. CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
  2.     IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
  3.     IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
  4.     IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
  5.     IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
  6.     IN_REQUESTED_COLUMN VARCHAR2
  7. ) RETURN VARCHAR2 IS
  8.     C_TEST_TABLE SYS_REFCURSOR;
  9.     RESULT       VARCHAR2(255);
  10. BEGIN
  11.     IF IN_COLUMN4 IS NULL THEN
  12.         OPEN C_TEST_TABLE FOR
  13.             SELECT *
  14.             FROM TEST_TABLE
  15.             WHERE COLUMN1 = COLUMN1
  16.               AND COLUMN2 = IN_COLUMN2
  17.               AND COLUMN3 = IN_COLUMN3;
  18.     ELSIF IN_COLUMN4 IS NOT NULL THEN
  19.         OPEN C_TEST_TABLE FOR
  20.             SELECT *
  21.             FROM TEST_TABLE
  22.             WHERE COLUMN1 = COLUMN1
  23.               AND COLUMN2 = IN_COLUMN2
  24.               AND COLUMN3 = IN_COLUMN3
  25.               AND COLUMN4 = IN_COLUMN4;
  26.     END IF;
  27.     FOR C_TEST_TABLE_REC IN C_TEST_TABLE
  28.         LOOP
  29.             IF IN_REQUESTED_COLUMN = 'COLUMN1' THEN
  30.                 RESULT := C_TEST_TABLE_REC.COLUMN1;
  31.                 EXIT;
  32.             ELSIF IN_REQUESTED_COLUMN = 'COLUMN2' THEN
  33.                 RESULT := C_TEST_TABLE_REC.COLUMN2;
  34.                 EXIT;
  35.             ELSIF IN_REQUESTED_COLUMN = 'COLUMN3' THEN
  36.                 RESULT := C_TEST_TABLE_REC.COLUMN3;
  37.                 EXIT;
  38.             END IF;
  39.         END LOOP;
  40.     RETURN RESULT;
  41. END;

I receive the following error:

> [Warning] ORA-24344: success with compilation error 19/27 PLS-00221:
> 'C_TEST_TABLE' is not a procedure or is undefined 19/5 PL/SQL:
> Statement ignored (1: 0): Warning: compiled but with compilation
> errors

that indicates that C_TEST_TABLE cannot be used after the declaration block

答案1

得分: 4

以下是您要翻译的内容：

  1. 无需使用游标：
  3. ```lang-sql
  4. CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
  5.     IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
  6.     IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
  7.     IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
  8.     IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
  9.     IN_REQUESTED_COLUMN VARCHAR2
  10. ) RETURN VARCHAR2 IS
  11.     result VARCHAR2(255);
  12. BEGIN
  13.   SELECT CASE IN_REQUESTED_COLUMN
  14.          WHEN 'COLUMN1' THEN column1
  15.          WHEN 'COLUMN2' THEN column2
  16.          WHEN 'COLUMN3' THEN column3
  17.          END
  18.   INTO   result
  19.   FROM   TEST_TABLE
  20.   WHERE  COLUMN1 = IN_COLUMN1
  21.   AND    COLUMN2 = IN_COLUMN2
  22.   AND    COLUMN3 = IN_COLUMN3
  23.   AND    (IN_COLUMN4 IS NULL OR COLUMN4 = IN_COLUMN4)
  24.   FETCH FIRST ROW ONLY;
  26.   RETURN result;
  27. EXCEPTION
  28.   WHEN NO_DATA_FOUND THEN
  29.     RETURN NULL;
  30. END;
  31. /

对于示例数据：

  1. CREATE TABLE test_table (column1, column2, column3, column4) AS
  2. SELECT 'A', 'B', 'C', 'D' FROM DUAL UNION ALL
  3. SELECT 'A', 'B', 'C', 'E' FROM DUAL;

然后：

  1. SELECT FIND_TEST_TABLE_COLUMN('A', 'B', 'C', 'D', 'COLUMN1') AS result1,
  2.        FIND_TEST_TABLE_COLUMN('A', 'B', 'C', NULL, 'COLUMN2') AS result2,
  3.        FIND_TEST_TABLE_COLUMN('X', 'Y', 'Z', NULL, 'COLUMN3') AS result3
  4. FROM   DUAL;

输出：

RESULT1 RESULT2 RESULT3
A B null

如果您想修复您的代码，那么您需要声明变量 C_TEST_TABLE_REC 并使用 FETCH（可以删除循环并简化代码为）：

  1. CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
  2.     IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
  3.     IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
  4.     IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
  5.     IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
  6.     IN_REQUESTED_COLUMN VARCHAR2
  7. ) RETURN VARCHAR2
  8. IS
  9.   C_TEST_TABLE     SYS_REFCURSOR;
  10.   C_TEST_TABLE_REC TEST_TABLE%ROWTYPE;
  11. BEGIN
  12.   OPEN C_TEST_TABLE FOR
  13.     SELECT *
  14.     FROM   TEST_TABLE
  15.     WHERE  COLUMN1 = IN_COLUMN1
  16.       AND  COLUMN2 = IN_COLUMN2
  17.       AND  COLUMN3 = IN_COLUMN3
  18.       AND  (IN_COLUMN4 IS NULL OR COLUMN4 = IN_COLUMN4);
  20.   FETCH C_TEST_TABLE INTO C_TEST_TABLE_REC;
  22.   IF C_TEST_TABLE%NOTFOUND THEN
  23.     RETURN NULL;
  24.   END IF;
  26.   IF IN_REQUESTED_COLUMN = 'COLUMN1' THEN
  27.     RETURN C_TEST_TABLE_REC.COLUMN1;
  28.   ELSIF IN_REQUESTED_COLUMN = 'COLUMN2' THEN
  29.     RETURN C_TEST_TABLE_REC.COLUMN2;
  30.   ELSIF IN_REQUESTED_COLUMN = 'COLUMN3' THEN
  31.     RETURN C_TEST_TABLE_REC.COLUMN3;
  32.   ELSE
  33.     RETURN NULL;
  34.   END IF;
  35. END;
  36. /

fiddle

  2. <details>
  3. <summary>英文:</summary>
  5. You don&#39;t need to use cursors:
  7. ```lang-sql
  8. CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
  9.     IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
  10.     IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
  11.     IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
  12.     IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
  13.     IN_REQUESTED_COLUMN VARCHAR2
  14. ) RETURN VARCHAR2 IS
  15.     result VARCHAR2(255);
  16. BEGIN
  17.   SELECT CASE IN_REQUESTED_COLUMN
  18.          WHEN &#39;COLUMN1&#39; THEN column1
  19.          WHEN &#39;COLUMN2&#39; THEN column2
  20.          WHEN &#39;COLUMN3&#39; THEN column3
  21.          END
  22.   INTO   result
  23.   FROM   TEST_TABLE
  24.   WHERE  COLUMN1 = IN_COLUMN1
  25.   AND    COLUMN2 = IN_COLUMN2
  26.   AND    COLUMN3 = IN_COLUMN3
  27.   AND    (IN_COLUMN4 IS NULL OR COLUMN4 = IN_COLUMN4)
  28.   FETCH FIRST ROW ONLY;
  30.   RETURN result;
  31. EXCEPTION
  32.   WHEN NO_DATA_FOUND THEN
  33.     RETURN NULL;
  34. END;
  35. /

Which, for the sample data:

  1. CREATE TABLE test_table (column1, column2, column3, column4) AS
  2. SELECT &#39;A&#39;, &#39;B&#39;, &#39;C&#39;, &#39;D&#39; FROM DUAL UNION ALL
  3. SELECT &#39;A&#39;, &#39;B&#39;, &#39;C&#39;, &#39;E&#39; FROM DUAL;

Then:

  1. SELECT FIND_TEST_TABLE_COLUMN(&#39;A&#39;, &#39;B&#39;, &#39;C&#39;, &#39;D&#39;, &#39;COLUMN1&#39;) AS result1,
  2.        FIND_TEST_TABLE_COLUMN(&#39;A&#39;, &#39;B&#39;, &#39;C&#39;, NULL, &#39;COLUMN2&#39;) AS result2,
  3.        FIND_TEST_TABLE_COLUMN(&#39;X&#39;, &#39;Y&#39;, &#39;Z&#39;, NULL, &#39;COLUMN3&#39;) AS result3
  4. FROM   DUAL;

Outputs:

RESULT1 RESULT2 RESULT3
A B null

If you want to fix your code then you need to declare the variable C_TEST_TABLE_REC and use FETCH (and can remove the loop and simplify the code to):

  1. CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
  2.     IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
  3.     IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
  4.     IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
  5.     IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
  6.     IN_REQUESTED_COLUMN VARCHAR2
  7. ) RETURN VARCHAR2
  8. IS
  9.   C_TEST_TABLE     SYS_REFCURSOR;
  10.   C_TEST_TABLE_REC TEST_TABLE%ROWTYPE;
  11. BEGIN
  12.   OPEN C_TEST_TABLE FOR
  13.     SELECT *
  14.     FROM   TEST_TABLE
  15.     WHERE  COLUMN1 = IN_COLUMN1
  16.       AND  COLUMN2 = IN_COLUMN2
  17.       AND  COLUMN3 = IN_COLUMN3
  18.       AND  (IN_COLUMN4 IS NULL OR COLUMN4 = IN_COLUMN4);
  20.   FETCH C_TEST_TABLE INTO C_TEST_TABLE_REC;
  22.   IF C_TEST_TABLE%NOTFOUND THEN
  23.     RETURN NULL;
  24.   END IF;
  26.   IF IN_REQUESTED_COLUMN = &#39;COLUMN1&#39; THEN
  27.     RETURN C_TEST_TABLE_REC.COLUMN1;
  28.   ELSIF IN_REQUESTED_COLUMN = &#39;COLUMN2&#39; THEN
  29.     RETURN C_TEST_TABLE_REC.COLUMN2;
  30.   ELSIF IN_REQUESTED_COLUMN = &#39;COLUMN3&#39; THEN
  31.     RETURN C_TEST_TABLE_REC.COLUMN3;
  32.   ELSE
  33.     RETURN NULL;
  34.   END IF;
  35. END;
  36. /

fiddle

huangapple
  • 本文由 发表于 2023年6月8日 19:33:16
  • 转载请务必保留本文链接：https://go.coder-hub.com/76431427.html
  • cursor
  • oracle
  • plsql
  • sql
  • sys-refcursor
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定