11.19. Database Access with SQL

11.19.1. Cursor FOR LOOPS

The cursor FOR LOOP requires use of SQL and a PL/SQL FOR LOOP. This is a simple approach to querying the database. You can construct a cursor for loop using any valid SQL statement.

The syntax is:

FOR your_name IN (SELECT rest of SQL statement LOOP
						your_name.column_name is defined here
END LOOP;

The following block selects all student names and the sum of their parking tickets. The LOOP temporary variable, REC, only exists for the duration of the LOOP. Within the loop we can access columns in the query through the record, REC.

 BEGIN FOR rec IN (SELECT student_name, sum(amount) parking_ticket_total FROM students a, student_vehicles b, parking_tickets c WHERE a.student_id = b.student_id ...

Get Programming Oracle® Triggers and Stored Procedures, Third Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.