Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

The INSERT Statement

Here is the syntax of the two basic types of INSERT statements:

  • Insert a single row with an explicit list of values.

    INSERT INTO table [(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 INTO table [(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);
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page