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