Oracle PL/SQL中sys_refcursor与对象类型搭配报错问题排查

错误原因 #

你代码里的核心问题是：sys_refcursor返回的是s1表的两列独立数据（id、name），但你试图把它批量抓取到tt类型（自定义rt对象的集合）里。Oracle无法自动将零散的列映射为自定义对象实例，所以触发了类型不匹配错误。

两种可行解决方法 #

方法1：将游标查询结果转换为自定义对象类型 #

修改调用游标时的查询语句，直接把每行数据封装成rt对象，让游标返回对象类型，和存储过程里的集合类型匹配。

修改后的完整代码：

create table s1  (id  number,name varchar2(10));
create table s2 as select * from s1 where 1=2;

insert into s1 values(1,'tri');
insert into s1 values(2,'ran');

create or replace type rt as object
(id  number,name varchar2(10));
CREATE or replace TYPE tt AS TABLE OF rt;

create or replace procedure chk (c_cursor in sys_refcursor) as
sweeper_assigned_t  tt;
begin
 loop
 fetch c_cursor bulk collect into sweeper_assigned_t;
 EXIT WHEN c_cursor%NOTFOUND;
 end loop;
 
  forall i in 1..sweeper_assigned_t.count
    insert into s2 ( id, name)
  values ( sweeper_assigned_t(i).id, sweeper_assigned_t(i).name);
  commit;

end chk;

declare
    c_cur sys_refcursor;
begin
    -- 关键改动：将查询结果封装为rt对象
    open c_cur
    for select rt(id, name) from s1 ;
    
    chk( c_cur);
    
end;
/

方法2：改用与表结构匹配的集合类型 #

如果不需要自定义对象类型，可以直接用s1%rowtype来定义集合，这样游标返回的列结构和集合类型完全匹配，无需转换。

修改后的完整代码：

create table s1  (id  number,name varchar2(10));
create table s2 as select * from s1 where 1=2;

insert into s1 values(1,'tri');
insert into s1 values(2,'ran');

-- 移除自定义对象类型，改用基于表行的集合
create or replace TYPE tt AS TABLE OF s1%rowtype;

create or replace procedure chk (c_cursor in sys_refcursor) as
sweeper_assigned_t  tt;
begin
 loop
 fetch c_cursor bulk collect into sweeper_assigned_t;
 EXIT WHEN c_cursor%NOTFOUND;
 end loop;
 
  forall i in 1..sweeper_assigned_t.count
    insert into s2 values sweeper_assigned_t(i); -- 可以直接用行记录插入
  commit;

end chk;

declare
    c_cur sys_refcursor;
begin
    open c_cur
    for select * from s1 ;
    
    chk( c_cur);
    
end;
/

验证 #

两种方法执行后，查询s2表都会看到两行数据：

select * from s2;

输出：

ID | NAME
---|-----
1  | tri
2  | ran

内容的提问来源于stack exchange，提问作者TBose