All the cursors declared by PL/SQL for SQL statements of a PL/SQL block are called Implicit Cursors. A programmer doesn’t need to write Open, Fetch and Close operations to manage an PL/SQL Implicit Cursor. It is internally done by Oracle Engine!!
Handling PL/SQL Implicit Cursors
Whenever any SQL statement is used in the code block an Implicit Cursor is automatically initiated the PL/SQL code , it is assigned system defined name SQL. This name is given to the current or the latest cursor. It means that at a time you can handle only one implicit cursor in PL/SQL code block.
You cannot use the cursor attributes in SQL statements. These are allowed only in PL/SQL code blocks whether anonymous or named.
If the code is created with multiple SQL statements then SQL will display cursor attributes of the latest SQL statement. The cursor attributes can be accessed like SQL%attributename. The following are the cursor attributes for PL/SQL Implicit Cursors.
| Attribute | Return Data Type | Working |
| SQL%ISOPEN | BOOLEAN | ISOPEN for an implicit cursor always returns false. This is because the implicit cursors are automatically created and destroyed. Its OPEN status is never maintained, |
| %FOUND | BOOLEAN | FOUND attribute return TRUE if the latest SQL statement in the PL/SQL block for which this attribute being checked is successfully executed. If it fails to execute this attribute returns False |
| %NOTFOUND | BOOLEAN | NOTFOUND attribute return TRUE if the latest SQL statement in the PL/SQL block for which this attribute being checked is not executed successfully. If it succeeds to execute this attribute returns False |
| %ROWCOUNT | NUMBER | ROWCOUNT attribute returns the count of rows affected by the latest DML Statement in the code block. |
Example
DECLARE
BEGIN
UPDATE employees set salary = salary+salary*2 where department_id=20;
if SQL%FOUND THEN
dbms_output.put_line('Query Executed');
ELSE
dbms_output.put_line('Query Failed');
END IF;
dbms_output.put_line('rows affected = ' ||SQL%ROWCOUNT);
UPDATE employees set salary = salary+salary*1 where department_id=50;
if SQL%FOUND THEN
dbms_output.put_line('Query Executed');
ELSE
dbms_output.put_line('Query Failed');
END IF;
dbms_output.put_line('rows affected= ' ||SQL%ROWCOUNT);
UPDATE employees set salary = salary+salary*3 where department_id=70;
if SQL%FOUND THEN
dbms_output.put_line('Query Executed');
ELSE
dbms_output.put_line('Query Failed');
END IF;
dbms_output.put_line('rows affected= ' ||SQL%ROWCOUNT);
UPDATE employees set salary = salary+salary*3 where department_id=200;
if SQL%FOUND THEN
dbms_output.put_line('Query Executed');
ELSE
dbms_output.put_line('Query Failed');
END IF;
dbms_output.put_line('rows affected= ' ||SQL%ROWCOUNT);
END;
Output
The above PL/SQL block contains three DML statements. The first UPDATE statement updates salaries of employees with department_id 50. IF-THEN-ELSE statement checks SQL%FOUND for the command’s successful execution. The message ‘Query Executed’ is displayed. There are two employees with department_id 50 so, after update the SQL%ROWCOUNT returns 2.
The second UPDATE statement updates salaries of employees with department_id 70. When SQL is used after this statement it refers to the implicit cursor automatically created for second update statement. IF-THEN-ELSE statement checks SQL%FOUND for second update command. It is true so, the message ‘Query Executed’ is displayed. There are 45 employees with department_id 70. After update the SQL%ROWCOUNT returns 45 indicating that 45 records are affected by this DML.
The third UPDATE statement updates salaries of employees with department_id 200. IF-THEN-ELSE statements checks SQL%FOUND for third update command. It is false, since no employees are having department_ID 200. The message ‘Query Failed’ is displayed. Since update has failed so SQL%ROWCOUNT returns 0 indicating that no records are affected by this DML statement.
Be First to Comment