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;
    /