Oracle8 Server Unleashed:Oracle8 Tools

SQL*Plus Execute Commands

These commands are used to initiate the processing of SQL statements and PL/SQL blocks, measure the processing time of SQL or PL/SQL statements, execute non-Oracle programs, execute SQL*Forms programs, or attain additional help.

/ Execute the SQL statement or PL/SQL block currently in the SQL buffer. (This is probably the most used of the SQL*Plus commands.)

• HELP topic Provides online assistance with SQL, PL/SQL, or SQL*Plus commands

• HOST Executes non-Oracle commands (operating system-dependent) without leaving SQL*Plus

• RUN Displays and executes the contents of the SQL buffer

• RUNFORM Executes a SQL*Forms program without leaving SQL*Plus

• TIMING Displays the system CPU time with the SQL prompt

SQL*Plus Editing Commands

The SQL buffer is a work area assigned to the SQL*Plus environment. This buffer only contains SQL or PL/SQL syntax. The contents of this buffer can be loaded, saved, and manipulated with the following commands.

• A or APPEND new text Appends text to the end of the current line of the SQL buffer.

• C or CHANGE/target text/new text/ Changes the target text to the new text on the current line in the SQL buffer.

• DEL Deletes the current line in the SQL buffer.

• EDIT filename Utilizes an operating system-dependent text editor. To edit the SQL buffer with an operating system-dependent text editor, simply leave off the filename.

• GET filename Reads an operating system-dependent file into the SQL buffer.

• I or INPUT text Adds the text after the current line in the SQL buffer.

• L or LIST number Displays the contents of the SQL buffer. When the number syntax is used, LIST will display the line number and make that line the current line in the SQL buffer.

• SAVE filename Saves the contents of the SQL buffer to an operating system- dependent file.

Tip:
An excellent method to utilize when creating SQL*Plus Command Files, utilizes these editing features to arrive at the query results desired: SAVE to the operating system, and then edit that file with EDIT to add the formatting and other desired features.

• START filename param1 param2 ... START will execute the contents of the SQL*Plus command file named in filename and pass any input parameters to the SQL*Plus command file.

Tip:
I find it convenient to utilize this START feature when creating various database objects. It allows for complete control over the order in which the objects are created. I simply create a SQL*Plus command file named INSTALL.SQL, create each DDL statement in its own SQL*Plus command file, and add a START command in this INSTALL.SQL file for each of the DDL SQL*Plus command files.

SQL*Plus Formatting Commands

The SQL*Plus formatting commands are used to manipulate the result set from a SQL query.

• BREAK ON column_name options This command controls the organization of rows returned by the query. BREAK can manipulate the appearance of the output by specifiying under what conditions a BREAK should occur and what actions should be taken at the BREAK. The output appearance can be controlled by skipping a line, skipping to top of next page, and providing totals when used in conjunction with COMPUTE. Any number of lines can be skipped at a BREAK point. BREAK points can be defined at the column level, for multiple columns, on a row, on a page, or on a report. See COMPUTE for BREAK examples. Entering BREAK by itself at the SQL prompt will display the current BREAK settings.

• BTITLE print_options and/or text or variable options BTITLE is used to place text at the bottom of each page. There are various print options to position text at various locations. BTITLE will simply center the text if no print options are specified. PRINT OPTIONS include BOLD, CENTER, COL, FORMAT, LEFT, RIGHT, SKIP, and TAB. BTITLE spelled out by itself will display the current text setting. Other options that can be specified are ON and OFF. BTITLE is on by default.

• CLEAR options Resets any of the SQL*Plus formatting commands, as well as clearing the screen.

• COLUMN column_name options COLUMN is used to alter the default display attributes for a given column (column_name) of a SQL query. There are a variety of options but the more common ones are FORMAT, HEADING, JUSTIFY, NEWLINE, NEW_VALUE, and NOPRINT. The FORMAT option is useful in applying editing to numeric fields, date masks to date fields, and specific lengths to variable length character fields. The HEADING option overrides the SQL*Plus default heading for the particular column. The JUSTIFY option overrides the SQL*Plus column alignment to the heading default. The NEWLINE option will print the column on the beginning of the next line. NEW_VALUE assigns the contents of the column to a SQL*Plus variable (see DEFINE). This value can then be used in conjunction with TTITLE or to store intermediate results for master/detail type reports and is useful to store and pass information between two or more separate SQL statements.
Examples:


COLUMN sal FORMAT $99,999.00 HEADING Salary



COLUMN home_dir NEW_VALUE home_path NOPRINT

The first SQL query would reference the home_dir; all other SQL queries would then reference the home_path for the information returned by the first SQL query.

• COMPUTE function OF options ON BREAK options COMPUTE calculates and prints totals for groupings of rows defined by the BREAK command. A variety of standard functions can be utilized. The most common option is the name of the column in the query that the total is to be calculated on. The BREAK option determines where the totals are to be printed and reset, as defined by the BREAK command.
Examples:


BREAK ON sales_rep SKIP 2



BREAK ON REPORT



COMPUTE SUM OF monthly_sales ON sales_rep



COMPUTE SUM OF commissions ON sales_rep



COMPUTE SUM OF monthly_sales ON REPORT



COMPUTE SUM OF commissions ON REPORT

This list will produce a report with totals of monthly_sales and commissions when the sales_rep column value changes, and then skip two lines and produce monthly_sales and commissions totals at the end of the report.

Note:
The COMPUTE command resets the accumulator fields back to zero after printing.

• TTITLE print_options and/or text or variable options TTITLE is used to place text at the top of each page. There are various print options to position text at various locations. TTITLE will center the text and add date and page numbers if no print options are specified. PRINT OPTIONS include BOLD, CENTER, COL, FORMAT, LEFT, RIGHT, SKIP, and TAB. TTITLE spelled out by itself will display the current text setting. Other options that can be specified are ON and OFF. TTITLE is on by default.

Table of Contents

Используются технологии uCoz