Проектирование схемы БД в среде ERwin. Проблема избыточности данных. Проблема обновления данных. Проблема удаления данных., страница 22

select stat into st2 from status_app where name_stat like 'активно'; update app set stat=st1 where acc=acc1 and stat=st2; select s$msc_ord.NEXTVAL into calc from dual;

select service into srv2 from services where lower(name_srv) like

'блокировка';

select sysdate into dat from dual; 

select n_msc into n_msc1 from need_msc where lower(name_st) like 'нет'; insert into msc_orders values(calc,app1,srv2,dat,null,n_msc1); end if; 

select s$calc.NEXTVAL into calc from dual; select sysdate into dat from dual;

insert into calc values(calc,acc1,srv1,price1,dat);

end loop; close cur1; commit;  exception when NO_DATA_FOUND then

dbms_output.put_line('Не хватает данных о статусах приложения обслуживания или о типах услуг'); end;

PROCEDURE WEB_INSERT_APP2 (tar in app.tarifid%type,quant1 out number,  quant2 out number, app1 in app.app%type, num1 out number,num2 out number,

acc1 in account.acc%type, cdate1 in contract.cdate%type,msysdn1 in app.msysdn%type,

st app.stat%type, num3 out number)        

is 

srv services.service%type; bal account.balance%type;  cdate2 app.cdate%type; key_srv number(15);  price number(15);

fsim  attribute_mask.sim%type;  fimsi attribute_mask.imsi%type; nsim  attribute_mask.next_sim%type;  nimsi attribute_mask.next_imsi%type;  lsim  attribute_mask.sim1%type;  limsi attribute_mask.imsi1%type;

fpin1  attribute_mask.first_pin1%type;  fpin2 attribute_mask.first_pin2%type; npin1 attribute_mask.next_pin1%type;  npin2 attribute_mask.next_pin2%type;  lpin1  attribute_mask.last_pin1%type;  lpin2 attribute_mask.last_pin2%type;

fpuck1  attribute_mask.first_puck1%type;  fpuck2 attribute_mask.first_puck2%type; npuck1 attribute_mask.next_puck1%type;  npuck2 attribute_mask.next_puck2%type;  lpuck1  attribute_mask.last_puck1%type;  lpuck2 attribute_mask.last_puck2%type; begin

select count(*) into quant1 from tarif_srv where tarifid=tar and service in(select service from services where lower(name_srv) like

'продажа sim-карта');

/*Есть ли услуга продажи сим-карты на ТП*/ if quant1>0 then

select quantity into quant2 from store where lower(name_inventory) like

'sim-карта';

/*Есть ли сим-карта на складе*/ if quant2>0 then

select service into srv from services where lower(name_srv) like

'продажа sim-карта';

select price into price from tarif_srv where service=srv and tarifid=tar;

select balance into bal from account where acc=acc1; bal:=bal-price;

/*Хватает ли денег на покупку сим-карты*/ if bal<0 then num1:=0; else num1:=1; select count(*) into num2 from attribute_mask where used=1;

/*Есть ли активная маска*/ if num2>0 then select sim,imsi,sim1,imsi1,next_sim,next_imsi,first_pin1,next_pin1,last_pin1,                                                      first_pin2,next_pin2,last_pin2,

                                                          first_puck1,next_puck1,last_puck1,

first_puck2,next_puck2,last_puck2 into fsim,fimsi,lsim,limsi,nsim,nimsi,           fpin1,npin1,lpin1,           fpin2,npin2,lpin2,

fpuck1,npuck1,lpuck1,

fpuck2,npuck2,lpuck2              from attribute_mask where used=1;

/*Если маска используется впервые*/ if nsim is null  then insert into app values(app1,acc1,msysdn1,fsim,fimsi,tar,cdate1,fpin1,fpin2,fpuck1,fpuck

2,st); update attribute_mask set next_sim=fsim+1,next_imsi=fimsi+1,

next_pin1=fpin1+1,next_pin2=fpin2+1,

next_puck1=fpuck1+1,next_puck2=fpuck2+1  where used=1; commit; 

select sysdate into cdate2 from dual; select s$app_srv.nextval into key_srv from dual; update account set balance=bal where acc=acc1; 

insert into app_srv values(app1,srv,cdate2,cdate2,key_srv,price); select s$calc.nextval into key_srv from dual; insert into calc values(key_srv,acc1,srv,price,cdate2); update store set quantity=quantity-1 where lower(name_inventory) like

'sim-карта'; select service into srv from services where lower(name_srv) like

'разблокировка';

commit; add_srv(app1,srv); else

Приложение 2. Описание формата CDR-файла