Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
|
|
View PDF |
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.
Syntax
insert statement ::=
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.
Usage Notes
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:
Related Topics