oracle分页的存储过程

来源：互联网发布：苹果mac重做系统编辑：程序博客网时间：2024/04/26 21:50

CREATE OR REPLACE PACKAGE BODY "PKG_DATA" AS
--分页存储过程
PROCEDURE pp_page (ascount OUT INTEGER,cur_Locations OUT T_CURSOR,astable IN VARCHAR2 ,PageSize IN INTEGER ,pagecurren IN INTEGER,showstr IN VARCHAR2,wherestr IN VARCHAR2 DEFAULT '(1=1)',orderstr IN VARCHAR2 DEFAULT '')
IS
s_sql VARCHAR2(1000);
li_num NUMBER(8);
lswheres VARCHAR2(200);
BEGIN
    --cur_Locations 返回结果集
    --ascount 记录数
    --astable -表名、视图名、查询语句
    -- PageSize -每页的大小(行数)
    -- pagecurren -要显示的页从1开始
    --showstr 要显示的字段列表
    --wherestr
    --orderstr

    IF wherestr='' OR wherestr IS NULL THEN
       lswheres :='1=1';
    ELSE
       lswheres := wherestr;
    END IF;

    li_num := pagecurren * PageSize;

    s_sql :='select count(1) from '|| astable ||
             ' where ('|| lswheres ||')' ;

    EXECUTE IMMEDIATE s_sql INTO ascount;

    s_sql :='select * from (select rownum r, '||   showstr ||' from '|| astable ||
             ' where rownum <='||li_num || ' and ('|| lswheres ||')' ||
             ')   temptable where temptable.r > '|| (li_num - PageSize) ;
    OPEN cur_Locations FOR
    s_sql ;
END pp_page;

--可排序
PROCEDURE pp_pageord (ascount OUT INTEGER,cur_Locations OUT T_CURSOR,astable IN VARCHAR2 ,PageSize IN INTEGER ,pagecurren IN INTEGER,showstr IN VARCHAR2,wherestr IN VARCHAR2 DEFAULT '(1=1)',orderstr IN VARCHAR2 DEFAULT '')
IS
s_sql VARCHAR2(500);
li_num NUMBER(8);
lswheres VARCHAR2(200);
BEGIN
    --cur_Locations 返回结果集
    --ascount 记录数
    --astable -表名、视图名、查询语句
    -- PageSize -每页的大小(行数)
    -- pagecurren -要显示的页从1开始
    --showstr 要显示的字段列表
    --wherestr
    --orderstr

    IF wherestr='' OR wherestr IS NULL THEN
       lswheres :='1=1';
    ELSE
       lswheres := wherestr;
    END IF;

    li_num := pagecurren * PageSize;

    s_sql :='select count(1) from '|| astable ||
             ' where ('|| lswheres ||')' ;

    EXECUTE IMMEDIATE s_sql INTO ascount;

    s_sql :='select * from (select rownum r, '||   showstr ||' from '|| astable ||
             ' where rownum <='||li_num || ' and ('|| lswheres ||')' ||
             ')   temptable where temptable.r > '|| (li_num - PageSize) ||' order by '|| orderstr;

    OPEN cur_Locations FOR
    s_sql ;
END pp_pageord;

   PROCEDURE pp_pageprint (ascount OUT INTEGER,cur_Locations OUT T_CURSOR,astable IN VARCHAR2 ,showstr IN VARCHAR2,wherestr IN VARCHAR2 DEFAULT '(1=1)',orderstr IN VARCHAR2 DEFAULT '')
IS
s_sql VARCHAR2(500);
li_num NUMBER(8);
lsorder VARCHAR2(100);
lswheres VARCHAR2(200);
BEGIN
    --cur_Locations 返回结果集
    --ascount 记录数
    --astable -表名、视图名、查询语句
    -- PageSize -每页的大小(行数)
    -- pagecurren -要显示的页从1开始
    --showstr 要显示的字段列表
    --wherestr
    --orderstr
    IF orderstr ='' OR orderstr IS NULL THEN
        lsorder :='';
     ELSE
       lsorder :=' order by ' || orderstr;
    END IF;
    IF wherestr='' OR wherestr IS NULL THEN
       lswheres :='1=1';
    ELSE
       lswheres := wherestr;
    END IF;

    s_sql :='select count(1) from '|| astable ||
             ' where '|| lswheres ;
    EXECUTE IMMEDIATE s_sql INTO ascount;

    s_sql :='select * from '|| astable ||
             ' where '|| lswheres ||' ' || lsorder ;
    OPEN cur_Locations FOR
    s_sql ;
END pp_pageprint;
--动态查询存储过程，返回类型为游标类型
PROCEDURE sp_select_data(out_rs OUT t_cursor,sqlstr IN VARCHAR2)
AS
   lsqlstr VARCHAR2(500);
BEGIN
    lsqlstr := sqlstr;
    OPEN out_rs FOR
     lsqlstr;
    --检验以上是否有错误，如有就执行以下语句
     EXCEPTION
     WHEN NO_DATA_FOUND THEN
      NULL;
      WHEN OTHERS THEN
      NULL;
END sp_select_data;

PROCEDURE ps_needtrans_count(ascount OUT INTEGER,ttrans OUT t_cursor) IS
    ls_sql VARCHAR2(1000);
    l1 NUMBER;
    l2 NUMBER;
    l3 NUMBER;
    l4 NUMBER;
    l5 NUMBER;
    l6 NUMBER;
    l7 NUMBER;
    l8 NUMBER;
    l9 NUMBER;
    l10 NUMBER;
    l11 NUMBER;
    l12 NUMBER;
    BEGIN
    ascount :=1;
    --SELECT count(1) into l1 FROM eq_jxjh_dx WHERE is_strans ='1';
    --SELECT count(1) into l2 FROM eq_jxjh_nd WHERE is_strans ='1';
    --SELECT count(1) into l3 FROM eq_jxjh_yf WHERE is_strans ='1';
    --SELECT count(1) into l4 FROM EQ_SBLL_GZSB WHERE is_strans ='1';
    --SELECT count(1) into l5 FROM EQ_SBLL_CHILD WHERE is_strans ='1';
    --SELECT count(1) into l6 FROM EQ_SBLL_JCPB WHERE is_strans ='1' ;
    --SELECT count(1) into l7 FROM EQ_SBLL_JSZL WHERE is_strans ='1';
    --SELECT count(1) into l8 FROM EQ_SBLL_JSZT WHERE is_strans ='1' ;
    --SELECT count(1) into l9 FROM EQ_SBLL_JX WHERE is_strans ='1';
    --SELECT count(1) into l10 FROM EQ_SBLL_SGJL WHERE is_strans ='1';
    --SELECT count(1) into l11 FROM EQ_SBLL_STATE WHERE is_strans ='1';
    --SELECT count(1) into l12 FROM EQ_SBLL_YZTS WHERE is_strans ='1' ;

    ls_sql :='select '''|| l1 || ''' as dx,'||
                    ''''|| l2 || ''' as nd,'||
                    ''''|| l3 || ''' as yf,'||
                    ''''|| l4 || ''' as gzsb,'||
                    ''''|| l5 || ''' as CHILD,'||
                    ''''|| l6 || ''' as jcpb,'||
                    ''''|| l7 || ''' as jszl,'||
                    ''''|| l8 || ''' as jszt,'||
                    ''''|| l9 || ''' as jx,'||
                    ''''|| l10 || ''' as sgjl,'||
                    ''''|| l11 || ''' as STATE,'||
                    ''''|| l12 || ''' as yzts from dual';
    OPEN ttrans FOR ls_sql;
    EXCEPTION
         WHEN OTHERS THEN
          NULL;
    END;

END;
/