A Quick Introduction to DML

It is outside the scope of this book to provide complete reference information about the features of DML statements in the Oracle SQL language. Instead, we provide a quick overview of the basic syntax, and then explore special features relating to DML inside PL/SQL, including:

· Examples of each DML statement

· Cursor attributes for DML statements

· Special PL/SQL features for DML statements, such as the RETURNING clause

For detailed information, I encourage you to peruse Oracle documentation or a SQL-specific text.

There are three DML statements available in the SQL language:

INSERT

Inserts one or more new rows into a table

UPDATE

Updates the values of one or more columns in an existing row in a table

DELETE

Removes one or more rows from a table

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 variablesBEGIN 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);

Here is an example of an INSERT SELECT FROM statement that creates "sequels" for each of my existing books. Notice that as I retrieve data from the existing book rows, I change the values of the columns. For example, I create a "dummy" ISBN and add one year to the publication date. Ah, if only it were that easy to update my oeuvre!:

BEGIN INSERT INTO books ( isbn, title, summary, author, date_published, page_count) SELECT SUBSTR (isbn, 1, LENGTH(isbn)-1) || 'X' title || ' - Part Deux', summary || ' plus newer stuff', author, ADD_MONTHS (date_published, 12), page_count FROM books WHERE UPPER (author) LIKE '%FEUERSTEIN, STEVEN%';END;