如何使用记录循环引用游标? [英] How to use record to loop a ref cursor?

问题描述

我想编写PL/SQL来测试包中的功能.程序包定义了一个游标类型

I want to write PL/SQL to test a function in a package. The package defines a cursor type

TYPE ref_cursor IS REF CURSOR;

我想基于该类型定义一条记录.

I want to define a record based on that type.

我的代码是:

DECLARE
  cur PACKAGE_NAME.ref_cursor; 
  rec cur%ROWTYPE;

为什么最后一行不正确?

why is last line not correct?

推荐答案

您不能基于弱类型的REF CURSOR定义记录类型.由于包中定义的游标类型可用于从具有任意列的任意查询中返回数据，因此PL/SQL编译器无法确定合适的记录类型以将数据提取到其中.

You can't define a record type based on a weakly-typed REF CURSOR. Since the cursor type defined in the package can be used to return data from an arbitrary query with arbitrary columns, the PL/SQL compiler can't determine an appropriate record type to fetch the data into.

如果您知道该函数返回的实际数据，则可以声明该类型的记录以将数据提取到其中.例如，如果我声明一个返回弱类型游标类型的函数，但是我知道该游标确实返回了基于EMP表的游标，则可以将数据提取到EMP%ROWTYPE记录中(请注意，是系统定义的弱类型REF CURSOR类型)

If you know the actual data being returned from the function, you could declare a record of that type to fetch the data into. For example, if I declare a function that returns a weakly-typed cursor type but I know that the cursor really returns a cursor based on the EMP table, I can fetch the data into an EMP%ROWTYPE record (note that SYS_REFCURSOR is a system-defined weakly-typed REF CURSOR type)

create or replace function f1
  return sys_refcursor
is
  l_rc sys_refcursor;
begin
  open l_rc
   for select *
         from emp;
  return l_rc;
end;

declare
  l_rc sys_refcursor;
  l_emp emp%rowtype;
begin
  l_rc := f1;
  loop
    fetch l_rc into l_emp;
    exit when l_rc%notfound;
    dbms_output.put_line( l_emp.empno );
  end loop;
end;

这篇关于如何使用记录循环引用游标?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！