Scribd
Upload
6 views3 pages

Employee and Department Management Procedures

The document outlines several PL/SQL procedures and functions related to employee and department management in a database. It includes a procedure to retrieve employee details, a function to check department existence, a function to get department name by employee ID, and a procedure to delete a department while reassigning employees. Each section is accompanied by an anonymous block for testing the functionality of the respective procedures and functions.

Uploaded by

Oğuzhan Elmas
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views3 pages

Employee and Department Management Procedures

The document outlines several PL/SQL procedures and functions related to employee and department management in a database. It includes a procedure to retrieve employee details, a function to check department existence, a function to get department name by employee ID, and a procedure to delete a department while reassigning employees. Each section is accompanied by an anonymous block for testing the functionality of the respective procedures and functions.

Uploaded by

Oğuzhan Elmas
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1.

Employee Full Name and Salary Procedure


Procedure:

CREATE OR REPLACE PROCEDURE GetEmployeeDetails (


p_emp_id IN employees.employee_id%TYPE,
p_full_name OUT VARCHAR2,
p_salary OUT [Link]%TYPE
) IS
BEGIN
SELECT first_name || ' ' || last_name, salary
INTO p_full_name, p_salary
FROM employees
WHERE employee_id = p_emp_id;

EXCEPTION
WHEN NO_DATA_FOUND THEN
p_full_name := NULL;
p_salary := NULL;
DBMS_OUTPUT.PUT_LINE('Employee not found.');
END;
/

Anonymous Block (Test):

DECLARE
v_name VARCHAR2(100);
v_sal NUMBER;
BEGIN
GetEmployeeDetails(101, v_name, v_sal);

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || NVL(v_name, 'N/A'));


DBMS_OUTPUT.PUT_LINE('Salary: ' || NVL(TO_CHAR(v_sal), 'N/A'));
END;
/

2. Check Department Existence Function


Function:

CREATE OR REPLACE FUNCTION CheckDepartment (


p_dept_id IN departments.department_id%TYPE
) RETURN BOOLEAN IS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM departments
WHERE department_id = p_dept_id;

RETURN (v_cnt > 0);


END;
/

Anonymous Block (Test):

BEGIN
IF CheckDepartment(10) THEN
DBMS_OUTPUT.PUT_LINE('Department exists.');
ELSE
DBMS_OUTPUT.PUT_LINE('Department does not exist.');
END IF;
END;
/

3. Department Name by Employee ID Function


Function:

CREATE OR REPLACE FUNCTION GetDepartmentName (


p_emp_id IN employees.employee_id%TYPE
) RETURN VARCHAR2 IS
v_dept_name departments.department_name%TYPE;
BEGIN
SELECT d.department_name
INTO v_dept_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.employee_id = p_emp_id;

RETURN v_dept_name;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
/

Anonymous Block (Test):

DECLARE
v_name VARCHAR2(100);
BEGIN
v_name := GetDepartmentName(101);
IF v_name IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Invalid Employee ID.');
ELSE
DBMS_OUTPUT.PUT_LINE('Department Name: ' || v_name);
END IF;
END;
/

4. Delete Department Procedure


Procedure:

CREATE OR REPLACE PROCEDURE DeleteDepartment (


p_dept_id IN departments.department_id%TYPE
) IS
v_cnt NUMBER;
v_updated NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM departments
WHERE department_id = p_dept_id;

IF v_cnt = 0 THEN
DBMS_OUTPUT.PUT_LINE('Department does not exist.');
RETURN;
END IF;

UPDATE employees
SET department_id = NULL
WHERE department_id = p_dept_id;

v_updated := SQL%ROWCOUNT;

DELETE FROM departments


WHERE department_id = p_dept_id;

DBMS_OUTPUT.PUT_LINE('Department deleted successfully.');


DBMS_OUTPUT.PUT_LINE('Employees reassigned to NULL: ' || v_updated);
END;
/

Anonymous Block (Test):

BEGIN
DeleteDepartment(10);
END;
/

You might also like