Previous | Table of Contents | Next |
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 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 .
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 .
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:
btitle skip n SYSDATE;
btitle left 'Malden Power' center 'Quality Control' right SYSDATE; btitle col 50 'Report';
btitle tab n 'Malden Power';
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:
Before setting up new conditions ( especially with the break and compute commands), it s a good idea to clear these options first.
Previous | Table of Contents | Next |