|PL/SQL User's Guide and Reference
Release 2 (9.2)
Part Number A96624-01
PL/SQL Language Elements, 26 of 52
INSERT statement adds new rows of data to a specified database table. For a full description of the
INSERT statement, see Oracle9i SQL Reference.
This is another (usually short) name for the referenced table or view.
This identifies a list of columns in a database table or view. Column names need not appear in the order in which they were defined by the
VIEW statement. However, no column name can appear more than once in the list. If the list does not include all the columns in a table, the missing columns are set to
NULL or to a default value specified in the
This clause lets you return values from inserted rows, thereby eliminating the need to
SELECT the rows afterward. You can retrieve the column values into variables and/or host variables, or into collections and/or host arrays. However, you cannot use the
RETURNING clause for remote or parallel inserts. For the syntax of
returning_clause, see "DELETE Statement".
This is any expression valid in SQL. For more information, see Oracle9i SQL Reference.
This is 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".
This is a
SELECT statement that returns a value or set of values. As many rows are added to the table as are returned by the subquery. It must return a value for every column in the column list or for every column in the table if there is no column list.
This identifies 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, which must be a nested table. Operator
TABLE informs Oracle that the value is a collection, not a scalar value.
This clause 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 only one value for each column in the column list. Also, 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');