CODEWITHSUNDEEP
oracle-databaseplsql
Cemre Mengü
Cemre Mengü

Reputation: 18774

Pl/SQL Looping through the values of a cursor

CREATE OR REPLACE Function TotalIncome
   ( name_in IN varchar2 )
   RETURN varchar2
IS
   total_val number(6);

   cursor c1 is
     select monthly_income
     from employees
     where name = name_in;

BEGIN

   total_val := 0;

   FOR employee_rec in c1
   LOOP
      total_val := total_val + employee_rec.monthly_income;
   END LOOP;

   RETURN total_val;

END;

In this example, I am being told that that we are looping through the values of but what I dont understand is if we are looping through the values why is employee_rec.monthly_income necessary ? Also, when I do a FETCH on employee_rec I seem to be getting an error so I guess they are different from each other.

Upvotes: 1

Views: 12765

Answers (2)

DazzaL
DazzaL

Reputation: 21993

you are looping through records in the result set.

in other words:

FOR employee_rec in c1

means open the cursor c1 and perform a fetch on it. For each row found, assign the row record to a record variable called employee_rec.

so to reference monthly_income in that, you have to say employee_rec.monthly_income and not just monthly_income on its own.

when I do a FETCH on employee_rec

employee_rec is not a cursor (its a variable) so you don't fetch from it. In this case the fetch from C1 is handled implicitly by the for loop, so no explicit fetch on your part is required.

Upvotes: 7

Xophmeister
Xophmeister

Reputation: 9219

You are not "looping through the values", you are looping through the records returned by your cursor. In your case, your records only contain one field -- monthly_income -- but in general, a record can contain many fields, which you can use as you see fit in each loop iteration.

Upvotes: 3

Related Questions