PL/SQL User's Guide and Reference

DELETE Statement

Description

The DELETE statement removes entire rows of data from a specified table or view. For a full description of the DELETE statement, see Oracle7 Server SQL Reference.

Syntax

delete_statement ::=

DELETE [FROM] {table_reference | (subquery)} [alias]
   [WHERE {search_condition | CURRENT OF cursor_name}];

table_reference ::=

[schema_name.]{table_name | view_name}[@dblink_name]

Keyword and Parameter Description

table_reference

This specifies a table or view, which must be accessible when you execute the DELETE statement, and for which you must have DELETE privileges.

subquery

This is a select statement that provides a value or set of values to the DELETE statement. The syntax of subquery is like the syntax of select_into_statement defined in "SELECT INTO Statement"

, except that subquery cannot have an INTO clause.

alias

This is another (usually short) name for the referenced table or view and is typically used in the WHERE clause.

WHERE search_condition

This clause 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.

WHERE CURRENT OF cursor_name

This clause 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.

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 SQL cursor and the cursor attributes %NOTFOUND, %FOUND, and %ROWCOUNT let you access useful information about the execution of a DELETE statement.

A DELETE statement might delete one or more rows or no rows. If one or more rows are deleted, you get the following results:

SQL%NOTFOUND yields FALSE

SQL%FOUND yields TRUE

SQL%ROWCOUNT yields the number of rows deleted

If no rows are deleted, you get these results:

SQL%NOTFOUND yields TRUE

SQL%FOUND yields FALSE

SQL%ROWCOUNT yields 0

Example

The following statement deletes from the bonus table all employees whose sales were below quota:

DELETE FROM bonus WHERE sales_amt < quota;