oracle 从函数中获取返回一个引用游标以记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1106555/
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
fetch from function returning a ref cursor to record
提问by neesh
I have a function in a package that returns a REF CURSOR to a RECORD. I am trying to call this function from a code block. The calling code looks like this:
我在包中有一个函数，可以将 REF CURSOR 返回到 RECORD。我正在尝试从代码块中调用此函数。调用代码如下所示：
declare
a_record package_name.record_name;
cursor c_symbols is select package_name.function_name('argument') from dual;
begin
open c_symbols;
loop
fetch c_symbols into a_record;
exit when c_symbols%notfound;
end loop;
close c_symbols;
end;
The function declaration as part of package_name looks something like this:
作为 package_name 一部分的函数声明如下所示：
TYPE record_name IS RECORD(
field_a varchar2(20);
);
TYPE record_cursor IS REF CURSOR RETURN record_name;
FUNCTION getsymbols(argument IN varchar2) return record_cursor;
When I try to run the calling code block, I get the exception: PLS-00386: type mismatch found at 'EXAMPLE_SYMBOLS' between FETCH cursor and INTO variables.
当我尝试运行调用代码块时，出现异常：PLS-00386：在 FETCH 游标和 INTO 变量之间的“EXAMPLE_SYMBOLS”处发现类型不匹配。
What should the type of a_record be and how can I access individual elements of the record I am fetching(of type record_name)?
a_record 的类型应该是什么，我如何访问我正在获取的记录的各个元素（record_name 类型）？
回答by Dave Costa
I suspect that you think that your cursor should be fetching rows from the REFCURSOR. It's not. The REFCURSOR is itself a cursor, you don't use another cursor to select from it.
我怀疑您认为游标应该从 REFCURSOR 获取行。它不是。REFCURSOR 本身就是一个游标，您不能使用另一个游标从中进行选择。
What your current cursor is doing is fetching a single row, with a single column, containing the result of the function call. Which is a record_cursornot a record_name, so you get a type mismatch.
您当前的游标正在做的是获取包含函数调用结果的单行、单列。哪个record_cursor不是 a record_name，因此您会遇到类型不匹配。
I suspect what you really want to do is something like this:
我怀疑你真正想做的是这样的：
declare
symbol_cursor package_name.record_cursor;
symbol_record package_name.record_name;
begin
symbol_cursor := package_name.function_name('argument');
loop
fetch symbol_cursor into symbol_record;
exit when symbol_cursor%notfound;
-- Do something with each record here, e.g.:
dbms_output.put_line( symbol_record.field_a );
end loop;
CLOSE symbol_cursor;
end;
回答by Jeffrey Kemp
The function returns a record_cursor, so I would expect a_recordshould also be a record_cursor. However, it is not clear why you are returning a ref cursor anyway - why can't the function return a record_nametype instead?
该函数返回 a record_cursor，所以我希望a_record也应该是 a record_cursor。但是，尚不清楚您为什么要返回 ref 游标 - 为什么该函数不能返回record_name类型？
回答by Juergen Hartelt
The pl/sql block to read out the ref cursor looks a bit strange to me. Oracle might not be able to match the type of your cursor c_symbolswith the type package_name.record_cursor.
读取引用游标的 pl/sql 块对我来说看起来有点奇怪。Oracle 可能无法将您的游标c_symbols类型与 type匹配package_name.record_cursor。
Suggestion:
建议：
- change the declaration of
c_symbolsto "c_symbols package_name.record_cursor" - replace the statement "
open c_symbols" with "c_symbols := package_name.function_name('argument')"
- 将声明更改
c_symbols为“c_symbols package_name.record_cursor” - 将语句“
open c_symbols”替换为“c_symbols := package_name.function_name('argument')”
As long as the called function really does return a cursor, that should work. Else, you might want to post actual source code.
只要被调用的函数确实返回一个游标，就应该可以工作。否则，您可能想要发布实际的源代码。
