存储过程分页查询(ORACLE)

 
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,
                          OrderFields    Varchar2,
                          PageIndex      int default 1,
                          PageSize       int default 1000,
                          TotalRows      out number,
                          PageData_out   out PageDataCursor);

  --根据模块预定义SQL语句执行的分页查询.
  --用法:根据模块号执行查询SQL语句,再添加查询过滤条件、排序字段条件、分页条件,组合SQL查询.
  Function PAGEDATA(ModuleNo     Varchar2,
                    WhereClause  Varchar2,
                    OrderFields  Varchar2,
                    PageIndex    int default 1,
                    PageSize     int default 1000,
                    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*ORDER\s+BY\s)', '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, '\(\d\s*=\s*\d\)', '(' || WhereClause || ')', 1, 1, 'min');
    end if;
    execute immediate AStringSQLCount
      into TotalRows;
  
    --B.拼接分页查询的SQL语句    
    --替换where (1 = 1)字符串为参数过滤条件.
    if (WhereClause is not null) then
      AStringSQLFind := REGEXP_REPLACE(AStringSQLFind, '\(\d\s*=\s*\d\)', '(' || WhereClause || ')', 1, 1, 'min');
    end if;
    --替换最后位置的ORDER BY语句段.
    if (OrderFields is not null) then
      APattern := '\s+ORDER\s+BY\s+(\w+\.)?\w+(\s+DESC|\s+ASC)?(,\s+(\w+\.)?\w+(\s+DESC|\s+ASC)?)?';
      if REGEXP_LIKE(OrderFields, '^(\s*ORDER\s+BY\s)', 'min') then
        AStringSQLFind := REGEXP_REPLACE(AStringSQLFind, APattern, ' ' || OrderFields, 1,
                                         REGEXP_COUNT(AStringSQLFind, APattern, 1, 'min'), 'min');
      else
        AStringSQLFind := REGEXP_REPLACE(AStringSQLFind, APattern, ' ORDER BY ' || OrderFields, 1,
                                         REGEXP_COUNT(AStringSQLFind, APattern, 1, 'min'), '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;




展开阅读全文

没有更多推荐了,返回首页