韩顺平.玩转oracle - 编写分页过程

分页是任何一个网站（bbs、网上商城、blog）都会使用到的技术

无返回值的存储过程

书号	书名	出版社

--上图为book表，编写一个过程，可以向book表添加书
create procedure sp_pro7(spbookid in number,spbookname in varchar2,sppublishhouse in varchar2) is
begin
insert into book values(spbookid,spbookname,sppublishhouse);
end;

有返回值的存储过程（非列表）

--编写一个过程，可以输入雇员的编号，返回该雇员的姓名
create procedure sp_pro8(spno in number,spname out varchar2) is
begin
select ename into spname from emp where empno=spno;
end;
/
--编写一个过程，输入雇员编号，返回该雇员的姓名、工资和岗位
create procedure sp_pro9(spno in number,spname out varchar2,spsal out number,spjob out varchar2) is
begin
select ename,sal,job into spname,spsal,spjob from emp
where empno=spno;
end;
/

有返回值的存储过程（列表[结果集]）

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外。
但由于是集合，所以不能用一般的参数，必须要用package

--编写一个过程，输入部门号，返回该部门所有雇员信息
--分为两部分
--第一部分，建一个包
create package test1 as
type test_cursor is ref cursor --定义类型为游标
end test1;
--第二部分，建立存储过程
create procedure test2(myno in number,p_cursor out test1.test_sursor) is
begin
open p_cursor for select * from emp
where deptno = no;
end test2;

/*编写一个存储过程，要求可以输入表名、每页显示记录数、当前页
返回总记录数、总页数和返回的结果集*/
--先开发一个包
create package test3 as
type test_cursor is ref cursor;
end test3;
create procedure fenye (
tablename in varchar2,pagesize in number,pagenow in number,
myrows out number,pagecount out number,p_cursor out test3.test_cursor) is
--定义部分
--定义sql语句 字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pagenow-1)* pagesize+1;
v_end number:= pagenow*pagesize;
begin
--执行部分
v_sql:='select * from (select t1.*,rownum rn from
(select * from '||tablename||')t1
where rownum <='||v_end||')
where rn >='||v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算myrows和pagecount
--组织一个sql：执行sql，并把返回的值赋给myrows
v_sql:='select count(*) from '||tablename;
excute immediate v_sql into myrows;
--计算pagecount
if mod(myrows,pagesize)=0 then
pagecount:=myrows/pagesize;
else
pagecount:=myrows/pagesize+1;
end if;
--关闭游标
close p_cursor;
end;
/