Functioning of CURSOR FOR LOOP with example

Explain the functioning of CURSOR FOR LOOP with example.

Lets have a look at what Cursors are before going to the Cursor FOR loop.

A Cursor is a PL/SQL construct and accesses the stored information in a named work area.

There are 2 types of cursors:

Implicit: queries that return only one row
Explicit: can be declared by us for the queries that return more than one row.

Example:
DECLARE
     CURSOR cursor_1 IS
     SELECT roll_no, student_name FROM student WHERE grade = 4;

A PL/SQL program opens a cursor, processes rows returned by a query, then closes the cursor.

This can be done with the help of:
     OPEN, FETCH, and CLOSE statements.

Cursor FOR Loops
Instead of using OPEN, FETCH, and CLOSE statements, coding can be simplified by using FOR loops.

A cursor FOR loop opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed.

In the example below, the cursor FOR loop implicitly declares stud_record as a record:
DECLARE
CURSOR cursor_1 IS
SELECT student_name, birthdate FROM student;
...
BEGIN
FOR stud_record IN cuesor_1 LOOP
...
...
END LOOP;
Define Simple/Explicit , Parametric and Internal/Implicit cursor
Implicit: queries that return only one row. Explicit: can be declared by us for the queries that return more than one row..
What is an SQL *FORMS?
Oracle Forms is part of Oracle's Internet Developer Suite. Its earlier versions were called SQL*Forms....
How do you control the constraints in forms?
You can suppress or customize error messages in Forms. This can be done by setting the message level of the system variable SYSTEM.MESSAGE_LEVEL....
Post your comment