This is an example of a cursor with parameters. The open statement passes parameter values to the cursor that will be used in the query when it is executed. For each parameter you define in the cursor there must be an actual parameter in the OPEN statement to correspond to it. The select statement used with a parameter does not have the into clause. In the code below the SET SERVEROUTPUT ON/OFF is used so that the dbms line can be used to convey what is happening. The program has 5 variables to hold data. There is also a cursor which takes a driveno and drivename from the drive table. The order is by driveno. The second cursor is the donation cursor which is set up with the parameter v_drive_no which is defined as a NUMBER. The select is taking the contamt from the donation table where the v_drive_no which has been converted to character with driveno which was defined as character.
In the loop the drive cursor was opened. Then the loop is entered which fetches from the cursor. It then goes through the open/close IF described in the previous example. Next the total accumulator is initialized to 0.
Now the donation_cursor is opened and with the open the numeric conversion of the
parameter v_current_drive_no (which was gotton from the fetch to the drive_cursor ) is
passed to the donation_cursor where it has the name v_drive_no. We now add to the total
accumulator and display the dbms line. At the end of the inner loop, the total is written
to a new table, the donation_cursor is closed and control returns to the outer cursor which
gets the next driveno and drivename. More detailed analysis of the logic is included with
the original version.
SET SERVEROUTPUT ON
DECLARE
v_current_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(v_drive_no NUMBER) IS
SELECT contamt FROM donation
WHERE TO_CHAR(v_drive_no) = driveno
ORDER BY driveno;
BEGIN
OPEN drive_cursor;
LOOP
FETCH drive_cursor INTO v_current_drive_no, v_drive_name;
EXIT WHEN drive_cursor%NOTFOUND;
IF donation_cursor%ISOPEN THEN
CLOSE donation_cursor;
END IF;
v_tot_contamt := 0;
OPEN donation_cursor (TO_NUMBER(v_current_drive_no));
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_current_drive_no, v_drive_name, v_tot_contamt);
CLOSE donation_cursor;
END LOOP;
CLOSE drive_cursor;
END;
/
SET SERVEROUTPUT OFF
SQL> @ cursor7a2 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 0The next version of this program uses the WHILE loop instead of the LOOP with the exit. The logic here has the initializing fetch outside the loop and the fetch that handles all other records from the cursor at the bottom of the loop, right before the END LOOP.
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;
FETCH drive_cursor INTO v_drive_no, v_drive_name;
WHILE drive_cursor%FOUND LOOP
IF donation_cursor%ISOPEN THEN
CLOSE donation_cursor;
END IF;
OPEN donation_cursor;
v_tot_contamt := 0;
FETCH donation_cursor INTO v_contamt;
WHILE donation_cursor%FOUND LOOP
v_tot_contamt := v_tot_contamt + v_contamt;
dbms_output.put_line('The current amount is: '||v_tot_contamt);
FETCH donation_cursor INTO v_contamt;
END LOOP;
INSERT into cont_info
VALUES(v_drive_no, v_drive_name, v_tot_contamt);
CLOSE donation_cursor;
FETCH drive_cursor INTO v_drive_no, v_drive_name;
END LOOP;
CLOSE drive_cursor;
END;
/
SET SERVEROUTPUT OFF
SQL> @ cursor7b 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