Producing Readable Output with SQLPlus

1
Producing Readable Output with SQLPlus

2
Objectives

• At the end of this lesson, you should be able to
• Produce queries that require an input variable
• Customize the SQLPlus environment
• Produce more readable output
• Create and execute script files
• Save customizations

3
Interactive Reports
I want to input query values at runtime.
...sal ? deptno ? .. ename ? ...
User
4
Substitution Variables

• Use SQLPlus substitution variables to
  temporarily store values
• Single ampersand ()
• Double ampersand ()
• DEFINE and ACCEPT commands
• Pass variable values between SQL statements
• Dynamically alter headers and footers

5
Using the Substitution Variable

• Use a variable prefixed with an ampersand () to
  prompt the user for a value.

SQLgt SELECT empno, ename, sal, deptno 2
FROM emp 3 WHERE empno employee_num

Enter value for employee_num 7369 EMPNO
ENAME SAL DEPTNO ---------
---------- --------- --------- 7369 SMITH
800 20

6
Using the SET VERIFY Command

• Toggling the display of the text of a command
  before and after SQLPlus replaces substitution
  variables with values.

SQLgt SET VERIFY ON SQLgt SELECT empno, ename, sal,
deptno 2 FROM emp 3 WHERE empno
employee_num

Enter value for employee_num 7369 old 3 WHERE
empno employee_num new 3 WHERE empno
7369 ...

7
Character and Date Values with Substitution
Variables

• Use single quotation marks for date and character
  values.

SQLgt SELECT ename, deptno, sal12 2 FROM
emp 3 WHERE job'job_title'

Enter value for job_title ANALYST ENAME
DEPTNO SAL12 ---------- ---------
--------- SCOTT 20 36000 FORD
20 36000

8
Specifying Column Names, Expressions, and Text at
Runtime

• Use substitution variables to supplement
• A WHERE condition
• An ORDER BY clause
• A column expression
• A table name
• An entire SELECT statement

9
Specifying Column Names, Expressions, and Text at
Runtime
SQLgt SELECT empno, ename, job, column_name 2
FROM emp 3 WHERE condition 4 ORDER
BY order_column
Enter value for column_name sal Enter value for
condition salgt3000 Enter value for
order_column ename EMPNO ENAME JOB
SAL --------- ---------- ---------
--------- 7902 FORD ANALYST
3000 7839 KING PRESIDENT 5000
7788 SCOTT ANALYST 3000
10
Using the Substitution Variable

• Use the double-ampersand () if you want to
  reuse the variable value without prompting the
  user each time.

SQLgt SELECT empno, ename, job, column_name 2
FROM emp 3 ORDER BY column_name

Enter value for column_name deptno EMPNO
ENAME JOB DEPTNO ---------
---------- --------- --------- 7839 KING
PRESIDENT 10 7782 CLARK
MANAGER 10 7934 MILLER CLERK
10 ... 14 rows selected.

11
Defining User Variables

• You can predefine variables using one of two
  SQLPlus commands
• DEFINECreate a CHAR datatype user variable
• ACCEPTRead user input and store it in a variable
• If you need to use a single space when using the
  DEFINE command, you must enclose the space within
  single quotation marks.

12
The ACCEPT Command

• Creates a customized prompt when accepting user
  input
• Explicitly defines a NUMBER or DATE datatype
  variable
• Hides user input for security reasons

ACCEPT variable datatype FORMAT format
PROMPT text HIDE
13
Using the ACCEPT Command
ACCEPT dept PROMPT 'Provide the department name
' SELECT FROM dept WHERE dname
UPPER('dept') /

Provide the department name Sales DEPTNO
DNAME LOC --------- --------------
------------- 30 SALES CHICAGO

14
DEFINE and UNDEFINE Commands

• A variable remains defined until you either
• Use the UNDEFINE command to clear it
• Exit SQLPlus
• You can verify your changes with the DEFINE
  command.
• To define variables for every session, modify
  your login.sql file so that the variables are
  created at startup.

15
Using the DEFINE Command

• Create a variable to hold the department name.

SQLgt DEFINE deptname sales SQLgt DEFINE deptname

DEFINE DEPTNAME "sales" (CHAR)

• Use the variable as you would any other variable.

SQLgt SELECT 2 FROM dept 3 WHERE
dname UPPER('deptname')

16
Customizing the SQLPlus Environment

• Use SET commands to control current session.
• Verify what you have set by using the SHOW
  command.

SET system_variable value
SQLgt SET ECHO ON
SQLgt SHOW ECHO echo ON
17
SET Command Variables

• ARRAYSIZE 20 n
• COLSEP _ text
• FEEDBACK 6 n OFF ON
• HEADING OFF ON
• LINESIZE 80 n
• LONG 80 n
• PAGESIZE 24 n
• PAUSE OFF ON text
• TERMOUT OFF ON

18
Saving Customizations in the login.sql File

• The login.sql file contains standard SET and
  other SQLPlus commands that are implemented at
  login.
• You can modify login.sql to contain additional
  SET commands.

19
SQLPlus Format Commands

• COLUMN column option
• TTITLE text OFF ON
• BTITLE text OFF ON
• BREAK ON report_element

20
The COLUMN Command

• Controls display of a column
• CLEARClears any column formats
• FORMAT formatChanges the display of the column
  using a format model
• HEADING textSets the column heading
• JUSTIFY alignAligns the column heading to be
  left, center, or right

COLUMN columnalias option
21
Using the COLUMN Command

• Create column headings.

COLUMN ename HEADING 'EmployeeName' FORMAT
A15 COLUMN sal JUSTIFY LEFT FORMAT
99,990.00 COLUMN mgr FORMAT 999999999 NULL 'No
manager'

• Display the current setting for the ENAME column.

COLUMN ename

• Clear settings for the ENAME column.

COLUMN ename CLEAR
22
COLUMN Format Models
Result N/A 1234 01234 1234 L1234
1234.00 1,234
Example N/A 999999 099999 9999
L9999 9999.99 9,999
Element An 9 0 L . ,
Description Sets a display width of n Single
zero-suppression digit Enforces leading
zero Floating dollar sign Local currency Position
of decimal point Thousand separator
23
Using the BREAK Command

• Suppresses duplicates and sections rows
• To suppress duplicates

SQLgt BREAK ON ename ON job

• To produce grand totals

SQLgt BREAK ON report

• To section out rows at break values

SQLgt BREAK ON ename SKIP 4 ON job SKIP2
24
Using the TTITLE and BTITLE Commands

• Display headers and footers

TTITLE textOFFON

• Set the report header

SQLgt TTITLE 'SalaryReport'

• Set the report footer

SQLgt BTITLE 'Confidential'
25
Creating a Script File to Run a Report

• 1. Create the SQL SELECT statement.
• 2. Save the SELECT statement to a script
  file.
• 3. Load the script file into an editor.
• 4. Add formatting commands before the
  SELECT statement.
• 5. Verify that the termination character
  follows the SELECT statement.

26
Creating a Script File to Run a Report

• 6. Clear formatting commands after the SELECT
  statement.
• 7. Save the script file.
• 8. Enter START filename to run the script.

27
Sample Report
Fri Oct 24
page 1
Employee
Report Job Category Employee
Salary -----------------------
----------------------- ----------------- CLERK
ADAMS
1,100.00 CLERK JAMES
950.00 CLERK MILLER
1,300.00 CLERK
SMITH
800.00 MANAGER BLAKE
2,850.00 MANAGER CLARK
2,450.00 MANAGER JONES
2,975.00 SALESMAN ALLEN
1,600.00 SALESMAN
MARTIN 1,250.00 SALESMAN
TURNER
1,500.00 SALESMAN WARD
1,250.00
Confidential
28
Summary

• Substitution variables can be used in script
  files with the following
• Single ampersand
• Double ampersand
• ACCEPT command
• DEFINE command
• UNDEFINE command
• A command line

29
Practice Overview

• Creating a query to display values using
  substitution variables
• Starting a command file containing variables
• Using the ACCEPT command

30
(No Transcript)
31
(No Transcript)
32
(No Transcript)