Week11 - Packages / Assess

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

1. Create a package named "staff_package". Declare and define the following 4 Procedures in this package. 1) Procedure to insert a new record in the staff table.     Name : addStaff     Parameters and their types :         id   staff.staff_id%type,         name   staff.staff_name%type         dept_id  staff.department_id%type   2) Procedure to delete a staff when staff id is passed as the input parameter.     Name : deleteStaff     Parameters and their types :         id  staff.staff_id%type          3) Procedure to list the details of all staff (id, name, department id) in ascending order based on staff id.      Name : listStaff      Parameters and their types :         It uses cursors         staff_details OUT SYS_REFCURSOR          4) Procedure to update a staff name (Given the old staff name and new staff name)       Name : updateStaff       Parameters and their types :         name  staff.staff_name%type,         new_name staff.staff_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 staff_package AS
    PROCEDURE addStaff(
        id IN staff.staff_id%TYPE,
        name IN staff.staff_name%TYPE,
        dept_id IN staff.department_id%TYPE
    );
    
    PROCEDURE deleteStaff(
        id IN staff.staff_id%TYPE
    );
    
    PROCEDURE listStaff(
        staff_details OUT SYS_REFCURSOR
    );
    
    PROCEDURE updateStaff(
        name IN staff.staff_name%TYPE,
        new_name IN staff.staff_name%TYPE
    );
END staff_package;
/

CREATE OR REPLACE PACKAGE BODY staff_package AS
    PROCEDURE addStaff(
        id IN staff.staff_id%TYPE,
        name IN staff.staff_name%TYPE,
        dept_id IN staff.department_id%TYPE
    ) IS
    BEGIN
        INSERT INTO staff
        VALUES (id, name, dept_id);
    END addStaff;
    
    PROCEDURE deleteStaff(
        id IN staff.staff_id%TYPE
    ) IS
    BEGIN
        DELETE FROM staff WHERE staff_id = id;
    END deleteStaff;
    
    PROCEDURE listStaff(
        staff_details OUT SYS_REFCURSOR
    ) IS
    BEGIN
        OPEN staff_details FOR
        SELECT *
        FROM staff
        ORDER BY staff_id ASC;
    END listStaff;
    
    PROCEDURE updateStaff(
        name IN staff.staff_name%TYPE,
        new_name IN staff.staff_name%TYPE
    ) IS
    BEGIN
        UPDATE staff
        SET staff_name = new_name
        WHERE staff_name = name;
    END updateStaff;
END staff_package;
/

2.Create a package named "calculator". Declare and define the following 4 Functions in this package. FUNCTION addNumbers( n1 number, n2 number) return number; FUNCTION subtractNumbers( n1 number, n2 number) return number; FUNCTION multiplyNumbers( n1 number, n2 number) return number; FUNCTION divideNumbers( n1 number, n2 number) return number; Note: Do not change the package 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 FUNCTIONS inside the package using appropriate calls 3. DO NOT submit the call statements. Submit only the CREATE PACKAGE query.

CREATE OR REPLACE PACKAGE calculator AS
    FUNCTION addNumbers(n1 NUMBER, n2 NUMBER) RETURN NUMBER;
    
    FUNCTION subtractNumbers(n1 NUMBER, n2 NUMBER) RETURN NUMBER;
    
    FUNCTION multiplyNumbers(n1 NUMBER, n2 NUMBER) RETURN NUMBER;
    
    FUNCTION divideNumbers(n1 NUMBER, n2 NUMBER) RETURN NUMBER;
END calculator;
/

CREATE OR REPLACE PACKAGE BODY calculator AS
    FUNCTION addNumbers(n1 NUMBER, n2 NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN n1 + n2;
    END addNumbers;
    
    FUNCTION subtractNumbers(n1 NUMBER, n2 NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN n1 - n2;
    END subtractNumbers;
    
    FUNCTION multiplyNumbers(n1 NUMBER, n2 NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN n1 * n2;
    END multiplyNumbers;
    
    FUNCTION divideNumbers(n1 NUMBER, n2 NUMBER) RETURN NUMBER IS
    BEGIN
        IF n2 = 0 THEN
            RETURN NULL;
        ELSE
            RETURN n1 / n2;
        END IF;
    END divideNumbers;
END calculator;
/