RDBMS - Cursor Explicit
Explicit Cursors:
->programmer defined
->used to fetch data from a SELECT query that returns multiple rows
->4 parts / steps of a Cursor:
1. Declare a cursor (associate the cursor with a SELECT query) (memory initialization)
syntax:
CURSOR cursor_name IS SELECT query;
Note: this statement is written within the DECLARE section
2. Open the cursor (runs the SELECT query) (memory allocation)
syntax:
OPEN cursor_name;
Note: this statement is written within the BEGIN section
3. fetch the cursor (retreive data returned by the SELECT query)
syntax:
FETCH cursor_name INTO var1, var2, var3, ... , varn
Note: this statement is written within the BEGIN section and inside a LOOP.
This statement fetches all the rows, row by row
4. Close the cursor (memory deallocation / release)
syntax:
CLOSE cursor_name;
Note: this statement is written within the BEGIN section after the data has been fetched.
------------------------------------------------------------------------------------
SELECT rollno, name, stream FROM stutab;
->write a PL/SQL code block that can fetch the rollno, name and stream of all the students in the stutab table.
(using LOOP)
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*VARIABLE DECLARATION*/
rl number(4);
nm varchar2(25);
str varchar2(3);
/*CURSOR DECLARATION*/
CURSOR cursor_stutab_1 IS SELECT rollno, name, stream FROM stutab;
BEGIN
/*OPEN THE CURSOR*/
OPEN cursor_stutab_1;
/*FETCH ALL THE RECORDS FROM THE CURSOR, RECORD BY RECORD*/
/*LOOP*/
LOOP
FETCH cursor_stutab_1 INTO rl, nm, str;
EXIT WHEN cursor_stutab_1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(rl || ' - ' || nm || ' - ' || str);
END LOOP;
/*CLOSE THE CURSOR*/
CLOSE cursor_stutab_1;
END;
/
=================================================
(using WHILE loop)
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*VARIABLE DECLARATION*/
rl number(4);
nm varchar2(25);
str varchar2(3);
/*CURSOR DECLARATION*/
CURSOR cursor_stutab_1 IS SELECT rollno, name, stream FROM stutab;
BEGIN
/*OPEN THE CURSOR*/
OPEN cursor_stutab_1;
/*FETCH THE 1st RECORD outside the loop*/
FETCH cursor_stutab_1 INTO rl, nm, str;
/*FETCH ALL THE RECORDS FROM THE CURSOR, RECORD BY RECORD*/
/*LOOP*/
WHILE cursor_stutab_1%found LOOP
DBMS_OUTPUT.PUT_LINE(rl || ' - ' || nm || ' - ' || str);
FETCH cursor_stutab_1 INTO rl, nm, str;
END LOOP;
/*CLOSE THE CURSOR*/
CLOSE cursor_stutab_1;
END;
/
---------------------------------------------------------------------
0 Comments