|
Oracle cursors - August 28, 2008 at 17:00 PM by Amit Satpute
Explain the functioning of CURSOR FOR LOOP with example.
Answer
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.
e.g.
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.
Answer
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.
|