lundi 28 mai 2018

PL/SQL exceptiion handling

I have PL/SQL Anonyms block to work with finding an employees based on their department_id, I written Procedure for that, on that procedure one input and one output

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 Then 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.

Aucun commentaire:

Enregistrer un commentaire