Lab_10_2

CREATE OR REPLACE PACKAGE EMP_PKG IS
PROCEDURE add_employee(p_emp_id IN VARCHAR2, p_emp_name IN VARCHAR2, p_emp_salary IN NUMBER, p_dept_id IN VARCHAR2);
PROCEDURE get_employee(p_emp_id IN VARCHAR2, p_emp_name OUT VARCHAR2, p_emp_salary OUT NUMBER, p_dept_id 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_emp_name IN VARCHAR2, p_emp_salary IN NUMBER, 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, emp_name, emp_salary, dept_id) 
    VALUES (p_emp_id, p_emp_name, p_emp_salary, 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
PROCEDURE get_employee(p_emp_id IN VARCHAR2, p_emp_name OUT VARCHAR2, p_emp_salary OUT NUMBER, p_dept_id OUT VARCHAR2) IS
BEGIN
    SELECT emp_name, emp_salary, dept_id
    INTO p_emp_name, p_emp_salary, p_dept_id
    FROM employees
    WHERE emp_id = p_emp_id;
    
    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20013, 'Employee ID not found.');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20013, 'Employee ID not found.');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20014, 'An unexpected error occurred: ' || SQLERRM);
END get_employee;

END EMP_PKG;
/