各位大哥大姐帮忙看下下面的过程:
这个过程中的中间表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;
这个过程中的中间表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;
我调试的结果就是在过程中在输入参数acct_month='201008'的情况下DW_A_USER_GPRS_MID中没acct_month=201008的数据。但是我将insert into之后的select语句单独执行的时候是有数据的,其中acct_month=‘201008’。
我不明白为什么在过程中insert之后还是空的
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;
(
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建表语句
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数据为空