自己从网上搜索的一个比较好的:create or replace package DB_Oper is -- Author : DSJ -- Created : 2008-2-27 10:22:51 -- Purpose :
-- Public type declarations
-- Public constant declarations -- <ConstantName> constant <Datatype> := <Value>; -- Public variable declarations --<VariableName> <Datatype>; -- Public function and procedure declarations -- function <FunctionName>(<Parameter> <Datatype>) return <Datatype>; type ref_DataSet IS REF CURSOR; function GetCount(mTableName varchar2, mTerm varchar2) return number; procedure return_DataSet( mTableName in varchar2, --表名 mTerm in varchar2, --条件 mPageSize in number, --每页显示记录数 mPageIndex in number, --当前页 mOrderField in varchar2, --排序字段 mOrderStyle in number, --排序方式 0 升序 1 降序 mTotalRecords out number, --总记录数 -- mSql out varchar2--, mDateSet out ref_DataSet --记录 ); end DB_Oper;
create or replace package body DB_Oper is ----- 获取记录数 ------ function GetCount(mTableName varchar2, mTerm varchar2) return number is i number; vSql varchar2(1000); begin i := 0; vSql := 'select count(*) from ' || mTableName ;
if length(mTerm) > 0 then vSql := vSql || ' where ' || mTerm; end if;
execute IMMEDIATE vSql into i;
return(i); end;
----- 数据分页 ---------- procedure return_DataSet( mTableName in varchar2, --表名 mTerm in varchar2, --条件 mPageSize in number, --每页显示记录数 mPageIndex in number, --当前页 mOrderField in varchar2, --排序字段 mOrderStyle in number, --排序方式 0 升序 1 降序 mTotalRecords out number, --总记录数 -- mSql out varchar2, mDateSet out ref_DataSet --记录集 ) is begin declare Invalid_Input Exception; vSql varchar2(1000); Start_page number; End_page number; n_PageIndex number; n_PageSize number; begin mTotalRecords := GetCount(trim(mTableName), trim(mTerm)); vSql := 'select * from ' || mTableName; if length(trim(mTerm)) > 0 then vSql := vSql || ' where ' || trim(mTerm) ; end if; if length(trim(mOrderField)) > 0 then begin vSql := vSql || ' order by ' || trim(mOrderField); if morderStyle > 0 then vSql := vSql || ' desc '; end if; end; end if;
-- n_PageSize := mPageSize; if mPageSize <= 0 then n_PageSize := 10; end if; n_PageIndex := mPageIndex; if mPageIndex <= 0 then n_PageIndex := 1; end if; if mTotalRecords > 0 and n_PageIndex > 1 then begin --如果n_pageindex大于实际的页数,则取实际页数 if n_PageIndex > round((mTotalRecords / n_PageSize) + 0.5) then n_PageIndex := round((mTotalRecords / n_PageSize) + 0.5); end if; end; end if; Start_page := (n_PageIndex - 1) * n_PageSize + 1; End_page := n_PageIndex * n_PageSize; vSql := 'SELECT * FROM (SELECT A.*, rownum r FROM ( ' || vSql || ' ) A WHERE rownum <= ' || End_page || ' ) B WHERE r >= '|| Start_page; -- vSql := 'SELECT * FROM (SELECT A.*, rownum r FROM ( select * from User_info where user_no like ''0000514268%'' order by user_no desc ) A WHERE rownum <= 10 ) B WHERE r >= 1'; -- Dbms_Output.put_line(vSql); -- mSql := vSql ;
Open mDateSet for vSql; exception When Invalid_Input Then open mDateSet for select null from dual; end; -- DBMs_output.put_line(mTotalRecords);
-- Created : 2008-2-27 10:22:51
-- Purpose :
-- Public type declarations
-- Public constant declarations
-- <ConstantName> constant <Datatype> := <Value>; -- Public variable declarations
--<VariableName> <Datatype>; -- Public function and procedure declarations
-- function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;
type ref_DataSet IS REF CURSOR;
function GetCount(mTableName varchar2, mTerm varchar2) return number;
procedure return_DataSet(
mTableName in varchar2, --表名
mTerm in varchar2, --条件
mPageSize in number, --每页显示记录数
mPageIndex in number, --当前页
mOrderField in varchar2, --排序字段
mOrderStyle in number, --排序方式 0 升序 1 降序
mTotalRecords out number, --总记录数
-- mSql out varchar2--,
mDateSet out ref_DataSet --记录
);
end DB_Oper;
function GetCount(mTableName varchar2, mTerm varchar2) return number is
i number;
vSql varchar2(1000);
begin
i := 0;
vSql := 'select count(*) from ' || mTableName ;
if length(mTerm) > 0 then
vSql := vSql || ' where ' || mTerm;
end if;
execute IMMEDIATE vSql into i;
return(i);
end;
----- 数据分页 ----------
procedure return_DataSet(
mTableName in varchar2, --表名
mTerm in varchar2, --条件
mPageSize in number, --每页显示记录数
mPageIndex in number, --当前页
mOrderField in varchar2, --排序字段
mOrderStyle in number, --排序方式 0 升序 1 降序
mTotalRecords out number, --总记录数
-- mSql out varchar2,
mDateSet out ref_DataSet --记录集
) is
begin
declare
Invalid_Input Exception;
vSql varchar2(1000);
Start_page number;
End_page number;
n_PageIndex number;
n_PageSize number;
begin
mTotalRecords := GetCount(trim(mTableName), trim(mTerm));
vSql := 'select * from ' || mTableName;
if length(trim(mTerm)) > 0 then
vSql := vSql || ' where ' || trim(mTerm) ;
end if;
if length(trim(mOrderField)) > 0 then
begin
vSql := vSql || ' order by ' || trim(mOrderField);
if morderStyle > 0 then
vSql := vSql || ' desc ';
end if;
end;
end if;
--
n_PageSize := mPageSize;
if mPageSize <= 0 then
n_PageSize := 10;
end if;
n_PageIndex := mPageIndex;
if mPageIndex <= 0 then
n_PageIndex := 1;
end if;
if mTotalRecords > 0 and n_PageIndex > 1 then
begin
--如果n_pageindex大于实际的页数,则取实际页数
if n_PageIndex > round((mTotalRecords / n_PageSize) + 0.5) then
n_PageIndex := round((mTotalRecords / n_PageSize) + 0.5);
end if;
end;
end if;
Start_page := (n_PageIndex - 1) * n_PageSize + 1;
End_page := n_PageIndex * n_PageSize;
vSql := 'SELECT * FROM (SELECT A.*, rownum r FROM ( ' || vSql || ' ) A WHERE rownum <= ' || End_page || ' ) B WHERE r >= '|| Start_page;
-- vSql := 'SELECT * FROM (SELECT A.*, rownum r FROM ( select * from User_info where user_no like ''0000514268%'' order by user_no desc ) A WHERE rownum <= 10 ) B WHERE r >= 1';
-- Dbms_Output.put_line(vSql);
-- mSql := vSql ;
Open mDateSet for vSql; exception
When Invalid_Input Then
open mDateSet for select null from dual;
end;
-- DBMs_output.put_line(mTotalRecords);
end return_DataSet;
end DB_Oper;