Untitled

create or replace procedure SP_H03_03 is
  v_mnr medewerkers.mnr%type;
  v_error varchar2(100);
  e_not_found exception;
  e_fk_found exception;
  pragma exception_init(e_fk_found, -2292);
  
BEGIN
  v_mnr := 7654;

  delete from medewerkers
  where mnr = v_mnr;
  
  delete from inschrijvingen
  where cursist = v_mnr;

  update uitvoeringen
  set docent = null;
  where docent = v_mnr
  
  if SQL%NOTFOUND
    then
      raise e_not_found;
  end if;
  
  dbms_output.put_line('Medewerker succesvol verwijderd');
  
EXCEPTION
  when e_fk_found
    then
      v_error := dbms_utility.format_error_stack();
  if v_error like %HOOFD_FK%
    then
      raise_application_error(-20000, 'Medewerker is afdelingshoofd');
  elseif v_error like %CHEF_FK%
    then
      raise_application_error(-20000, 'Medewerker is chef afdeling');
      end if;
      
   when e_not_found
    then
      raise_application_error(-20000, 'Medewerker bestaat niet');
   when others 
    then
      raise_application_error(-20000, 'Onbekende fout deed zich voor');
END;