Week9 - Cursors / Design

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

1. Write a PL/SQL simple procedure named increase_servicecharge with 3 parameters. The first input parameter is increasedcharge of type number. The second input parameter is mname of type varchar. The third output parameter is status of type varchar. This procedure is used to increase the service_charge by increasedcharge passed as an argument of the models whose manufacturer_name is mname passed as an argument.

Design Rules:
1) If the models are available with the manufacturer_name mname, then set the status parameter with the number of rows updated with the new service_charge. The status stored format is like below
Service charge incremented for models
2) If the models are not available with the manufacturer_name mname, then set the status parameter like ‘No models available’

Use the below skeleton:
Procedure name: increase_servicecharge
Input parameters: increasedcharge of type number and mname of type varchar
Output parameter: status of type varchar

Note:
Do not change the procedure name
Do not change the argument count and order
Do not change the output text.

Instructions:

1. Create the procedure successfully
2. Once the procedure is created, check the functionality of the procedure using different anonymous block call.
3. DO NOT submit the anonymous block. Submit only the CREATE PROCEDURE query.

    CREATE OR REPLACE PROCEDURE increase_servicecharge (
        increasedcharge IN NUMBER,
        mname IN VARCHAR2,
        status OUT VARCHAR2
    ) AS
        rows_updated NUMBER;
    BEGIN
        UPDATE Model
        SET Service_Charge = Service_Charge + increasedcharge
        WHERE Manufacturer_model_code = (
            SELECT Manufacturer_code
            FROM Manufacturer
            WHERE manufacture_Name = mname
        );

        rows_updated := SQL%ROWCOUNT;

        IF rows_updated > 0 THEN
            status := 'Service charge incremented for ' || rows_updated || ' models';
        ELSE
            status := 'No models available';
        END IF;
    END increase_servicecharge;
    /
    SET SERVEROUTPUT ON;

    DECLARE
        increasedcharge_param NUMBER := 250;
        mname_param VARCHAR2(100) := 'Benz';
        status_param VARCHAR2(100);
    BEGIN
        increase_servicecharge(increasedcharge_param, mname_param, status_param);
        DBMS_OUTPUT.PUT_LINE('Status: ' || status_param);
    END;
    /

2. Write a PL/SQL simple procedure named ‘display_manufacturer’ to display the manufacturer_code, manufacturer_name and total service_charge of all the models belonging to that manufacturer. Display the details of the manufacturer who have atleast one model of vehicle. Display the details in ascending order based on manufacturer_code.

Hint: Use cursor

Use the below skeleton:
Procedure name: display_manufacturer

Note:
Do not change the procedure name
Do not change the argument count and order
Do not change the output text. Header and --- are given in the template itself.

Instructions:

1. Create the procedure successfully
2. Once the procedure is created, check the functionality of the procedure using different anonymous block call.
3. DO NOT submit the anonymous block. Submit only the CREATE PROCEDURE query.

    CREATE OR REPLACE PROCEDURE display_manufacturer IS
        CURSOR manufacturer_cursor IS
            SELECT m.manufacturer_code, m.manufacture_name, SUM(model.service_charge) AS total_service_charge
            FROM Manufacturer m
            JOIN Model ON m.manufacturer_code = Model.manufacturer_model_code
            GROUP BY m.manufacturer_code, m.manufacture_name
            HAVING COUNT(Model.model_code) > 0
            ORDER BY m.manufacturer_code ASC;

        v_manufacturer_code Manufacturer.manufacturer_code%TYPE;
        v_manufacturer_name Manufacturer.manufacture_name%TYPE;
        v_total_service_charge NUMBER;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Manufacturer code  Manufacturer name Total Service Charge');
    dbms_output.put_line('--------------------------------------------------------');
        OPEN manufacturer_cursor;
        LOOP
            FETCH manufacturer_cursor INTO v_manufacturer_code, v_manufacturer_name, v_total_service_charge;
            EXIT WHEN manufacturer_cursor%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(v_manufacturer_code || ' ' || v_manufacturer_name || ' ' || v_total_service_charge);
        END LOOP;
        CLOSE manufacturer_cursor;
    END display_manufacturer;
    /
    SET SERVEROUTPUT ON;

    BEGIN
        display_manufacturer;
    END;
    /

3. Write a PL/SQL simple procedure named display_mechanics to display the names of all mechanics and number of mechanics available in the system. Display the mechanic names in descending order.

Hint: Use cursor

Use the below skeleton:
Procedure name: display_mechanics

Note:
Do not change the procedure name
Do not change the argument count and order
Do not change the output text. Header and --- are given in the template itself.

Instructions:

1. Create the procedure successfully
2. Once the procedure is created, check the functionality of the procedure using different anonymous block call.
3. DO NOT submit the anonymous block. Submit only the CREATE PROCEDURE query.

    CREATE OR REPLACE PROCEDURE display_mechanics IS
        v_mechanic_name Mechanic.name%TYPE;
        v_total_mechanics NUMBER;

        CURSOR mechanics_cursor IS
            SELECT name FROM Mechanic ORDER BY name DESC;

    BEGIN
    dbms_output.put_line('Mechanic Name');
    dbms_output.put_line('-------------');
        SELECT COUNT(*) INTO v_total_mechanics FROM Mechanic;

        OPEN mechanics_cursor;
        LOOP
            FETCH mechanics_cursor INTO v_mechanic_name;
            EXIT WHEN mechanics_cursor%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(v_mechanic_name);
        END LOOP;
        CLOSE mechanics_cursor;

        DBMS_OUTPUT.PUT_LINE('Number of mechanics: ' || v_total_mechanics);
    END display_mechanics;
    /
    SET SERVEROUTPUT ON;

    BEGIN
        display_mechanics;
    END;
    /