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

num:=SHOW_ALERT('ALERT_PHONE');

Go_Item('CONTRACT.NAMEORG');

ELSIF :CONTRACT.HOUSE IS NULL then

Set_Alert_Property('ALERT_PHONE',TITLE,'Ошибка');  Set_Alert_Property('ALERT_PHONE',ALERT_MESSAGE_TEXT,'Вы не можете оставить поле "Дом" пустым');

num:=SHOW_ALERT('ALERT_PHONE');

Go_Item('CONTRACT.HOUSE');

ELSE

select count(*) into num from contacts where contactid=:contract.address;

if num=0

then

Message('Адреса с таким кодом не существует');

else

select count(*) into num from companies where id_company=:contract.nameorg;

if num=0

then

Message('Компании с таким кодом не существует'); else commit;

field:=Find_Item('CONTRACT.HOUSE');

set_item_property(field,INSERT_ALLOWED,PROPERTY_false);  set_item_property(field,UPDATE_ALLOWED,PROPERTY_false); field:=Find_Item('CONTRACT.SIRNAME'); set_item_property(field,UPDATE_ALLOWED,PROPERTY_FALSE); field:=Find_Item('CONTRACT.FNAME');

set_item_property(field,UPDATE_ALLOWED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.SNAME');  set_item_property(field,UPDATE_ALLOWED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.NAMEORG');  set_item_property(field,UPDATE_ALLOWED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.ADDRESS');  set_item_property(field,UPDATE_ALLOWED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.PHONE');  set_item_property(field,UPDATE_ALLOWED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.SIRNAME');  set_item_property(field,INSERT_ALLOWED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.FNAME');  set_item_property(field,INSERT_ALLOWED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.SNAME');  set_item_property(field,INSERT_ALLOWED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.NAMEORG');  set_item_property(field,INSERT_ALLOWED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.ADDRESS');  set_item_property(field,INSERT_ALLOWED,PROPERTY_FALSE);  set_item_property(field,UPDATE_ALLOWED,PROPERTY_FALSE);        field:=Find_Item('CONTRACT.PHONE');

set_item_property(field,INSERT_ALLOWED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.CREATE_CONTRACT');  set_item_property(field,ENABLED,PROPERTY_TRUE);  field:=Find_Item('CONTRACT.DELETE_CONTRACT');  set_item_property(field,ENABLED,PROPERTY_TRUE);  field:=Find_Item('CONTRACT.ENTER_QUERY');  set_item_property(field,ENABLED,PROPERTY_TRUE);  field:=Find_Item('CONTRACT.EXECUTE_QUERY');  set_item_property(field,ENABLED,PROPERTY_TRUE);  field:=Find_Item('CONTRACT.PRE_CONTRACT');  set_item_property(field,ENABLED,PROPERTY_TRUE);  field:=Find_Item('CONTRACT.NEXT_CONTRACT');

set_item_property(field,ENABLED,PROPERTY_TRUE);  GO_ITEM('CONTRACT.CONTRACT');  field:=Find_Item('CONTRACT.CHANGE_CONTRACT');  set_item_property(field,ENABLED,PROPERTY_TRUE);  field:=Find_Item('CONTRACT.COMMIT');  set_item_property(field,ENABLED,PROPERTY_FALSE);  field:=Find_Item('CONTRACT.ROLLBACK');

set_item_property(field,ENABLED,PROPERTY_FALSE);

end if;

end if;

end if;

end;

Листинг некоторых хранимых процедур         

PROCEDURE CALC_PA is app1 number(15); srv1 number(15); price1 number(15); acc1 number(15); bal1 number(15); st1 number; st2 number; calc number(15); dat date; srv2 number(15); n_msc1 number; cursor cur1 is

select app_srv.app,app_srv.service,app_srv.price,account.acc 

from account,app,APP_SRV,status_app,services,types where account.acc=app.acc and app.app=app_srv.app and app.stat=status_app.stat and app_srv.service=services.service and services.typeid=types.typeid and app_srv.pdate>sysdate and app.stat in(select stat from 

status_app where name_stat like 'активно') and services.typeid in(select typeid from types

where name_type like 'периодическая' or name_type like 'аренда'); begin open cur1; loop

fetch cur1 into app1,srv1,price1,acc1; exit when cur1%NOTFOUND;

update account set balance=balance-price1 where acc=acc1; commit;

select balance into bal1 from account where acc=acc1; if bal1<0 then

select stat into st1 from status_app where name_stat like

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