Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

PL/SQL

Learn step by step



Cursors

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 CLOSE statement 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.

Attributes
Return Value Example
%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:

DECLARE var_rows number(5);

BEGIN

UPDATE employee

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');

END IF;

END;

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.



Related Videos