如何声明弱类型 SYS_REFCURSOR 变量的 %ROWTYPE? [英] how to declare %ROWTYPE of a variable that is a weakly typed SYS_REFCURSOR?

问题描述

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

W.r.t code below I can not declare the type of fetch-into-variable as the underlying table's %ROWTYPE because the SYS_REFCURSOR is on a select that joins two tables and also selects a few functions called on the attributes of the underlying two tables; i.e I can't declare as 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.

Of course I don't want to define a static table R with columns as returned in the SYS_REFCURSOR and then declare as L_RECORD R%ROWTYPE.

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

And hence the question: how to declare %ROWTYPE of a variable that is a weakly typed SYS_REFCURSOR ?

推荐答案

简而言之，你不能.您需要为将要返回的每一列定义一个变量.

The short answer is, you can't. You'd need to define a variable for each column that wil be returned.

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, ... ;

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

This is painful but manageable as long as you know what you're expecting in the ref cursor. Using T.* in your procedure makes this fragile though, as adding a column to the table would break the code that thinks it knows what columns there are and what order they're in. (You can also break it between environments if the tables aren't built consistently - I've seen places where column ordering is different in different environments). You'll probably want to make sure you're only selecting the columns you really care about anyway, to avoid having to define variables for things you'll never read.

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

From 11g you can use the DBMS_SQL package to convert your sys_refcursor into a DBMS_SQL cursor, and you can interrogate that to determine the columns. Just as an example of what you can do, this will print out the value of every column in every row, with the column name:

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;
/

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

That's not of much practical use, and for brevity I'm treating every value as a string since I just want to print it anyway. Look at the docs and search for examples for more practical applications.

如果您只需要引用光标中的几列，我想，您可以循环 l_desc 并记录 column_name 是您感兴趣的任何位置的位置, 作为数值变量；然后您可以稍后通过该变量引用该列，您通常会在游标循环中使用该名称.取决于您对数据的处理方式.

If you only want a few columns from your ref cursor you could, I suppose, loop around l_desc and record the position where column_name is whatever you're interested in, as a numeric variable; you could then refer to the column by that variable later where you would normally use the name in a cursor loop. Depends what you're doing with the data.

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

But unless you're expecting to not know the column order you're getting back, which is unlikely since you seem to control the procedure - and assuming you get rid of the .*s - you're probably much better off reducing the returned columns to the minimum you need and just declaring them all individually.

这篇关于如何声明弱类型 SYS_REFCURSOR 变量的 %ROWTYPE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！