1.11. Changing Data It is beyond the scope of this book to provide complete reference information about the features of DML statements in the Oracle SQL language. Instead, I 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
1.11.1. 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);
1.11.2. The UPDATE Statement You can update one or more columns in one or more rows using the UPDATE statement . Here is the basic syntax : UPDATE table SET col1 = val1 [, col2 = val2, ... colN = valN] [WHERE WHERE_clause];
The WHERE clause is optional; if you do not supply one, all rows in the table are updated. Here are some examples of UPDATEs: Uppercase all the titles of books in the book table. UPDATE books SET title = UPPER (title);
Run a utility procedure that removes the time component from the publication date of books written by specified authors (the argument in the procedure) and uppercases the titles of those books. As you can see, you can run an UPDATE statement standalone or within a PL/SQL block: CREATE OR REPLACE PROCEDURE remove_time ( author_in IN VARCHAR2) IS BEGIN UPDATE books SET title = UPPER (title), date_published = TRUNC (date_published) WHERE author LIKE author_in; END;
1.11.3. The DELETE Statement You can use the DELETE statement to remove one, some, or all the rows in a table. Here is the basic syntax : DELETE FROM table [WHERE WHERE_clause];
The WHERE clause is optional in a DELETE statement. If you do not supply one, all rows in the table are deleted. Here are some examples of DELETEs: Delete all the books from the books table: DELETE FROM books;
Delete all the books from the books table that were published prior to a certain date and return the number of rows deleted: CREATE OR REPLACE PROCEDURE remove_books ( date_in IN DATE, removal_count_out OUT PLS_INTEGER) IS BEGIN DELETE FROM books WHERE date_published < date_in; removal_count_out := SQL%ROWCOUNT; END;
Of course, all of these DML statements can become qualitatively more complex as you deal with real-world entities. You can, for example, update multiple columns with the contents of a subquery. As of Oracle9i Database, you can replace a table name with a table function that returns a result set upon which the DML statement acts. See Chapter 3 for details. Oracle provides several cursor attributes for the implicit cursors "behind" your DML statements, described in the next section. 1.11.4. Cursor Attributes for DML Operations Oracle allows you to access information about the most recently executed native DML statement by referencing one of the implicit SQL cursor attributes (these are identical to those listed in Table 1-6). Table 1-8 describes the values returned by these attributes for DML statements. Table 1-8. Implicit SQL cursor attributes for DML statementsName | Description |
|---|
SQL%FOUND | Returns TRUE if one or more rows were modified (created, changed, removed) successfully. | SQL%NOTFOUND | Returns TRUE if no rows were modified by the DML statement. | SQL%ROWCOUNT | Returns number of rows modified by the DML statement. |
Now let's see how we can use cursor attributes with implicit cursors. Use SQL%FOUND to determine if your DML statement affected any rows. For example, from time to time an author will change his name and want a new name used for all of his books. So I create a small procedure to update the name and then report back via a Boolean variable whether any rows were modified: CREATE OR REPLACE PROCEDURE change_author_name ( old_name_in IN books.author%TYPE, new_name_in IN books.author%TYPE, changes_made_out OUT BOOLEAN) IS BEGIN UPDATE books SET author = new_name_in WHERE author = old_name_in; changes_made_out := SQL%FOUND; END;
Use SQL%ROWCOUNT when you need to know exactly how many rows were affected by your DML statement. Here is a reworking of the above name-change procedure that returns a bit more information: CREATE OR REPLACE PROCEDURE change_author_name ( old_name_in IN books.author%TYPE, new_name_in IN books.author%TYPE, rename_count_out OUT PLS_INTEGER) IS BEGIN UPDATE books SET author = new_name_in WHERE author = old_name_in; rename_count_out := SQL%ROWCOUNT; END;
1.11.5. DML and Exception Handling When an exception occurs in a PL/SQL block, Oracle does not roll back any of the changes made by DML statements in that block. It is up to you, the manager of the application's logical transaction, to decide what sort of behavior should occur. Here are some things to keep in mind in this regard: If your block is an autonomous transaction (described later in this chapter), then you must perform a rollback or commit (usually a rollback) when an exception is raised. You can use savepoints to control the scope of a rollback. In other words, you can roll back to a particular savepoint and thereby preserve a portion of the changes made in your session. Savepoints are also explored later in this chapter. If an exception propagates past the outermost block (i.e., it goes unhandled), then, in most host execution environments for PL/SQL, like SQL*Plus, an unqualified rollback is automatically executed, reversing any outstanding changes.
1.11.6. Bulk DML with the FORALL Statement Oracle introduced a significant enhancement to PL/SQL's DML capabilities for Oracle8i Database and above with the FORALL statement . FORALL tells the PL/SQL runtime engine to bulk bind into the SQL statement all of the elements of one or more collections before sending anything to the SQL engine. Why would this be useful? We all know that PL/SQL is tightly integrated with the underlying SQL engine in the Oracle database. PL/SQL is the database programming language of choice for Oracleeven though you can now (at least theoretically) use Java inside the database, as well. But this tight integration does not necessarily mean that no overhead is associated with running SQL from a PL/SQL program. When the PL/SQL runtime engine processes a block of code, it executes the procedural statements within its own engine but passes the SQL statements on to the SQL engine. The SQL layer executes the SQL statements and then returns information to the PL/SQL engine, if necessary. This transfer of control between the PL/SQL and SQL engines is called a context switch . Each time a switch occurs, there is additional overhead. There are a number of scenarios in which many switches occur and performance degrades. Starting with Oracle8i Database, Oracle offers two enhancements to PL/SQL that allow you to bulk together multiple context switches into a single switch, thereby improving the performance of your applications. These enhancements are FORALL and BULK COLLECT (explained earlier). When the statement is bulk bound and passed to SQL, the SQL engine executes the statement once for each index number in the range. In other words, the same SQL statements are executed, but they are all run in the same round trip to the SQL layer, minimizing the context switches. This is shown in Figure 1-4. 1.11.6.1. Syntax of the FORALL Statement Although the FORALL statement contains an iteration scheme (i.e., it iterates through all the rows of a collection), it is not a FOR loop. Consequently, it has neither a LOOP nor an END LOOP statement. Its syntax is as follows: Figure 1-4. One context switch with FORALL 
FORALL index_row IN [ lower_bound ... upper_bound | INDICES OF indexing_collection| VALUES OF indexing_collection ] [ SAVE EXCEPTIONS ] sql_statement;
where:
index_row Specified collection that the FORALL will iterate through.
lower_bound Starting index number (row or collection element) for the operation.
upper_bound Ending index number (row or collection element) for the operation.
sql_statement SQL statement to be performed on each collection element.
indexing_collection PL/SQL collection used to select the indices in the bind array referenced in the sql_statement. The INDICES OF and VALUES_OF alternatives are available starting with Oracle Database 10g.
SAVE EXCEPTIONS Optional clause that tells FORALL to process all rows, saving any exceptions that occur.
You must follow these rules when using FORALL: The body of the FORALL statement must be a single DML statementan INSERT, UPDATE, or DELETE. The DML statement must reference collection elements, indexed by the index_row variable in the FORALL statement . The scope of the index_row variable is the FORALL statement only; you may not reference it outside of that statement. Note, though, that the upper and lower bounds of these collections do not have to span the entire contents of the collection(s). Do not declare a variable for index_row. It is declared implicitly as PLS_INTEGER by the PL/SQL engine. The lower and upper bounds must specify a valid range of consecutive index numbers for the collection(s) referenced in the SQL statement. Sparsely filled collections will raise the following error: ORA-22160: element at index [3] does not exist
See the diffcount.sql file on the book's web site for an example of this scenario. Note that Oracle Database 10g offers the INDICES OF and VALUES OF syntax to support sparse collections (in which rows are not filled sequentially). Fields within collections of records may not be referenced within the DML statement. Instead, you can only reference the row in the collection as a whole, whether the fields are collections of scalars or collections of more complex objects. For example, the code below: DECLARE TYPE employee_aat IS TABLE OF employee%ROWTYPE INDEX BY PLS_INTEGER; l_employees employee_aat; BEGIN FORALL l_index IN l_employees.FIRST .. l_employees.LAST INSERT INTO employee (employee_id, last_name) VALUES (l_employees (l_index).employee_id , l_employees (l_index).last_name ); END;
will cause the following compilation error: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records The collection subscript referenced in the DML statement cannot be an expression. For example, the following script: DECLARE names name_varray := name_varray ( ); BEGIN FORALL indx IN names.FIRST .. names.LAST DELETE FROM emp WHERE ename = names(indx+10); END;
will cause the following error: PLS-00430: FORALL iteration variable INDX is not allowed in this context
1.11.6.2. FORALL Examples Here are some examples of the use of the FORALL statement: Let's rewrite the order_books procedure to use FORALL: CREATE OR REPLACE PROCEDURE order_books ( isbn_in IN name_varray, new_count_in IN number_varray) IS BEGIN FORALL indx IN isbn_in.FIRST .. isbn_in.LAST UPDATE books SET page_count = new_count_in (indx) WHERE isbn = isbn_in (indx); END;
Notice that the only changes in this example are to change FOR to FORALL, and to remove the LOOP and END LOOP keywords. This use of FORALL accesses and passes to SQL each of the rows defined in the two collections. Figure 1-4 shows the change in behavior that results. The next example shows how the DML statement can reference more than one collection. In this case, I have three collections: denial, patient_name, and illnesses. Only the first two are subscripted, and so individual elements of the collection are passed to each INSERT. The third column in health_coverage is a collection listing preconditions. Because the PL/SQL engine bulk binds only subscripted collections, the illnesses collection is placed in that column for each row inserted: FORALL indx IN denial.FIRST .. denial.LAST INSERT INTO health_coverage VALUES (denial(indx), patient_name(indx), illnesses);
Use the RETURNING clause in a FORALL statement to retrieve information about each separate DELETE statement. Notice that the RETURNING clause in FORALL must use BULK COLLECT INTO (the corresponding "bulk" operation for queries): CREATE OR REPLACE FUNCTION remove_emps_by_dept (deptlist dlist_t) RETURN enolist_t IS enolist enolist_t; BEGIN FORALL aDept IN deptlist.FIRST..deptlist.LAST DELETE FROM emp WHERE deptno IN deptlist(aDept) RETURNING empno BULK COLLECT INTO enolist; RETURN enolist; END;
Use the indices defined in one collection to determine which rows in the binding array (the collection referenced inside the SQL statement) will be used in the dynamic INSERT. FORALL indx IN INDICES OF l_top_employees EXECUTE IMMEDIATE 'INSERT INTO ' || l_table || ' VALUES (:emp_pky, :new_salary) USING l_new_salaries(indx).employee_id, l_new_salaries(indx).salary;
|
