英文:
Cannot Iterate Dynamically Created SYS_REFCURSOR in Oracle Function
问题
我正在面对一个我正在处理的函数的问题。该函数旨在根据输入参数和所请求的表的列名检索列值。然而，当我尝试编译其代码时，出现以下错误：
[警告] ORA-24344：编译错误成功 19/27 PLS-00221：'C_TEST_TABLE' 不是过程或未定义 19/5 PL/SQL：语句被忽略（1: 0）：警告：已编译但存在编译错误
这表明在声明块之后不能使用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:
CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
IN_REQUESTED_COLUMN VARCHAR2
) RETURN VARCHAR2 IS
C_TEST_TABLE SYS_REFCURSOR;
RESULT VARCHAR2(255);
BEGIN
IF IN_COLUMN4 IS NULL THEN
OPEN C_TEST_TABLE FOR
SELECT *
FROM TEST_TABLE
WHERE COLUMN1 = COLUMN1
AND COLUMN2 = IN_COLUMN2
AND COLUMN3 = IN_COLUMN3;
ELSIF IN_COLUMN4 IS NOT NULL THEN
OPEN C_TEST_TABLE FOR
SELECT *
FROM TEST_TABLE
WHERE COLUMN1 = COLUMN1
AND COLUMN2 = IN_COLUMN2
AND COLUMN3 = IN_COLUMN3
AND COLUMN4 = IN_COLUMN4;
END IF;
FOR C_TEST_TABLE_REC IN C_TEST_TABLE
LOOP
IF IN_REQUESTED_COLUMN = 'COLUMN1' THEN
RESULT := C_TEST_TABLE_REC.COLUMN1;
EXIT;
ELSIF IN_REQUESTED_COLUMN = 'COLUMN2' THEN
RESULT := C_TEST_TABLE_REC.COLUMN2;
EXIT;
ELSIF IN_REQUESTED_COLUMN = 'COLUMN3' THEN
RESULT := C_TEST_TABLE_REC.COLUMN3;
EXIT;
END IF;
END LOOP;
RETURN RESULT;
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
以下是您要翻译的内容：
无需使用游标：
```lang-sql
CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
IN_REQUESTED_COLUMN VARCHAR2
) RETURN VARCHAR2 IS
result VARCHAR2(255);
BEGIN
SELECT CASE IN_REQUESTED_COLUMN
WHEN 'COLUMN1' THEN column1
WHEN 'COLUMN2' THEN column2
WHEN 'COLUMN3' THEN column3
END
INTO result
FROM TEST_TABLE
WHERE COLUMN1 = IN_COLUMN1
AND COLUMN2 = IN_COLUMN2
AND COLUMN3 = IN_COLUMN3
AND (IN_COLUMN4 IS NULL OR COLUMN4 = IN_COLUMN4)
FETCH FIRST ROW ONLY;
RETURN result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
/
对于示例数据：
CREATE TABLE test_table (column1, column2, column3, column4) AS
SELECT 'A', 'B', 'C', 'D' FROM DUAL UNION ALL
SELECT 'A', 'B', 'C', 'E' FROM DUAL;
然后：
SELECT FIND_TEST_TABLE_COLUMN('A', 'B', 'C', 'D', 'COLUMN1') AS result1,
FIND_TEST_TABLE_COLUMN('A', 'B', 'C', NULL, 'COLUMN2') AS result2,
FIND_TEST_TABLE_COLUMN('X', 'Y', 'Z', NULL, 'COLUMN3') AS result3
FROM DUAL;
输出：
| RESULT1 | RESULT2 | RESULT3 |
|---|---|---|
| A | B | null |
如果您想修复您的代码，那么您需要声明变量 C_TEST_TABLE_REC 并使用 FETCH（可以删除循环并简化代码为）：
CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
IN_REQUESTED_COLUMN VARCHAR2
) RETURN VARCHAR2
IS
C_TEST_TABLE SYS_REFCURSOR;
C_TEST_TABLE_REC TEST_TABLE%ROWTYPE;
BEGIN
OPEN C_TEST_TABLE FOR
SELECT *
FROM TEST_TABLE
WHERE COLUMN1 = IN_COLUMN1
AND COLUMN2 = IN_COLUMN2
AND COLUMN3 = IN_COLUMN3
AND (IN_COLUMN4 IS NULL OR COLUMN4 = IN_COLUMN4);
FETCH C_TEST_TABLE INTO C_TEST_TABLE_REC;
IF C_TEST_TABLE%NOTFOUND THEN
RETURN NULL;
END IF;
IF IN_REQUESTED_COLUMN = 'COLUMN1' THEN
RETURN C_TEST_TABLE_REC.COLUMN1;
ELSIF IN_REQUESTED_COLUMN = 'COLUMN2' THEN
RETURN C_TEST_TABLE_REC.COLUMN2;
ELSIF IN_REQUESTED_COLUMN = 'COLUMN3' THEN
RETURN C_TEST_TABLE_REC.COLUMN3;
ELSE
RETURN NULL;
END IF;
END;
/
<details>
<summary>英文:</summary>
You don't need to use cursors:
```lang-sql
CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
IN_REQUESTED_COLUMN VARCHAR2
) RETURN VARCHAR2 IS
result VARCHAR2(255);
BEGIN
SELECT CASE IN_REQUESTED_COLUMN
WHEN 'COLUMN1' THEN column1
WHEN 'COLUMN2' THEN column2
WHEN 'COLUMN3' THEN column3
END
INTO result
FROM TEST_TABLE
WHERE COLUMN1 = IN_COLUMN1
AND COLUMN2 = IN_COLUMN2
AND COLUMN3 = IN_COLUMN3
AND (IN_COLUMN4 IS NULL OR COLUMN4 = IN_COLUMN4)
FETCH FIRST ROW ONLY;
RETURN result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
/
Which, for the sample data:
CREATE TABLE test_table (column1, column2, column3, column4) AS
SELECT 'A', 'B', 'C', 'D' FROM DUAL UNION ALL
SELECT 'A', 'B', 'C', 'E' FROM DUAL;
Then:
SELECT FIND_TEST_TABLE_COLUMN('A', 'B', 'C', 'D', 'COLUMN1') AS result1,
FIND_TEST_TABLE_COLUMN('A', 'B', 'C', NULL, 'COLUMN2') AS result2,
FIND_TEST_TABLE_COLUMN('X', 'Y', 'Z', NULL, 'COLUMN3') AS result3
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):
CREATE OR REPLACE FUNCTION FIND_TEST_TABLE_COLUMN(
IN_COLUMN1 TEST_TABLE.COLUMN1 % TYPE,
IN_COLUMN2 TEST_TABLE.COLUMN2 % TYPE,
IN_COLUMN3 TEST_TABLE.COLUMN3 % TYPE,
IN_COLUMN4 TEST_TABLE.COLUMN4 % TYPE,
IN_REQUESTED_COLUMN VARCHAR2
) RETURN VARCHAR2
IS
C_TEST_TABLE SYS_REFCURSOR;
C_TEST_TABLE_REC TEST_TABLE%ROWTYPE;
BEGIN
OPEN C_TEST_TABLE FOR
SELECT *
FROM TEST_TABLE
WHERE COLUMN1 = IN_COLUMN1
AND COLUMN2 = IN_COLUMN2
AND COLUMN3 = IN_COLUMN3
AND (IN_COLUMN4 IS NULL OR COLUMN4 = IN_COLUMN4);
FETCH C_TEST_TABLE INTO C_TEST_TABLE_REC;
IF C_TEST_TABLE%NOTFOUND THEN
RETURN NULL;
END IF;
IF IN_REQUESTED_COLUMN = 'COLUMN1' THEN
RETURN C_TEST_TABLE_REC.COLUMN1;
ELSIF IN_REQUESTED_COLUMN = 'COLUMN2' THEN
RETURN C_TEST_TABLE_REC.COLUMN2;
ELSIF IN_REQUESTED_COLUMN = 'COLUMN3' THEN
RETURN C_TEST_TABLE_REC.COLUMN3;
ELSE
RETURN NULL;
END IF;
END;
/
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论