naaani

CREATE OR REPLACE PACKAGE EMP_PKG IS
PROCEDURE add_employee(p_emp_id IN VARCHAR2, p_first_name IN VARCHAR2, p_last_name IN VARCHAR2, p_email IN VARCHAR2, p_dept_id IN VARCHAR2);
PROCEDURE get_employee(p_dept_id IN VARCHAR2, p_first_name OUT VARCHAR2, p_last_name OUT VARCHAR2, p_email OUT VARCHAR2);
END EMP_PKG;

CREATE OR REPLACE PACKAGE BODY EMP_PKG IS

-- Private function to validate department ID
FUNCTION valid_deptid(p_dept_id IN VARCHAR2) RETURN BOOLEAN IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM departments
    WHERE dept_id = p_dept_id;
    
    RETURN v_count > 0;
END valid_deptid;

-- Public procedure to add an employee
PROCEDURE add_employee(p_emp_id IN VARCHAR2, p_first_name IN VARCHAR2, p_last_name IN VARCHAR2, p_email IN VARCHAR2, p_dept_id IN VARCHAR2) IS
BEGIN
    IF NOT valid_deptid(p_dept_id) THEN
        RAISE_APPLICATION_ERROR(-20010, 'Invalid department ID.');
    END IF;
    
    INSERT INTO employees (emp_id, first_name, last_name, email, dept_id) 
    VALUES (p_emp_id, p_first_name, p_last_name, p_email, p_dept_id);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        RAISE_APPLICATION_ERROR(-20011, 'Employee ID already exists.');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20012, 'An unexpected error occurred: ' || SQLERRM);
END add_employee;

-- Public procedure to get an employee by department ID
PROCEDURE get_employee(p_dept_id IN VARCHAR2, p_first_name OUT VARCHAR2, p_last_name OUT VARCHAR2, p_email OUT VARCHAR2) IS
BEGIN
    SELECT first_name, last_name, email
    INTO p_first_name, p_last_name, p_email
    FROM employees
    WHERE dept_id = p_dept_id AND ROWNUM = 1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20013, 'No employee found in the specified department.');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20014, 'An unexpected error occurred: ' || SQLERRM);
END get_employee;

END EMP_PKG;