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;
/
oracle分页的存储过程
最新推荐文章于 2024-10-12 22:35:37 发布