The INSERT statement
adds one or more new rows of data to a database table. For a full description
of the INSERT statement, see Oracle Database SQL Reference.
insert statement ::=
Description of the illustration insert_statement.gif
Keyword and Parameter Description
alias
Another (usually short) name for the referenced table or
view.
column_name[, column_name]...
A list of columns in a database table or view. The
columns can be listed in any order, as long as the expressions in the VALUES clause are
listed in the same order. Each column name can only be listed once. If the list
does not include all the columns in a table, each missing columns is set to NULL or to a
default value specified in the CREATE TABLE statement.
returning_clause
Returns values from inserted rows, eliminating the need
to SELECT the rows afterward. You can retrieve
the column values into variables or into collections. You cannot use the RETURNING clause for
remote or parallel inserts. If the statement does not affect any rows, the
values of the variables specified in the RETURNING clause are
undefined. For the syntax of returning_clause, see "RETURNING
INTO Clause".
sql_expression
Any expression valid in SQL. For example, it
could be a literal, a PL/SQL variable, or a SQL query that returns a single
value. For more information, see Oracle Database SQL Reference. PL/SQL also lets you
use a record variable here.
subquery
A SELECT statement that
provides a set of rows for processing. Its syntax is like
that of select_into_statement without
the INTO clause. See "SELECT
INTO Statement".
subquery3
A SELECT statement that
returns a set of rows. Each row returned by the select statement is
inserted into the table. The subquery must return a
value for every column in the column list, or for
every column in the table if there is no column list.
table_reference
A table or view that must be accessible when you execute
the INSERT statement, and for which you must have INSERT privileges. For the
syntax of table_reference, see "DELETE
Statement".
TABLE (subquery2)
The
operand of TABLE is a SELECT statement
that returns a single column value representing a nested table. This operator
specifies that the value is a collection, not a scalar value.
Assigns the values of expressions to corresponding
columns in the column list. If there is no column list, the first value
is inserted into the first column defined by the CREATE TABLE statement,
the second value is inserted into the second column, and so on. There must be
one value for each column in the column list. The datatypes
of the values being inserted must be compatible with the datatypes
of corresponding columns in the column list.
Character
and date literals in the VALUES list must be
enclosed by single quotes ('). Numeric literals are not enclosed by quotes.
The
implicit cursor SQL and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you
access useful information about the execution of an INSERT statement.
For
examples, see the following:
Example
6-1, "Data Manipulation With PL/SQL"
Example
6-5, "Using CURRVAL and NEXTVAL"
Example
6-7, "Using SQL%FOUND"
Example
6-37, "Using ROLLBACK"
Example
6-38, "Using SAVEPOINT With ROLLBACK"
Example
6-46, "Declaring an Autonomous Trigger"
Example
6-48, "Calling an Autonomous Function"
Example
7-1, "Examples of Dynamic SQL"
Example
9-3, "Creating the emp_admin Package"
Related Topics
"DELETE
Statement"
"SELECT
INTO Statement"
"UPDATE
Statement"