October 2005
Intermediate to advanced
454 pages
14h 44m
English
Here is the syntax of the two basic types of INSERT statements:
Insert a single row with an explicit list of values.
INSERT INTOtable[(col1, col2, ...,coln)] VALUES (val1, val2, ..., valn);
Insert one or more rows into a table as defined by a SELECT statement against one or more other tables.
INSERT INTOtable[(col1, col2, ..., coln)] AS SELECT ...;
Let’s look at some examples of INSERT statements executed within a PL/SQL block. First, I insert a new row into the book table. Notice that I do not need to specify the names of the columns if I provide a value for each column.
BEGIN
INSERT INTO book
VALUES ('1-56592-335-9',
'Oracle PL/SQL Programming',
'Reference for PL/SQL developers,' ||
'including examples and best practice ' ||
'recommendations.',
'Feuerstein,Steven, with Bill Pribyl',
TO_DATE ('01-SEP-1997','DD-MON-YYYY'),
987);
END;I can also list the names of the columns and provide the values as variables, instead of literal values:
DECLARE
l_isbn book.isbn%TYPE := '1-56592-335-9';
... other declarations of local variables
BEGIN
INSERT INTO books (
isbn, title, summary, author,
date_published, page_count)
VALUES (
l_isbn, l_title, l_summary, l_author,
l_date_published, l_page_count);