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.
delete ::=
Description of the illustration delete_statement.gif
table_reference ::=
Description of the illustration table_reference.gif
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.
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:
Example
6-1, "Data Manipulation With PL/SQL"
Example
6-5, "Using CURRVAL and NEXTVAL"
Example
6-7, "Using SQL%FOUND"
Example
6-8, "Using SQL%ROWCOUNT"
Example
6-29, "Stored Procedure to Open a Ref Cursor"
Example
6-30, "Stored Procedure to Open Ref Cursors with Different Queries"
Example
11-2, "Issuing DELETE Statements in a Loop"
Example
11-16, "Using FORALL With BULK COLLECT"
Related Topics
"FETCH
Statement"
"INSERT
Statement"
"SELECT
INTO Statement"
"UPDATE
Statement"