Week9 - Stored Procedures / Design

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

1. Create a procedure named 'insertConnection' which has connection_name as an input parameter with varchar2 as its datatype. This procedure should take the count of the existing table records(electricity_connection_type) and add 1 with that to generate the new electricity_connection_type id.The newly generated id along with the connection_name should be inserted into the electricity_connection_type table.

Hints:
Procedure name : insertConnection
Parameters : connection_name with type varchar2

    CREATE OR REPLACE PROCEDURE insertConnection(connection_name IN VARCHAR2) IS
        new_id NUMBER;
    BEGIN
        SELECT COUNT(*) + 1 INTO new_id FROM electricity_connection_type;

        INSERT INTO electricity_connection_type(id, connection_name)
        VALUES (new_id, connection_name);

        COMMIT;
    END insertConnection;
    /

2. Create a procedure named 'insertMeter' which takes 2 input parameters namely, meter_number is type of varchar2 and building_id is type of int. This procedure will take the count of the existing table records(meter) and add 1 with that to generate the new meter id.The newly generated id along with the meter_number and building_id should be inserted into the meter table.

Hints:
Procedure name : insertMeter
Parameters : meter_number(varchar2) ,building_id(int)

    CREATE OR REPLACE PROCEDURE insertMeter(
        meter_number IN VARCHAR2,
        building_id IN INT
    ) IS
        new_id NUMBER;
    BEGIN
        SELECT COUNT(*) + 1 INTO new_id FROM meter;

        INSERT INTO meter(id, meter_number, building_id)
        VALUES (new_id, meter_number, building_id);

        COMMIT;

    END insertMeter;
    /

3. Create a procedure named 'findTotalUnits' which has meterNumber as an input parameter with varchar as its datatype and status as an output parameter with integer as its datatype. This procedure should display the sum of total_units of the electricity_reading for the meterNumber passed as parameter.

Hints:
Procedure name : findTotalUnits
Parameters : meterNumber(input parameter),status(output parameter).

    CREATE OR REPLACE PROCEDURE findTotalUnits(
        meterNumber IN VARCHAR2,
        status OUT INTEGER
    ) AS
        totalUnits INTEGER;
    BEGIN
        SELECT SUM(total_units) INTO totalUnits
        FROM electricity_reading
        WHERE meter_id = (
          SELECT id
          FROM meter
          WHERE meter_number=meterNumber
        );

        status := totalUnits;
    END findTotalUnits;
    /

4. Create a procedure named 'findConnection' which which has contactNumber as an input parameter with varchar as its data type and and connection as an output parameter with varchar as its datatype. This procedure should find the name of the connection for the contactNumber passed as parameter.

Hints:
Procedure name :findConnection
Parameters : contactNumber(varchar),connection(varchar)

    CREATE OR REPLACE PROCEDURE findConnection(
        contactNumber IN VARCHAR2,
        connection OUT VARCHAR2
    ) AS
    BEGIN
        SELECT connection_name into connection
        FROM electricity_connection_type
        WHERE id = (
          SELECT connection_type_id
          FROM building_type
          WHERE id = (
            SELECT building_type_id
            FROM building
            WHERE contact_number=contactNumber
            )
          );
    END;
    /

5. Create a procedure named 'getBillLevel' which takes 1 input parameter namely, bill_id int and 1 output parameter namely, level varchar2. This procedure should determine the level of the bill as either PLATINUM or GOLD based on the total units consumed for the month. This procedure should set the level as GOLD if the total units for the bill is less than 10000 units and the level is PLATINUM if the total units is greater than or equal to 10000 units.

Hints:
Procedure name :getBillLevel
Parameters : bill_id is type of int,level is type of varchar2

    CREATE OR REPLACE PROCEDURE getBillLevel(
        bill_id IN INT,
        level OUT VARCHAR2
    ) AS
        totalUnits INT;
    BEGIN
        SELECT SUM(total_units) INTO totalUnits
        FROM bill
        WHERE id = bill_id;

        IF totalUnits < 10000 THEN
            level := 'GOLD';
        ELSE
            level := 'PLATINUM';
        END IF;
    END;
    /