A Sample PL/SQL Programs

This appendix tells you where to find collections of sample PL/SQL programs, for your own study and testing.

Where to Find PL/SQL Sample Programs

You can find some sample programs in the PL/SQL demo directory. For the location of the directory, see the Oracle installation guide for your system. These samples are typically older ones based on the SCOTT schema, with its EMP and DEPT tables.

Most examples in this book have been made into complete programs that you can run under the HR sample schema, with its EMPLOYEES and DEPARTMENTS tables.

The Oracle Technology Network web site has a PL/SQL section with many sample programs to download, at http://otn.oracle.com/tech/pl_sql/. These programs demonstrate many language features, particularly the most recent ones. You can use some of the programs to compare performance of PL/SQL across database releases.

For examples of calling PL/SQL from other languages, see Oracle Database Java Developer's Guide and Pro*C/C++ Programmer's Guide.

Exercises for the Reader

Here are some PL/SQL programming constructs that are helpful to know. After learning from the sample programs in this book and on the web, check to see that you are familiar with writing each of these constructs.

An anonymous PL/SQL block.
A PL/SQL stored procedure.
A SQL CALL statement that invokes a stored procedure. An anonymous block that invokes the stored procedure.
A PL/SQL stored function.
A SQL query that calls the stored function.
A PL/SQL package.
An anonymous block or a stored procedure that calls a packaged procedure. A SQL query that calls a packaged function.
A SQL*Plus script, or a set of scripts called from a master script, that creates a set of procedures, functions, and packages.
A FORALL statement (instead of a regular loop) to issue multiple INSERT, UPDATE, or DELETE statements.