Question

oracle sys_refcursor调用

如图，在pl/sql中，或Dbeaver中怎么调用？谢谢？

Answer 1

　　这是一个分页功能
　　create or replace procedure pro_teac_page
(
in_index in number,
in_size in number,
ou_resu out sys_refcursor,
ou_count out number
)
as
v_start number;
v_end number;
v_sql varchar2(500);
begin
select count(*) into ou_count from teacher;
v_start := (in_index-1)*in_size;
v_end := in_index * in_size;
v_sql :=
'select * from'||
' (select tno,tname,deptno,sal,rownum as r from teacher ) '||
' where r> :1 and r <= :2 ';
open ou_resu for v_sql using v_start , v_end;

end pro_teac_page;

　　--调用过程 时报 第10行错误提示 变量不在列表中
　　declare
v_index number :=1;
v_size number :=5;
v_resu sys_refcursor;
v_tea_row teacher%rowtype;
v_count number;
begin
pro_teac_page(v_index,v_size,v_resu,v_count);
loop
fetch v_resu into v_tea_row;
exit when v_resu%notfound;
dbms_output.put_line(v_tea_row.tno||' '||v_tea_row.tname||' '||v_tea_row.sal);

end loop;
close v_resu;
end;