10.3 PL/SQL Tables and DML Statements

Tables are PL/SQL constructs. PL/SQL is a linguistic extension of the Oracle SQL language, but it is distinct from SQL. When PL/SQL executes a SQL DML statement (SELECT, INSERT, UPDATE, or DELETE), it actually passes control to the RDBMS or SQL kernel, so the PL/SQL statement containing the DML must conform to the SQL standards. Generally PL/SQL is designed to hide this distinction, but when you work with PL/SQL tables -- just as when you work with records (the other PL/SQL composite data structure) -- you will have to be aware of the differences between SQL and PL/SQL.

The PL/SQL table structure is clearly not a part of the SQL language and standards, in that it employs array-like syntax to access rows in the PL/SQL table. Keep the following restrictions in mind when you work with PL/SQL tables:

• There is no concept of transaction integrity with PL/SQL tables. You cannot commit information to a PL/SQL table or roll back changes from the table.

• You cannot SELECT from PL/SQL tables. There is no way to perform set-at-a-time processing to retrieve data from a PL/SQL table. This is a programmatic construct in a programmatic language. Instead you can use PL/SQL loops to move through the contents of a PL/SQL table, one row at a time.

• You cannot issue DML statements (INSERTs, UPDATEs, and DELETEs) against PL/SQL tables (though PL/SQL Release 2.3 does offer a DELETE operator).

Of course, you can always use the individual rows of a table anywhere in a SQL DML statement where you can use an expression of a compatible type.

In this stage of PL/SQL's evolution, PL/SQL tables remain relatively simple in structure and restricted in usage. You can expect to see rapid progress in the sophistication, performance, and flexibility of this data structure in the coming years, as is evidenced by the enhancements offered in PL/SQL Release 2.3.