|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
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.
Keyword and Parameter Description
Another (usually short) name for the referenced table or view.
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
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 "DELETE Statement".
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.
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".
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.
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".
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
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
%ISOPEN let you access useful information about the execution of an
The following examples show various forms of
INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > sal * 0.25; ... INSERT INTO emp (empno, ename, job, sal, comm, deptno) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30); ... INSERT INTO dept VALUES (my_deptno, UPPER(my_dname), 'CHICAGO');