Oracle9i Release 1 and Release 2 have added exciting new functionality to the PL/SQL language. These are summarized in the following sections and are covered more thoroughly in the indicated chapters.
Record-based DML
New to Oracle9i Release 2, you can now use records in INSERT and UPDATE statements. Here is an example:
CREATE OR REPLACE PROCEDURE set_book_info (book_in IN books%ROWTYPE)ISBEGIN INSERT INTO books VALUES (book_in);EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE books SET ROW = book_in WHERE isbn = book_in.isbn;END;This enhancement offers some very compelling advantages over working with individual variables or fields within a record:
Very concise code
You can "stay above the fray" and work completely at the record level. There is no need to declare individual variables or to decompose a record into its fields when passing that data to the DML statement.
More robust code
By working with %ROWTYPE records and not explicitly manipulating fields in those records, your code is less likely to break as changes are made to the tables and views upon which the records are based.
You can find additional information about record-based DML in Chapter 13.
Table functions
A table function is a function that returns a result set (in the form of a PL/SQL collection) and can be called in the FROM clause of a query. Table functions were available in a very limited fashion in Oracle8i, but Oracle9i has greatly expanded the scope and usefulness of these functions. In Oracle9i it is now possible for you to write functions that do the following:
Return rows from a result set in "pipelined fashion"
This means that data is returned while the function is still executing.
Participate fully in parallel query execution
In other words, the function can be run simultaneously within multiple "slave processes" against different, partitioned elements of data.
Here is an example of the header of a pipelined table function that can run in parallel, defined so that all the rows for a given department go to the same slave process, and all rows are delivered consecutively:
CREATE OR REPLACE FUNCTION my_transform_fn (p_input_rows in employee_info.recur_t) RETURN employee_info.transformed_t PIPELINED CLUSTER P_INPUT_ROWS BY (department) PARALLEL_ENABLE (PARTITION P_INPUT_ROWS BY HASH (department))You can find additional information about table functions, pipelining, and parallel execution in Chapter 16.
