oracle动态游标

/*
动态游标使用：
1：在包规范中声明自定义游标类型
type type_cursor is ref cursor;
*/


create or replace package pkg_query_all
is
  type type_ref_cursor is ref cursor;
  
  procedure queryall(p_tname nvarchar2);
  procedure queryall(p_tname nvarchar2, o_list out type_ref_cursor);
end pkg_query_all;


---------------------------------
create or replace package body pkg_query_all
is
  procedure queryall(p_tname nvarchar2)
  is 
    teacher_list pkg_query_all.type_ref_cursor;
    item teacher%rowtype;
    v_sql varchar2(1000);
  begin
    v_sql := 'select * from teacher where 1=1 ';
    if p_tname is not null then
      v_sql := v_sql || ' and tname = '''||p_tname||'''';
    end if;  
  
    open teacher_list for v_sql;
    
    loop
      fetch teacher_list into item;
      exit when teacher_list%notfound;
      dbms_output.put_line(item.tno||'-'||item.tname);
    end loop;
    
    close teacher_list;
  end queryall;
  
  procedure queryall(p_tname nvarchar2, o_list out type_ref_cursor)
  is 
    teacher_list type_ref_cursor;
    v_sql varchar2(1000);
  begin
    v_sql := 'select * from teacher where 1=1 ';
    if p_tname is not null then
      v_sql := v_sql || ' and tname = '''||p_tname||'''';
    end if;
    open teacher_list for v_sql;
    
    o_list := teacher_list;
  end queryall;
end pkg_query_all;
---------------------------------
--测试
select * from teacher;
begin
  pkg_query_all.queryall('郭嘉');
end;


declare 
  teacher_list pkg_query_all.type_ref_cursor;
  item teacher%rowtype;
begin


  pkg_query_all.queryall('曹操', teacher_list);  --参数如果为空，使用null
  
  loop
    fetch teacher_list into item;
    exit when teacher_list%notfound;
    dbms_output.put_line(item.tno||'-'||item.tname);
  end loop;
end;