系统动态游标SYS_REFCURSOR的使用

系统动态游标SYS_REFCURSOR的使用

准备数据

-- 创建表
create table test_user_info(
    user_id integer primary key,--primary key
    user_name varchar2(20),
    sex varchar2(2)
);
  
-- 插入测试数据
insert into test_user_info(user_id,user_name,sex) values(1,'JerryWong','M');
insert into test_user_info(user_id,user_name,sex) values(2,'tingting','F'); 
commit;
-- 查数 
select * from test_user_info;

准备存储过程

-- 创建过程取数 
create or replace procedure test_0124(p_cursor out sys_refcursor)
as
begin
  open p_cursor for
  select * from test_user_info;
end;

使用测试

-- 测试1
declare
  v_cursor sys_refcursor;
  u test_user_info%rowtype;
begin
  test_0124(v_cursor);
  --loop fetch v_cursor into u.user_id, u.user_name,u.sex;    
  loop 
    fetch v_cursor into u;          
    exit when v_cursor%notfound;     
    dbms_output.put_line(u.user_id||'-'||u.user_name||'-'||u.sex); 
  end loop;
end;


-- 测试2
declare
  v_cursor sys_refcursor;
  user_id integer;
  user_name varchar2(20);
  sex varchar2(2);
begin
  test_0124(v_cursor);
  loop 
    fetch v_cursor into user_id, user_name,sex;          
    exit when v_cursor%notfound;
    dbms_output.put_line(user_id||'-'||user_name||'-'||sex);
  end loop;
end;

删除测试数据

-- 删除测试过程
drop procedure test_0124
-- 删除测试表
drop table test_user_info

附:普通动态游标的创建

declare
  type rc is ref cursor; -- 定义类型
  cursor c is
    select * from dual; -- 普通静态游标

  r_cursor  rc; -- 普通动态游标
  sr_cursor sys_refcursor; -- 系统动态游标
begin
  if (to_char(sysdate, 'mi') >= 40) then
    -- ref cursor with dynamic sql 
    open r_cursor for 'select * from dim_employee';
    open sr_cursor for 'select * from dim_org_dept';
    
  elsif (to_char(sysdate, 'mi') <= 20) then
    -- ref cursor with static sql 
    open r_cursor for select * from dim_org_dept;
    open sr_cursor for select * from dim_employee;
      
  else
    -- ref cursor with static sql 
    open r_cursor for select * from dual;
    open sr_cursor for select * from dual;
      
  end if;
  
  -- the "normal" static cursor 
  open c;
  
end;