Cursor Example:
Before doing this example, I created a table called cont_info to receive the results.
SQL CODE:
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.
PL/SQL CODE:
SET SERVEROUTPUT ON
DECLARE
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;
BEGIN
OPEN drive_cursor;
LOOP
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;
LOOP
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;
END;
/
SET SERVEROUTPUT OFF
SQL CODE:
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;
DRI DRIVENAME CONTAMT
--- --------------- ---------
100 Kids Shelter 105
200 Animal Home 75
300 Health Aid 20
400 Half Way 0
SQL> SELECT * FROM donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
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.
Drive_cursor
100 Kids Shelter
v_drive_no
100
v_drive_name
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)
25
20
10
50
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.
Cont_info
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.
Drive_cursor
200 Animal Home
v_drive_no
200
v_drive_name
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)
40
35
The fetch will get the donation of 40 and process it and processing will continue as
described above.