ASSIGNMENT 10
TASK1
CODE1 a: Create the package specification including the
procedures and function headings as public constructs.
CREATE OR REPLACE PACKAGE job_pkg AS
PROCEDURE add_job(p_job_id IN VARCHAR2, p_job_title IN VARCHAR2);
PROCEDURE upd_job(p_job_id IN VARCHAR2, p_job_title IN VARCHAR2);
PROCEDURE del_job(p_job_id IN VARCHAR2);
FUNCTION get_job(p_job_id IN VARCHAR2) RETURN VARCHAR2;
END job_pkg;
OUTPUT:
CODE1 b: Create the package body with the
implementations for each of the subprograms.
CREATE OR REPLACE PACKAGE BODY job_pkg AS
PROCEDURE add_job(p_job_id IN VARCHAR2, p_job_title IN VARCHAR2) IS
BEGIN
INSERT INTO jobs (job_id, job_title) VALUES (p_job_id, p_job_title);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Job ID already exists.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END add_job;
PROCEDURE upd_job(p_job_id IN VARCHAR2, p_job_title IN VARCHAR2) IS
BEGIN
UPDATE jobs
SET job_title = p_job_title
WHERE job_id = p_job_id;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No job found with the given ID.');
ELSE
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END upd_job;
PROCEDURE del_job(p_job_id IN VARCHAR2) IS
BEGIN
DELETE FROM jobs
WHERE job_id = p_job_id;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No job found with the given ID.');
ELSE
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END del_job;
FUNCTION get_job(p_job_id IN VARCHAR2) RETURN VARCHAR2 IS
v_job_title VARCHAR2(255);
BEGIN
SELECT job_title
INTO v_job_title
FROM jobs
WHERE job_id = p_job_id;
RETURN v_job_title;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'No job found with the given ID.';
WHEN OTHERS THEN
RETURN 'An error occurred: ' || SQLERRM;
END get_job;
END job_pkg;
/
OUTPUT:
CODE 1C:
Delete the following stand-alone procedures and function you
just packaged using the Procedures and Functions nodes in the
Object Navigation tree:
1) The ADD_JOB, UPD_JOB, and DEL_JOB procedures
2) The GET_JOB function
CODE:
DROP PROCEDURE add_job;
DROP PROCEDURE upd_job;
DROP PROCEDURE del_job;
DROP FUNCTION get_job;
CODE 1D:
Delete the following stand-alone procedures and function you
just packaged using the Procedures and Functions nodes in the
Object Navigation tree:
BEGIN
job_pkg.add_job(p_job_id => 'IT_SYSAN', p_job_title => 'SYSTEMS ANALYST');
END;
e. Query the JOBS table to see the result.
SELECT * FROM jobs WHERE job_id = 'IT_SYSAN';
TASK 2:
a. Create a package specification and a package body
called EMP_PKG that contains the following procedures
and function that you created earlier:
1) ADD EMPLOYEE procedure as a public construct 2) GET
EMPLOYEE procedure as a public construct
3) VALID DEPTID function as a private construct
CODE:
CREATE OR REPLACE PACKAGE EMP_PKG AS
-- Public procedures
PROCEDURE ADD_EMPLOYEE(p_dept_id IN NUMBER, p_emp_name IN VARCHAR2,
p_email IN VARCHAR2);
PROCEDURE GET_EMPLOYEE(p_emp_id IN NUMBER);
-- Private function
FUNCTION VALID_DEPTID(p_dept_id IN NUMBER) RETURN BOOLEAN;
END EMP_PKG;
CREATE OR REPLACE PACKAGE BODY EMP_PKG AS
-- Public procedure to add an employee
PROCEDURE ADD_EMPLOYEE(p_dept_id IN NUMBER, p_emp_name IN VARCHAR2,
p_email IN VARCHAR2) IS
BEGIN
IF NOT VALID_DEPTID(p_dept_id) THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid Department ID: ' || p_dept_id);
END IF;
INSERT INTO EMPLOYEES (EMP_NAME, EMAIL, DEPT_ID)
VALUES (p_emp_name, p_email, p_dept_id);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002, 'Error adding employee: ' || SQLERRM);
END ADD_EMPLOYEE;
-- Public procedure to get employee details
PROCEDURE GET_EMPLOYEE(p_emp_id IN NUMBER) IS
v_emp_name EMPLOYEES.EMP_NAME%TYPE;
v_email EMPLOYEES.EMAIL%TYPE;
v_dept_id EMPLOYEES.DEPT_ID%TYPE;
BEGIN
SELECT EMP_NAME, EMAIL, DEPT_ID
INTO v_emp_name, v_email, v_dept_id
FROM EMPLOYEES
WHERE EMP_ID = p_emp_id;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
DBMS_OUTPUT.PUT_LINE('Email: ' || v_email);
DBMS_OUTPUT.PUT_LINE('Department ID: ' || v_dept_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with ID: ' || p_emp_id);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error retrieving employee: ' || SQLERRM);
END GET_EMPLOYEE;
-- Private function to validate department ID
FUNCTION VALID_DEPTID(p_dept_id IN NUMBER) 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;
END EMP_PKG;
/
b. Invoke the EMP_PKG.ADD EMPLOYEE procedure, using
department ID 15 for employee Jane Harris with the email
ID JAHARRIS. Because department ID 15 does not exist,
you should get an error message as specified in the
exception handler of your procedure.
BEGIN
EMP_PKG.ADD_EMPLOYEE(p_dept_id => 15, p_emp_name => 'Jane Harris', p_email =>
'JAHARRIS');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
c. Invoke the ADD_EMPLOYEE package procedure by using
department ID 80 for employee David Smith with the email ID
DASMITH.
CODE:
BEGIN
EMP_PKG.ADD_EMPLOYEE(p_dept_id => 80, p_emp_name => 'David Smith', p_email =>
'DASMITH');
DBMS_OUTPUT.PUT_LINE('Employee added successfully.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
d. Query the EMPLOYEES table to verify that the new employee
was added.
CODE:
SELECT * FROM EMPLOYEES WHERE EMP_NAME = 'David Smith';
