Oracle数据库分页存储过程解读。
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
????COUNToracle账号_SQL VARCHAR2(2000);
????SELECT_SQL VARCHAR2(2000);
????start_num NUMBER;
????end_num NUMBER;
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_SQL INTO 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;