 |
|
DML Command Syntax and
Examples
Oracle Tips by
Burleson
|
For those of you who aren’t familiar with the basic format of DML
statements in Oracle, I’ve provided this reference. This reference is
by no means a bible of every possible permutation of a statement, but
it does cover each statement quite thoroughly. Chances are, the
material presented here will be applicable in more than 99 percent of
the DML statements you will write. Specifically, this appendix covers
the DELETE, INSERT, SELECT, and UPDATE
statements.
The DELETE Statement
The DELETE statement is used to remove one or more rows from
a table. The basic format of a DELETE statement is as follows:
DELETE
FROM <table>
WHERE <one or more data conditions>;
The use of FROM is entirely optional, although the table
name must be specified. The use of the WHERE clause is also
optional, but excluding it will cause all the rows in the table to be
deleted.
If you want to delete all rows in a table, you might consider using
the TRUNCATE command instead. This command removes the rows in
the table without generating rollback information. If you are
absolutely positive that you don’t want the data, TRUNCATE is
much faster than DELETE.
Be careful, though! Once you empty a table in this way, its
contents are gone. If you later need the data, you will have to have
your DBA recover the data from a backup.
The INSERT Statement
The INSERT statement is used to add a new row of data to a
table. The basic format of an INSERT statement is as follows:
INSERT
INTO <table>
(<column listings>)
VALUES (<column values>);
If you are inserting column values in the order in which the
table’s columns are defined, you may omit the column listing.
Otherwise, you must include the column listings so Oracle will place
the new data values in the proper columns.
It is possible to create multiple rows with a single INSERT
statement. This is called a multiple insert. The format of a
multiple insert is as follows:
INSERT
INTO <destination table>
<destination table column listings>
SELECT <source table column listings>
FROM <source table>;
You may use an asterisk in place of the source table column
listings to indicate that all of the source table’s columns should be
selected. If this is the case, the layout of the source and
destination tables must be exactly alike.
Multiple inserts are commonly used to copy data within a table back
into the table with different primary key values. This is very useful
when creating test data.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
