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;