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

问题描述

下面的WRT代码我无法将fetch-into-variable的类型声明为基础表的％ROWTYPE，因为SYS_REFCURSOR位于连接两个表的select上，并且还选择了一些在基础两个表的属性上调用的函数;即我不能声明为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, ... ;

这很痛苦，但只要您知道ref游标中的期望，就可以管理.在过程中使用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.

如果您只想从ref光标中获取几列，我想可以在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.

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

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屋！