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

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

--------------  带有返回值的过程procedure ------------------create or replace procedure pro1(v_in_empno in number, v_out_ename out emp.ename%type) isbegin  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) asbegin  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) isbegin  --打开游标  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;