Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page