This is an old revision of the document!
-------------------------------------------------------- -- DDL for Procedure INSERT_EMPLOYEE -------------------------------------------------------- set define off; CREATE OR REPLACE PROCEDURE "HR"."INSERT_EMPLOYEE" ( P_FIRST_NAME IN VARCHAR2 , P_LAST_NAME IN VARCHAR2 , P_DEPARTMENT_NAME IN departments.department_name%type , P_SALARY IN NUMBER , p_email in varchar2 , p_phone_number in varchar2 , p_hire_date in date , p_job_title in varchar2 , p_commission_pct in number , p_manager_id in number ) AS v_employee_id number; v_department_id departments.department_id%type; v_manager_id number; v_job_id jobs.job_id%type; v_max_salary number; BEGIN begin select department_id into v_department_id from departments where department_name = P_DEPARTMENT_NAME; exception when no_data_found then raise_application_error(-20001, 'no department found'); end; begin select job_id into v_job_id from jobs where job_title = p_job_title; exception when no_data_found then raise_application_error(-20002, 'no such job_title found'); end; v_manager_id := 101; select max(salary) into v_max_salary from employees; if p_salary > v_max_salary then raise_application_error(-20003, 'too much money exception'); end if; merge into employees e using (select 1 from dual) on (first_name = p_first_name and last_name = p_last_name) when matched then update set email = p_email, phone_number = p_phone_number, hire_date = p_hire_date, job_id = v_job_id, salary = p_salary, commission_pct = p_commission_pct, manager_id = v_manager_id, department_id = v_department_id when not matched then insert ( employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ) values ( employees_seq.nextval, p_first_name, p_last_name, p_email, p_phone_number, p_hire_date, v_job_id, p_salary, p_commission_pct, v_manager_id, v_department_id ); END INSERT_EMPLOYEE; /