通用存储过程（类型/时间）

最新推荐文章于 2022-05-07 18:36:25 发布

渊博

最新推荐文章于 2022-05-07 18:36:25 发布

阅读量563

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/zyb507/article/details/8626763

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

create or replace procedure TJ_GONGDI_BARCHART(yearValue varchar2,cur_out out sys_refcursor) is
V_SQL varchar(4000);

begin

V_SQL:='

select szqx,sum(areaZ)as areaZ,sum(areaG)as areaG from
(
select szqx, substr(to_char(createdate,''YYYY-MM-dd''),6,2)as z_month,null as areaZ,sde.st_area(shape) as areaG from TDHBGZSPT
where substr(to_char(createdate,''YYYY-MM-dd''),0,4)='||yearValue ||'
union
select szqx, substr(to_char(createdate,''YYYY-MM-dd''),6,2)as z_month,null as areaZ,sde.st_area(shape) as areaG from TDCRGZSPT
where substr(to_char(createdate,''YYYY-MM-dd''),0,4)='||yearValue ||'
union

select szqx,substr(to_char(createdate,''YYYY-MM-dd''),6,2) as z_month,sde.st_area(shape) as areaZ,null as areaG from TDZDGZSPT
where substr(to_char(createdate,''YYYY-MM-dd''),0,4)='||yearValue||'

)group by szqx order by szqx';

open cur_out for V_SQL;

end;

create or replace procedure TJ_GONGDI_LINEARGRAPH(yearValue varchar2,cur_out out sys_refcursor) IS

V_SQL varchar(4000);

begin

V_SQL:='

select z_month,sum(areaZ)as areaZ,sum(areaG) as areaG from
(
   select to_char(createdate,''mm'')as z_month,null as areaZ,sde.st_area(shape) as areaG from TDHBGZSPT
   where substr(to_char(createdate,''YYYY-MM-dd''),0,4)='||yearValue||'
   union
   select to_char(createdate,''mm'')as z_month,null as areaZ,sde.st_area(shape) as areaG from TDHBGZSPT
   where substr(to_char(createdate,''YYYY-MM-dd''),0,4)='||yearValue||'
   union
   select to_char(createdate,''mm'') as z_month,sde.st_area(shape) as areaZ,null as areaG from TDZDGZSPT
   where substr(to_char(createdate,''YYYY-MM-dd''),0,4)='||yearValue||'

)
group by z_month order by z_month';

open cur_out for V_SQL;

end;