alter table SMSPEC drop constraint SMCSPECCAUSE;
create or replace procedure CUT_DATABASE (v_date date) is
v_cnt number;
begin
for c in ( select h.id, h.doctype, h.docstate, h.createdat, h.locationto, h.locationfrom, h.location, s.quantity
from smdocuments h, smspec s
where h.id = s.docid and s.article = '011406'
and ( h.locationto = 4 or h.locationfrom = 4 or h.location = 4)
and h.createdat < v_date
) loop
delete from SMDOCUMENTS where id = c.id;
v_cnt := v_cnt + 1;
end loop;
dbms_output.put_line('deleted '||v_cnt||' documents.');
end;
begin
for c in (
select s.doctype, s.docid, s.specitem, s.CAUSETYPE, s.CAUSEID, s.CAUSESPECITEM from SMSPEC s
where (s.causetype is not null or s.causeid is not null or s.causespecitem is not null )
and not exists
(select 1 from SMSPEC s2 where s2.DOCTYPE = s.CAUSETYPE and s2.DOCID = s.CAUSEID and s2.SPECITEM = s.CAUSESPECITEM)
) loop
update SMSPEC s set s.CAUSETYPE = NULL, s.CAUSEID = NULL, s.CAUSESPECITEM = NULL
where s.doctype = c.doctype and s.docid = c.docid and s.specitem = c.specitem;
end loop;
end;
/
alter table SMSPEC
add constraint SMCSPECCAUSE foreign key (CAUSETYPE, CAUSEID, CAUSESPECITEM)
references SMSPEC (DOCTYPE, DOCID, SPECITEM);