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