pl/sql分页

2019独角兽企业重金招聘Python工程师标准>>>

分页

建表
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;
 

转载于:https://my.oschina.net/869088067/blog/2249421