---公勉卡
create table c_accnbr_for_free
(
segment_id number(9),
segment_desc varchar2(50),
acc_nbr number(30),
billing_mode varchar2(5),
state varchar2(5),
region_id number(5),
region_name varchar2(50),
latn_id number(5),
latn_name varchar2(50),
serv_id number(30),
acct_id number(30),
single_serv_flag number(1),
nbr_type number(1)
)
create table c_accnbr_for_free_bak as
select * from c_accnbr_for_free;
---重复数据
create table temp_20140526
as
select * from c_accnbr_for_free where acc_nbr in (select c.acc_nbr from
(select acc_nbr,count(*) from c_accnbr_for_free group by acc_nbr having count(*)>1) c);
--处理重复数据
1,4重复的取4
3,4重复的取4
1,3重复的取1
select * from temp_20140526 ; --32- 1 / 2 =15
delete from temp_20140526 where acc_nbr in(
select acc_nbr from temp_20140526 where nbr_type in(1,4) group by acc_nbr having count(*)>1) and nbr_type=1; --26 (13)
delete from temp_20140526 where acc_nbr in(
select acc_nbr from temp_20140526 where nbr_type in(3,4) group by acc_nbr having count(*)>1) and nbr_type=3; --2 (1)
delete from temp_20140526 where acc_nbr in(
select acc_nbr from temp_20140526 where nbr_type in(1,3) group by acc_nbr having count(*)>1) and nbr_type=3; --2 (1)
select distinct acc_nbr from c_accnbr_for_free where acc_nbr in (select c.acc_nbr from
(select acc_nbr,count(*) from c_accnbr_for_free group by acc_nbr having count(*)>1) c);
select * from temp_20140526 where acc_nbr=17008510004 for update;
select acc_nbr,count(*) from temp_20140526 group by acc_nbr ;
--2478 - 32 + 15 = 2461
delete from c_accnbr_for_free where acc_nbr in(select acc_nbr from temp_20140526); ---32
insert into c_accnbr_for_free
select * from temp_20140526;
select acc_nbr,count(*) from c_accnbr_for_free group by acc_nbr having count(*)>1;
---去重后备份
create table c_accnbr_for_free_bak1
as
select * from c_accnbr_for_free;
create or replace procedure p_accnbr_for_free IS
segmentId NUMBER;
segmentDesc VARCHAR2(50);
accNbr NUMBER;
billingMode VARCHAR2(5);
state VARCHAR2(5);
regionId NUMBER;
regionName VARCHAR2(50);
latnId NUMBER;
latnName VARCHAR2(50);
servId NUMBER;
acctId NUMBER;
singleServFlag NUMBER;
nbrType NUMBER;
v_1 NUMBER;
str1 varchar2(500);
cursor str_free is
select s.segment_id,c.acc_nbr, s.billing_mode, s.state, s.region_id, s.serv_id,c.nbr_type
from c_accnbr_for_free c, serv s
where c.acc_nbr = s.acc_nbr;
begin
for f in str_free
loop
segmentId :=f.segment_id;
segmentDesc :=''; ---重新提取
accNbr :=f.acc_nbr;
billingMode :=f.billing_mode;
state :=f.state;
regionId :=f.region_id;
regionName :=''; --重新提取
latnId :=0; --重新提取
latnName :=''; --重新提取
servId :=f.serv_id;
acctId :=0; --重新提取
singleServFlag :=-1; --重新提取
nbrType :=f.nbr_type;
-- 转售商描述(segmentDesc)
if segmentId is not null then
begin
execute immediate ' select partner_desc from emulatory_partner where party_role_id='||segmentId into segmentDesc;
exception when no_data_found then
segmentDesc :='';
end;
end if;
--地市名称(regionName)
if regionId is not null then
begin
execute immediate ' select distinct region_name from region_latn where region_id='||regionId into regionName;
exception when no_data_found then
regionName :='';
end;
end if;
--省份编码(latnId),省份名称(latnName)
if regionId is not null then
begin
execute immediate ' select distinct latn_id,latn_name from region_latn where region_id='||regionId into latnId, latnName;
exception when no_data_found then
latnId :=0;
latnName :='';
end;
end if;
--帐户ID (acctId)
if servId is not null then
begin
execute immediate ' select acct_id from serv_acct where state=''00A'' and serv_id='||servId into acctId;
exception when no_data_found then
acctId :=0;
end;
end if;
--单设备标识( singleServFlag )
if acctId is not null then
begin
execute immediate 'select count(*) from serv_acct where state=''00A'' and acct_id='||acctId into v_1;
if v_1=1 then
begin
singleServFlag :=1;
end;
elsif v_1 > 1 then
begin
singleServFlag :=0;
end;
else
begin
singleServFlag :=-1;
end;
end if;
exception when no_data_found then
singleServFlag :=-1;
end;
end if;
--更新数据
execute immediate 'update c_accnbr_for_free set SEGMENT_ID='||segmentId||',
SEGMENT_DESC=TO_CHAR('''||segmentDesc||'''),
BILLING_MODE=TO_CHAR('''||billingMode||'''),
STATE=TO_CHAR('''||state||'''),
REGION_ID='||regionId||',
REGION_NAME=TO_CHAR('''||regionName||'''),
LATN_ID='||latnId||',
LATN_NAME=TO_CHAR('''||latnName||'''),
SERV_ID='||servId||',
ACCT_ID='||acctId||',
SINGLE_SERV_FLAG='||singleServFlag||'
WHERE NBR_TYPE='||nbrType ||' AND ACC_NBR='||accNbr ;
commit;
end loop;
end;
select count(*) from serv_acct where state='00A' acct_id=991100110000000183;
select count(*) from serv_acct where state='00A' and acct_id=9;
select * from serv_acct where serv_id=993100100000008370;
select acct_id from serv_acct where state='00A' and serv_id=999000220000002914 ;
--------------------------------------------------------------------------
select count(*) from c_accnbr_for_free where acc_nbr='17008760314';
select
acct_id,count(*)
from serv_acct where serv_id in(
select serv_id from serv where acc_nbr in(select acc_nbr from c_accnbr_for_free)
)and state='00A'
group by acct_id having count(*)>1;
993100100000008370
997000630000003024
select *from offer_discount where offer_id in(134016712,134016718);
select * from balance_type
700
select * from c_accnbr_for_free where acc_nbr=17001040711;
select * from serv where acc_nbr=17001040711;
---在serv表不存在的用户
select acc_nbr,nbr_type from c_accnbr_for_free where acc_nbr not in(select acc_nbr from serv); --1334
select count(*) from c_accnbr_for_free where billing_mode is not null; --1128
---------------------
select * from staff;
select distinct staff_id from balance_source
--已执行
grant select on mvno_info.c_accnbr_for_free to MVNO_A_SN ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_GM ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_LY ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_DXT ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_TY ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_HJSJ ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_WWZC ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_JD ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_LM ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_LLKJ ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_CJT ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_FXZX ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_SWHL ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_CJSD ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_ASD ;
--已执行
create synonym MVNO_A_SN .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_GM .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LY .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_DXT .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_TY .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_HJSJ .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_WWZC .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_JD .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LM .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LLKJ .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_CJT .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_FXZX .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_SWHL .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_CJSD .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_ASD .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_SN.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_GM.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LY.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_DXT.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_TY.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_HJSJ.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_WWZC.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_JD.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LM.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LLKJ.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_CJT.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_FXZX.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_SWHL.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_CJSD.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_ASD.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
2014-06-22 1:08@yuqiaolu.pudongqu.shanghai.
create table c_accnbr_for_free
(
segment_id number(9),
segment_desc varchar2(50),
acc_nbr number(30),
billing_mode varchar2(5),
state varchar2(5),
region_id number(5),
region_name varchar2(50),
latn_id number(5),
latn_name varchar2(50),
serv_id number(30),
acct_id number(30),
single_serv_flag number(1),
nbr_type number(1)
)
create table c_accnbr_for_free_bak as
select * from c_accnbr_for_free;
---重复数据
create table temp_20140526
as
select * from c_accnbr_for_free where acc_nbr in (select c.acc_nbr from
(select acc_nbr,count(*) from c_accnbr_for_free group by acc_nbr having count(*)>1) c);
--处理重复数据
1,4重复的取4
3,4重复的取4
1,3重复的取1
select * from temp_20140526 ; --32- 1 / 2 =15
delete from temp_20140526 where acc_nbr in(
select acc_nbr from temp_20140526 where nbr_type in(1,4) group by acc_nbr having count(*)>1) and nbr_type=1; --26 (13)
delete from temp_20140526 where acc_nbr in(
select acc_nbr from temp_20140526 where nbr_type in(3,4) group by acc_nbr having count(*)>1) and nbr_type=3; --2 (1)
delete from temp_20140526 where acc_nbr in(
select acc_nbr from temp_20140526 where nbr_type in(1,3) group by acc_nbr having count(*)>1) and nbr_type=3; --2 (1)
select distinct acc_nbr from c_accnbr_for_free where acc_nbr in (select c.acc_nbr from
(select acc_nbr,count(*) from c_accnbr_for_free group by acc_nbr having count(*)>1) c);
select * from temp_20140526 where acc_nbr=17008510004 for update;
select acc_nbr,count(*) from temp_20140526 group by acc_nbr ;
--2478 - 32 + 15 = 2461
delete from c_accnbr_for_free where acc_nbr in(select acc_nbr from temp_20140526); ---32
insert into c_accnbr_for_free
select * from temp_20140526;
select acc_nbr,count(*) from c_accnbr_for_free group by acc_nbr having count(*)>1;
---去重后备份
create table c_accnbr_for_free_bak1
as
select * from c_accnbr_for_free;
create or replace procedure p_accnbr_for_free IS
segmentId NUMBER;
segmentDesc VARCHAR2(50);
accNbr NUMBER;
billingMode VARCHAR2(5);
state VARCHAR2(5);
regionId NUMBER;
regionName VARCHAR2(50);
latnId NUMBER;
latnName VARCHAR2(50);
servId NUMBER;
acctId NUMBER;
singleServFlag NUMBER;
nbrType NUMBER;
v_1 NUMBER;
str1 varchar2(500);
cursor str_free is
select s.segment_id,c.acc_nbr, s.billing_mode, s.state, s.region_id, s.serv_id,c.nbr_type
from c_accnbr_for_free c, serv s
where c.acc_nbr = s.acc_nbr;
begin
for f in str_free
loop
segmentId :=f.segment_id;
segmentDesc :=''; ---重新提取
accNbr :=f.acc_nbr;
billingMode :=f.billing_mode;
state :=f.state;
regionId :=f.region_id;
regionName :=''; --重新提取
latnId :=0; --重新提取
latnName :=''; --重新提取
servId :=f.serv_id;
acctId :=0; --重新提取
singleServFlag :=-1; --重新提取
nbrType :=f.nbr_type;
-- 转售商描述(segmentDesc)
if segmentId is not null then
begin
execute immediate ' select partner_desc from emulatory_partner where party_role_id='||segmentId into segmentDesc;
exception when no_data_found then
segmentDesc :='';
end;
end if;
--地市名称(regionName)
if regionId is not null then
begin
execute immediate ' select distinct region_name from region_latn where region_id='||regionId into regionName;
exception when no_data_found then
regionName :='';
end;
end if;
--省份编码(latnId),省份名称(latnName)
if regionId is not null then
begin
execute immediate ' select distinct latn_id,latn_name from region_latn where region_id='||regionId into latnId, latnName;
exception when no_data_found then
latnId :=0;
latnName :='';
end;
end if;
--帐户ID (acctId)
if servId is not null then
begin
execute immediate ' select acct_id from serv_acct where state=''00A'' and serv_id='||servId into acctId;
exception when no_data_found then
acctId :=0;
end;
end if;
--单设备标识( singleServFlag )
if acctId is not null then
begin
execute immediate 'select count(*) from serv_acct where state=''00A'' and acct_id='||acctId into v_1;
if v_1=1 then
begin
singleServFlag :=1;
end;
elsif v_1 > 1 then
begin
singleServFlag :=0;
end;
else
begin
singleServFlag :=-1;
end;
end if;
exception when no_data_found then
singleServFlag :=-1;
end;
end if;
--更新数据
execute immediate 'update c_accnbr_for_free set SEGMENT_ID='||segmentId||',
SEGMENT_DESC=TO_CHAR('''||segmentDesc||'''),
BILLING_MODE=TO_CHAR('''||billingMode||'''),
STATE=TO_CHAR('''||state||'''),
REGION_ID='||regionId||',
REGION_NAME=TO_CHAR('''||regionName||'''),
LATN_ID='||latnId||',
LATN_NAME=TO_CHAR('''||latnName||'''),
SERV_ID='||servId||',
ACCT_ID='||acctId||',
SINGLE_SERV_FLAG='||singleServFlag||'
WHERE NBR_TYPE='||nbrType ||' AND ACC_NBR='||accNbr ;
commit;
end loop;
end;
select count(*) from serv_acct where state='00A' acct_id=991100110000000183;
select count(*) from serv_acct where state='00A' and acct_id=9;
select * from serv_acct where serv_id=993100100000008370;
select acct_id from serv_acct where state='00A' and serv_id=999000220000002914 ;
--------------------------------------------------------------------------
select count(*) from c_accnbr_for_free where acc_nbr='17008760314';
select
acct_id,count(*)
from serv_acct where serv_id in(
select serv_id from serv where acc_nbr in(select acc_nbr from c_accnbr_for_free)
)and state='00A'
group by acct_id having count(*)>1;
993100100000008370
997000630000003024
select *from offer_discount where offer_id in(134016712,134016718);
select * from balance_type
700
select * from c_accnbr_for_free where acc_nbr=17001040711;
select * from serv where acc_nbr=17001040711;
---在serv表不存在的用户
select acc_nbr,nbr_type from c_accnbr_for_free where acc_nbr not in(select acc_nbr from serv); --1334
select count(*) from c_accnbr_for_free where billing_mode is not null; --1128
---------------------
select * from staff;
select distinct staff_id from balance_source
--已执行
grant select on mvno_info.c_accnbr_for_free to MVNO_A_SN ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_GM ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_LY ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_DXT ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_TY ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_HJSJ ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_WWZC ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_JD ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_LM ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_LLKJ ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_CJT ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_FXZX ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_SWHL ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_CJSD ;
grant select on mvno_info.c_accnbr_for_free to MVNO_A_ASD ;
--已执行
create synonym MVNO_A_SN .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_GM .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LY .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_DXT .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_TY .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_HJSJ .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_WWZC .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_JD .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LM .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LLKJ .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_CJT .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_FXZX .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_SWHL .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_CJSD .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_ASD .c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_SN.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_GM.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LY.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_DXT.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_TY.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_HJSJ.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_WWZC.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_JD.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LM.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_LLKJ.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_CJT.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_FXZX.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_SWHL.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_CJSD.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
create synonym MVNO_A_ASD.c_accnbr_for_free for mvno_info.c_accnbr_for_free;
2014-06-22 1:08@yuqiaolu.pudongqu.shanghai.