readme.html

介绍Oracle PL SQL编程

  </tr>
  <tr>
    <td><a href="Chapter4/UpdateDual.sql">UpdateDual.sql</a></td>
    <td>This script demonstrates how DML works with PL/SQL.</td>
  </tr>
  <tr>
    <td><a href="Chapter4/WhereCurrentOf.sql">WhereCurrentOf.sql</a></td>
    <td>This script demonstrates how UPDATEs work with PL/SQL and the WHERE CURRENT
      OF clause.</td>
  </tr>
  <tr>
    <td><a href="Chapter4/WhileLoop.sql">WhileLoop.sql</a></td>
    <td>This script demonstrates the use of the WHILE loop.</td>
  </tr>
</table>
<h4><a name="CH5"></a>Chapter 5: Records</h4>
<table width="100%" border="1">
  <tr>
    <td width="29%"><a href="Chapter5/create_addressbook1.sql">create_addressbook1.sql</a></td>
    <td width="71%">This script builds objects for the example with standard Oracle data types.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_function1.sql">create_function1.sql</a></td>
    <td>This script demonstrates using a record type as a function return value.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_function2.sql">create_function2.sql</a></td>
    <td>This script demonstrates using an object type as a function return value.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_function2e.sql">create_function2e.sql</a></td>
    <td>This script demonstrates using an object type can not be assigned a %ROWTYPE record type.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_record1.sql">create_record1.sql</a></td>
    <td>This script demonstrates using a record type to populate a table.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_record2.sql">create_record2.sql</a></td>
    <td>This script demonstrates using explicit definition to define a record type.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_record3.sql">create_record3.sql</a></td>
    <td>This script demonstrates using explicit definition to define record
    types and a compound record type; and, the use of nested types.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_record4.sql">create_record4.sql</a></td>
    <td>This script demonstrates using a database object to define a record type.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_record5.sql">create_record5.sql</a></td>
    <td>This script demonstrates using compound database objects to define a record type.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_record6.sql">create_record6.sql</a></td>
    <td>This script demonstrates using explicit definition to define record types and a compound record type; and, the use of nested types.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_record7.sql">create_record7.sql</a></td>
    <td>This script demonstrates passing database objects as actual parameters to a procedure.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/create_user.sql">create_user.sql</a></td>
    <td>This script verifies and defines the PLSQL user.</td>
  </tr>
  <tr>
    <td><a href="Chapter5/query_record1.sql">query_record1.sql</a></td>
    <td>This script verifies the success of the following programs.<br>
    - create_record1.sql<br>
    - create_record2.sql<br>
    - create_record3.sql</td>
  </tr>
</table>
<h4><a name="CH6"></a>Chapter 6: Collections</h4>
<table width="100%" border="1">
  <tr>
    <td width="29%"><a href="Chapter6/bulk_collect1.sql">bulk_collect1.sql</a></td>
    <td width="71%">This script demonstrates how to do a bulk collect into an associative array.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/bulk_collect2.sql">bulk_collect2.sql</a></td>
    <td>This script demonstrates how to do a bulk collect into a nested table.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/bulk_collect3.sql">bulk_collect3.sql</a></td>
    <td>This script demonstrates how to do a non-bulk select into elements of a PL/SQL table.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/count.sql">count.sql</a></td>
    <td>This script demonstrates how to use the Oracle10g Collection API COUNT method against an element.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_addressbook2.sql">create_addressbook2.sql</a></td>
    <td>This script builds objects for the example with standard Oracle data types
    in all but one column. It uses a varray of VARCHAR2(30 CHAR) for the
    ADDRESSES.STREET_ADDRESS column.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_addressbook2e.sql">create_addressbook2e.sql</a></td>
    <td>This script builds objects for the example with standard Oracle data types in all but one column.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_addressbook3.sql">create_addressbook3.sql</a></td>
    <td>This script builds objects for the example with standard Oracle data types
    in all but one column.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_addressbook3e.sql">create_addressbook3e.sql</a></td>
    <td>This script builds objects for the example with standard Oracle data types
    in all but one column.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_assocarray1.sql">create_assocarray1.sql</a></td>
    <td>This script builds an associative array.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_assocarray2.sql">create_assocarray2.sql</a></td>
    <td>This script demonstrates you cannot traverse an associative array until elements are initialized.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_assocarray3.sql">create_assocarray3.sql</a></td>
    <td>This script demonstrates you can avoid traversing an associative array
    where no elements are initialized.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_assocarray4.sql">create_assocarray4.sql</a></td>
    <td>This script demonstrates you cannot use the Collection API EXTEND method
    to allocated space.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_assocarray5.sql">create_assocarray5.sql</a></td>
    <td>This script demonstrates initialization and assignment with a numeric
    index value to an associative array.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_assocarray5e.sql">create_assocarray5e.sql</a></td>
    <td>This script demonstrates initialization and assignment with a numeric
    index value to an associative array.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_assocarray6.sql">create_assocarray6.sql</a></td>
    <td>This script demonstrates initialization and assignment with a unique
    string index value to an associative array.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_nestedtable1.sql">create_nestedtable1.sql</a></td>
    <td>This defines a three element nested table built with a three item constructor of null values.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_nestedtable2.sql">create_nestedtable2.sql</a></td>
    <td>This constructs a null element nested table, then extends it one element at a time.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_nestedtable3.sql">create_nestedtable3.sql</a></td>
    <td>This constructs a null element nested table type in the database, then extends it
    one element at a time.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_nestedtable4.sql">create_nestedtable4.sql</a></td>
    <td>This constructs a two varrays and one nested table type in the database,
    then it assigns the contents of the varrays into a nested table.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_user.sql">create_user.sql</a></td>
    <td>This script verifies and defines the PLSQL user.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_varray1.sql">create_varray1.sql</a></td>
    <td>This defines a varray with a three element constructor of null elements.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_varray2.sql">create_varray2.sql</a></td>
    <td>This defines a varray with a null element constructor and extends it one element at a time.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_varray3.sql">create_varray3.sql</a></td>
    <td>This defines a varray with a three element constructor of null elements and attempt to populate it beyond three elements.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/create_varray4.sql">create_varray4.sql</a></td>
    <td>This defines a varray with a null element constructor and extends it one element at a time
    by a formula.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/delete.sql">delete.sql</a></td>
    <td>This script demonstrates how to use the Oracle10g Collection API
    DELETE method against a set of elements.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/exists.sql">exists.sql</a></td>
    <td>This script demonstrates how to use the Oracle10g Collection API
    EXISTS method against an element.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/extend.sql">extend.sql</a></td>
    <td>This script demonstrates how to use the Oracle10g Collection API
    EXTEND method against an element.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/first.sql">first.sql</a></td>
    <td>This script demonstrates how to use the Oracle10g Collection API
    FIRST and LAST methods against a collection.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/limit.sql">limit.sql</a></td>
    <td>This script demonstrates how to use the Oracle10g Collection API
    LIMIT method against a collection.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/nestedtable_dml1.sql">nestedtable_dml1.sql</a></td>
    <td>This script demonstrates using a stored function to resolve the
    one-to-many relation of a nested table in a row of data.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/nestedtable_dml2.sql">nestedtable_dml2.sql</a></td>
    <td>This script demonstrates how to update nested tables.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/nestedtable_dml3.sql">nestedtable_dml3.sql</a></td>
    <td>This script demonstrates the update of an element of a nested table
    collection.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/nestedtable_dml4.sql">nestedtable_dml4.sql</a></td>
    <td>This script demonstrates the update of an element of a nested table
    collection.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/trim.sql">trim.sql</a></td>
    <td>This script demonstrates how to use the Oracle10g Collection API
    TRIM method against a collection.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/varray_dml1.sql">varray_dml1.sql</a></td>
    <td>This script inserting and reading from a varray.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/varray_dml2.sql">varray_dml2.sql</a></td>
    <td>This script demonstrates how to update varrays.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/varray_dml3.sql">varray_dml3.sql</a></td>
    <td>This script demonstrates the update of an element of a varray
    collection.</td>
  </tr>
  <tr>
    <td><a href="Chapter6/varray_dml4.sql">varray_dml4.sql</a></td>
    <td>This script demonstrates the update of an element of a varray
    collection.</td>
  </tr>
</table>
<h4><a name="CH7"></a>Chapter 7: Error Handling</h4>
<p>For this set of examples, make sure you run tables.sql after creating the schema.
  All other example scripts depend on the objects created in tables.sql.</p>
<table width="100%" border="1">
  <tr>
    <td width="29%"><a href="Chapter7/CreateUser.sql">CreateUser.sql</a></td>
    <td width="71%">This script creates the plsql user for chapter 7 examples.
      You must run this script as SYS or SYSTEM as SYSDBA. The script can be rerun.</td>
  </tr>
  <tr>
    <td height="39"><a href="Chapter7/autoRollback.sql">autoRollback.sql</a></td>
    <td>This script demonstrates how the server will roll back the current transaction
      if the top level block exits with an unhandled exception.</td>
  </tr>
  <tr>
    <td><a href="Chapter7/DuplicateHandlers.sql">DuplicateHandlers.sql</a></td>
    <td>This example illustrates the PLS-483 error.</td>
  </tr>
  <tr>
    <td><a href="Chapter7/DupValOnIndex.sql">DupValOnIndex.sql</a></td>
    <td>This block will raise the DUP_VAL_ON_INDEX exception.</td>
  </tr>
  <tr>
    <td><a href="Chapter7/ExceptionInit.sql">ExceptionInit.sql</a></td>
    <td>This script demonstrates the EXCEPTION_INIT pragma.</td>
  </tr>
  <tr>
    <td><a href="Chapter7/OutOfScope.sql">OutOfScope.sql</a></td>
    <td>This script demonstrates the scope of exceptions.</td>
  </tr>
  <tr>
    <td><a href="Chapter7/SQLERRM.sql">SQLERRM.sql</a></td>
    <td>This script demonstrates the use of SQLERRM.</td>
  </tr>
  <tr>
    <td><a href="Chapter7/tables.sql">tables.sql</a></td>
    <td>This script creates the tables and data used by other examples in this
      chapter. </td>
  </tr>
  <tr>
    <td><a href="Chapter7/UserDefined.sql">UserDefined.sql</a></td>
    <td>This script demonstrates user defined exceptions.</td>
  </tr>
  <tr>
    <td><a href="Chapter7/VerifyAuthors.sql">VerifyAuthors.sql</a></td>
    <td>This procedure demonstrates the use of RAISE_APPLICATION_ERROR.</td>
  </tr>
</table>
<h4><a name="CH8"></a>Chapter 8: Creating Procedures, Functions, and Packages</h4>
<p>For this set of examples, make sure you run tables.sql after creating the schema.
  All other example scripts depend on the objects created in tables.sql.</p>
<table width="100%" border="1">
  <tr>
    <td width="29%"><a href="Chapter8/CreateUser.sql">CreateUser.sql</a></td>
    <td width="71%">This script creates the plsql user for chapter 8 examples.
      You must run this script as SYS or SYSTEM as SYSDBA. The script can be rerun.</td>
  </tr>
  <tr>
    <td><a href="Chapter8/AddNewAuthor.sql">AddNewAuthor.sql</a></td>
    <td>This script demonstrates how to create and call a simple procedure.</td>
  </tr>
  <tr>
    <td><a href="Chapter8/AddNewBook.sql">AddNewBook.sql</a></td>
    <td>This procedure will insert a new book into the books table. It also demonstrates
      default parameters.</td>
  </tr>
  <tr>
    <td><a href="Chapter8/callANA.sql">callANA.sql</a></td>
    <td>This block illustrates how to call the AddNewAuthor function.</td>
  </tr>
  <tr>
    <td><a href="Chapter8/CallMe.sql">CallMe.sql</a></td>
    <td>This script demonstrates positional vs. named parameter passing.</td>
  </tr>
  <tr>
    <td><a href="Chapter8/calls.sql">calls.sql</a></td>
    <td>This script demonstrates the use of the CALL statement.</td>
  </tr>
  <tr>
    <td><a href="Chapter8/callSL.sql">callSL.sql</a></td>
    <td>This block illustrates how to call a packaged procedure which takes a
      named type parameter.</td>
  </tr>
  <tr>
    <td><a href="Chapter8/CopyFast.sql">CopyFast.sql</a></td>
    <td>This package illustrates the possible speed benefits of NOCOPY.</td>
  </tr>
  <tr>
    <td height="23"><a href="Chapter8/InventoryOps.sql">InventoryOps.sql</a></td>
    <td>This script demonstrates a package.</td>
  </tr>
  <tr>
    <td><a href="Chapter8/InventoryOps2.sql">InventoryOps2.sql</a></td>
    <td>This version of the InventoryOps package contains an extra procedure in
      the package body.</td>
  </tr>
  <tr>
    <td><a href="Chapter8/NoCopyTest.sql">NoCopyTest.sql</a></td>
    <td>This script illustrates the behavior of NOCOPY.</td>
  </tr>
  <tr>
    <td><a href="Chapter8/noparams.sql">noparams.sql</a></td>
    <td>This procedure and function demonstrate the syntax of calling subprograms
      with no parameters.</td>
  </tr>
  <tr>
    <td height="23"><a href="Chapter8/objectOverload.sql">objectOverload.sql</a></td>
    <td>This script demonstrates overloading based on user defined object types.</td>
  </tr>
  <tr>
    <td height="23"><a href="Chapter8/overload.sql">overload.sql</a></td>
    <td>This version of InventoryOps demonstrates an overloaded procedure, StatusList.</td>
  </tr>
  <tr>
    <td height="23"><a href="Chapter8/packageError.sql">packageError.sql</a></td>
    <td>This package will not compile because the specification and body do not