I have to create procedure for updating of row in table DEPARTMENTS. Parameters are department_id, department name, location, manager_id of department.Then i have to check if department name is unique, if we have such a location in TABLE locations, if there is manager_id in TABLE employees. If requirements are not met, records have to go in ERROR_DEPARTMENTS. Unfortunately i don't know how to include every of parameters for check. I do a research and i am thinking that i need to use IF/THEN, but i am not sure where and how.
create unique index uni_dept on department_name;
create table error_depart as select * From departments where 1 = 2;
create or replace procedure UPD_DEPARTMENT (v_depid in number, v_depn in
varchar2, v_lid in number,v_phn in number)
is
begin
update DEPARTMENTS d set
d.department_name = v_depn,
d.location_id = v_lid
where d.department_id = v_depid;
exception
when dup_val_on_index then
insert into ERROR_DEPART (department_id,
department_name,location_id) values (v_depid, v_depn,v_lid);
end;
begin
UPD_DEPARTMENT(10, 'Finance', 1900,4);
UPD_DEPARTMENT(110, 'Naval', 1900,3);
end;
Aucun commentaire:
Enregistrer un commentaire