-- ex3
CREATE OR REPLACE PROCEDURE newJob(p_empid employees.employee_id%TYPE, p_jobid jobs.job_id%TYPE)
AS
    v_count NUMBER(1,0);
    v_current_jobid employees.job_id%TYPE;
    v_depId departments.department_id%TYPE;
    v_hiredate employees.hire_date%TYPE;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM employees
    WHERE employee_id = p_empid;

    IF v_count = 0 THEN
        raise_application_error(-20000, 'no such employee:['||p_empid||']');
    END IF;

    SELECT job_id
    INTO v_current_jobid
    FROM employees
    WHERE employee_id = p_empid;

    IF p_jobid = v_current_jobid THEN
        raise_application_error(-20000, 'same job:['||p_jobid||']');
    END IF;

    SELECT hire_date
    INTO v_hiredate
    FROM employees
    WHERE employee_id = p_empid;

    SELECT department_id
    INTO v_depId
    FROM employees
    WHERE employee_id = p_empid;

    INSERT INTO job_history
    VALUES (p_empid, v_hiredate, (SELECT CURRENT_DATE - 1 FROM dual), p_jobid, v_depId);

    UPDATE employees
    SET job_id = p_jobid,
        hire_date = (SELECT CURRENT_DATE FROM dual)
    WHERE employee_id = p_empid;
END;