oracle - 如何声明弱类型 SYS_REFCURSOR 变量的 %ROWTYPE？

Question

下面的 W.r.t 代码我不能将 fetch-into-variable 的类型声明为基础表的 %ROWTYPE，因为 SYS_REFCURSOR 位于连接两个表的选择上，并且还选择了一些在基础两个表的属性上调用的函数；即我不能声明为 L_RECORD T%ROWTYPE

---
DECLARE
  P_RS SYS_REFCURSOR;
  L_RECORD P_RS%ROWTYPE;
BEGIN
  CAPITALEXTRACT(
    P_RS => P_RS
  );
    OPEN P_RS;
    LOOP
      BEGIN
        FETCH P_RS INTO L_RECORD;
        EXIT WHEN P_RS%NOTFOUND;
        ...
      EXCEPTION
        WHEN OTHERS THEN
        ...
      END;
    END LOOP;
    CLOSE P_RS;
END;
--------
CREATE or REPLACE PROCEDURE CAPITALEXTRACT
(
    p_rs OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_rs for 
     select t.*,tminusone.*, f(t.cash), g(t.cash) FROM T t, TMINUSONE tminusone
    where t.ticket=tminusone.ticket;
END CAPITALEXTRACT;

当然，我不想使用 SYS_REFCURSOR 中返回的列定义静态表 R，然后声明为 L_RECORD R%ROWTYPE。

因此问题是:
如何声明弱类型 SYS_REFCURSOR 变量的 %ROWTYPE ？

Answer 1

简短的回答是，你不能。您需要为将返回的每一列定义一个变量。

DECLARE
    P_RS SYS_REFCURSOR;
    L_T_COL1 T.COL1%TYPE;
    L_T_COL1 T.COL2%TYPE;
    ...

然后获取到列列表:

FETCH P_RS INTO L_T_COL1, L_T_COL2, ... ;

这很痛苦，但只要您知道在 ref 光标中期望什么，就可以管理。使用 T.*但是，在您的过程中使这变得脆弱，因为向表中添加一列会破坏认为它知道有哪些列以及它们处于什么顺序的代码。(如果没有构建表，您也可以在环境之间破坏它始终如一 - 我已经看到列顺序在不同环境中不同的地方)。您可能希望确保您只选择您真正关心的列，以避免必须为您永远不会阅读的内容定义变量。

从 11g 开始，您可以使用 DBMS_SQL 包转换您的 sys_refcursor成 DBMS_SQL游标，您可以询问它以确定列。作为您可以执行的操作的示例，这将打印出每一行中每一列的值，以及列名:

DECLARE
    P_RS SYS_REFCURSOR;
    L_COLS NUMBER;
    L_DESC DBMS_SQL.DESC_TAB;
    L_CURS INTEGER;
    L_VARCHAR VARCHAR2(4000);
BEGIN
    CAPITALEXTRACT(P_RS => P_RS);
    L_CURS := DBMS_SQL.TO_CURSOR_NUMBER(P_RS);
    DBMS_SQL.DESCRIBE_COLUMNS(C => L_CURS, COL_CNT => L_COLS,
        DESC_T => L_DESC);

    FOR i IN 1..L_COLS LOOP
        DBMS_SQL.DEFINE_COLUMN(L_CURS, i, L_VARCHAR, 4000);
    END LOOP;

    WHILE DBMS_SQL.FETCH_ROWS(L_CURS) > 0 LOOP
        FOR i IN 1..L_COLS LOOP
            DBMS_SQL.COLUMN_VALUE(L_CURS, i, L_VARCHAR);
            DBMS_OUTPUT.PUT_LINE('Row ' || DBMS_SQL.LAST_ROW_COUNT
                || ': ' || l_desc(i).col_name
                || ' = ' || L_VARCHAR);
        END LOOP;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(L_CURS);
END;
/

这没什么实际用途，为了简洁起见，我将每个值都视为字符串，因为无论如何我只想打印它。查看文档并搜索更多实际应用的示例。

如果您只需要引用光标中的几列，我想您可以在 l_desc 周围循环。并记录 column_name所在的位置是您感兴趣的任何内容，作为数字变量；然后您可以稍后通过该变量引用该列，您通常会在游标循环中使用该名称。取决于你对数据做什么。

但是，除非您希望不知道要返回的列顺序，否则这不太可能，因为您似乎控制了该过程 - 并假设您摆脱了 .* s - 您最好将返回的列减少到所需的最小值，然后单独声明它们。