Oracle PL/SQL for DBAs

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 FROM table
     [WHERE WHERE_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.