 |
|
Scripting in SQL*Plus
Oracle Tips by
Burleson
|
SQL*Plus contains a number of features that allow developers to
write complex scripts to accomplish a variety of tasks. Although many
of these features have been supplanted by the evolution of reporting
tools for Oracle, the features still exist and can be used to give
your scripts some extra oomph.
SQL*Plus Commands
SQL*Plus provides numerous commands that allow you to control
almost every aspect of the way data is displayed. Some of these
commands are now used infrequently at best, but a general familiarity
with them will give you a better understanding of the kinds of tasks
that you can accomplish with SQL scripts.
In addition to these commands, SQL*Plus provides a number of
functions (such as min() and max() ) that handle common
operations. These functions are documented in Appendix A, along with
some other useful functions.
The @ Command
The @ (at sign) command is used to open and run a script
from the SQL*Plus command prompt, as shown in the following example:
@set_grade 999999999 2103 'A'
The / Command
The / (forward slash) command instructs SQL*Plus to execute the
commands stored in the command buffer.
Commenting
SQL*Plus and PL/SQL support both single and multiple line comments,
as shown in the following example:
-- This is a single line comment.
/*
This is a multiline comment, like you might find in a C program.
*/
You may use either style of commenting inside your scripts.
The accept Command
You can use the accept command to have your script require
input from the user. The syntax for the accept command is:
accept < variable > datatype [prompt < input prompt >}]
where variable is the name of the variable, datatype
is char or number, and input prompt is the prompt
that should be displayed. You may use the no prompt option if
you do not want a prompt for input, as shown in the following
statement:
accept password char no prompt;
If for some reason you do not wish for the user’s input to be
echoed to the screen, you may use the hide option, as follows:
accept password char no prompt hide;
The break Command
The break command instructs SQL*Plus to perform certain
actions when a specified event occurs while processing the results of
a SELECT command. There are four types of events that can be
specified as part of the break command: expression,
row, page, and report.
- expression—The name of a column being
queried is used as an event. Every time the value of the specified
column changes, SQL*Plus will perform the action defined in the
break event for the expression. In Listing 3.5, the type
column is specified as the break event. When the value of
this column changes, SQL*Plus calculates the average value of the
length column for every type object.
- row—Every row retrieved is used as an
event.
- page—The end of a page is used as an
event.
- report—The end of the report is used as
an event.
Multiple events can be used with the break statement.
Consider again the script shown in Listing 3.5, and notice that the
report calculates the average number of lines for each type of object
and for all the objects. This is accomplished with the following
break command:
break on type report skip page
There is a distinct set of actions that can be performed with a
break: skip n, skip page, noduplicates,
and duplicates.
- skip n—Instructs SQL*Plus to
skip n lines when the event occurs.
- skip page—Instructs SQL*Plus to skip to
the next page when the event occurs.
- noduplicates—Instructs SQL*Plus to
print blanks for the value of a break column when the value
hasn’t changed. This is usually abbreviated as nodup.
- duplicates—Instructs SQL*Plus to always
print the value of a break column.
When used alone, the break command will display the break
events that are currently in place.
The btitle Command
The btitle command instructs SQL*Plus to print a title (or
footer) at the bottom of each page of a report. The command allows you
to perform the following actions:
- You can use multiple lines in the footer using
the skip clause, as shown in this example:
btitle skip n SYSDATE;
In this example, n is the number of
lines to be skipped and defaults to one line. If n is set to
0, SQL*Plus returns to the beginning of the current line.
- You can align text to the left, right, or
center, or to a specified column, as follows:
btitle left 'Malden Power' center 'Quality Control' right SYSDATE;
btitle col 50 'Report';
- You can specify a number of tabs for alignment
of the footer, as shown in this example:
btitle tab n 'Malden Power';
In this example, n is an integer value;
a negative value skips backward, and a positive value skips forward.
- You can turn the title off and on without
affecting its definition, as shown in this example:
btitle off;
btitle on;
The clear Command
The clear command allows you to erase certain settings from
your current SQL*Plus environment using the following conditions:
- breaks—Erases all defined break
conditions set by the break command.
- buffer—Erases text from the current
buffer.
- columns—Erases all options set by the
column command.
- computes—Erases all options set by the
compute command.
- sql—Erases the SQL command buffer.
Before setting up new conditions (especially with the break
and compute commands), it’s a good idea to clear these options
first.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.
|
