存储过程分页查询(ORACLE)

发布时间：2020-12-12 15:34:07 所属栏目：百科来源：网络整理

导读：CREATE OR REPLACE PACKAGE PKG_QUERY_PAGEDATA IS -- 存储过程分页查询包. -- 原创 : 三界. -- CREATED : 2011-12-06 09:00 -- EDIT : 2012-01-11 10:30/2014-09-09 15:00/ --留档备查. TYPE PageDataCursor IS REF CURSOR; --根据表名称和查询过滤条件、排

CREATE OR REPLACE PACKAGE PKG_QUERY_PAGEDATA IS
-- 存储过程分页查询包. -- 原创 : 三界. -- CREATED : 2011-12-06 09:00 -- EDIT : 2012-01-11 10:30/2014-09-09 15:00/
--留档备查. TYPE PageDataCursor IS REF CURSOR; --根据表名称和查询过滤条件、排序字段条件、分页条件，组合SQL查询. PROCEDURE TABLEPAGEDATA(QueryTableName Varchar2, WhereClause Varchar2,Tohoma; font-size:14px; line-height:22.4px"> OrderFields Varchar2,Tohoma; font-size:14px; line-height:22.4px"> PageIndex int default 1,Tohoma; font-size:14px; line-height:22.4px"> PageSize int default 1000,Tohoma; font-size:14px; line-height:22.4px"> TotalRows out number,Tohoma; font-size:14px; line-height:22.4px"> PageData_out out PageDataCursor); --根据模块预定义SQL语句执行的分页查询. --用法：根据模块号执行查询SQL语句，再添加查询过滤条件、排序字段条件、分页条件，组合SQL查询. Function PAGEDATA(ModuleNo Varchar2,Tohoma; font-size:14px; line-height:22.4px"> WhereClause Varchar2,Tohoma; font-size:14px; line-height:22.4px"> OrderFields Varchar2,Tohoma; font-size:14px; line-height:22.4px"> PageIndex int default 1,Tohoma; font-size:14px; line-height:22.4px"> PageSize int default 1000,Tohoma; font-size:14px; line-height:22.4px"> PageData_out out PageDataCursor) RETURN NUMBER; END;

CREATE OR REPLACE PACKAGE BODY PKG_QUERY_PAGEDATA IS
--根据表名称组合SQL语句执行的分页查询. PROCEDURE TABLEPAGEDATA(QueryTableName Varchar2, WhereClause Varchar2, OrderFields Varchar2, PageIndex int default 1, PageSize int default 1000, TotalRows out number, PageData_out out PageDataCursor) AS AStringSQL varchar2(8000); BEGIN --QueryTableName:查询表名称. --WhereClause:查询过滤条件. --OrderFields:排序字段，允许包含关键字DESC/ASC. --PageIndex:查询页码:1-n.默认1. --PageSize:每页记录数.默认1000. --TotalRows:输出所有页总记录行数. --PageData_out:输出查询页的记录集. --A.拼接统计记录总行数的SQL语句. if (WhereClause is not null) then AStringSQL := 'SELECT COUNT(*) FROM ' || QueryTableName || ' WHERE ' || WhereClause; else AStringSQL := 'SELECT COUNT(*) FROM ' || QueryTableName; end if; execute immediate AStringSQL into TotalRows; --B.拼接分页查询的SQL语句 if (WhereClause is not null) then AStringSQL := 'SELECT * FROM ' || QueryTableName || ' WHERE ' || WhereClause; else AStringSQL := 'SELECT COUNT(*) FROM ' || QueryTableName; end if; if (OrderFields is not null) then if REGEXP_LIKE(OrderFields,'^(s*ORDERs+BYs)','min') THEN AStringSQL := AStringSQL || ' ' || OrderFields; else AStringSQL := AStringSQL || ' ORDER BY ' || OrderFields; end if; end if; --嵌套分页查询语句 AStringSQL := 'SELECT * FROM (SELECT T.*,ROWNUM AS ROWNO FROM (' || AStringSQL || ') T WHERE ROWNUM < ' || to_char(PageSize * PageIndex + 1) || ') TT WHERE ROWNO > ' || to_char(PageSize * (PageIndex - 1)); open PageData_out for AStringSQL; END;
--根据模块预定义SQL语句执行的分页查询. Function PAGEDATA(ModuleNo Varchar2, WhereClause Varchar2, OrderFields Varchar2, PageIndex int default 1, PageSize int default 1000, PageData_out out PageDataCursor) RETURN NUMBER AS AStringSQLFind varchar2(8000); AStringSQLCount varchar2(8000); APattern varchar2(1000); TotalRows number; BEGIN --ModuleNo:模块编号. --WhereClause:查询过滤条件.允许空值. --OrderFields:排序字段，也允许包含关键字DESC/ASC. --PageIndex:查询页码:1-n. --PageSize:每页记录数.默认每页1000行. --PageData_out:输出查询页记录集. --TotalRows:输出所有页总记录行数. --根据模块编号读取预定义的统计记录数SQL和分页查询SQL语句. --SQL语句预定义存储表：A_SQLMASTER. --预定义的统计记录数的SQL Example:SELECT COUNT(*) FROM USERS A JOIN COMPANY B ON (A.CORP_ID=B.CORP_ID) WHERE (1=1) --预定义的分页查询的SQL Example:SELECT A.*,B.CORP_NAME FROM USERS A JOIN COMPANY B ON (A.CORP_ID=B.CORP_ID) WHERE (1=1) ORDER BY USER_NAME DESC SELECT MIN(SQLWITHORDER),MIN(SQLSTATROW) INTO AStringSQLFind,AStringSQLCount FROM A_SQLMASTER WHERE MODULE_NO = ModuleNo AND ROWNUM = 1; --A.拼接统计记录总行数的SQL语句. --换替where (1 = 1)字符串为参数过滤条件. if (WhereClause is not null) then AStringSQLCount := REGEXP_REPLACE(AStringSQLCount,'(ds*=s*d)','(' || WhereClause || ')',1,'min'); end if; execute immediate AStringSQLCount into TotalRows; --B.拼接分页查询的SQL语句 --替换where (1 = 1)字符串为参数过滤条件. if (WhereClause is not null) then AStringSQLFind := REGEXP_REPLACE(AStringSQLFind,'min'); end if; --替换最后位置的ORDER BY语句段. if (OrderFields is not null) then APattern := 's+ORDERs+BYs+(w+.)?w+(s+DESC|s+ASC)?(,s+(w+.)?w+(s+DESC|s+ASC)?)?'; if REGEXP_LIKE(OrderFields,'min') then AStringSQLFind := REGEXP_REPLACE(AStringSQLFind,APattern,' ' || OrderFields, REGEXP_COUNT(AStringSQLFind,'min'),'min'); else AStringSQLFind := REGEXP_REPLACE(AStringSQLFind,' ORDER BY ' || OrderFields,'min'); end if; end if; --嵌套分页查询语句. --注意对rownum别名的使用，内层嵌套用rownum，外层嵌套用别名ROWNO. AStringSQLFind := 'SELECT * FROM (SELECT T.*,ROWNUM AS ROWNO FROM (' || AStringSQLFind || ') T WHERE ROWNUM < ' || to_char(PageSize * PageIndex + 1) || ') TT WHERE ROWNO > ' || to_char(PageSize * (PageIndex - 1)); open PageData_out for AStringSQLFind; RETURN TotalRows; END; END;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!