Created
December 11, 2013 08:18
-
-
Save Wind4/7906741 to your computer and use it in GitHub Desktop.
Oracle 分頁存儲過程
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE PACKAGE pkg_Pagination IS | |
TYPE type_cur IS REF Cursor; --定义数据返回游标 | |
PROCEDURE Pagination ( | |
querySQL in varchar2, --SQL查询语句 | |
pageNum in number, --当前页数 | |
pageSize in number, --单页数量 | |
totalRecord out number, --返回总记录数 | |
v_cur out type_cur --返回数据游标 | |
); | |
END pkg_Pagination; | |
/ | |
CREATE OR REPLACE PACKAGE BODY pkg_Pagination IS | |
PROCEDURE Pagination ( | |
querySQL in varchar2, --SQL查询语句 | |
pageNum in number, --当前页数 | |
pageSize in number, --单页数量 | |
totalRecord out number, --返回总记录数 | |
v_cur out type_cur --返回数据游标 | |
) AS | |
--定义局部变量 | |
v_sql varchar2(4000); | |
v_beginRecord number; | |
v_endRecord number; | |
BEGIN | |
--计算总数 | |
v_sql := 'SELECT COUNT(*) FROM (' || querySQL || ')'; | |
EXECUTE IMMEDIATE v_sql INTO totalRecord; | |
--计算位置 | |
v_beginRecord := (pageNum - 1) * pageSize + 1; | |
v_endRecord := v_beginRecord + pageSize -1; | |
--检查起始位置 | |
IF v_beginRecord < 1 THEN | |
v_beginRecord := 1; | |
END IF; | |
--检查结束位置 | |
IF v_endRecord > totalRecord THEN | |
v_endRecord := totalRecord; | |
END IF; | |
--组装、查询SQL | |
/* 分页方法一 */ | |
--v_sql := 'SELECT * FROM (' || querySQL || ') WHERE rn BETWEEN ' || v_beginRecord || ' AND ' || v_endRecord; | |
/* 分页方法二 */ | |
--v_sql := 'SELECT * FROM (SELECT t.*, rownum rn FROM (' || querySQL || ') t WHERE rownum <= ' || v_endRecord || ') re WHERE re.rn >= ' || v_beginRecord; | |
v_sql := 'SELECT * FROM (SELECT t.*, rownum rn FROM (' || querySQL || ') t WHERE rownum <= ' || v_endRecord || ') re WHERE re.rn >= ' || v_beginRecord; | |
OPEN v_cur FOR v_sql; | |
END Pagination; | |
END pkg_Pagination; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment