The DELETE Statement
You can use the DELETE statement to remove one, some, or all the rows in a table. Here is the basic syntax :
DELETE FROMtable[WHEREWHERE_clause];
The WHERE clause is optional in a DELETE statement. If you do not supply one, all rows in the table are deleted. Here are some examples of DELETEs:
Delete all the books from the books table:
DELETE FROM books;
Delete all the books from the books table that were published prior to a certain date and return the number of rows deleted:
CREATE OR REPLACE PROCEDURE remove_books ( date_in IN DATE, removal_count_out OUT PLS_INTEGER) IS BEGIN DELETE FROM books WHERE date_published < date_in; removal_count_out := SQL%ROWCOUNT; END;
Of course, all of these DML statements can become qualitatively more complex as you deal with real-world entities. You can, for example, update multiple columns with the contents of a subquery. As of Oracle9i Database, you can replace a table name with a table function that returns a result set upon which the DML statement acts. See Chapter 3 for details.
Oracle provides several cursor attributes for the implicit cursors “behind” your DML statements, described in the next section.