17,377
社区成员
发帖
与我相关
我的任务
分享
if (v_SqlWhere='') or (v_sqlWhere is NULL) then
v_sql := 'begin select count(*) into :v_totalRecord from ' || v_TableName || '; end; ';
else
v_sql := 'begin select count(*) into :v_totalRecord from ' || v_TableName || ' where ' || v_sqlWhere || '; end; ';
end if;
execute immediate v_sql using out v_totalRecord;
if (v_SqlWhere='') or (v_sqlWhere is NULL) then
v_sql := 'select count(*) from ' || v_TableName;
else
v_sql := 'select count(*) from ' || v_TableName || ' where ' || v_sqlWhere;
end if;
execute immediate v_sql into v_totalRecord;
--刚从SQL转向Oracle,好不容易改了,楼主测试下
CREATE OR REPLACE PACKAGE pkg_Page
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE Page (v_TableName varchar2, --表名
v_Fields varchar2 := '*', --字段名(全部字段为*)
v_OrderField varchar2, --排序字段(必须!支持多字段)
v_sqlWhere varchar2,--条件语句(不用加where)
v_pageSize number, --每页多少条记录
v_pageIndex number := 1 , --指定当前为第几页
v_TotalPage out number,
p_rc OUT myrctype);
END pkg_Page;
/
CREATE OR REPLACE PACKAGE BODY pkg_Page
AS
Procedure Page(
v_TableName varchar2, --表名
v_Fields varchar2 := '*', --字段名(全部字段为*)
v_OrderField varchar2, --排序字段(必须!支持多字段)
v_sqlWhere varchar2,--条件语句(不用加where)
v_pageSize number, --每页多少条记录
v_pageIndex number := 1 , --指定当前为第几页
v_TotalPage out number,
p_rc OUT myrctype) --返回总页数
as
v_sql varchar2(4000);
v_totalRecord number;
v_total varchar2(4000);
v_StartRecord number;
v_EndRecord number;
v_Fields2 varchar2(4000);
v_pageIndex2 number;
begin
if (v_SqlWhere='') or (v_sqlWhere is NULL) then
v_sql := 'begin select count(*) into :v_totalRecord from ' || v_TableName || '; end; ';
else
v_sql := 'begin select count(*) into :v_totalRecord from ' || v_TableName || ' where ' || v_sqlWhere || '; end; ';
end if;
execute immediate v_sql using out v_totalRecord;
--计算总数量
v_TotalPage := v_totalRecord;--CEILING((v_totalRecord || 0.0)/v_PageSize) || 1
v_total := v_totalRecord;
v_Fields2 := v_Fields || ',' || v_total || ' as totalcount';
if (v_SqlWhere='') or (v_sqlWhere is NULL) then
v_sql := 'Select * FROM (select ROW_NUMBER() Over(order by ' || v_OrderField || ') as rn,' || v_Fields || ' from ' || v_TableName ;
else
v_sql := 'Select * FROM (select ROW_NUMBER() Over(order by ' || v_OrderField || ') as rn,' || v_Fields || ' from ' || v_TableName || ' where ' || v_SqlWhere ;
end if;
--处理页数超出范围情况
if v_PageIndex<=0 then
v_pageIndex2 := 1;
end if;
if v_pageIndex>v_TotalPage then
v_pageIndex2 := v_TotalPage;
end if;
--处理开始点和结束点
v_StartRecord := (v_pageIndex2-1)*v_PageSize + 1;
v_EndRecord := v_StartRecord + v_pageSize - 1;
--继续合成sql语句
v_Sql := v_Sql || ') ' || v_TableName || ' where rn between ' || to_char(v_StartRecord) || ' and ' || to_char(v_EndRecord);
open p_rc for v_sql;
commit;
end Page;
END pkg_Page;
/