Oracle问题

Question

这是我的语句，各位老大帮忙啦，谢谢^_^~~~
  procedure refresh_rpt(str_country in VARCHAR2) AS
  BEGIN
    SELECT COUNTRY_ID
    INTO int_country_id
    FROM TB_COUNTRY
    WHERE COUNTRY_NAME=str_country;    DELETE FROM TB_PROMOTER_PERFORMANCE_RPT
    WHERE COUNTRY_ID=int_country_id;
    
    INSERT INTO TB_PROMOTER_PERFORMANCE_RPT(PROMOTER_ID,PROMOTION_ID,GROUP_ID,SELL_THRU,TARGET,COUNTRY_ID)
    SELECT NVL(A.PROMOTER_ID,B.PROMOTER_ID)   AS PROMOTER_ID
          ,NVL(A.PROMOTION_ID,B.PROMOTION_ID) AS PROMOTION_ID
          ,NVL(A.GROUP_ID,B.GROUP_ID)         AS GROUP_ID
          ,NVL(A.SELL_THRU,0)                 AS SELL_THRU
          ,NVL(B.TARGET,0)                    AS TARGET
          ,int_country_id                     AS COUNTRY_ID
    FROM (SELECT C.PROMOTER_ID
                ,B.PROMOTION_ID
                ,D.GROUP_ID
                ,COUNT(A.IMEI_NO) AS SELL_THRU
          FROM TB_PROMOTION_IMEI         A
          INNER JOIN TB_PROMOTION_MAIL   B ON A.SERIAL_NO=B.SERIAL_NO AND B.COUNTRY_ID=int_country_id
          INNER JOIN TB_CHANNEL_PROMOTER C ON B.CHANNEL_ID=C.CHANNEL_ID
          INNER JOIN TB_PDGROUP_ITEM     D ON A.MARKET_PRODUCT_ID=D.MARKET_PRODUCT_ID
          WHERE A.COUNTRY_ID=int_country_id
          GROUP BY C.PROMOTER_ID
                  ,B.PROMOTION_ID
                  ,D.GROUP_ID)        A
    FULL JOIN TB_PROMOTER_TARGET      B ON A.PROMOTER_ID=B.PROMOTER_ID
                                       AND A.PROMOTION_ID=B.PROMOTION_ID
                                       AND A.GROUP_ID=B.GROUP_ID;
    COMMIT;
  END refresh_rpt;

Answer 1

那样的话，会不会复杂很多，游标不会用，刚从sql转到oracle，很多地方不同，sql的话用临时表就可以了。
但是我不明白为什么这样的语句会出错……是不是要parse什么的动作啊？

Answer 2

你的procedure中的int_country_id没有声明.
procedure refresh_rpt(str_country in VARCHAR2) AS
  int_country_id integer;
  BEGIN
 ...

Answer 3

声明了，我把申明略掉了，这个没有问题……
就算不申明也不会出ora-00600错误啊~~~有谁知道为什么会有这个错误，用游标的话会不会效率很低啊？我从来没有用过，谢谢大家指导~~

Answer 4

我用游标试过了……CURSOR p_rpt IS SELECT NVL(A.PROMOTER_ID,B.PROMOTER_ID)   AS PROMOTER_ID
          ,NVL(A.PROMOTION_ID,B.PROMOTION_ID) AS PROMOTION_ID
          ,NVL(A.GROUP_ID,B.GROUP_ID)         AS GROUP_ID
          ,NVL(A.SELL_THRU,0)                 AS SELL_THRU
          ,NVL(B.TARGET,0)                    AS TARGET
          ,int_country_id                     AS COUNTRY_ID
    FROM (SELECT C.PROMOTER_ID
                ,B.PROMOTION_ID
                ,D.GROUP_ID
                ,COUNT(A.IMEI_NO) AS SELL_THRU
          FROM TB_PROMOTION_IMEI         A
          INNER JOIN TB_PROMOTION_MAIL   B ON A.SERIAL_NO=B.SERIAL_NO AND B.COUNTRY_ID=int_country_id
          INNER JOIN TB_CHANNEL_PROMOTER C ON B.CHANNEL_ID=C.CHANNEL_ID
          INNER JOIN TB_PDGROUP_ITEM     D ON A.MARKET_PRODUCT_ID=D.MARKET_PRODUCT_ID
          WHERE A.COUNTRY_ID=int_country_id
          GROUP BY C.PROMOTER_ID
                  ,B.PROMOTION_ID
                  ,D.GROUP_ID)        A
    FULL JOIN TB_PROMOTER_TARGET      B ON A.PROMOTER_ID=B.PROMOTER_ID
                                       AND A.PROMOTION_ID=B.PROMOTION_ID
                                       AND A.GROUP_ID=B.GROUP_ID;然后用FETCH一行一行Insert进去，可是还是有原来的问题……