Week11 - Packages / Design

The questions can come in any order, so make sure you are selecting right option for all questions.

1. Create a package named "dept_package". Declare and define the following 4 Procedures in this package. 1) Procedure to insert a new record in the department table.     Name : addDepartment     Parameters and their types :         dept_id   department.department_id%type         dept_name  department.department_name%type         dept_block_number  department.department_block_number%type   2) Procedure to delete a department when department id is passed as the input parameter.     Name : deleteDepartment     Parameters and their types :         dept_id   department.department_id%type          3) Procedure to list the details of all departments (id, name and block number) in ascending order based on id.      Name : listDepartment      Parameters and their types :         It uses cursors         dept_details OUT SYS_REFCURSOR          4) Procedure to update a department name (Given the old dept name and new dept name)       Name : updateDepartment       Parameters and their types :         dept_name  department.department_name%type         new_dept_name  department.department_name%type   Note: Do not change the package or procedure name Do not change the argument count and order Do not change the output text. Instructions: 1. Create the package successfully 2. Once the package is created, check the functionality of the procedure using different anonymous block call. 3. DO NOT submit the anonymous block. Submit only the CREATE PACKAGE query.

CREATE OR REPLACE PACKAGE dept_package AS
    PROCEDURE addDepartment(
        dept_id IN department.department_id%TYPE,
        dept_name IN department.department_name%TYPE,
        dept_block_number IN department.department_block_number%TYPE
    );
    
    PROCEDURE deleteDepartment(
        dept_id IN department.department_id%TYPE
    );
    
    PROCEDURE listDepartment(
        dept_details OUT SYS_REFCURSOR
    );
    
    PROCEDURE updateDepartment(
        dept_name IN department.department_name%TYPE,
        new_dept_name IN department.department_name%TYPE
    );
END dept_package;
/

CREATE OR REPLACE PACKAGE BODY dept_package AS
    PROCEDURE addDepartment(
        dept_id IN department.department_id%TYPE,
        dept_name IN department.department_name%TYPE,
        dept_block_number IN department.department_block_number%TYPE
    ) IS
    BEGIN
        INSERT INTO department (department_id, department_name, department_block_number)
        VALUES (dept_id, dept_name, dept_block_number);
    END addDepartment;
    
    PROCEDURE deleteDepartment(
        dept_id IN department.department_id%TYPE
    ) IS
    BEGIN
        DELETE FROM department WHERE department_id = dept_id;
    END deleteDepartment;
    
    PROCEDURE listDepartment(
        dept_details OUT SYS_REFCURSOR
    ) IS
    BEGIN
        OPEN dept_details FOR
        SELECT department_id, department_name, department_block_number
        FROM department
        ORDER BY department_id;
    END listDepartment;
    
    PROCEDURE updateDepartment(
        dept_name IN department.department_name%TYPE,
        new_dept_name IN department.department_name%TYPE
    ) IS
    BEGIN
        UPDATE department
        SET department_name = new_dept_name
        WHERE department_name = dept_name;
    END updateDepartment;
END dept_package;
/

Create a package named "subject_package". Declare and define the following 4 Procedures in this package. 1) Procedure to insert a new record in the subject table.     Name : addSubject     Parameters and their types :         sub_id   subject.subject_id%type         sub_name   subject.subject_name%type,         sub_code  subject.subject_code%type,         staff_id subject.staff_id%type   2) Procedure to delete a subject when subject id is passed as the input parameter.     Name : deleteSubject     Parameters and their types :         sub_id   subject.subject_id%type          3) Procedure to list the details of all subjects (id, name, code and staff) in ascending order based on subject id.      Name : listSubject      Parameters and their types :         It uses cursors         subject_details OUT SYS_REFCURSOR          4) Procedure to update a subject name (Given the old subject name and new subject name)       Name : updateSubject       Parameters and their types :         sub_name  subject.subject_name%type         new_sub_name subject.subject_name%type Note: Do not change the package or procedure name Do not change the argument count and order Do not change the output text. Instructions: 1. Create the package successfully 2. Once the package is created, check the functionality of the procedure using different anonymous block call. 3. DO NOT submit the anonymous block. Submit only the CREATE PACKAGE query.

CREATE OR REPLACE PACKAGE subject_package AS
    PROCEDURE addSubject(
        sub_id IN subject.subject_id%TYPE,
        sub_name IN subject.subject_name%TYPE,
        sub_code IN subject.subject_code%TYPE,
        staff_id IN subject.staff_id%TYPE
    );
    
    PROCEDURE deleteSubject(
        sub_id IN subject.subject_id%TYPE
    );
    
    PROCEDURE listSubject(
        subject_details OUT SYS_REFCURSOR
    );
    
    PROCEDURE updateSubject(
        sub_name IN subject.subject_name%TYPE,
        new_sub_name IN subject.subject_name%TYPE
    );
END subject_package;
/

CREATE OR REPLACE PACKAGE BODY subject_package AS
    PROCEDURE addSubject(
        sub_id IN subject.subject_id%TYPE,
        sub_name IN subject.subject_name%TYPE,
        sub_code IN subject.subject_code%TYPE,
        staff_id IN subject.staff_id%TYPE
    ) IS
    BEGIN
        INSERT INTO subject
        VALUES (sub_id, sub_name, sub_code, staff_id);
    END addSubject;
    
    PROCEDURE deleteSubject(
        sub_id IN subject.subject_id%TYPE
    ) IS
    BEGIN
        DELETE FROM subject WHERE subject_id = sub_id;
    END deleteSubject;
    
    PROCEDURE listSubject(
        subject_details OUT SYS_REFCURSOR
    ) IS
    BEGIN
        OPEN subject_details FOR
        SELECT *
        FROM subject
        ORDER BY subject_id ASC;
    END listSubject;
    
    PROCEDURE updateSubject(
        sub_name IN subject.subject_name%TYPE,
        new_sub_name IN subject.subject_name%TYPE
    ) IS
    BEGIN
        UPDATE subject
        SET subject_name = new_sub_name
        WHERE subject_name = sub_name;
    END updateSubject;
END subject_package;
/