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