请教一个存储过程中的插入数据问题

Question

各位大哥大姐帮忙看下下面的过程：
这个过程中的中间表DW_A_USER_GPRS_MID我单独对其之后的select语句运行的时候（其中acct_month='201008')是有acct_month='201008'的数据的，但是为什么在运行过程中这个表没有这个月份的数据？由于有清目标表的语句，为什么目标表DW_A_USER_GPRS_CG没有被完全清空而有数据呢？
PROCEDURE P_DW_A_USER_GPRS_CG(V_ACCT_MONTH IN VARCHAR2,
                                V_RETCODE    OUT VARCHAR2,
                                V_RETINFO    OUT VARCHAR2) AS
    /*@
      ****************************************************************
      *名称 --%@NAME:  P_DW_A_USER_GPRS_CG
      *功能描述 --%@COMMENT: 提供潜在GPRS用户的渠道和客户分群
      *执行周期 --%@PERIOD:月
      *参数 --%@PARAM:V_DATE 日期,格式YYYYMM
      *参数 --%@PARAM:V_RETCODE  过程运行结束成功与否标志
      *参数 --%@PARAM:V_RETINFO  过程运行结束成功与否描述
      *来源表 --%@FROM:DW_A_USER_GPRS
      *目标表 --%@TO: DW_A_USER_GPRS_CG
      *修改记录 --%@MODIFY:
      ******************************************************************
    @*/
  
    V_PROCNAME     VARCHAR2(40);
    V_PKG          VARCHAR2(40);
    V_ROWLINE      NUMBER;
    V_DTAL_DATE    DATE;
    V_OFFER_DATE DATE;
    V_SERV_DATE DATE;
  
  BEGIN
    V_PROCNAME := 'P_DW_A_USER_GPRS_CG';
    V_PKG      := 'APP';
  
    select statis_date
      into V_DTAL_DATE---这个值单独跑出来是'20100907'
      from (select *
              from masa370.tb_mk_sc_user_dtal t
             where t.statis_date is not null
             order by t.statis_date desc)
     where rownum = 1;
  
     select deal_date
      into V_OFFER_DATE
      from (select *
              from masa370.tb_dw_pd_user_prod_offer_cur t
             where t.deal_date is not null
             order by t.deal_date desc)
     where rownum = 1;
     
    select deal_date
    into V_SERV_DATE
    from (select *
           from masa370.tb_dw_pd_user_service_cur t
          where t.deal_date is not null
          order by t.deal_date desc)
    where rownum = 1;
  
    --日志部分
    P_INSERT_LOG(V_ACCT_MONTH, V_PKG, V_PROCNAME, '99', SYSDATE);
  
    --插入数据
    EXECUTE IMMEDIATE 'ALTER TABLE DW_A_USER_GPRS_MID TRUNCATE PARTITION PART' ||
                      V_ACCT_MONTH;
  
    INSERT /*+APPEND*/
    INTO DW_A_USER_GPRS_MID nologging
      SELECT A.ACCT_MONTH,
             A.USER_ID,
             A.SERV_NUMBER,
             A.CHANNEL_1_ID,
             A.CHANNEL_1_TP,
             A.CHANNEL_2_ID,
             A.CHANNEL_2_TP,
             A.AGE,
             A.GENDER_ID,
             A.PRIORITY,
             A.CUST_RULE_COUNT
        FROM (SELECT *
                FROM DW_A_USER_GPRS T
               WHERE T.ACCT_MONTH = V_ACCT_MONTH
                 AND T.PRIORITY >= 2
                 and not exists (select d.serv_number
                        from dw_a_user_commen_filter d
                       where d.user_type in ('1', '2', '3')
                         and d.acct_month = V_ACCT_MONTH
                         and d.serv_number = t.serv_number) --排除红名单、黑名单、员工号
                 and not exists
               (select H.USER_ID
                        from masa370.tb_mk_sc_user_dtal H --排除G3, MO套餐(送GPRS流量)用户
                       where H.statis_date = V_DTAL_DATE
                         and H.product_id in
                             ('N02563', 'N02557', 'N02558', 'N02556', 'N02795',
                              'N02794', 'N02559', 'N02560', 'N02561', 'N02562',
                              'ND45', 'N02408', 'N02417', 'N02766')
                         and H.USER_ID = T.USER_ID)
                and not exists  --排除已经营销过的GPRS用户
                (select g.user_id  
                from dw_a_user_gprs_ca g
               where g.user_id = t.user_id) ) A,
             (SELECT TT.USER_ID
                FROM (SELECT TT1.USER_ID
                        FROM DW_A_USER_COMMEN_NORMAL TT1
                       WHERE TT1.ACCT_MONTH = V_ACCT_MONTH
                         AND TT1.USER_TYPE = '1'
                      INTERSECT
                      SELECT TT2.USER_ID
                        FROM DW_A_USER_COMMEN_NORMAL TT2
                       WHERE TT2.ACCT_MONTH = V_ACCT_MONTH
                         AND TT2.USER_TYPE = '2') TT) B --正常用户且手机终端支持GPRS
       WHERE A.USER_ID = B.USER_ID;
    V_ROWLINE := SQL%ROWCOUNT;
    COMMIT;
    
    --5元GPRS潜在用户
    EXECUTE IMMEDIATE 'truncate table DW_A_USER_GPRS_CG';
    
    --目前使用标准(自由)套餐的潜在用户
    INSERT /*+APPEND*/
    INTO DW_A_USER_GPRS_CG nologging
    select distinct t1.user_id,
                  t1.serv_number,
                  '1' type 
    from dw_a_user_gprs_mid t1,
         (select trim(user_id) user_id --标准(自由)套餐
            from masa370.tb_dw_pd_user_prod_offer_cur a
           where deal_date = V_OFFER_DATE
             and offer_id in ('100160000118', '100160000159')
             and trunc(start_date) <= V_OFFER_DATE
             and trunc(end_date) >= V_OFFER_DATE) t2
    where t1.user_id = t2.user_id
      and t1.acct_month=V_ACCT_MONTH;
    
    V_ROWLINE := SQL%ROWCOUNT;
    COMMIT;
 --更新日志
    V_RETCODE := 'SUCCESS';
    P_UPDATE_LOG(V_ACCT_MONTH,
                 V_PKG,
                 V_PROCNAME,
                 '结束',
                 'SUCCESS',
                 SYSDATE,
                 V_ROWLINE);
  
  EXCEPTION
    WHEN OTHERS THEN
      V_RETCODE := 'FAIL';
      V_RETINFO := SQLERRM;
      P_UPDATE_LOG(V_ACCT_MONTH,
                   V_PKG,
                   V_PROCNAME,
                   V_RETINFO,
                   'FAIL',
                   SYSDATE,
                   V_ROWLINE);
  END;

Answer 1

整个过程跟字段选取啥的没啥关系，就是不明白为啥insert into DW_A_USER_GPRS_MID 之后的select语句单独运行有参数为201008的数据，而跑过程之后中间表就没有acct_month=‘201008’的数据

Answer 2

而且既然中间表DW_A_USER_GPRS_MID 没有数据，并且目标表DW_A_USER_GPRS_CG运行前有清表语句，为何最后目标表DW_A_USER_GPRS_CG还有数据存在？

Answer 3

最终目的就是往目标表DW_A_USER_GPRS_CG中插入数据，只不过过程中用到了中间表DW_A_USER_GPRS_MID，中间表DW_A_USER_GPRS_MID是存在分区的，但是目标表没分区。
我调试的结果就是在过程中在输入参数acct_month='201008'的情况下DW_A_USER_GPRS_MID中没acct_month=201008的数据。但是我将insert into之后的select语句单独执行的时候是有数据的，其中acct_month=‘201008’。
我不明白为什么在过程中insert之后还是空的

Answer 4

是不是分区表数据发生了重排列  DW_A_USER_GPRS_CG这个里面有acct_month=‘201008’的数据没

Answer 5

哦，可能我说错了，是中间表没有acct_month=‘201008’的数据，但是他有之前几个月的数据，在插入目标表之前应该先执行清表语句，所有在插入之前目标表应该为空，插入过程中由于中间表中没有acct_month=‘201008’的数据，所以最后得出的目标表应该是没有数据的。但是查看目标表却有数据，应该是以前的数据，就是说可能的原因是插入前的清表语句并没有执行。

Answer 6

语句和逻辑都没问题   感觉就是你的分区表那块没处理好 下班了 good luck

Answer 7

好的，谢谢你了啊，我自己在看看是不是这个原因吧。迷茫ing。。

Answer 8

---格式写好看点啊
PROCEDURE P_DW_A_USER_GPRS_CG(V_ACCT_MONTH IN VARCHAR2,
                              V_RETCODE    OUT VARCHAR2,
                              V_RETINFO    OUT VARCHAR2) AS
  /*@
  ****************************************************************
  *名称 --%@NAME: P_DW_A_USER_GPRS_CG
  *功能描述 --%@COMMENT: 提供潜在GPRS用户的渠道和客户分群
  *执行周期 --%@PERIOD:月
  *参数 --%@PARAM:V_DATE 日期,格式YYYYMM
  *参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志
  *参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述
  *来源表 --%@FROM:DW_A_USER_GPRS
  *目标表 --%@TO: DW_A_USER_GPRS_CG
  *修改记录 --%@MODIFY:
  ******************************************************************
  @*/  V_PROCNAME   VARCHAR2(40);
  V_PKG        VARCHAR2(40);
  V_ROWLINE    NUMBER;
  V_DTAL_DATE  DATE;
  V_OFFER_DATE DATE;
  V_SERV_DATE  DATE;BEGIN
  V_PROCNAME := 'P_DW_A_USER_GPRS_CG';
  V_PKG      := 'APP';  select statis_date
    into V_DTAL_DATE ---这个值单独跑出来是'20100907'
    from (select *
            from masa370.tb_mk_sc_user_dtal t
           where t.statis_date is not null
           order by t.statis_date desc)
   where rownum = 1;  select deal_date
    into V_OFFER_DATE
    from (select *
            from masa370.tb_dw_pd_user_prod_offer_cur t
           where t.deal_date is not null
           order by t.deal_date desc)
   where rownum = 1;  select deal_date
    into V_SERV_DATE
    from (select *
            from masa370.tb_dw_pd_user_service_cur t
           where t.deal_date is not null
           order by t.deal_date desc)
   where rownum = 1;  --日志部分
  P_INSERT_LOG(V_ACCT_MONTH, V_PKG, V_PROCNAME, '99', SYSDATE);  --插入数据
  EXECUTE IMMEDIATE 'ALTER TABLE DW_A_USER_GPRS_MID TRUNCATE PARTITION PART' ||
                    V_ACCT_MONTH;  INSERT /*+APPEND*/
  INTO DW_A_USER_GPRS_MID nologging
    SELECT A.ACCT_MONTH,
           A.USER_ID,
           A.SERV_NUMBER,
           A.CHANNEL_1_ID,
           A.CHANNEL_1_TP,
           A.CHANNEL_2_ID,
           A.CHANNEL_2_TP,
           A.AGE,
           A.GENDER_ID,
           A.PRIORITY,
           A.CUST_RULE_COUNT
      FROM (SELECT *
              FROM DW_A_USER_GPRS T
             WHERE T.ACCT_MONTH = V_ACCT_MONTH
               AND T.PRIORITY >= 2
               and not exists (select d.serv_number
                      from dw_a_user_commen_filter d
                     where d.user_type in ('1', '2', '3')
                       and d.acct_month = V_ACCT_MONTH
                       and d.serv_number = t.serv_number) --排除红名单、黑名单、员工号
               and not exists
             (select H.USER_ID
                      from masa370.tb_mk_sc_user_dtal H --排除G3, MO套餐(送GPRS流量)用户
                     where H.statis_date = V_DTAL_DATE
                       and H.product_id in
                           ('N02563', 'N02557', 'N02558', 'N02556', 'N02795',
                            'N02794', 'N02559', 'N02560', 'N02561', 'N02562',
                            'ND45', 'N02408', 'N02417', 'N02766')
                       and H.USER_ID = T.USER_ID)
               and not exists --排除已经营销过的GPRS用户
             (select g.user_id
                      from dw_a_user_gprs_ca g
                     where g.user_id = t.user_id)) A,
           (SELECT TT.USER_ID
              FROM (SELECT TT1.USER_ID
                      FROM DW_A_USER_COMMEN_NORMAL TT1
                     WHERE TT1.ACCT_MONTH = V_ACCT_MONTH
                       AND TT1.USER_TYPE = '1'
                    INTERSECT
                    SELECT TT2.USER_ID
                      FROM DW_A_USER_COMMEN_NORMAL TT2
                     WHERE TT2.ACCT_MONTH = V_ACCT_MONTH
                       AND TT2.USER_TYPE = '2') TT) B --正常用户且手机终端支持GPRS
     WHERE A.USER_ID = B.USER_ID;
  V_ROWLINE := SQL%ROWCOUNT;
  COMMIT;  --5元GPRS潜在用户
  EXECUTE IMMEDIATE 'truncate table DW_A_USER_GPRS_CG';  --目前使用标准(自由)套餐的潜在用户
  INSERT /*+APPEND*/
  INTO DW_A_USER_GPRS_CG nologging
    select distinct t1.user_id, t1.serv_number, '1' type
      from dw_a_user_gprs_mid t1,
           (select trim(user_id) user_id --标准(自由)套餐
              from masa370.tb_dw_pd_user_prod_offer_cur a
             where deal_date = V_OFFER_DATE
               and offer_id in ('100160000118', '100160000159')
               and trunc(start_date) <= V_OFFER_DATE
               and trunc(end_date) >= V_OFFER_DATE) t2
     where t1.user_id = t2.user_id
       and t1.acct_month = V_ACCT_MONTH;  V_ROWLINE := SQL%ROWCOUNT;
  COMMIT;
  --更新日志
  V_RETCODE := 'SUCCESS';
  P_UPDATE_LOG(V_ACCT_MONTH,
               V_PKG,
               V_PROCNAME,
               '结束',
               'SUCCESS',
               SYSDATE,
               V_ROWLINE);EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
    P_UPDATE_LOG(V_ACCT_MONTH,
                 V_PKG,
                 V_PROCNAME,
                 V_RETINFO,
                 'FAIL',
                 SYSDATE,
                 V_ROWLINE);
END;

Answer 9

额，这个写的确实比我的写的要清晰啊。但是问题是输入参数是201008，过程在跑的时候为什么mid表没有acct_month=201008的数据，而把insert into mid之后的select单独运行就用acct_month=‘201008’的数据？

Answer 10

呵呵,这个应该是用plsql developer 的edit->pl/sql beautifier整理了下.

Answer 11

DW_A_USER_GPRS_MID的建表语句发来看看

Answer 12

create table DW_A_USER_GPRS_MID
(
  ACCT_MONTH      VARCHAR2(6),
  USER_ID         VARCHAR2(16),
  SERV_NUMBER     VARCHAR2(16),
  CHANNEL_1_ID    VARCHAR2(16),
  CHANNEL_1_TP    VARCHAR2(1),
  CHANNEL_2_ID    VARCHAR2(16),
  CHANNEL_2_TP    VARCHAR2(1),
  AGE             VARCHAR2(4),
  GENDER_ID       VARCHAR2(4),
  PRIORITY        VARCHAR2(4),
  CUST_RULE_COUNT VARCHAR2(4)
)
partition by list (ACCT_MONTH)
(
  partition PART201003 values ('201003')
    tablespace TBS_USER_01
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 2M
      next 2M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART201004 values ('201004')
    tablespace TBS_USER_01
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 2M
      next 2M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART201005 values ('201005')
    tablespace TBS_USER_01
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 2M
      next 2M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART201006 values ('201006')
    tablespace TBS_USER_01
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 2M
      next 2M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART201007 values ('201007')
    tablespace TBS_USER_01
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 2M
      next 2M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition PART201008 values ('201008')
    tablespace TBS_USER_01
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 2M
      next 2M
      minextents 1
      maxextents unlimited
      pctincrease 0
    )
);这个是DW_A_USER_GPRS_MID建表语句

Answer 13

select出来的数据中acct_month字段是啥值?

Answer 14

select出来的值就是整个过程的输入参数值‘201008’啊，过程中我输入参数设置为‘201008’。然后mid表没有‘201008’的数据，后来我单独select看是不是就是不存在这个值，我选取acct_month等于‘201008’，但是结果是有值的，当然acct_month全是201008

Answer 15

其实我mid 之后的select单独拿出来运行就是想看下这个select语句到底能不能产生acct_month=201008的数据，结果是产生了，但是在插入到mid表之后mid表却没有。。

Answer 16

SELECT A.ACCT_MONTH,
       A.USER_ID,
       A.SERV_NUMBER,
       A.CHANNEL_1_ID,
       A.CHANNEL_1_TP,
       A.CHANNEL_2_ID,
       A.CHANNEL_2_TP,
       A.AGE,
       A.GENDER_ID,
       A.PRIORITY,
       A.CUST_RULE_COUNT
  FROM (SELECT *
          FROM DW_A_USER_GPRS T
         WHERE T.ACCT_MONTH = '201008'
           AND T.PRIORITY >= 2
           and not exists (select d.serv_number
                  from dw_a_user_commen_filter d
                 where d.user_type in ('1', '2', '3')
                   and d.acct_month = '201008'
                   and d.serv_number = t.serv_number) --排除红名单、黑名单、员工号
           and not exists
         (select H.USER_ID
                  from masa370.tb_mk_sc_user_dtal H --排除G3, MO套餐(送GPRS流量)用户
                 where H.statis_date = to_date('20100908', 'yyyymmdd')
                   and H.product_id in ('N02563',
                                        'N02557',
                                        'N02558',
                                        'N02556',
                                        'N02795',
                                        'N02794',
                                        'N02559',
                                        'N02560',
                                        'N02561',
                                        'N02562',
                                        'ND45',
                                        'N02408',
                                        'N02417',
                                        'N02766')
                   and H.USER_ID = T.USER_ID)
           and not exists --排除已经营销过的GPRS用户
         (select g.user_id
                  from dw_a_user_gprs_ca g
                 where g.user_id = t.user_id)) A,
       (SELECT TT.USER_ID
          FROM (SELECT TT1.USER_ID
                  FROM DW_A_USER_COMMEN_NORMAL TT1
                 WHERE TT1.ACCT_MONTH = '201008'
                   AND TT1.USER_TYPE = '1'
                INTERSECT
                SELECT TT2.USER_ID
                  FROM DW_A_USER_COMMEN_NORMAL TT2
                 WHERE TT2.ACCT_MONTH = '201008'
                   AND TT2.USER_TYPE = '2') TT) B --正常用户且手机终端支持GPRS
 WHERE A.USER_ID = B.USER_ID;
运行结果是存在的
201008 17820024914304 15090576021 N92009 1 N92384 2 27 0 2 1
201008 NU06503315857 13643703067 N92858 2 N92486 2 36 0 2 1
201008 17820016188767 15090635505 N41101 7 N4000U 2 19 0 8 1
201008 17820025116657 15237063562 YzzkDX 4 N42455 2 18 0 4 1
201008 17820022035051 15237083682 N32031 2 N32039 2 999 2 4 1
201008 17820015290536 15090622191 N52232 2 N52232 8 23 2 4 1
201008 N4032129554 13938923869 N4001Y 2 N41100 7 45 0 2 1
201008 NU08901194662 15138562858 N92751 2 4 0 8 1
201008 17820011183689 15225215923 N20004 2 YzzkDX 4 20 2 4 1
201008 NU08B02293703 13592369279 YzzkDX 4 SB2B02 2 20 1 4 1
201008 NU08901685036 15903705369 N62308 2 N12818 2 24 0 2 1
插入岛mid之后mid数据为空

Answer 17

谢谢大家了，问题得到解决了，我用一个和mid表一样结构的表来完成了那个过程，应该是我的mid表被锁了。