oracle 如何返回 pl/sql 表的游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11150595/
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 return a Cursor for pl/sql table
提问by zapumal
I select data from several tables. Then i need to edit the data returned from the cursor before returning. The cursor will then be passed to a perl script to display the rows.
我从几个表中选择数据。然后我需要在返回之前编辑从光标返回的数据。然后将光标传递给 perl 脚本以显示行。
To that i build a pl/sql table as in the following code. What i need to know is how to return the to that table ?
为此，我构建了一个 pl/sql 表，如下面的代码所示。我需要知道的是如何返回到那个表？
At present i get the error "table or view doesn't exist". Test code i use for a simple table is attached here.
目前我收到错误“表或视图不存在”。我用于简单表格的测试代码附在此处。
CREATE OR REPLACE FUNCTION test_rep
RETURN SYS_REFCURSOR
AS
CURSOR rec_Cur IS
SELECT table1.NAME,
table1.ID
FROM TESTREPORT table1;
TYPE rec_Table IS TABLE OF rec_Cur%ROWTYPE INDEX BY PLS_INTEGER;
working_Rec_Table rec_Table;
TYPE n_trade_rec IS RECORD
(
NAME VARCHAR2(15),
ID NUMBER
);
TYPE ga_novated_trades IS TABLE OF n_trade_rec index by VARCHAR2(15);
va_novated_trades ga_novated_trades;
v_unique_key VARCHAR2(15);
TYPE db_cursor IS REF CURSOR;
db_cursor2 db_cursor;
BEGIN
OPEN rec_Cur;
FETCH rec_Cur BULK COLLECT INTO working_Rec_Table;
FOR I IN 1..working_Rec_Table.COUNT LOOP
v_unique_key := working_Rec_Table(I).NAME;
va_novated_trades(v_unique_key).NAME := working_Rec_Table(I).NAME;
va_novated_trades(v_unique_key).ID := working_Rec_Table(I).ID;
END LOOP; --FOR LOOP
OPEN db_cursor2 FOR SELECT * FROM va_novated_trades; --ERROR LINE
CLOSE rec_Cur;
RETURN db_cursor2;
END test_rep;
/
采纳答案by A.B.Cade
Basically there is a way to select from a table typein oracle using the TABLE()function
基本上有一种方法可以table type使用该TABLE()函数从oracle 中进行选择
SELECT * FROM table(va_novated_trades);
But this works only for schematable types and on plsql tables(table types defined in the SCHEMA and not in a plsql package):
但这仅适用于模式表类型和plsql tables（在 SCHEMA 中定义的表类型，而不是在 plsql 包中）：
CREATE TYPE n_trade_rec AS OBJECT
(
NAME VARCHAR2(15),
ID NUMBER
);
CREATE TYPE ga_novated_trades AS TABLE OF n_trade_rec;
But I still think you should try to do it all in a query (and/or in the perl script),
但我仍然认为您应该尝试在查询中（和/或在 perl 脚本中）完成这一切，
For example, there is one field where i have to analyse the 4th character and then edit other fields accordingly
例如，有一个字段，我必须分析第 4 个字符，然后相应地编辑其他字段
This can be achieved in the query, could be something like:
这可以在查询中实现，可能是这样的：
select case when substr(one_field, 4, 1) = 'A' then 'A.' || sec_field
when substr(one_field, 4, 1) = 'B' then 'B.' || sec_field
else sec_field
end as new_sec_field,
case when substr(one_field, 4, 1) = 'A' then 100 * trd_field
when substr(one_field, 4, 1) = 'B' then 1000 * trd_field
else trd_field
end as new_trd_field,
-- and so on
from TESTREPORT
