MyTetra Share
Делитесь знаниями!
delete_job
Время создания: 18.11.2017 09:59
Раздел: PL/SQL - Задачник - Exceptions
Запись: xintrea/mytetra_db_mcold/master/base/151098835028vb7f1xdh/text.html на raw.githubusercontent.com

Написать процедуру удаления job_id из jobs если данный job не используется как в employees так и в job_history


create or replace procedure delete_job (p_job_id varchar)

is

v_count number(3);

r_job exception;

begin

select count(*)

into v_count

from(select employee_id

from employees where job_id = p_job_id

union

select employee_id

from job_history where job_id = p_job_id);

if v_count = 0 then

delete from jobs where job_id = p_job_id;

if sql%notfound then

raise_application_error(-20201, 'Invalid Job Id');

end if;

else

raise r_job;

--raise_application_error(-20200, 'Job cannot be deleted as it has employees');

end if;

exception

when r_job then

raise_application_error(-20200, 'Job cannot be deleted as it has employees');

end;

 
MyTetra Share v.0.59
Яндекс индекс цитирования