这是我写的一个存储过程,然后一直报错
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := . ( % ;
麻烦帮忙看看是哪里错了,谢谢
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) 小区数,
AVG(ZAIPIN_SHIWAI_NONGCUN) 载频数,
AVG(JIZHAN_SHINEI_NONGCUN) 基站数,
AVG(XIAOQU_SHINEI_NONGCUN) 小区数,
AVG(ZAIPIN_SHINEI_NONGCUN) 载频数,
'''' 覆盖面积,
AVG(XIAOQU_SHIWAI) 宏站个数,
AVG(XIAOQU_SHINEI) 室分个数,
AVG(XIAOQU) 小区个数,
AVG(ZAIPIN) 载频总数
from SXPM.CM_GSM_COUNTRY_JIHUABU
where COLLECT_DATE>=:L_START_DATE and COLLECT_DATE<=:L_END_DATE
group by L_DATE,CITY_NAME,COUNTRY';
open V_RS for SQLSTR using L_START_DATE,L_END_DATE;
end;
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := . ( % ;
麻烦帮忙看看是哪里错了,谢谢
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) 小区数,
AVG(ZAIPIN_SHIWAI_NONGCUN) 载频数,
AVG(JIZHAN_SHINEI_NONGCUN) 基站数,
AVG(XIAOQU_SHINEI_NONGCUN) 小区数,
AVG(ZAIPIN_SHINEI_NONGCUN) 载频数,
'''' 覆盖面积,
AVG(XIAOQU_SHIWAI) 宏站个数,
AVG(XIAOQU_SHINEI) 室分个数,
AVG(XIAOQU) 小区个数,
AVG(ZAIPIN) 载频总数
from SXPM.CM_GSM_COUNTRY_JIHUABU
where COLLECT_DATE>=:L_START_DATE and COLLECT_DATE<=:L_END_DATE
group by L_DATE,CITY_NAME,COUNTRY';
open V_RS for SQLSTR using L_START_DATE,L_END_DATE;
end;
简单看了下:“ SQLSTR :='select L_DATE 时间, ”如果没理解错的话,你这里是想取一个常量,那这么写就是错误的!!!
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) 小区数,
AVG(ZAIPIN_SHIWAI_NONGCUN) 载频数,
AVG(JIZHAN_SHINEI_NONGCUN) 基站数,
AVG(XIAOQU_SHINEI_NONGCUN) 小区数,
AVG(ZAIPIN_SHINEI_NONGCUN) 载频数,
'''' 覆盖面积,
AVG(XIAOQU_SHIWAI) 宏站个数,
AVG(XIAOQU_SHINEI) 室分个数,
AVG(XIAOQU) 小区个数,
AVG(ZAIPIN) 载频总数
from SXPM.CM_GSM_COUNTRY_JIHUABU
where COLLECT_DATE>=:L_START_DATE and COLLECT_DATE<=:L_END_DATE
group by L_DATE,CITY_NAME,COUNTRY'; OPEN V_RS FOR SQLSTR
USING L_START_DATE, L_END_DATE;
END;