DELETE Statement

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

Syntax

Text description of the illustration delete_statement.gif

Keyword and Parameter Description

alias

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

BULK COLLECT

This clause instructs the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. The SQL engine bulk-binds all collections referenced in the RETURNING INTO list. The corresponding columns must store scalar (not composite) values. For more information, see "Reducing Loop Overhead for Collections with Bulk Binds".

returning_clause

This clause lets you return values from the deleted rows, thereby eliminating the need to SELECT the rows beforehand. You can retrieve the column values into variables and/or host variables, or into collections and/or host arrays. However, you cannot use the RETURNING clause for remote or parallel deletes.

subquery

This is a SELECT statement that provides a set of rows for processing. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement".

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.

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.

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.

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.

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.

Examples

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

DELETE FROM bonus WHERE sales_amt < quota;

The following statement returns two column values from a deleted row into local variables:

DECLARE
   my_empno emp.empno%TYPE;
   my_ename emp.ename%TYPE;
   my_job   emp.job%TYPE;
BEGIN
   ...
   DELETE FROM emp WHERE empno = my_empno
      RETURNING ename, job INTO my_ename, my_job;
END;

You can combine the BULK COLLECT clause with a FORALL statement, in which case, the SQL engine bulk-binds column values incrementally. In the following example, if collection depts has 3 elements, each of which causes 5 rows to be deleted, then collection enums has 15 elements when the statement completes:

FORALL j IN depts.FIRST..depts.LAST
   DELETE FROM emp WHERE deptno = depts(j)
      RETURNING empno BULK COLLECT INTO enums;

The column values returned by each execution are added to the values returned previously.

Related Topics

FETCH Statement, SELECT INTO Statement