CODEWITHSUNDEEP
sqloracle-databaseplsql
Munchy
Munchy

Reputation: 95

PL/SQL Procedure, raise no_data_found during insert

So here is the code as given to me in my oracle class:

CREATE OR REPLACE PROCEDURE add_job_hist
    (p_emp_id     IN      employees.employee_id%TYPE,
    p_new_jobid   IN      jobs.job_id%TYPE)  IS
BEGIN
    INSERT INTO job_history
    SELECT employee_id, hire_date, SYSDATE, job_id, department_id
    FROM employees
    WHERE employee_id = p_emp_id;

    UPDATE employees
    SET hire_date = SYSDATE,
        job_id = p_new_jobid,
        salary = (SELECT min_salary + 500
                  FROM jobs
                  WHERE job_id = p_new_jobid)
    WHERE employee_id = p_emp_id;

    DBMS_OUTPUT.PUT_LINE('Added employee ' || p_emp_id || '
        details to the JOB_HISTORY table');
    DBMS_OUTPUT.PUT_LINE('Updated current job of employee ' ||
        p_emp_id|| 'to ' || p_new_jobid);

EXCEPTION
WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR  (-20001, 'Employee does not exist!');
END add_job_hist;
/

The problem is that this is not a "SELECT INTO" statement, so the WHEN NO_DATA_FOUND exception isn't processed. I can raise my own user-defined exception, but it doesn't seem to let me do this in a procedure. How can I fix this so that it handles the exception? Because right now, if i give a p_emp_id that does not exist, it handles it anyway and the instructions do not want any additional procedure variables.

Thanks!

Upvotes: 2

Views: 4317

Answers (1)

San
San

Reputation: 4538

Check using SQL%ROWCOUNT after the insert statement, if it returns 0, then raise no_data_found explicitly.

CREATE OR REPLACE PROCEDURE add_job_hist(p_emp_id    IN employees.employee_id%TYPE,
                                         p_new_jobid IN jobs.job_id%TYPE) IS
BEGIN
  INSERT INTO job_history
    SELECT employee_id,
           hire_date,
           SYSDATE,
           job_id,
           department_id
      FROM employees
     WHERE employee_id = p_emp_id;
  IF SQL%ROWCOUNT = 0 THEN
    RAISE no_data_found;
  END IF;
  UPDATE employees
     SET hire_date = SYSDATE,
         job_id    = p_new_jobid,
         salary   =
         (SELECT min_salary + 500 FROM jobs WHERE job_id = p_new_jobid)
   WHERE employee_id = p_emp_id;
  dbms_output.put_line('Added employee ' || p_emp_id || '
    details to the JOB_HISTORY table');
  dbms_output.put_line('Updated current job of employee ' || p_emp_id || 'to ' || p_new_jobid);
EXCEPTION
  WHEN no_data_found THEN
    raise_application_error(-20001, 'Employee does not exist!');
END add_job_hist;
/

Upvotes: 5

Related Questions