存储过程练习_01

---公勉卡

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.