存储过程报错，求解

存储cursorselectsql

解决方案 »

  1.   

    游标里面有INTO语句，不能这么搞
      

  2.   


     CREATE OR REPLACE PROCEDURE I_INMS_SITE_DAY(IN_USERID  IN VARCHAR2,
                           IN_AREAID   IN VARCHAR2,
                                OUT_CURSOR  OUT SYS_REFCURSOR) IS            
      CURSOR C_INMS_SITE_DAY IS 
        select to_char(t.create_date, 'YYYY') into i_year,
           to_char(t.create_date, 'MM') into i_month,
           a.code into i_code,
           a.name into i_name,
           count(*) into i_count,
           decode(to_char(t.create_date, 'DD'), '1', count(*), 0)  FIRST 
           decode(to_char(t.create_date, 'DD'), '2', count(*), 0)  SECOND
                            
           ..
           ..
           ..                            
        from TAB_SI_SITENAME t, tab_si_user u, tab_si_area a
         where t.user_id = u.id
         and u.area_id = a.id
         group by a.code,
            a.name,
            to_char(t.create_date, 'YYYY'),
            to_char(t.create_date, 'MM'),
            to_char(t.create_date, 'DD')
      
    BEGIN  FOR CUR IN C_INMS_SITE_DAY LOOP
        INSERT INTO INMS_SITE_DAY(ID,AREA,AMOUNT,CODE,MONTH,FIRST,SECOND,THIRD,FOURTH,FIFTH,SIXTH,SEVENTH,EIGHTH,NINTH,TENTH,ELEVENTH,TWELFTH,THIRTEENTH,FOURTEENTH,FIFTEENTH,
        SIXTEENTH,SEVENTEENTH,EIGHTEENTH,NINETEENTH,TWENTIETH,ELEVENTH,TWELFTH,THIRTEENTH,FOURTEENTH,FIFTEENTH,SIXTEENTH,SEVENTEENTH,EIGHTEENTH,NINETEENTH,TWENTIETH,
        TWENTY_FIRST,TWENTY_SECOND,TWENTY_THIRD,TWENTY_FOURTH,TWENTY_FIFTH,TWENTY_SIXTH,TWENTY_SEVENTH,TWENTY_EIGHTH,TWENTY_NINTH,THIRTIETH,THIRTY_FIRST)
        VALUES(cur.FIRST,cur.SECOND,......)
          end if;  END LOOP;
    COMMIT;
    END P_INMS_SITE_DAY;这样写
      

  3.   

    游标里面不要用into，另外那个end if 多余了吧 
      

  4.   


    CREATE OR REPLACE PROCEDURE I_INMS_SITE_DAY(IN_USERID  IN VARCHAR2,
                         IN_AREAID   IN VARCHAR2,
                              OUT_CURSOR  OUT SYS_REFCURSOR) IS
      i_year     VARCHAR2(100);
      i_month    VARCHAR2(100);
      i_code     VARCHAR2(32);
      i_name     VARCHAR2(100);
      i_count    NUMBER;
      i_first    NUMBER;
      i_second   NUMBER;
      i_third    NUMBER;
      i_fourth   NUMBER;
      i_fifth    NUMBER;
      i_sixth    NUMBER;
      i_seventh  NUMBER;
      i_eighth   NUMBER;
      i_ninth    NUMBER;
      i_tenth    NUMBER;
      i_eleventh    NUMBER;
      i_twelfth     NUMBER;
      i_thirteenth    NUMBER;
      i_fourteenth   NUMBER;
      i_fifteenth    NUMBER;
      i_sixteenth   NUMBER;
      i_seventeenth    NUMBER;
        i_eighteenth   NUMBER;
      i_nineteenth    NUMBER;
      i_twentieth   NUMBER;
      i_twenty_first    NUMBER;
      i_twenty_second   NUMBER;
      i_twenty_third    NUMBER;
      i_twenty_fourth   NUMBER;
      i_twenty_fifth    NUMBER;
      i_twenty_sixth   NUMBER;
      i_twenty_seventh    NUMBER;
      i_twenty_eighth   NUMBER;
      i_twenty_ninth    NUMBER;
      i_thirtieth   NUMBER;
      i_thirty_first    NUMBER;   

      CURSOR C_INMS_SITE_DAY IS 
      select to_char(t.create_date, 'YYYY'),
     to_char(t.create_date, 'MM'),
     a.code,
     a.name ,
     count(*),
     decode(to_char(t.create_date, 'DD'), '1', count(*), 0) "1" ,
     decode(to_char(t.create_date, 'DD'), '2', count(*), 0) "2" ,
     decode(to_char(t.create_date, 'DD'), '3', count(*), 0) "3" ,
     decode(to_char(t.create_date, 'DD'), '4', count(*), 0) "4" ,
           decode(to_char(t.create_date, 'DD'), '5', count(*), 0) "5" ,
           decode(to_char(t.create_date, 'DD'), '6', count(*), 0) "6" ,                           
           decode(to_char(t.create_date, 'DD'), '7', count(*), 0) "7" ,
           decode(to_char(t.create_date, 'DD'), '8', count(*), 0) "8" ,
           decode(to_char(t.create_date, 'DD'), '9', count(*), 0) "9" ,
           decode(to_char(t.create_date, 'DD'), '10', count(*), 0) "10" ,
           decode(to_char(t.create_date, 'DD'), '11', count(*), 0) "11",
           decode(to_char(t.create_date, 'DD'), '12', count(*), 0) "12" ,
           decode(to_char(t.create_date, 'DD'), '13', count(*), 0) "13" ,
           decode(to_char(t.create_date, 'DD'), '14', count(*), 0) "14" ,
           decode(to_char(t.create_date, 'DD'), '15', count(*), 0) "15" ,
           decode(to_char(t.create_date, 'DD'), '16', count(*), 0) "16" ,                           
           decode(to_char(t.create_date, 'DD'), '17', count(*), 0) "17" ,
           decode(to_char(t.create_date, 'DD'), '18', count(*), 0) "18" ,
           decode(to_char(t.create_date, 'DD'), '19', count(*), 0) "19" ,
           decode(to_char(t.create_date, 'DD'), '20', count(*), 0) "20" ,  
           decode(to_char(t.create_date, 'DD'), '21', count(*), 0) "21" ,
           decode(to_char(t.create_date, 'DD'), '22', count(*), 0) "22" ,
           decode(to_char(t.create_date, 'DD'), '23', count(*), 0) "23" ,
           decode(to_char(t.create_date, 'DD'), '24', count(*), 0) "24" ,
           decode(to_char(t.create_date, 'DD'), '25', count(*), 0) "25" ,
           decode(to_char(t.create_date, 'DD'), '26', count(*), 0) "26" ,                           
           decode(to_char(t.create_date, 'DD'), '27', count(*), 0) "27",
           decode(to_char(t.create_date, 'DD'), '28', count(*), 0) "28" ,
           decode(to_char(t.create_date, 'DD'), '29', count(*), 0) "29" ,
           decode(to_char(t.create_date, 'DD'), '30', count(*), 0) "30" ,  
           decode(to_char(t.create_date, 'DD'), '31', count(*), 0) "31"  
           
           into  i_year,i_month,i_code, i_name,i_first,i_second,i_third,i_fourth,i_fifth,i_sixth, i_seventh,i_seventh,i_eighth,i_ninth,i_tenth,i_eleventh,
           i_twelfth,i_thirteenth,i_fourteenth, i_fifteenth,i_sixteenth,i_seventeenth,i_eighteenth,i_nineteenth,i_twentieth,i_twenty_first,i_twenty_second,
           i_twenty_third,i_twenty_fourth,i_twenty_fifth,i_twenty_sixth,i_twenty_seventh,i_twenty_eighth,i_twenty_ninth,i_thirtieth, i_thirty_first 
                                         
        from INMS_SITE_NAME t,
        INMS_SI_USER u,
        INMS_AREA a
         where t.user_id = u.id
         and u.area_id = a.id
         group by a.code,
            a.name,
            to_char(t.create_date, 'YYYY'),
            to_char(t.create_date, 'MM'),
            to_char(t.create_date, 'DD');
    BEGIN  FOR CUR IN C_INMS_SITE_DAY LOOP
        INSERT INTO INMS_SITE_DAY(ID,AREA,AMOUNT,CODE,MONTHS,FIRSTS,SECONDS,THIRD,FOURTH,FIFTH,SIXTH,SEVENTH,EIGHTH,NINTH,TENTH,ELEVENTH,TWELFTH,THIRTEENTH,FOURTEENTH,FIFTEENTH,SIXTEENTH,SEVENTEENTH,EIGHTEENTH,NINETEENTH,TWENTIETH,TWENTY_FIRST,TWENTY_SECOND,TWENTY_THIRD,TWENTY_FOURTH,TWENTY_FIFTH,TWENTY_SIXTH,TWENTY_SEVENTH,TWENTY_EIGHTH,TWENTY_NINTH,THIRTIETH,THIRTY_FIRST)
        VALUES(i_inms_site_day_id,i_name,i_count,i_code,i_month,i_first,i_second,i_third,i_fourth,i_fifth,i_sixth,i_seventh,i_eighth,i_ninth,i_tenth,i_eleventh,i_twelfth,i_thirteenth,i_fourteenth,i_fifteenth,i_sixteenth,    i_seventeenth,i_eighteenth,i_nineteenth,i_twentieth,i_twenty_first,i_twenty_second,i_twenty_third,i_twenty_fourth,i_twenty_fifth,i_twenty_sixth,i_twenty_seventh,i_twenty_eighth,i_twenty_ninth,i_thirtieth,i_thirty_first);
      END LOOP;
    COMMIT;
    END I_INMS_SITE_DAY;
    现在报这个问题了 求解啊
      

  5.   

    问题较多。
    1、游标定义里面别用into语句，直接别名待使用就可以
    2、不用定义那些into的变量i _XX.
    3、在for循环游标后，直接
         FOR CUR IN C_INMS_SITE_DAY LOOP
        INSERT INTO INMS_SITE_DAY
          (ID,
           AREA,
          ...
         )
         values(
        cur.XX --取游标内的值
        ...
        )
    4、decode(to_char(t.create_date, 'DD'), '1', count(*), 0) 这个判断逻辑没看懂，是不是你想要的是
        sum(decode(to_char(t.create_date, 'DD'), '1', 1, 0))  as i_first
      

  6.   

    第四个是行列转换
    我改了改CREATE OR REPLACE PROCEDURE I_INMS_SITE_DAY() IS  i_year           VARCHAR2(100);
      i_month          VARCHAR2(100);
      i_code           VARCHAR2(32);
      i_name           VARCHAR2(100);
      i_count          NUMBER;
      i_first          NUMBER;
      i_second         NUMBER;
      i_third          NUMBER;
      i_fourth         NUMBER;
      i_fifth          NUMBER;
      i_sixth          NUMBER;
      i_seventh        NUMBER;
      i_eighth         NUMBER;
      i_ninth          NUMBER;
      i_tenth          NUMBER;
      i_eleventh       NUMBER;
      i_twelfth        NUMBER;
      i_thirteenth     NUMBER;
      i_fourteenth     NUMBER;
      i_fifteenth      NUMBER;
      i_sixteenth      NUMBER;
      i_seventeenth    NUMBER;
      i_eighteenth     NUMBER;
      i_nineteenth     NUMBER;
      i_twentieth      NUMBER;
      i_twenty_first   NUMBER;
      i_twenty_second  NUMBER;
      i_twenty_third   NUMBER;
      i_twenty_fourth  NUMBER;
      i_twenty_fifth   NUMBER;
      i_twenty_sixth   NUMBER;
      i_twenty_seventh NUMBER;
      i_twenty_eighth  NUMBER;
      i_twenty_ninth   NUMBER;
      i_thirtieth      NUMBER;
      i_thirty_first   NUMBER;
      
     cursor  OUT_CURSOR is
        select to_char(t.create_date, 'YYYY') ,i_year,
               to_char(t.create_date, 'MM'), i_month,
               a.code ,i_code,
               a.name ,i_name,
               count(*) i_count,
               decode(to_char(t.create_date, 'DD'), '1', count(*), 0) "1" ,i_first,
               decode(to_char(t.create_date, 'DD'), '2', count(*), 0) "2" ,i_second,
               decode(to_char(t.create_date, 'DD'), '3', count(*), 0) "3" ,i_third,
               decode(to_char(t.create_date, 'DD'), '4', count(*), 0) "4" ,i_fourth,
               decode(to_char(t.create_date, 'DD'), '5', count(*), 0) "5" ,i_fifth,
               decode(to_char(t.create_date, 'DD'), '6', count(*), 0) "6" ,i_sixth,
               decode(to_char(t.create_date, 'DD'), '7', count(*), 0) "7" ,i_seventh,
               decode(to_char(t.create_date, 'DD'), '8', count(*), 0) "8" ,i_eighth,
               decode(to_char(t.create_date, 'DD'), '9', count(*), 0) "9" ,i_ninth,
               decode(to_char(t.create_date, 'DD'), '10', count(*), 0) "10" ,i_tenth,
               decode(to_char(t.create_date, 'DD'), '11', count(*), 0) "11" ,i_eleventh,
               decode(to_char(t.create_date, 'DD'), '12', count(*), 0) "12" ,i_twelfth,
               decode(to_char(t.create_date, 'DD'), '13', count(*), 0) "13" ,i_thirteenth,
               decode(to_char(t.create_date, 'DD'), '14', count(*), 0) "14" ,i_fourteenth,
               decode(to_char(t.create_date, 'DD'), '15', count(*), 0) "15" ,i_fifteenth,
               decode(to_char(t.create_date, 'DD'), '16', count(*), 0) "16" ,i_sixteenth,
               decode(to_char(t.create_date, 'DD'), '17', count(*), 0) "17" ,i_seventeenth,
               decode(to_char(t.create_date, 'DD'), '18', count(*), 0) "18" ,i_eighteenth,
               decode(to_char(t.create_date, 'DD'), '19', count(*), 0) "19" ,i_nineteenth,
               decode(to_char(t.create_date, 'DD'), '20', count(*), 0) "20" ,i_twentieth,
               decode(to_char(t.create_date, 'DD'), '21', count(*), 0) "21" ,i_twenty_first,
               decode(to_char(t.create_date, 'DD'), '22', count(*), 0) "22" ,i_twenty_second,
               decode(to_char(t.create_date, 'DD'), '23', count(*), 0) "23" ,i_twenty_third,
               decode(to_char(t.create_date, 'DD'), '24', count(*), 0) "24" ,i_twenty_fourth,
               decode(to_char(t.create_date, 'DD'), '25', count(*), 0) "25" ,i_twenty_fifth,
               decode(to_char(t.create_date, 'DD'), '26', count(*), 0) "26" ,i_twenty_sixth,
               decode(to_char(t.create_date, 'DD'), '27', count(*), 0) "27" ,i_twenty_seventh,
               decode(to_char(t.create_date, 'DD'), '28', count(*), 0) "28" ,i_twenty_eighth,
               decode(to_char(t.create_date, 'DD'), '29', count(*), 0) "29" ,i_twenty_ninth,
               decode(to_char(t.create_date, 'DD'), '30', count(*), 0) "30" ,i_thirtieth,
               decode(to_char(t.create_date, 'DD'), '31', count(*), 0) "31" ,i_thirty_first
        /*
          into i_year,
               i_month,
               i_code,
               i_name,
               i_count,
               i_first,
               i_second,
               i_third,
               i_fourth,
               i_fifth,
               i_sixth,
               i_seventh,
               i_eighth,
               i_ninth,
               i_tenth,
               i_eleventh,
               i_twelfth,
               i_thirteenth,
               i_fourteenth,
               i_fifteenth,
               i_sixteenth,
               i_seventeenth,
               i_eighteenth,
               i_nineteenth,
               i_twentieth,
               i_twenty_first,
               i_twenty_second,
               i_twenty_third,
               i_twenty_fourth,
               i_twenty_fifth,
               i_twenty_sixth,
               i_twenty_seventh,
               i_twenty_eighth,
               i_twenty_ninth,
               i_thirtieth,
               i_thirty_first*/
        
          from INMS_SITE_NAME t, INMS_SI_USER u, INMS_AREA a
         where t.user_id = u.id
           and u.area_id = a.id
         group by a.code,
                  a.name,
                  to_char(t.create_date, 'YYYY'),
                  to_char(t.create_date, 'MM'),
                  to_char(t.create_date, 'DD');
    BEGIN
      FOR CUR IN OUT_CURSOR LOOP
        INSERT INTO INMS_SITE_DAY
          (ID,
           AREA,
           AMOUNT,
           CODE,
           MONTHS,
           FIRSTS,
           SECONDS,
           THIRD,
           FOURTH,
           FIFTH,
           SIXTH,
           SEVENTH,
           EIGHTH,
           NINTH,
           TENTH,
           ELEVENTH,
           TWELFTH,
           THIRTEENTH,
           FOURTEENTH,
           FIFTEENTH,
           SIXTEENTH,
           SEVENTEENTH,
           EIGHTEENTH,
           NINETEENTH,
           TWENTIETH,
           TWENTY_FIRST,
           TWENTY_SECOND,
           TWENTY_THIRD,
           TWENTY_FOURTH,
           TWENTY_FIFTH,
           TWENTY_SIXTH,
           TWENTY_SEVENTH,
           TWENTY_EIGHTH,
           TWENTY_NINTH,
           THIRTIETH,
           THIRTY_FIRST)
        VALUES
          (CUR.I_YEAR,
           CUR.I_NAME,
           CUR.I_COUNT,
           CUR.I_CODE,
           CUR.I_MONTH,
           CUR.I_FIRST,
           CUR.I_SECOND,
           CUR.I_THIRD,
           CUR.I_FOURTH,
           CUR.I_FIFTH,
           CUR.I_SIXTH,
           CUR.I_SEVENTH,
           CUR.I_EIGHTH,
           CUR.I_NINTH,
           CUR.I_TENTH,
           CUR.I_ELEVENTH,
           CUR.I_TWELFTH,
           CUR.I_THIRTEENTH,
           CUR.I_FOURTEENTH,
           CUR.I_FIFTEENTH,
           CUR.I_SIXTEENTH,
           CUR.I_SEVENTEENTH,
           CUR.I_EIGHTEENTH,
           CUR.I_NINETEENTH,
           CUR.I_TWENTIETH,
           CUR.I_TWENTY_FIRST,
           CUR.I_TWENTY_SECOND,
           CUR.I_TWENTY_THIRD,
           CUR.I_TWENTY_FOURTH,
           CUR.I_TWENTY_FIFTH,
           CUR.I_TWENTY_SIXTH,
           CUR.I_TWENTY_SEVENTH,
           CUR.I_TWENTY_EIGHTH,
           CUR.I_TWENTY_NINTH,
           CUR.I_THIRTIETH,
           CUR.i_thirty_first);
      END LOOP;
      COMMIT;
    END I_INMS_SITE_DAY;现在这个问题了
      

  7.   

    select to_char(t.create_date, 'YYYY') ,i_year,
               to_char(t.create_date, 'MM'), i_month,
               a.code ,i_code,
               a.name ,i_name,
               count(*) i_count,
               decode(to_char(t.create_date, 'DD'), '1', count(*), 0) "1" ,i_first,
               decode(to_char(t.create_date, 'DD'), '2', count(*), 0) "2" ,i_second,
    这个逗号是什么意思，应该是这样吧
    select to_char(t.create_date, 'YYYY')  as i_year,
               to_char(t.create_date, 'MM') as i_month,
               a.code  as i_code,
               a.name as i_name,
               count(*)  as i_count,
               decode(to_char(t.create_date, 'DD'), '1', count(*), 0) as i_first,
               decode(to_char(t.create_date, 'DD'), '2', count(*), 0)as i_second,
    还有最后记得关游标
      

  8.   

    楼主可以直接用   INSERT INTO  SELECT直接快速高效解决，干嘛要用游标？