oracle plsql实现多表检索游标

需求：

  sam，sam_sub是有关联关系的两个表

  sam是主表，sam中一条数据对应的可能会有多条sam_sub数据

 

设计：

  plsql中可以通过cursor(SELECT * FROM TBL)定义游标，且可以作为被检索的字段。

 

实现：

declare
    type cursam is ref cursor return sam%rowtype;
    type cursam_sub is ref cursor return sam_sub%rowtype;
    --
    cursor c (p_v varchar2) is select 
        cursor (select * from sam where samkey = p_v ) as main
        ,cursor (select * from sam_sub where fk_samkey = p_v) as sub
    from dual where 1 = 1;
    --
    m cursam;
    mr sam%rowtype;
    s cursam_sub;
    sr sam_sub%rowtype;
    --
begin
    open c( 'id001' );
    --
    if c%notfound then
        close c;
        return;
    end if;
    --
    fetch c into m,s;
    --m
    fetch m into mr;
    if m%notfound then
        close c;
        return;
    end if;
    dbms_output.put_line('m.key = ' || mr.samkey);
    --s
    loop fetch s into sr;
        exit when s%notfound;
        dbms_output.put_line('s.key = ' || sr.fk_samkey);
    end loop;
    close c;    
end;