oracle 如何声明弱类型 SYS_REFCURSOR 变量的 %ROWTYPE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11187376/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to declare %ROWTYPE of a variable that is a weakly typed SYS_REFCURSOR?
提问by Vishal Saxena
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
下面的代码我不能将 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;
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 中返回的列定义静态表 R，然后声明为 L_RECORD R%ROWTYPE。
And hence the question: how to declare %ROWTYPE of a variable that is a weakly typed SYS_REFCURSOR ?
因此，问题是：如何声明弱类型 SYS_REFCURSOR 变量的 %ROWTYPE ？
回答by Alex Poole
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;
...
And then fetch into the list of columns:
然后获取到列列表：
FETCH P_RS INTO L_T_COL1, L_T_COL2, ... ;
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.
这很痛苦，但只要您知道在 ref 光标中期望什么，就可以管理。T.*但是，在您的过程中使用会使这变得脆弱，因为向表中添加一列会破坏认为它知道有哪些列以及它们处于什么顺序的代码。（如果表不是，您也可以在环境之间破坏它始终如一地构建 - 我已经看到在不同环境中列排序不同的地方）。您可能希望确保您只选择您真正关心的列，以避免必须为您永远不会阅读的内容定义变量。
From 11g you can use the DBMS_SQLpackage to convert your sys_refcursorinto a DBMS_SQLcursor, 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:
从 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;
/
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.
这没什么实际用途，为了简洁起见，我将每个值都视为字符串，因为无论如何我只想打印它。查看文档并搜索更多实际应用的示例。
If you only want a few columns from your ref cursor you could, I suppose, loop around l_descand record the position where column_nameis 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.
如果你只想要你的 ref 光标中的几列，我想你可以循环l_desc并记录column_name你感兴趣的位置，作为一个数字变量；然后您可以稍后通过该变量引用该列，您通常会在游标循环中使用该名称。取决于你对数据做什么。
But unless you're expectingto 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.
但是，除非您希望不知道要返回的列顺序，这不太可能，因为您似乎控制了该过程 - 并假设您摆脱了.*s - 您最好将返回的列减少到您需要的最低限度，只需单独声明它们。
