Implicit Cursors

Implicit Cursors

When you execute DML statements like

DELETE

INSERT

UPDATE

SELECT

implicit statements are created to process these statements.

Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are

%FOUND
%NOTFOUND
%ROWCOUNT
%ISOPEN.

For example

When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected. When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.

Attributes

%FOUN

Return Value

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

Example

SQL%FOUND

Attributes

%NOTFOUND

Return Value

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.

Example

SQL%NOTFOUND

Attributes

%ROWCOUNT

Return Value

Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT SQL%ROWCOUNT

Example

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.