Cursor Example:

Before doing this example, I created a table called cont_info to receive the results.


SQL> DESC cont_info;
 Name                            Null?    Type
 ------------------------------- -------- ----
 DRIVENO                                  VARCHAR2(3)
 DRIVENAME                                VARCHAR2(15)
 CONTAMT                                  NUMBER(6,2)

SQL> edit cursor6a
The code that I have written will take the first drive from the drive table, go to the donation table and accumulate all of the contributions to that drive and write the results as a row/record on the cont_info table. It will then go back and read the next record/row from the drive table and go to the donation table and accumulate all of the contributions for that drive. Processing will stop when all of the drives from the drive table have been processed.

In the PL/SQL code below I first declared a series of variables. The first three, will hold the information taken in from the drive and donation tables. The v_tot_contamt will hold an accumulated total that will eventually be written to the cont_info table. Since I am using two tables, I have two cursors. The first cursor, the drive_cursor has a select on the driveno and the drivename from the drive table. Notice that the order is set to driveno. The second cursor is on the donation table and selects contamt where the driveno is equal to the driveno selected from the first cursor. The comparison in the where will work because the actual retrieval is done with the fetchand because the drive_cursor has already been opened. Again, the order is on driveno.

The BEGIN opens the drive_cursor only. It then goes into a loop. The fetch is the first thing in the loop. The fetch uses the drive_cursor to take in the things named in the drive_cursor select (the driveno and the drivename) and put them into the variables v_drive_no and v_drive_name. If the fetch was unsuccessful then the loop will be exected. If the fetch is successful, the logic drops to the IF statement and checks to see if the donation_cursor is open (it shouldn't be), if it is, it gets closed and the IF is ended. Then the logic drops down and opens the donation_cursor and establishes the v_tot_contamt as 0. Note that this is critical. When I first wrote this code I forgot to do the initialization which means that v_tot_contamt got treated as null and the processing involving it did not happen.

We then enter an inner loop where we use the donation_cursor in the fetch to get the contamt and store it in the variable v_contamt. The fetch uses the cursor select which says compare what is already in v_drive_no (remember it got put there with the fetch in the outer loop that used the drive_cursor) to driveno. If there is not a match, in other words if there is no longer a record/row in the donation table that matches the driveno that was taken from the drive table and stored in v_drive_no, then the logic will exit the loop. If there is a match, then the contamt that came in with the cursor fetch and was stored in v_contamt will be added to the total which is stored in v_tot_contamt. To show where v_tot_contamt was as we processed, I used the dbms_output line. This is here for demonstration/debugging purposes and would be removed if I was really executing the block professionally.

This ends the inner loop. I now have the total that I want to put in the cont_info table and so I write a record to the table using the insert with the driveno, drivename and the total contribution amount. I then close the donation table and loop back up to the beginning of the outer loop where I will fetch the next drive from the drive table and resume processing. This means that I will again open the donation_cursor and this time it will be filled with donations that match the second fetch from the drive_cursor. When all of the drives have been processed, the outer loop ends and the drive table is closed. Again note that set serveroutput on/off and the dbms_output are in there for demonstration purposes only.


  v_drive_no      drive.driveno%TYPE;
  v_drive_name    drive.drivename%TYPE;
  v_contamt       donation.contamt%TYPE;
  v_tot_contamt   cont_info.contamt%TYPE;
  CURSOR drive_cursor IS
     SELECT driveno, drivename FROM drive
     ORDER BY driveno;
  CURSOR donation_cursor IS
     SELECT contamt FROM donation 
     WHERE v_drive_no = driveno
     ORDER BY driveno;
     OPEN drive_cursor;
       FETCH drive_cursor INTO v_drive_no, v_drive_name;
       EXIT WHEN drive_cursor%NOTFOUND;
       IF donation_cursor%ISOPEN THEN
         CLOSE donation_cursor;
       END IF;
       OPEN donation_cursor;
       v_tot_contamt := 0;
         FETCH donation_cursor INTO v_contamt;
         EXIT WHEN donation_cursor%NOTFOUND;
         v_tot_contamt := v_tot_contamt + v_contamt;
         dbms_output.put_line('The current amount is: '||v_tot_contamt);
       END LOOP;
       INSERT into cont_info 
          VALUES(v_drive_no, v_drive_name, v_tot_contamt);
       CLOSE donation_cursor;
     END LOOP;
     CLOSE drive_cursor;


SQL> @ cursor6a
The current amount is: 25
The current amount is: 45
The current amount is: 55
The current amount is: 105
The current amount is: 40
The current amount is: 75
The current amount is: 10
The current amount is: 20

PL/SQL procedure successfully completed.

Input truncated to 20 characters
SQL> SELECT * FROM cont_info;

--- --------------- ---------
100 Kids Shelter          105
200 Animal Home            75
300 Health Aid             20
400 Half Way                0

SQL> SELECT * FROM donation;

----- --- --------- ---------
11111 100 07-JAN-99        25
12121 200 23-FEB-99        40
23456 100 03-MAR-99        20
33333 300 10-MAR-99        10
22222 100 14-MAR-99        10
12121 100 04-JUN-99        50
11111 200 12-JUN-99        35
23456 300 14-JUN-99        10

8 rows selected.
Now lets look at a conceptual view of the processing as it is executed. There are two cursors: the drive_cursor and the donation_cursor. The drive_cursor is controlled in the outer loop and the donation_cursor is controlled in the inner loop. The first fetch from the drive cursor gets 100 Kids Shelter which are stored in v_drive_no and v_drive_name.

100 Kids Shelter
Kids Shelter

Then the donation cursor is opened and the v_tot_contamt is set to 0. The donation cursor is selecting the rows/records that match the v_drive_no so the following records will move into the donation_cursor.

Donation_cursor (all of the records will match to the 100 in v_drive_no)

The fetch from the donation_cursor will first process the 25 and add the amount to v_tot_contamt which will now contain 25. The 25 will be printed using the dbms line. The loop will continue and the fetch will now get 20 and add it to v_tot_contamt making it 45 (this will be printed using the dbms line). The loop will continue and 10 will be processed making v_tot_contamt 55 (this will be printed using the dbms line). The loop will continue and 50 will be processed making v_tot_contamt 105 (this will be printed). The next pass through the loop has nothing to Fetch since the four records in the donation_cursor have been processed. Therefore the EXIT will be taken since %NOTFOUND is true. At this point a record/row will be written to cont_info containing the drive number, the drivename and the contents of v_tot_contamt.

100 Kids Shelter 105

Then the donation_cursor will be closed and control will be returned to the outer loop. The first fetch in the outer loop fetches the next record/row from the drive_cursor and puts the information into v_drive_no and v_drive_name.

200 Animal Home
Animal Home

After the if closes the donation_cursor if it is open, the donation_cursor is opened and the v_tot_contamt is set to 0. The information in the donation_cursor will match the 200 in the v_drive_no so you now have the following:

Donation_cursor (all of the records will match to the 200 in v_drive_no)

The fetch will get the donation of 40 and process it and processing will continue as described above.