|Table of Contents|
For those of you who arent familiar with the basic format of DML statements in Oracle, Ive 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 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 dont 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 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 tables 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 tables 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.
The SELECT statement is used to retrieve data from a table. The basic format of a SELECT statement is as follows:
SELECT <column listing> FROM <source table> <source table alias> WHERE <one or more data conditions>;
If you wish to select all the columns that match your WHERE clause, you may use an asterisk instead of naming all the columns from your source table. The use of the WHERE clause is optional, but excluding the clause will cause all the rows of data to be retrieved from the table.
The use of a table alias is optional, but it is quite common in queries that join two or more tables to return data. The basic format when using a table alias is as follows:
SELECT <column listing> FROM <source table> <source table alias>, <source table> <source table alias> WHERE <one or more data conditions>;
A table alias is most often used to abbreviate a tables name so that columns in the WHERE clause may be referenced with the abbreviation instead of the table name. When two columns in different tables share the same name, the desired column must be identified using either a table name or table alias.
The use of an optional ORDER BY clause is quite common, as well. The basic format of an ORDER BY clause is as follows:
SELECT <column listing> FROM <table> <table alias> WHERE <one or more data conditions> ORDER BY <column>;
The ORDER BY clause instructs Oracle to gather the data and then sort it according to one or more conditions. Use of this clause adds overhead to the performance of the statement.
The UPDATE statement is used to modify data that already exists in a table. The basic format of an UPDATE statement is as follows:
UPDATE <table> SET <column> = <value>, <column> = <value> WHERE <one or more data conditions>;
You may specify as many columns as you like in the SET clause. The use of the WHERE clause is (once again) optional, but excluding the clause will cause every row in the table to be updated.
|Table of Contents|