How can I get the total of Fruits and their corresponding color + calories from 2 tables using only 2 cursors? PL/SQL

I have 2 tables:

FruitColor (Fruit Color)
  Yellow
  Orange
  Red
  Green

Fruit (Fruit, Calories, Fruit Color)
Apple  100    red
Banana 150    yellow
Orange 125    orange
Mango  200    orange
Pineapple 250 yellow
Grapefruit 90 orange
Durian 300    green

I need to be able to match the colors and add the amount of fruits in that color category, along with the total number of calories... So the output should look something like...

The Total Number of Fruits that are orange is 3, with 415 calories.

This has to be done in 2 cursors. I can't seem to figure out how to have the grab one row, add it to a variable, and continue to loop through the table until it finds another row of the same color, add it to that same variable until it can't find another of that same type, then it starts over and looks to add the sum of another fruit and its calories.

Thank you so much for any help. My current PL/SQL code is basically worthless as I haven't gotten anywhere in 2 days...

Solution

Here are a couple examples that might fit with the goals in your assignment, depending on what is allowed there. Both will use the same data, so that will be set up first:

Create the tables and add the data:

CREATE TABLE FRUIT_COLOR(
  COLOR_NAME VARCHAR2(32) NOT NULL PRIMARY KEY
);

INSERT INTO FRUIT_COLOR VALUES ('Yellow');
INSERT INTO FRUIT_COLOR VALUES ('Orange');
INSERT INTO FRUIT_COLOR VALUES ('Red');
INSERT INTO FRUIT_COLOR VALUES ('Green');

CREATE TABLE FRUIT(
  FRUIT_NAME VARCHAR2(64) NOT NULL PRIMARY KEY,
  CALORIES NUMBER,
  COLOR_NAME VARCHAR2(32) NOT NULL REFERENCES FRUIT_COLOR(COLOR_NAME)
);

INSERT INTO FRUIT VALUES ('Apple',100,'Red');
INSERT INTO FRUIT VALUES ('Banana',150,'Yellow');
INSERT INTO FRUIT VALUES ('Orange',125,'Orange');
INSERT INTO FRUIT VALUES ('Mango',200,'Orange');

INSERT INTO FRUIT VALUES ('Pineapple',200,'Yellow');
INSERT INTO FRUIT VALUES ('Grapefruit',90,'Orange');
INSERT INTO FRUIT VALUES ('Durian',300,'Green');

Then create the pl/sql blocks. If your assignment is only restricting you to defining 2 cursors (but you are allowed to fetch/process data more than once), then nested-loops may be all that is required.

In this first example, we'll loop through each color one-by-one in an implicit cursor, and for each one, we'll loop through each fruit one-by-one in another implicit cursor, recording how many fruits and calories have accumulated as we go. This isn't efficient, but it is straightforward.

But this may be cheating on the only two cursor rule for the assignment, as the inner implicit cursor needs to be exhausted multiple times.

DECLARE
  V_CALORIES_COUNT NUMBER;
  V_COLOR_COUNT NUMBER;
  BEGIN

  <<COLOR_LOOP>>
  FOR COLOR_POINTER IN (SELECT COLOR_NAME FROM FRUIT_COLOR ORDER BY COLOR_NAME ASC)
    LOOP
  V_CALORIES_COUNT := 0;
    V_COLOR_COUNT := 0;

    <<FRUIT_LOOP>>
    FOR FRUIT_POINTER IN (SELECT CALORIES, COLOR_NAME FROM FRUIT)
      LOOP
      IF FRUIT_POINTER.COLOR_NAME = COLOR_POINTER.COLOR_NAME
        THEN
        V_CALORIES_COUNT := V_CALORIES_COUNT + FRUIT_POINTER.CALORIES;
        V_COLOR_COUNT := V_COLOR_COUNT + 1;
      END IF;
    END LOOP FRUIT_LOOP;
    DBMS_OUTPUT.PUT_LINE(UTL_LMS.FORMAT_MESSAGE('The Total Number of Fruits that are %s is %s, with %s calories. ',COLOR_POINTER.COLOR_NAME,TO_CHAR(V_COLOR_COUNT),TO_CHAR(V_CALORIES_COUNT)));
  END LOOP COLOR_LOOP;

END;
/

Then run it:

The Total Number of Fruits that are Green is 1, with 300 calories.
The Total Number of Fruits that are Orange is 3, with 415 calories.
The Total Number of Fruits that are Red is 1, with 100 calories.
The Total Number of Fruits that are Yellow is 2, with 350 calories.

PL/SQL procedure successfully completed.

If you really want to only iterate through each cursor once, one way to do this is to accumulate the counts into a keyed data-structure(s) as you go. In this example, I'll use (two) maps with colors as keys and numbers as values that accumulate as the cursor iterates.

Then at the end, the contents of the maps are printed out. (Maybe this is cheating too, as it creates additional data structures)

DECLARE
  TYPE COLOR_NAME_COUNT IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  V_FRUIT_COUNT COLOR_NAME_COUNT;
  V_CALORIE_COUNT COLOR_NAME_COUNT;
  V_COLOR_KEY VARCHAR2(64);
BEGIN
  FOR COLOR_POINTER IN (SELECT COLOR_NAME FROM FRUIT_COLOR ORDER BY COLOR_NAME ASC)
  LOOP
    V_FRUIT_COUNT(COLOR_POINTER.COLOR_NAME) := 0;
    V_CALORIE_COUNT(COLOR_POINTER.COLOR_NAME) := 0;
  END LOOP;

  FOR FRUIT_POINTER IN (SELECT CALORIES, COLOR_NAME FROM FRUIT ORDER BY COLOR_NAME ASC)
  LOOP
    V_FRUIT_COUNT(FRUIT_POINTER.COLOR_NAME) := V_FRUIT_COUNT(FRUIT_POINTER.COLOR_NAME) + 1;
    V_CALORIE_COUNT(FRUIT_POINTER.COLOR_NAME) := V_CALORIE_COUNT(FRUIT_POINTER.COLOR_NAME) + FRUIT_POINTER.CALORIES;
  END LOOP;

  V_COLOR_KEY := V_FRUIT_COUNT.FIRST;
  LOOP
    EXIT WHEN V_COLOR_KEY IS NULL;
    DBMS_OUTPUT.PUT_LINE(UTL_LMS.FORMAT_MESSAGE('The Total Number of Fruits that are %s is %s, with %s calories. ',V_COLOR_KEY,TO_CHAR(V_FRUIT_COUNT(V_COLOR_KEY)),TO_CHAR(V_CALORIE_COUNT(V_COLOR_KEY))));
    V_COLOR_KEY := V_FRUIT_COUNT.NEXT(V_COLOR_KEY);
  END LOOP;

END;
/

And run it:

The Total Number of Fruits that are Green is 1, with 300 calories.
The Total Number of Fruits that are Orange is 3, with 415 calories.
The Total Number of Fruits that are Red is 1, with 100 calories.
The Total Number of Fruits that are Yellow is 2, with 350 calories.

PL/SQL procedure successfully completed.