Oracle in a Nutshell

Keywords

sql_statement

INSERT, UPDATE, DELETE, MERGE, or SELECT INTO statement.

RETURNING

Use the RETURNING clause in INSERT, UPDATE, and DELETE statements to obtain data modified by the associated DML statement. This clause allows you to avoid an additional SELECT statement to query the results of the DML statement. For example:

BEGIN
   UPDATE activity SET last_accessed := SYSDATE
   WHERE UID = user_id
   RETURNING last_accessed, cost_center 
   INTO timestamp, chargeback_acct;

attribute

Keyword directly follows last line in SQL statement, without a space. May be:

SQL%ISOPEN

Always returns FALSE for implicit cursors because the cursor is opened implicitly and closed immediately after the statement is executed.

SQL%FOUND

Returns TRUE if one or more rows were inserted, merged, updated, or deleted or if only one row was selected; FALSE if no row was affected.

SQL%NOTFOUND

Returns NULL before the statement; TRUE if no row was selected, merged, updated, inserted, or deleted; FALSE if one or more rows were affected.

SQL%ROWCOUNT

Returns the number of rows affected by the cursor.

SQL%BULK_ROWCOUNT

Pseudo associative array containing number of records modified by FORALL statement (see the “FORALL” entry later in this chapter) for each collection element.

SQL%BULK_EXCEPTIONS

Pseudo associatve array containing number of rows modified by the FORALL statement (see the “FORALL” entry later in this chapter) for each collection element.