Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
|
|
View PDF |
The DELETE
statement removes entire rows of data from a specified table or view. For a full description of the DELETE
statement, see Oracle Database SQL Reference.
Syntax
delete ::=
table_reference ::=
Keyword and Parameter Description
alias
Another (usually short) name for the referenced table or view. Typically referred to later in the WHERE
clause.
Returns columns from the deleted rows into PL/SQL collections, as specified by the RETURNING
INTO
list. The corresponding columns must store scalar (not composite) values. For more information, see "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL".
returning_clause
Returns values from the deleted rows, eliminating the need to SELECT
the rows first. You can retrieve the column values into individual variables or into collections. You cannot use the RETURNING
clause for remote or parallel deletes. If the statement does not affect any rows, the values of the variables specified in the RETURNING
clause are undefined. See "RETURNING INTO Clause".
subquery
A SELECT
statement that provides a set of rows for processing. Its syntax is like the select_into_statement
without the INTO
clause. See "SELECT INTO Statement".
table_reference
A table or view, which must be accessible when you execute the DELETE
statement, and for which you must have DELETE
privileges.
TABLE (subquery2)
The operand of TABLE
is a SELECT
statement that returns a single column value, which must be a nested table. Operator TABLE
informs Oracle that the value is a collection, not a scalar value.
Refers to the latest row processed by the FETCH
statement associated with the cursor identified by cursor_name
. The cursor must be FOR
UPDATE
and must be open and positioned on a row. If the cursor is not open, the CURRENT
OF
clause causes an error.
If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND
.
WHERE search_condition
Conditionally chooses rows to be deleted from the referenced table or view. Only rows that meet the search condition are deleted. If you omit the WHERE
clause, all rows in the table or view are deleted.
Usage Notes
You can use the DELETE
WHERE
CURRENT
OF
statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor FOR
loop), provided the associated query is FOR
UPDATE
. This statement deletes the current row; that is, the one just fetched.
The implicit cursor SQL
and the cursor attributes %NOTFOUND
, %FOUND
, and %ROWCOUNT
let you access useful information about the execution of a DELETE
statement.
For examples, see the following:
Related Topics