Explicit cursors
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there
is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the
information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is
associated with this statement. For INSERT operations, the cursor holds the data that needs to be
inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has
attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has
additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with
the FORALL statement. The following table provides the description of the most used attributes −
%FOUND
Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT
INTO statement returned one or more rows. Otherwise, it returns FALSE.
%NOTFOUND
The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement
affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ISOPEN
Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after
executing its associated SQL statement.
%ROWCOUNT
Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a
SELECT INTO statement.
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example.
Example
he following program will update the table and increase the salary of each customer by 500 and use
the SQL%ROWCOUNT attribute to determine the number of rows affected −
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
When the above code is executed at the SQL prompt, it produces the following result −
6 customers selected
PL/SQL procedure successfully completed.
Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over the context area. An
explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a
SELECT Statement which returns more than one row.
The syntax for creating an explicit cursor is −