Oracle 创建分页存储过程（转帖）

原贴地址：http://19880614.blog.51cto.com/4202939/1316560

ps：源代码还有很多错误，我修改了

 

---------------------------------------------这是分割线-------------------------------------------------------

以下为创建包

create or replace package p_page is-- Author : PHARAOHS-- Created : 2006-4-30 14:14:14-- Purpose : 分页过程TYPE type_cur IS REF CURSOR;--定义游标变量用于返回记录集PROCEDURE Pagination(Pindex in number, ---分页索引Psql in varchar2, --产生dataset的sql语句Psize in number, --页面大小Pcount out number, --返回分页总数v_cur out type_cur ); --返回当前页数据记录procedure PageRecordsCount(Psqlcount in varchar2, --产生dataset的sql语句Prcount out number); ---返回记录总数end p_page;

 

---------------------------------------------这是分割线-------------------------------------------------------

以下创建包体

create or replace package body p_page isPROCEDURE Pagination (Pindex in number,Psql in varchar2,Psize in number,Pcount out number,v_cur out type_cur)ASv_sql VARCHAR2(1000);v_count number;v_Plow number;v_Phei number;Begin------------------------------------------------------------取分页总数v_sql := 'select count (*) from (' || Psql || ')';execute immediate v_sql into v_count;Pcount := ceil (v_count/Psize);------------------------------------------------------------显示任意页内容v_Phei := Pindex * Psize + Psize;v_Plow := v_Phei - Psize + 1;--Psql := 'select rownum rn,t.* from zzda t' ; --rownumv_sql := 'select * from (' || Psql || '( where rn between ' || v_Plow || ' and ' || v_Phei ;open v_cur for v_sql;End Pagination;--**************************************************************************************procedure PageRecordsCount(Psqlcount in varchar2,Prcount out number)asv_sql varchar2(1000);v_prcount number;beginv_sql := 'select count(*) from (' || Psqlcount || ')';execute immediate v_sql into v_prcount;Prcount := v_prcount; --返回记录总数end PageRecordsCount;--**************************************************************************************end p_page;

 

转载于:https://www.cnblogs.com/iyoume2008/p/4665792.html

相关资源：JAVA上百实例源码以及开源项目