存储过程练习_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:[e-mail].