You can use temporary and defined variables in a SQL*Plus script. This allows you to create scripts that prompt the user for entry of variables that can then be used to generate reports. You ll find the SQL*Plus scripts referenced in this section in the Zip file you can download from this book s web site.
Tip | Bear in mind that SQL*Plus was not specifically designed as a reporting tool. If you have complex reporting requirements, you should use software like Oracle Reports. |
The following script report1.sql uses a temporary variable named product_id_var in the WHERE clause of a SELECT statement:
-- suppress display of the statements and verification messages SET ECHO OFF SET VERIFY OFF SELECT product_id, name, price FROM products WHERE product_id = &product_id_var;
The SET ECHO OFF command stops SQL*Plus from displaying the SQL statements and commands in the script. SET VERIFY OFF suppresses display of the verification messages. I put these two commands in to minimize the number of extra lines displayed by SQL*Plus when you run the script.
You can run report1.sql in SQL*Plus using the @ command. For example:
SQL> @ C:\SQL\report1.sql Enter value for product_id_var: 2 PRODUCT_ID NAME PRICE ---------- ------------------------------ ---------- 2 Chemistry 30
You can give this script to another user and they can run it without them having to know SQL.
The following script (named report2.sql ) uses the ACCEPT command to define a variable named product_id_var :
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; -- clean up UNDEFINE product_id_var
Notice that a user-friendly prompt is specified for the entry of product_id_var , and that product_id_var is removed at the end of the script ”this makes the script cleaner.
You can run the report2.sql script using SQL*Plus:
SQL> @ C:\SQL\report2.sql Enter product id: 4 PRODUCT_ID NAME PRICE ---------- ------------------------------ ---------- 4 Tank War 13.95
You can pass a value to a variable when you run your script. When you do this, you reference the variable in your script using a number. The following script report3.sql shows an example of this; notice the variable is identified using & 1 :
SET ECHO OFF SET VERIFY OFF SELECT product_id, name, price FROM products WHERE product_id = &1;
When you run report3.sql , you supply the variable s value after the script name. The following example passes the value 3 to report3.sql :
SQL> @ C:\SQL\report3.sql 3 PRODUCT_ID NAME PRICE ---------- ------------------------------ ---------- 3 Supernova 25.99
You can add any number of parameters, with each value specified on the command line corresponding to the matching number in the file. The first parameter corresponds to & 1 , the second to & 2 , and so on. The following script report4.sql shows an example of this:
SET ECHO OFF SET VERIFY OFF SELECT product_id, product_type_id, name, price FROM products WHERE product_type_id = &1 AND price > &2;
The following example run of report4.sql shows the addition of two values for & 1 and & 2 , which are set to 1 and 9.99, respectively:
SQL> @ C:\SQL\report4.sql 1 9.99 PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE ---------- --------------- ------------------------------ ---------- 1 1 Modern Science 19.95 2 1 Chemistry 30
Because & 1 is set to 1, the product_type_id column in the WHERE clause is set to 1. Also, & 2 is set to 9.99, so the price column in the WHERE clause is set to 9.99. Therefore, rows with a product_type_id of 1 and a price greater than 9.99 are displayed.
You add a header and footer to your report using the TTITLE and BTITLE commands. The following script report5.sql shows this:
TTITLE 'Product Report' BTITLE 'Thanks for running the report' SET ECHO OFF SET VERIFY OFF SET PAGESIZE 30 SET LINESIZE 70 CLEAR COLUMNS COLUMN product_id HEADING ID FORMAT 99 COLUMN name HEADING 'Product Name' FORMAT A20 WORD_WRAPPED COLUMN description HEADING Description FORMAT A30 WORD_WRAPPED COLUMN price HEADING Price FORMAT .99 SELECT product_id, name, description, price FROM products; CLEAR COLUMNS
The following example shows a run of report5.sql :
SQL> @ C:\SQL\report5.sql Fri May 16 page 1 Product Report ID Product Name Description Price --- -------------------- ------------------------------ ------- 1 Modern Science A description of modern .95 science 2 Chemistry Introduction to Chemistry .00 3 Supernova A star explodes .99 4 Tank War Action movie about a future .95 war 5 Z Files Series on mysterious .99 activities 6 2412: The Return Aliens return .95 7 Space Force 9 Adventures of heroes .49 8 From Another Planet Alien from another planet .99 lands on Earth 9 Classical Music The best classical music .99 10 Pop 3 The best popular music .99 11 Creative Yell Debut album .99 12 My Front Line Their greatest hits .49 Thanks for running the report
You can add a subtotal for a column using a combination of the BREAK ON and COMPUTE commands. BREAK ON causes SQL*Plus to break up output based on a change in a column value, and COMPUTE causes SQL*Plus to compute a value for a column.
The following script report6.sql shows how to compute a subtotal for products of the same type:
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 9.99 SELECT product_type_id, name, price FROM products ORDER BY product_type_id; CLEAR COLUMNS
The following example shows a run of report6.sql :
SQL> @ C:\SQL\report6.sql PRODUCT_TYPE_ID NAME Price --------------- ------------------------------ -------- 1 Modern Science .95 Chemistry .00 *************** -------- sum .95 2 Supernova .99 Tank War .95 Z Files .99 2412: The Return .95 *************** -------- sum 4.88 3 Space Force 9 .49 From Another Planet .99 *************** -------- sum .48 4 Classical Music .99 Pop 3 .99 Creative Yell .99 *************** -------- sum .97 My Front Line .49 *************** -------- sum .49
Notice that whenever a new value for product_type_id is encountered , SQL*Plus breaks up the output and computes a sum for the price columns for the rows with the same product_type_id . The product_type_id value is only shown once for rows with the same product_type_id . For example, Modern Science and Chemistry are both books and have a product_type_id of 1, and 1 is shown once for Modern Science. The sum of the prices for these two books is $49.95. The other sections of the report contain the sum of the prices for products with different product_type_id values.