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;