USING SQL PLUS
We have already learned several SQL commands for reformatting data such as rtrim and string concatenation ‘||’. In addition to these commands SQL*Plus has specific commands for creating formatted reports. In this section we will discuss most of them. As with all the other SQL*Plus and PL/SQL exercises we have done so far, it is a good idea to save these exercises in a script for future use.
Editing SQL Statements:
On Windows the ed or edit command will by default edit your SQL contents in Notepad in Unix or Linux the default is the vi editor. It is possible to change that with the DEFINE _EDITOR command but most people don't. There are single line edit commands such as Append, Change, Del, List, Save, etc. that are available, however most of these are through backs to the days when we didn't have full page editors and are rarely used any more.
SPOOLING:
Spooling is a mechanism in SQL-PLUS that allows you echo all of your
commands and results to a file. We will use this for most of our labs.
Example:
SQL> SPOOL filename
When you are done spool, you can turn spooling of by issuing the following command:
SQL> SPOOL OFF
FORMATTING COLUMNS:
SELECT product_id, name, description, price FROM products WHERE product_id < 6;
Result:
PRODUCT_ID NAME
---------- ------------------------------
DESCRIPTION PRICE
-------------------------------------------------- ----------
1 Modern Science
A description of modern science 19.95
2 Chemistry
Introduction to Chemistry 30
3 Supernova
A star explodes 25.99
PRODUCT_ID NAME
---------- ------------------------------
DESCRIPTION PRICE
-------------------------------------------------- ----------
4 Tank War
Action movie about a future war 13.95
5 Z Files
Series on mysterious activities 49.99
Not Very Pretty!
CHANGING COLUMN
HEADINGS:
The column headings above are not very descriptive of the columns
contents you can change the headings to improve readability.
Example:
CLEAR COLUMNS
COLUMN Product_id
HEADING 'Product ID'
COLUMN name HEADING 'Product Name' FORMAT A13 WORD_WRAPPED
COLUMN description FORMAT A13 WORD_WRAPPED
COLUMN price
SELECT product_id, name, description, price FROM products WHERE product_id < 6;
Output:
Product ID Product Name DESCRIPTION PRICE
---------- ------------- ------------- -------
1 Modern A description $19.95
Science of modern
science
2 Chemistry Introduction $30.00
to Chemistry
3 Supernova A star $25.99
explodes
4 Tank War Action movie $13.95
Product ID Product Name DESCRIPTION PRICE
---------- ------------- ------------- -------
about a
future war
5 Z Files Series on $49.99
mysterious
activities
The CLEAR COLUMNS command is a safety feature to clear
any previous column changes. Without
this command, any previous changes that you made to column headings or formats
would still be in place. The next
commands make the required changes to the column headings.
CHANGING COLUMN
FORMATS IN A REPORT:
You can use the COLUMN command to change more than just the column
headings. You can also use the
COLUMN command to change the width of the column or the way the entries appear
in the column.
Example:
CLEAR COLUMNS
COLUMN slsr HEADING 'Sales Rep|Number/Name' FORMAT
A18
COLUMN cust HEADING 'Customer|Number/Name' FORMAT
A18
COLUMN bal HEADING 'Current|Balance' FORMAT $9,990.99
COLUMN cred HEADING 'Credit|Limit' FORMAT $9,990.99
COLUMN avail HEADING 'Available|Credit' FORMAT
$9,990.99
SELECT * FROM report1;
OutPut:
Sales
Rep
Customer
Current
Credit Available
Number/Name
Number/Name Balance
Limit Credit
------------------ ------------------ ---------- ---------- ----------
3 - Mary Jones 124
- Sally Adams $818.75
$1,000.00 $181.25
3 - Mary Jones 412
- Sally Daniel $1,817.50
$2,000.00 $182.50
3 - Mary Jones 622
- Dan Marin $945.74 $1,000.00
$54.26
12 - Miguel Diaz 311 - Don
Charles $825.75
$1,000.00 $174.25
12 - Miguel Diaz 405 - Al
Williams $402.75
$1,500.00 $1,097.25
12 - Miguel Diaz 522 - Mary
Nelson $98.75
$1,500.00 $1,401.25
The first two COLUMN commands change the format for SLSR
and CUST columns to A18. The letter
A indicates that the column is alphanumeric (another name for character); the 18
indicates that the column is to be 18 characters long.
The next three COLUMN commands change the format of the three numeric columns in the view. In each case, the new format is $9,990.99. The 9s indicated that the value is numeric. The two 9s to the right of the decimal point indicate that each number will display with two decimal places. The total number of 9s indicates the size of the column by representing the largest number that can be displayed. The dollar sign indicates that the values will display as currency. Finally, the zero immediately to the left of the decimal point indicates that a value of zero will display as $0.00. If you use all nines ($9,999.99), a value of zero will not display, zero values would be left blank.
You can also just clear a single column with SQL> COLUMN product_id CLEAR
SETTING PAGE AND LINE SIZE
SET PAGESIZE nnn -- Sets the number lines per page.
SQL> SET PAGESIZE 100
SQL> SELECT product_id, name, description, price FROM products WHERE product_id < 6;
Output:
Product ID Product Name DESCRIPTION PRICE
---------- ------------- ------------- -------
1 Modern A description $19.95
Science of modern
science
2 Chemistry Introduction $30.00
to Chemistry
3 Supernova A star $25.99
explodes
4 Tank War Action movie $13.95
about a
future war
5 Z Files Series on $49.99
mysterious
activities
Notice the second header is now gone.
SET LINESIZE nnn -- This will set the number of characters per each line.
SQL> SET LINESIZE 60
SQL> SELECT * FROM customers;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB
----------- ---------- ---------- ---------
PHONE
------------
1 John Brown 01-JAN-65
800-555-1211
2 Cynthia Green 05-FEB-68
800-555-1212
3 Steve White 16-MAR-71
800-555-1213
4 Gail Black
800-555-1214
5 Doreen Blue 20-MAY-70
SQL> SET LINESIZE 80
SQL> SELECT * FROM customers;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- --------- ------------
1 John Brown 01-JAN-65 800-555-1211
2 Cynthia Green 05-FEB-68 800-555-1212
3 Steve White 16-MAR-71 800-555-1213
4 Gail Black 800-555-1214
5 Doreen Blue 20-MAY-70
USING VARIABLES:
Temporary Variables are only valid for the SQL statement in which they are referenced.
Defined Variables is created independent of a SQL statement and persist until you explicitly remove it or exit SQL*Plus with the UNDEFINE command..
Variables are most useful when scripting SQL statements that will be run at a later date and a value for the SQL statement may vary each time it is executed. Defined variables are the most useful for this.
Example - Temporary Variable:
SELECT product_id, name, price FROM products
WHERE product_id = &product_id_var;
Enter value for product_id_var: 2
old 2: WHERE product_id = &product_id_var
new 2: WHERE product_id = 2
Product ID Product Name PRICE
---------- ------------- -------
2 Chemistry $30.00
You can change the Temporary variable character with the SET DEFINE command. Also you can element the echo of 'old' and 'new' with the SET VERIFY OFF
SQL> SET VERIFY OFF
SQL> SET DEFINE '#'
SQL> SELECT product_id, name, price FROM products
WHERE product_id = #product_id_var;
Enter value for product_id_var: 3
Product ID Product Name PRICE
---------- ------------- -------
3 Supernova $25.99
You can use temporary variables almost anywhere in the SQL Statement, including in columns and even the table name.
SELECT name, &col_var FROM &table_var
WHERE &col_var = &col_val;
If you use the same variable twice you can double the variable character '&&' and it will not prompted you twice.
SELECT name, &&col_var FROM &table_var
WHERE &&col_var = &col_val;
Example - DEFINEd Variables:
SQL> DEFINE product_id_var = 7
SQL> DEFINE product_id_var
DEFINE PRODUCT_ID_VAR = "7" (CHAR)
SQL> DEFINE -- By itself will show you all variables defined.
SELECT product_id, name, price FROM products
WHERE product_id = &product_id_var;
PRODUCT_ID NAME PRICE ---------- ------------------------------ ---------- 7 Space Force 9 13.49
ACCEPT variable_name [type] [FORMAT format] [PROMPT prompt] [HIDE]
SQL> ACCEPT customer_id_var NUMBER FORMAT 99 PROMPT 'Customer ID: '
SQL> ACCEPT date_var DATE FORMAT 'DD-MON-YYYY' PROMPT 'Date: '
SQL> ACCEPT passward_var CHAR PROMPT 'Password: ' HIDE
SQL> DEFINE;
DEFINE _DATE = "05-FEB-07" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "XE" (CHAR)
DEFINE _USER = "SYSTEM" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Express Edition Release 10.2.0.1.0 -
Production" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
DEFINE PRODUCT_ID_VAR = "7" (CHAR)
DEFINE CUSTOMER_ID_VAR = 2 (NUMBER)
DEFINE PASSWARD_VAR = "williwig" (CHAR)
SQL> UNDEFINE date_var
As I mentioned earlier one of the most useful places for variables is in a script.
report1.sql:
SET ECHO OFF
SET VERIFY OFF
SELECT product_id, name, price
FROM products
WHERE product_id = &product_id_var;
SQL> @report1.sql
Enter value for product_id_var: 3
PRODUCT_ID NAME PRICE
---------- ------------------------------ ----------
3 Supernova 25.99
-- VERSIION 2:
SET ECHO OFF
SET VERIFY OFF
ACCEPT product_id_var NUMBER FORMAT 99 PROMPT 'Enter Product ID: '
SELECT product_id, name, price
FROM products
WHERE product_id = &product_id_var;
-- cleanup UNDEFINE product_id_var
SQL> @c:\timlin\cis360_1\report1.sql;
Enter Product ID: 4
PRODUCT_ID NAME PRICE
---------- ------------------------------ ----------
4 Tank War 13.95
PASSING a Value into a Script: Variables can be passed into scripts using a numeric reference. This is the same way it works in Unix & Perl scripting. I.e 1 is the first value passed in, 2 is the second and so on.
SET ECHO OFF
SET VERIFY OFF
SELECT product_id, name, price
FROM products
WHERE product_id = &1 AND price > &2;
SQL> @report1.sql 3 10
PRODUCT_ID NAME PRICE
---------- ------------------------------ ----------
3 Supernova 25.99
ADDING A HEADER
& FOOTER TO A REPORT:
The next step is add a title to the report.
To add a title at the top of the report, use the TTITLE (top) command.
To add a title at the bottom of the report, use the BTITLE command.
Then you include the desired title in the TTITLE command.
If the title will extend over two lines, separate the two portions by a
vertical line.
Example:
CLEAR COLUMNS
TTITLE 'Product Report'
BTITLE 'Thanks for running the report'
SET ECHO OFF
SET VERIFY OFF
SET PAGESIZE 30
SET LINESIZE 70
COLUMN product_id HEADING 'ID' FORMAT 99
COLUMN name HEADING 'Product Name' FORMAT A20 WORD_WRAPPED
COLUMN description HEADING 'Product Description' FORMAT A30 WORD_WRAPPED
COLUMN price HEADING 'Price' FORMAT $99.99
SELECT
product_id, name, description, price FROM products;
CLEAR COLUMNS
Mon Feb 05 page 1
Product Report
ID Product Name Product Description Price
--- -------------------- ------------------------------ -------
1 Modern Science A description of modern $19.95
science
2 Chemistry Introduction to Chemistry $30.00
3 Supernova A star explodes $25.99
4 Tank War Action movie about a future $13.95
war
5 Z Files Series on mysterious $49.99
activities
6 2412: The Return Aliens return $14.95
7 Space Force 9 Adventures of heroes $13.49
8 From Another Planet Alien from another planet $12.99
lands on Earth
9 Classical Music The best classical music $10.99
10 Pop 3 The best popular music $15.99
11 Creative Yell Debut album $14.99
12 My Front Line Their greatest hits $13.49
Thanks for running the report
12 rows selected.
In order for the title to display appropriately, you can adjust the line size by using the SET LINESIZE command. The line size determines where the title appears when it is centered across the line. In the above report the SET LINESIZE command is used to set the line size to 70 characters. In this report, a line size of 70 characters is appropriate and places the title in the correct position on the line
COMPUTING SUBTOTALS:
Example:
BREAK ON product_type_id
COMPUTE SUM OF price ON product_type_id
SET ECHO OFF
SET VERIFY OFF
SET PAGESIZE 50
SET LINESIZE 70
CLEAR COLUMNS
COLUMN price HEADING Price FORMAT $999.99
SELECT product_type_id, name, price FROM products
ORDER BY product_type_id;
CLEAR COLUMNS
Ouput:
Mon Feb 05 page 1
Product Report
PRODUCT_TYPE_ID NAME Price
--------------- ------------------------------ --------
1 Modern Science $19.95
Chemistry $30.00
*************** --------
sum $49.95
2 Z Files $49.99
Tank War $13.95
Supernova $25.99
2412: The Return $14.95
*************** --------
sum $104.88
3 Space Force 9 $13.49
From Another Planet $12.99
*************** --------
sum $26.48
4 Classical Music $10.99
Pop 3 $15.99
Creative Yell $14.99
*************** --------
sum $41.97
My Front Line $13.49
*************** --------
sum $13.49
Thanks for running the report
12 rows selected.
The COMPUTE command uses the standard SQL statistical functions to
calculate values to include in the report.
Recall these functions are: AVG, COUNT, MAX, MIN, STDEV, SUM, VARIANCE.
Any of these functions could have been used in place of the SUM in the
COMPUTE command, however SUM was appropriate for the purpose of this report.
On the last two COMPUTE commands, the keyword REPORT is
used in place of a column name. This
indicates that we wished this computation to take place at the end of the
report.
There are two other commands in the above example that
are useful if the report will be displayed on the screen: SET PAGESIZE and SET PAUSE.
In this example, the SET PAGESIZE command sets the number of lines that
display on a single page of the report to 24 (the screen line size).
The SET PAUSE ON command causes the display of the report to pause after
each screen. To display the next
screen, press the Enter key.
Many times you will not want to display the report to the screen but might want to send it to a file to save or to print. Doing this is simple, we just make the first command in the report script the “SPOOL filename” and the last command “SPOOL OFF”.
Automatically Generiting SQL Statements: You can query the Oracle System tables (which are meta-data: data about data) to generate SQL Statements, in fact itself does this.
SELECT 'DROP TABLE ' || table_name || ';' FROM user_tables;
COMMAND
SUMMARY:
COMMAND
DESCRIPTION
BREAK ON Groups data in a report on a specified column.
BTITLE Adds a title at the bottom of a report.
BTITLE OFF Clears any previously specified title at the bottom of the report.
CLEAR BREAK Clears any previously specified report break.
CLEAR COLUMNS Clears any previous column changes.
CLEAR COMPUTE Clears any previously specified report computations.
COLUMN Changes the name of a column.
COMPUTE Calculates a count, minimum, maximum, sum, average, standard deviation, or a variance on the values in a column in a report.
HEADING Assigns a new column heading.
ON REPORT Indicates that a calculation is to be performed on all values in the report.
SET FEEDBACK OFF Turns of the message indicating the number of rows selected by a query in a report
SET LINESIZE Indicates the maximum number of characters on a line.
SET PAGESIZE Indicates the number of lines on a page.
SET PAUSE Indicates whether the screen display pauses after each screen of data.
SKIP n Inserts n blank line(s) between groups in a report.
SPOOL Sends query output to a file that can be printed.
TTITLE Adds a title at the top of a report.
TTITLE OFF Clears any previously specified title at the top of a report.