分页
建表
create table book (bookId number,bookName varchar2(50),publishHouse varchar2(50))
编写过程
--in:这是一个输入参数
--out:这是一个输出参数
create or replace procedure sp_book1(spBookId in number,spbookName in varchar2,
sppublishHouse in varchar2) is
begin
insert into book values(spBookId,spbookName,sppublishHouse);
end;
有输入和输出的存储过程
create or replace procedure sp_book2
(spno in number,spName out varchar2) is
begin
select ename into spName from emp where empno=spno;
end;
--返回结果集的过程
1.创建一个包,在该包中定义一个book_cursor，是一个游标
create or replace package bookpackage as
type book_cursor is ref cursor;
end bookpackage;
2.创建过程
create or replace procedure sp_book3(spNo in number,p_cursor out bookpackage.book_cursor) is
begin
open p_cursor for select * from emp where deptno=spNo;
end;
--oracle的分页
select t1.*,rownum rn from (select * from emp) t1;
select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
select * from
(select t1.*,rownum rn from (select * from emp) t1 where rownum<=10)
where rn >= 6;
开始分页
create or replace procedure fenye
(
tableName in varchar2,
Pagesize in number,
pageNow in number,
myrows out number,--总记录
myPageCount out number,--总页数
p_cursor out bookpackage.book_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和myPageCount
--组织一个sql
v_sql:='select count(*)from'||tableName;
--执行sql,并把返回值，赋给myrows;
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesize;
else
myPageCount:=myrows/Pagesize+1;
end if;
--关闭游标
close p_cursor;
end;
