Table of Contents Previous Next



2.3.65 INSERT
INSERT -- create new rows in a table
INSERT INTO table[@dblink ] [ ( column [, ...] ) ]
{ VALUES ( { expression | DEFAULT } [, ...] )
[ RETURNING return_expression [, ...]
{ INTO { record | variable [, ...] }
| BULK COLLECT INTO collection [, ...] } ]
| query }
INSERT allows you to insert new rows into a table. You can insert a single row at a time or several rows as a result of a query.
The RETURNING INTO { record | variable [, ...] } clause may only be specified when the INSERT command is used within an SPL program and only when the VALUES clause is used.
The RETURNING BULK COLLECT INTO collection [, ...] clause may only be specified if the INSERT command is used within an SPL program. If more than one collection is specified as the target of the BULK COLLECT INTO clause, then each collection must consist of a single, scalar field – i.e., collection must not be a record. return_expression evaluated for each inserted row, becomes an element in collection starting with the first element. Any existing rows in collection are deleted. If the result set is empty, then collection will be empty.
You must have INSERT privilege to a table in order to insert into it. If you use the query clause to insert rows from a query, you also need to have SELECT privilege on any table used in the query.
Database link name identifying a remote database. See the CREATE DATABASE LINK command for information on database links.
A query (SELECT statement) that supplies the rows to be inserted. Refer to the SELECT command for a description of the syntax.
An expression that may include one or more columns from table. If a column name from table is specified in return_expression, the value substituted for the column when return_expression is evaluated is determined as follows:
If the column specified in return_expression is assigned a value in the INSERT command, then the assigned value is used in the evaluation of return_expression.
If the column specified in return_expression is not assigned a value in the INSERT command and there is no default value for the column in the table’s column definition, then null is used in the evaluation of return_expression.
If the column specified in return_expression is not assigned a value in the INSERT command and there is a default value for the column in the table’s column definition, then the default value is used in the evaluation of return_expression.
A record whose field the evaluated return_expression is to be assigned. The first return_expression is assigned to the first field in record, the second return_expression is assigned to the second field in record, etc. The number of fields in record must exactly match the number of expressions and the fields must be type-compatible with their assigned expressions.
A variable to which the evaluated return_expression is to be assigned. If more than one return_expression and variable are specified, the first return_expression is assigned to the first variable, the second return_expression is assigned to the second variable, etc. The number of variables specified following the INTO keyword must exactly match the number of expressions following the RETURNING keyword and the variables must be type-compatible with their assigned expressions.
A collection in which an element is created from the evaluated return_expression. There can be either a single collection which may be a collection of a single field or a collection of a record type, or there may be more than one collection in which case each collection must consist of a single field. The number of return expressions must match in number and order the number of fields in all specified collections. Each corresponding return_expression and collection field must be type-compatible.
In this second example, the column, comm, is omitted and therefore it will have the default value of null:
The third example uses the DEFAULT clause for the hiredate and comm columns rather than specifying a value:


Table of Contents Previous Next