|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
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.
Keyword and Parameter Description
Another (usually short) name for the referenced table or view. Typically referred to later in the
Returns columns from the deleted rows into PL/SQL collections, as specified by the
INTO list. The corresponding columns must store scalar (not composite) values. For more information, see "Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)".
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.
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".
A table or view, which must be accessible when you execute the
DELETE statement, and for which you must have
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
UPDATE and must be open and positioned on a row. If the cursor is not open, the
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
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
OF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor
FOR loop), provided the associated query is
UPDATE. This statement deletes the current row; that is, the one just fetched.
The implicit cursor
SQL and the cursor attributes
%ROWCOUNT let you access useful information about the execution of a
The following statement deletes the rows that match a condition:
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
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.