Sql Oracle函数中无法动态迭代创建的SYS_REFCURSOR

您不需要使用游标:

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;

输出:

如果要修复代码，则需要声明变量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;
/

fiddle个