PL/SQL exception-handling

Question

I have PL/SQL anonymous block to work with finding an employees based on their department_id. I wrote a procedure for that.

Code

CREATE OR REPLACE PROCEDURE find_employees (
    p_dept_no         IN     NUMBER,
    p_error_message      OUT VARCHAR2)
AS
    v_dept_no   NUMBER;
    dept_chk    EXCEPTION;

    CURSOR find_emp
    IS
        SELECT employee_id,
               first_name,
               last_name,
               salary,
               hire_date
          FROM employees
         WHERE department_id = p_dept_no;
BEGIN
    -- Check if the department_id in departments table
    IF Condition
    THEN                                --Need to check the condition here
        RAISE dept_chk;
    END IF;

    FOR i IN find_emp
    LOOP
        DBMS_OUTPUT.put_line (i.employee_id);
        DBMS_OUTPUT.put_line (i.first_name);
        DBMS_OUTPUT.put_line (i.last_name);
        DBMS_OUTPUT.put_line (i.salary);
        DBMS_OUTPUT.put_line (i.hire_date);
    END LOOP;
EXCEPTION
    WHEN dept_chk
    THEN
        p_error_message := 'Please enter valid department number';
END find_employees;

How to check if the department_id in departments table

Note:

On that procedure there is one input parameter p_dept_no as INPUT and p_error_message is the output parameter.

I need to check the if the department_id is in in the departments table then automatically the records will show other wise it's showing an exception so there i need to check the condition how it's possible let me know Thanks in advance.

I'm never keen on the design where a procedure handles an exception by returning successfully and passing back some error message via an out parameter. I think it would be better if it either did nothing when there was no such department ID, or else failed properly. In this case, you can just keep a count of the number of rows processed, and do something if it's 0. — William Robertson
– William Robertson, Commented May 28, 2018 at 9:52

Aleksej · Accepted Answer · 2018-05-28 10:08:44Z

Given that you already scan the table, you can simply set a variable in within the loop and check its value outside. For example:

CREATE OR REPLACE PROCEDURE find_employees(p_dept_no IN 
NUMBER,p_error_message OUT VARCHAR2)
AS 
    v_dept_no NUMBER;
    dept_chk EXCEPTION;
    vEmployeeFound boolean := false;  -- a boolean variable
CURSOR find_emp 
   IS 
SELECT 
    employee_id,  first_name,  last_name,  salary,  hire_date
FROM    
    employees
WHERE
    department_id = p_dept_no;
BEGIN    

FOR i in find_emp
LOOP
    dbms_output.put_line(i.employee_id);
    dbms_output.put_line(i.first_name);
    dbms_output.put_line(i.last_name);
    dbms_output.put_line(i.salary);
    dbms_output.put_line(i.hire_date);
    vEmployeeFound := true;                   -- set the variable
END LOOP;       

-- Check if the department_id in departments table
IF  NOT vEmployeeFound  THEN -- check the variable value
    RAISE dept_chk;
END IF;

EXCEPTION
    WHEN dept_chk THEN
    p_error_message:='Please enter valid department number';
END find_employees;

Collectives™ on Stack Overflow

PL/SQL exception-handling

Code

Note:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Code

Note:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related