oracle中使用触发器实现查询分页功能
oracle包分为包规范和包体一：首先创建包规范
create or replace package p_pagewithgroup is
-- Author : 肖伟
-- Created : 2014/3/18
-- Purpose : 分组分页过程
TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
PROCEDURE UP_GetRecordWithGroupByPage(
tblName in varchar2, --表名
PageSize in number, --页面大小
PageIndex in number, --当前页
IsReCount out number,--返回总条数
strWhere in varchar2,-- 查询条件 (注意: 不要加 where)
strColums in varchar2, --字段集合，逗号分割
strSelectColums in varchar2, --字段集合，逗号分割
strGroup in varchar2,--分组条件语句
strOrder in varchar2,-- 排序条件语句
v_cur out type_cur --返回当前页数据记录
);
end p_pagewithgroup;
二：创建包结构体
create or replace package body p_pagewithgroup is
PROCEDURE UP_GetRecordWithGroupByPage(
tblName in varchar2, --表名
PageSize in number, --页面大小
PageIndex in number, --当前页
IsReCount out number,--返回总条数
strWhere in varchar2,-- 查询条件 (注意: 不要加 where)
strColums in varchar2, --字段集合，逗号分割
strSelectColums in varchar2, --字段集合，逗号分割
strGroup in varchar2,--分组条件语句
strOrder in varchar2,-- 排序条件语句
v_cur out type_cur --返回当前页数据记录
)
AS
--定义变量
v_sql VARCHAR2(8000);
v_count number;
v_row_start NUMBER; --开始记录
v_row_end NUMBER; --结束记录
begin
------------------------------------------------------------显示总条数
v_sql := 'select count(1) from ' || tblName;
IF strWhere is not NULL OR strWhere <> ''
THEN
v_sql := v_sql || ' where ' || strWhere;
END IF;
IF strGroup is not null or strGroup <> ''
then
v_sql := v_sql || ' group by ' || strGroup;
end if;
DBMS_OUTPUT.put_line (v_sql);
--v_sql:='select count(1) from BS_USER where 1=1';
EXECUTE IMMEDIATE v_sql into v_count ;
IsReCount:=v_count;
------------------------------------------------------------显示任意页内容
-- IF PageSize<1 THEN
-- PageSize:=1;
-- END IF;
v_row_start := (PageIndex - 1) * PageSize + 1;
v_row_end := PageIndex * PageSize;
v_sql:='select '||strSelectColums||' from (select t.*,RowNum as rn from (select '||strColums||' from '||tblName;
IF strWhere is not NULL OR strWhere <> ''
THEN
v_sql := v_sql || ' where ' || strWhere;
END IF;
IF strGroup is not null or strGroup <> ''
then
v_sql := v_sql || ' group by ' || strGroup;
end if;
IF strOrder is not null or strOrder <> ''
then
v_sql := v_sql || ' order by ' || strOrder;
end if;
v_sql := v_sql ||') t) where rn between '||v_row_start||' and '||v_row_end;
DBMS_OUTPUT.put_line (v_sql);
open v_cur for v_sql;
End UP_GetRecordWithGroupByPage;
----------------------------------------------------------
end p_pagewithgroup;
三：测试
首先打开PL/SQL command window
SQL>declare
-- Local variables here
IsReCount number;--返回总条数
my_cur p_pagewithgroup.type_cur ; --返回当前页数据记录
begin
-- Test statements here
p_pagewithgroup.UP_GetRecordWithGroupByPage('T_DIVISION',5,1,ISReCount,' 1=1 ',' * ',' * ',null,null,my_cur );
DBMS_OUTPUT.PUT_LINE(TO_CHAR(IsReCount));
end;
分享到：