为什么把 游标放进去就报错。

Question

CREATE OR REPLACE PROCEDURE CML_WATER_COMPARE_AREA
                                   (I_DATE_START IN VARCHAR2:='2010-01-01',
                      I_DATE_END   IN VARCHAR2:='2010-01-31',
                      myRE_CURSOR OUT PKG_CML_SALEWATER_COMPARE.COMPARE_CURSOR) IS
  BEGIN
 insert into currentmonth
 SELECT      AREA_NAME,
             SUM(AMOUNT) AMOUNT,
             SUM(STATISTIC_AMOUNT) STATISTIC_AMOUNT
        FROM (SELECT DISTINCT BS.FT_ID || BS.GL_SEQ_NBR FT_ID,
                              BS.ACCT_ID,
                              BS.FREEZE_DTTM,
                              BS.PARENT_ID,
                              BS.AMOUNT AMOUNT,
                              BS.STATISTIC_AMOUNT,
                              CASE
                                WHEN BS.SA_TYPE_CD = 'C0BC' THEN
                                 0
                                WHEN BS.STATISTIC_AMOUNT = 0 THEN
                                 0
                                ELSE
                                 BS.AMOUNT / BS.STATISTIC_AMOUNT
                              END UNIT_PRICE,
                              BS.ORDER_SEQ AS WATER_TYPE,
                              BS.DES AS WATER_TYPE_DES,
                              BS.DST_ID,
                              BS.AREA_NAME
                FROM CML_VRS_FTGL BS
               WHERE (BS.START_DATE between TO_DATE(I_DATE_START, 'YYYY-MM-DD') AND TO_DATE(I_DATE_END, 'YYYY-MM-DD')
                 AND       BS.START_DATE = BS.END_DATE)
                 AND FREEZE_DTTM  between TO_DATE(I_DATE_START, 'YYYY-MM-DD') AND  TO_DATE(I_DATE_END, 'YYYY-MM-DD')                 AND FT_TYPE_FLG IN ('BS', 'BX')
                 AND SA_TYPE_CD = 'C0WA'
                 AND ((DST_ID NOT IN ('W-LIC-REV', 'W-M5G-REV') AND
                     AMOUNT <> 0) OR (DST_ID IN ('W-LIC-REV', 'W-M5G-REV')))
                     )--WATER_TYPE, WATER_TYPE_DES, UNIT_PRICE,
       GROUP BY  AREA_NAME;       -----------
       ---previousmonth insert into previousmonth
 SELECT      AREA_NAME,             SUM(AMOUNT) AMOUNT,
             SUM(STATISTIC_AMOUNT) STATISTIC_AMOUNT
        FROM (SELECT DISTINCT BS.FT_ID || BS.GL_SEQ_NBR FT_ID,
                              BS.ACCT_ID,
                              BS.FREEZE_DTTM,
                              BS.PARENT_ID,
                              BS.AMOUNT AMOUNT,
                              BS.STATISTIC_AMOUNT,
                              CASE
                                WHEN BS.SA_TYPE_CD = 'C0BC' THEN
                                 0
                                WHEN BS.STATISTIC_AMOUNT = 0 THEN
                                 0
                                ELSE
                                 BS.AMOUNT / BS.STATISTIC_AMOUNT
                              END UNIT_PRICE,
                              BS.ORDER_SEQ AS WATER_TYPE,
                              BS.DES AS WATER_TYPE_DES,
                              BS.DST_ID,
                              BS.AREA_NAME
                FROM CML_VRS_FTGL BS
               WHERE (BS.START_DATE between add_months(TO_DATE(I_DATE_START, 'YYYY-MM-DD'),-1) AND add_months(TO_DATE(I_DATE_END, 'YYYY-MM-DD') ,-1)
                 AND       BS.START_DATE = BS.END_DATE)
                 AND FREEZE_DTTM  between add_months(TO_DATE(I_DATE_START, 'YYYY-MM-DD'),-1) AND  add_months(TO_DATE(I_DATE_END, 'YYYY-MM-DD'),-1)                 AND FT_TYPE_FLG IN ('BS', 'BX')
                 AND SA_TYPE_CD = 'C0WA'
                 AND ((DST_ID NOT IN ('W-LIC-REV', 'W-M5G-REV') AND
                     AMOUNT <> 0) OR (DST_ID IN ('W-LIC-REV', 'W-M5G-REV')))
                     )--WATER_TYPE, WATER_TYPE_DES, UNIT_PRICE,
       GROUP BY  AREA_NAME;
       --------------       ---lastyearcurrentmonth
              insert into lastyearcurrentmonth
 SELECT      AREA_NAME,             SUM(AMOUNT) AMOUNT,
             SUM(STATISTIC_AMOUNT) STATISTIC_AMOUNT
        FROM (SELECT DISTINCT BS.FT_ID || BS.GL_SEQ_NBR FT_ID,
                              BS.ACCT_ID,
                              BS.FREEZE_DTTM,
                              BS.PARENT_ID,
                              BS.AMOUNT AMOUNT,
                              BS.STATISTIC_AMOUNT,
                              CASE
                                WHEN BS.SA_TYPE_CD = 'C0BC' THEN
                                 0
                                WHEN BS.STATISTIC_AMOUNT = 0 THEN
                                 0
                                ELSE
                                 BS.AMOUNT / BS.STATISTIC_AMOUNT
                              END UNIT_PRICE,
                              BS.ORDER_SEQ AS WATER_TYPE,
                              BS.DES AS WATER_TYPE_DES,
                              BS.DST_ID,
                              BS.AREA_NAME
                FROM CML_VRS_FTGL BS
               WHERE (BS.START_DATE between add_months(TO_DATE(I_DATE_START, 'YYYY-MM-DD'),12) AND add_months(TO_DATE(I_DATE_END, 'YYYY-MM-DD') ,12)
                 AND       BS.START_DATE = BS.END_DATE)
                 AND FREEZE_DTTM  between add_months(TO_DATE(I_DATE_START, 'YYYY-MM-DD'),12) AND  add_months(TO_DATE(I_DATE_END, 'YYYY-MM-DD'),12)                 AND FT_TYPE_FLG IN ('BS', 'BX')
                 AND SA_TYPE_CD = 'C0WA'
                 AND ((DST_ID NOT IN ('W-LIC-REV', 'W-M5G-REV') AND
                     AMOUNT <> 0) OR (DST_ID IN ('W-LIC-REV', 'W-M5G-REV')))
                     )
       GROUP BY  AREA_NAME;       OPEN myRE_CURSOR FOR
       select lastyearcurrentmonth.AREA_NAME   areaname,
       lastyearcurrentmonth.AMOUNT   lastyearamount,
       previousmonth.AMOUNT   previousamount,
       currentmonth.AMOUNT   currentamount,
        to_number(currentmonth.AMOUNT) -to_number(previousmonth.AMOUNT)  cpamount,
         (to_number(currentmonth.AMOUNT) -to_number(previousmonth.AMOUNT))/to_number(previousmonth.AMOUNT)   cprate,
         to_number(currentmonth.AMOUNT) -to_number(lastyearcurrentmonth.AMOUNT) clamount ,
          (to_number(currentmonth.AMOUNT) -to_number(lastyearcurrentmonth.AMOUNT))/to_number(currentmonth.AMOUNT) clamount
        from previousmonth inner join lastyearcurrentmonth  on previousmonth.AREA_NAME=
       lastyearcurrentmonth.AREA_NAME inner join currentmonth on currentmonth.AREA_NAME=previousmonth.AREA_NAME     end;

Answer 1

   select lastyearcurrentmonth.AREA_NAME   areaname,
       lastyearcurrentmonth.AMOUNT   lastyearamount,
       previousmonth.AMOUNT   previousamount,
       currentmonth.AMOUNT   currentamount,
        to_number(currentmonth.AMOUNT) -to_number(previousmonth.AMOUNT)  cpamount,
         (to_number(currentmonth.AMOUNT) -to_number(previousmonth.AMOUNT))/to_number(previousmonth.AMOUNT)   cprate,
         to_number(currentmonth.AMOUNT) -to_number(lastyearcurrentmonth.AMOUNT) clamount ,
          (to_number(currentmonth.AMOUNT) -to_number(lastyearcurrentmonth.AMOUNT))/to_number(currentmonth.AMOUNT) clamount
        from previousmonth inner join lastyearcurrentmonth  on previousmonth.AREA_NAME=
       lastyearcurrentmonth.AREA_NAME inner join currentmonth on currentmonth.AREA_NAME=previousmonth.AREA_NAME没有错误

Answer 2

另外，把 PKG_CML_SALEWATER_COMPARE.COMPARE_CURSOR 的定义贴出来看看

Answer 3

create or replace package PKG_CML_SALEWATER_COMPARE as  TYPE COMPARE_CURSOR IS REF CURSOR;  procedure CML_WATER_COMPARE_AREA(I_DATE_START VARCHAR2,
                                   I_DATE_END VARCHAR2  ,
                                    RE_CURSOR OUT COMPARE_CURSOR);
  
end PKG_CML_SALEWATER_COMPARE;

Answer 4

Compilation errors for PROCEDURE CISADM.CML_WATER_COMPARE_AREAError: PL/SQL: ORA-00933: SQL 命令未正确结束
Line: 141Error: PL/SQL: SQL Statement ignored
Line: 129
Compilation errors for PROCEDURE CISADM.CML_WATER_COMPARE_AREAError: PL/SQL: ORA-00933: SQL 命令未正确结束
Line: 141Error: PL/SQL: SQL Statement ignored
Line: 129
Text: lastyearcurrentmonth.AMOUNT   lastyearamount,Error: PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
        begin
          case declare end exception exit for goto if loop mod null
          pragma raise return select update while with <an identifier>
          <a double-quoted delimited-identifier> <a bind variable> <<
          close current delete fetch lock insert open rollback
          savepoint set sql execute commit forall merge pipe
Line: 142

Answer 5

是 open for代码块少了分号