CREATE OR REPLACE PACKAGE FY_PAGE
IS
--声明游标
TYPE CURSOR_PAGE IS REF CURSOR;
END;
--分页存储过程
CREATE OR REPLACE PROCEDURE
PROC_FY
(
P_TABLENAME VARCHAR2, --表名
P_TABLECOLUMN VARCHAR2, --排序查询列
P_ORDER VARCHAR2, --排序
P_PAGESIZE NUMBER, --每页大小
P_CURPAGE NUMBER, --当前页
P_WHERE VARCHAR2, --查询条件
P_ROWCOUNT
OUT NUMBER, --总条数
P_PAGECOUNT
OUT NUMBER, --总页数
P_CURSOR
OUT
FY_PAGE.CURSOR_PAGE
) --结果集
IS
COUNT_SQLVARCHAR2(2000);
SELECT_SQLVARCHAR2(2000);
start_numNUMBER;
end_numNUMBER;
BEGIN
--查询总条数
COUNT_SQL:='SELECT COUNT(*) FROM '||
P_TABLENAME;
IF P_WHERE IS NOT NULL THEN
COUNT_SQL:=
COUNT_SQL||' '||
P_WHERE;
END IF;
--执行
EXECUTE IMMEDIATE
COUNT_SQLINTO
P_ROWCOUNT;
--总页数
IF MOD(
P_ROWCOUNT,
P_PAGESIZE)=0 THEN
P_PAGECOUNT:=
P_ROWCOUNT/
P_PAGESIZE;
ELSE
P_PAGECOUNT:=
P_ROWCOUNT/
P_PAGESIZE+1;
END IF;
--当前页起始编号
start_num:=(
P_CURPAGE-1)*
P_PAGESIZE+1;
--当前页结束编号
end_num:=
P_CURPAGE*
P_PAGESIZE;
--结果集
SELECT_SQL := 'SELECT * FROM (SELECT '||
P_TABLENAME||'.*,
ROW_NUMBER() OVER(ORDER BY '||
P_TABLECOLUMN||
' '||
P_ORDER||') AID FROM '||
P_TABLENAME||' '||
P_WHERE||') A WHERE AID BETWEEN '||
start_num||' AND '||
end_num;
OPEN P_CURSOR FOR
SELECT_SQL;
END PROC_FY;
评论 (0)