0909

CREATE TABLE jobs (
job_id VARCHAR2(10) PRIMARY KEY,
job_title VARCHAR2(35) NOT NULL
);

CREATE OR REPLACE PACKAGE JOB_PKG IS
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);
PROCEDURE get_job(p_job_id IN VARCHAR2, p_job_title OUT VARCHAR2);
END JOB_PKG;

CREATE OR REPLACE PACKAGE BODY JOB_PKG IS

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);
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        RAISE_APPLICATION_ERROR(-20001, 'Job ID already exists.');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20002, 'An unexpected 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%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20003, 'Job ID not found.');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20004, 'An unexpected 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%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20005, 'Job ID not found.');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20006, 'An unexpected error occurred: ' || SQLERRM);
END del_job;

PROCEDURE get_job(p_job_id IN VARCHAR2, p_job_title OUT VARCHAR2) IS
BEGIN
    SELECT job_title
    INTO p_job_title
    FROM jobs
    WHERE job_id = p_job_id;
    
    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20007, 'Job ID not found.');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20007, 'Job ID not found.');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20008, 'An unexpected error occurred: ' || SQLERRM);
END get_job;

END JOB_PKG;