从函数中获取并返回引用光标以进行记录

本文介绍了从函数中获取并返回引用光标以进行记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在包装中有一个函数，该函数将REF CURSOR返回到RECORD.我试图从代码块中调用此函数.调用代码如下:

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:

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;

作为package_name一部分的函数声明看起来像这样:

The function declaration as part of package_name looks something like this:

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;

当我尝试运行调用代码块时，出现异常:PLS-00386:在FETCH游标和INTO变量之间的'EXAMPLE_SYMBOLS'中发现类型不匹配.

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.

a_record的类型应该是什么?如何访问我要提取的(record_name类型的)记录的各个元素?

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)?

推荐答案

我怀疑您认为您的光标应该从REFCURSOR获取行.它不是. REFCURSOR本身就是一个游标，您无需使用其他游标进行选择.

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.

您当前的游标正在执行的操作是获取包含函数调用结果的单行单列.这是一个record_cursor而不是record_name，因此您会遇到类型不匹配的情况.

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_cursor not a record_name, so you get a type mismatch.

我怀疑您真正想做的事情是这样的:

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;