 |
|
PL/SQL in Scripts
Oracle Tips by
Burleson
|
The use of IF-THEN-ELSE logic, looping
structures, and other features can make writing a powerful script much
simpler. It’s extremely difficult to simulate these logical control
structures using only SQL statements.
Anonymous PL/SQL
Blocks
Every use of PL/SQL in your script will be done through an
anonymous PL/SQL block. Even a call to a stored procedure or
function must be executed through an anonymous block. An anonymous
PL/SQL block is nothing more than a PL/SQL block that isn’t already
compiled in the data dictionary. This is shown in the following
example:
DECLARE
nGPA number := 0;
BEGIN
SELECT overall_gpa
INTO nGPA
FROM STUDENTS
WHERE ssn = 999999999;
END;
One of the most common uses of anonymous PL/SQL blocks is a call to
a stored PL/SQL object, typically for testing purposes.
The exec statement is a quick method to create an anonymous
PL/SQL block, but it doesn’t allow you to define any variables. The
use of the exec statement is shown in this example, which calls
the Annual_Review() procedure.
exec Annual_Review;
If the Annual_Review() procedure required parameters, these
would have to be passed as literal values to the procedure. Using
exec simply nests the call to the stored object between a BEGIN
and an END.
Using DBMS_Output
and UTL_File
The DBMS_Output package is often used within anonymous
PL/SQL blocks to display the values returned from queries and calls to
stored PL/SQL objects. The results of queries inside PL/SQL blocks
contained in a script are not spooled to standard output (the terminal
or monitor). This package provides a useful method of generating
messages that are spooled to standard output. In order to use the
DBMS_Output package, you must use the set serveroutput
on command at the beginning of your script.
The UTL_File package (introduced with Oracle 7.3) can be
used within scripts instead of spooling output to a file with the
spool command. The package writes to and reads from files at the
operating system level. This package is especially useful if the
output of the script needs to be stored in a file.
More information about these packages is provided in Chapter 9.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
