Oracle（23）pl/sql编程 分页过程

①带有单个返回值的过程procedure

--------------  带有返回值的过程procedure ------------------
create or replace procedure pro1(v_in_empno in number, v_out_ename out emp.ename%type) is
begin
  select ename into v_out_ename from emp where empno=v_in_empno;
end;
---------------- 另外一种写法 ---as----------------
create or replace procedure pro1(v_in_empno in number, v_out_ename out emp.ename%type) as
begin
  select ename into v_out_ename from emp where empno=v_in_empno;
end pro1;

②带有返回值列表的过程procedure

create or replace package pack1 is
--定义一个游标数据类型
type my_cursor is ref cursor;
end;
/

create or replace procedure pro1(v_in_deptno in number, v_out_result out pack1.my_cursor) is
begin
  --打开游标
  open v_out_result for select * from emp where deptno = v_in_deptno;
  --不能在这里关闭游标
  --close v_out_result;
end;

③带有返回值列表的分页过程procedure

create or replace procedure fenyePro
(v_in_table in varchar2, 
v_in_pagesize in number, v_in_pagenow in number,
v_out_result out pack1.my_cursor) is
--定义变量
v_sql varchar2(2000);
v_start number;
v_end number;

begin
  --计算v_start，v_end
  v_start:=v_in_pagesize*(v_in_pagenow-1) + 1;
  v_end:= v_in_pagesize*v_in_pagenow;

  v_sql:='select t2.* from  (select t1.*, rownum rn from (select * from '
  ||v_in_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
  --打开游标
  open v_out_result for v_sql;

  --不能在这里关闭游标
  --close v_out_result;
end;