lundi 13 mai 2019

IF/THEN in PL-SQL procedure

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