What is the Cursors?
A cursor is a pointer to a private SQL memory area allocated (in PGA memory) for processing an SQL statement. The cursor is used to process one by one the records (rows of tables) returned by the SQL statement in question.
Two types of cursors can be distinguished:
Implicit Cursor: When a user issues an SQL command, Oracle generates a cursor for processing that command. This cursor created and managed by Oracle is called an implicit cursor.
Explicit cursor: if you want to manage an SQL command within your PL / SQL code, you can explicitly create a cursor to process the rows returned by the command one by one.
Here is an example :
DECLARED
name emp.ename% TYPE;
salary emp.sal% TYPE;
CURSOR C1 IS SELECT ename, NVL (sal, 0) FROM emp;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO name, salary;
EXIT WHEN C1% NOTFOUND;
DBMS_OUTPUT.PUT_LINE (name || 'earns' || salary || 'dollars');
END LOOP;
CLOSE C1;
END;
Explanations:
CURSOR C1: used to declare a cursor with the name C1
name emp.ename% TYPE means that the variable name is of the same type as the ename column of the emp table (Another predefined type% ROWTYPE can be used to declare a record variable of the same type as the record of a table)
Open C1: open the cursor
Fetch: fetch the current record and load it into the name and salary variables and then advance the pointer to the next record
C1% NOTFOUND: returns TRUE if the cursor points beyond the last record. Other attributes of the cursor can be used:
% FOUND,
% ROWCOUNT which returns the number of rows returned by the SQL order of the cursor,
% ISOPEN which returns true if the cursor is open.
Close C1: used to close the cursor and free the resources.
For advance topics of Oracle DBA tutorials and tips. Stay connected with our following blogs. Dba tips and Dbametrix.
