Static or embedded SQL are SQL statements that are written natively into your PL/SQL programs (as opposed to defining them as expressions for execution as dynamic SQL).
An implicit cursor is a session cursor that is constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a
SELECT or DML statement. You cannot control an implicit cursor, but you can get information from its attributes.
Oracle defines a number of attributes of implicit cursors, whose value can be obtained through the
attribute always refers to the most recently run
SELECT or DML statement. If no such statement has run, the value of
An implicit cursor closes after its associated statement runs; however, its attribute values remain available until another
SELECT or DML statement runs.
The most recently run
SELECT or DML statement might be in a different scope (another subprogram call that has now completed, for example). To save an attribute value for later use, assign it to a local variable immediately. Otherwise, other operations, such as subprogram invocations, might change the value of the attribute before you can test it.
SQL%ROWCOUNT returns NULL if no SELECT or DML statement has run. Otherwise, it returns the number of rows returned by a SELECT statement or affected by a DML statement (a PLS_INTEGER).
If a SELECT INTO statement without a BULK COLLECT clause returns multiple rows, PL/SQL raises the predefined exception TOO_MANY_ROWS and SQL%ROWCOUNT returns 1, not the actual number of rows that satisfy the query.
Furthermore, the value of SQL%ROWCOUNT attribute is unrelated to the state of a transaction. Therefore:
- When a transaction rolls back to a savepoint, the value of SQL%ROWCOUNT is not restored to the value it had before the save point.
- When an autonomous transaction ends, SQL%ROWCOUNT is not restored to the original value in the parent transaction.