日期:2014-05-17 浏览次数:21068 次
CREATE PROCEDURE "SXPM"."CM_GSM_COUNTRY_JIHUABU_P"
/**
* Auther : yangbx
* Cdate : 2013-08-19
* 项目
*/
(GRADING in number,--粒度
TIME in varchar2,--日期
V_RS out PKG_PUB_RS.RS
)is
SQLSTR varchar2(20000);
L_START_DATE date;
L_END_DATE date;
L_DATE date;
begin
select TO_DATE(TIME, 'yyyy-mm-dd') into L_DATE from DUAL;
if GRADING = 0 then --月粒度
select TRUNC(L_DATE, 'MONTH'),last_day(trunc(L_DATE,'MONTH'))
into L_START_DATE, L_END_DATE
from DUAL;
elsif GRADING = 1 then --周粒度
--根据日期计算出本周
select TRUNC(L_DATE, 'd') + 1, TRUNC(L_DATE, 'd') + 7
into L_START_DATE, L_END_DATE
from DUAL;
elsif GRADING = 2 then --天粒度
select L_DATE,L_DATE
into L_START_DATE, L_END_DATE
from DUAL;
end if;
SQLSTR :='select L_DATE 时间,
CITY_NAME 地市,
COUNTRY 区县,
'''' 覆盖面积,
AVG(JIZHAN_SHIWAI_CHENGSHI) 基站数,
AVG(XIAOQU_SHIWAI_CHENGSHI) 小区数,
AVG(ZAIPIN_SHIWAI_CHENGSHI) 载频数,
AVG(JIZHAN_SHINEI_CHENGSHI) 基站数,
AVG(XIAOQU_SHINEI_CHENGSHI) 小区数,
AVG(ZAIPIN_SHINEI_CHENGSHI) 载频数,
'''' 覆盖面积,
AVG(JIZHAN_SHIWAI_NONGCUN) 基站数,
AVG(XIAOQU_SHIWAI_NONGCUN) 小区数,