Cursor is a temporary memory area (context area) where Oracle executes SQL statements. Oracle associates every SELECT statement with a cursor to hold the query information in this context area.
Types of Cursor
There are two types of cursors - implicit and explicit cursor.
Implicit cursor: Oracle automatically (implicit) controls or processes the information of SQL statement executed. In this process, the user is unaware of implicit cursor. Oracle automatically performs the OPEN, FETCH, and CLOSE operations.
Explicit cursor: Explicit cursor is used for the query that returns more than one row of data. These cursors are explicitly declared in the DECLARE section of the PL/SQL block. This declaration allows to sequentially process each row of data as the cursor returns it. In explicit cursor DECLARE,OPEN,FETCH,and CLOSE operations are done by the programmer.
The process of working with an explicit cursor:
- Declare: The cursor is initialised into temporary memory area.
- Open: The cursor is opened which is declared, and the temporary memory area is allotted.
- Fetch: Cursor which is declared and opened can now retrieve rows from data.
- Close: The
CLOSEstatement disables the cursor, and releases the temporary memory area.
The status of the cursor for each of these attributes are defined in the below table.
|%FOUND||The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT... INTO statement return at least one row.||SQL%FOUND|
|The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT... INTO statement do not return a row.|
|%NOTFOUND||The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT... INTO statement return at least one row.||SQL%NOTFOUND|
|The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT... INTO statement does not return a row.|
|%ROWCOUNT||Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT||SQL%ROWCOUNT|
For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
In the above PL/SQL Block, the salaries of all the employees in the 'employee' table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in 'employee' table.