forum.lissyara.su

Код: Выделить всё

 CREATE OR REPLACE PACKAGE ADBOLNICA.rasul_pkg is 
 procedure set_initial_state; 
 procedure save_id_p (uch number, id number); 
 procedure calc_vrem_preb; 
 cnt number := 0 ; 
 uch_in number; 
 end;

Код: Выделить всё

 CREATE OR REPLACE PACKAGE BODY ADBOLNICA.rasul_pkg is 
 type istoria_table is table of number index by pls_integer; 
 id_patient istoria_table; 
 empty istoria_table; 

 procedure set_initial_state is 
 begin 
 cnt:=cnt+1; 
 if (cnt=1) then 
 id_patient := empty; 
 end if; 
 end; 

 procedure save_id_p (uch number, id number) is 
 begin 
 id_patient(uch):=id; 
 end; 

 procedure calc_vrem_preb is 
 --declare 

 date_v date; 
 date_p date; 
 begin 
 if cnt=1 then 
 uch_in := id_patient.first; 
 end if; 
 while uch_in is not null loop 
 select date_vipes into date_v from istoria where uch_nom = uch_in and id_p = id_patient(uch_in); 
 select date_post into date_p from istoria where uch_nom = uch_in and id_p = id_patient(uch_in); 
 if (cnt<=2) then 
 if (date_v is null) then begin cnt:=1; 
 update istoria set vrem_prebivania=0 where uch_nom=uch_in; end; 
 else begin cnt:=1; 
 update istoria set vrem_prebivania=trunc(date_v-date_p) where uch_nom=uch_in; end; 
 end if; 
 end if; 
 id_patient.delete(uch_in); 
 uch_in:=id_patient.next(uch_in); 
 end loop; cnt:=0; 

 end; 
 end;

Код: Выделить всё

 CREATE OR REPLACE TRIGGER ADBOLNICA.rasul_trg3 
 before update 
 on istoria
 begin
 rasul_pkg.set_initial_state; 
 end; 


 CREATE OR REPLACE TRIGGER ADBOLNICA.rasul_trg4 
 after update on istoria 
 FOR EACH ROW
 begin
 rasul_pkg.save_id_p(:new.uch_nom,:new.id_p); 
 end;


 CREATE OR REPLACE TRIGGER ADBOLNICA.rasul_trg5 
 After update 
 on istoria
 begin
 rasul_pkg.calc_vrem_preb; 
 end;

Код: Выделить всё

UPDATE istoria SET date_vipes=sysdate where ID_P>=2 and ID_P<=5

Код: Выделить всё

 UPDATE istoria SET date_vipes=sysdate where ID_P>=2 and ID_P<=5