Listing 2.3. continued

Sales Region       Total Sales
------------       -----------

North East         $99,204.00
South East         $72,900.00
Mid West           $921,892.00
Plains             $69,916.00
South West         $98,421.00
West               $1,027,653.00
North West         $87,367.00

Confidential


column sales_region clear
column sales_total clear
ttitle off
btitle off

In Listing 2.3, the format clause was used to control the positions of certain punctuation marks as well as output character suppression. Use the following formats to meet your requirements:

• 9—Represents a single zero suppression digit.
• ,—Represents the position of a comma.
• .—Represents the position of a period.
• $—Represents a floating dollar sign.
• An—Defines a display width of n for character and date columns.

Bind Variables

Bind variables are variables created outside PL/SQL but referenced within a PL/SQL block. In this chapter, you define bind variables as variables that are created in SQL*Plus. These variables can then be referenced in a PL/SQL program as if they were declared variables in the PL/SQL program. Bind variables are necessary because variables declared in PL/SQL programs are not accessible to SQL*Plus. Bind variables are used frequently to manage subprogram return codes.

To create a bind variable, use the variable command.

The Syntax for the variable Command
The syntax is

variable variable_name type

where variable name is the name of the bind variable that you want to create, and type is the datatype for the variable.

To reference the bind variable in a PL/SQL subprogram, precede the bind variable with a colon (:). For example, the following statement references the bind variable sales_total by assigning the value of zero to it in a PL/SQL program:

:sales_total :=0

To display the value of the sales_total bind variable, use the SQL*Plus print command, which is

print sales_total

Substitution Variables

A substitution variable is a user-defined variable name that is preceded by an ampersand (&). SQL*Plus treats the substitution variable as though it were the value of the substitution variable, rather than the variable itself. You can use substitution variables anywhere in SQL and SQL*Plus commands, except as the first command entered during a SQL*Plus session. If you do not supply a value for the substitution variable, SQL*Plus will prompt you for a value.

In the following examples, the variable sort1 is defined as position and the variable table1 is defined as master_parts. The first example uses substitution variables:

break on &sort1
SELECT * from &table1
ORDER BY &sort 1 asc

The next example uses the values of the substitution variables:

break on position
SELECT * from master_parts
ORDER BY position asc

Both of the preceding examples produce the same results.

The following system variables affect substitution variables:

• set scan—Turns substitution on and off.
• set define—Defines the substitution character. By default it is the ampersand (&).
• set escape—Defines the escape character that you would use before the substitution character. This character instructs SQL*Plus to treat the substitution character as an ordinary character.
• set verify on—Defines whether each line of the command file is listed before and after substitution.
• set concat—Defines the concatenation character. By default it is the period (.).

The following example uses the concatenation character. SQL*Plus will automatically query the user for the value of sales because it is not previously defined:

SQL> SELECT * from master_parts
WHERE parts_codes = `&c_parts.001'

Enter value for c_parts: 21345

This will be interpreted as:

SQL> SELECT * from master_sales
WHERE sales_region = `21345001'

Now look at a realistic example where the ORDER BY clause is used with the substitution variable. Notice in Listing 2.4 that SQL*Plus queries the user for input to every substitution variable.

INPUT/OUTPUT
Listing 2.4. ORDER BY clause with substitution variables.

SQL> SELECT * from master_personnel
SQL> WHERE job = `&c_job'
SQL> ORDER BY &c_job

Enter value for c_job: teacher
old SELECT * from master_personnel
old WHERE job = `&c_type'
new SELECT * from master_personnel
new WHERE job = `teacher'


Enter value for c_job: teacher
old ORDER BY &c_job
new ORDER BY `teacher'

Note from Listing 2.4 that you had to enter in the value for the substitution variable &c_job twice. SQL*Plus will prompt you for every substitution variable. You can avoid being prompted repeatedly for the same values by using the double ampersand (&&). This directs SQL*Plus to automatically define the substitution variable with && as equal to the substitution variable with one ampersand (&). This way, you can recode the preceding statement to avoid the reprompting as follows:

SELECT * from master_personnel
WHERE job = `&c_job'
ORDER BY &&c_job

Building a Simple Break Report

A break report is a report in which duplicate values are suppressed in the specified column. Additionally, in the break report, a line will be skipped every time a value changes in the break column. The following is the normal (non-break) output from the master_sales table:

OUTPUT

Monthly Sales
For June


sales_region         sales_totals
------------         ------------
North East           55100
North East           43104
North East           1004
South East           72900
Mid West             811460
Mid West             110432
Plains               69916
North West           31012
North West           21101
North West           5243
North West           30011

Confidential

Listing 2.5 contains the SQL*Plus commands that will be issued to create a break report.

INPUT/OUTPUT
Listing 2.5. Break report.

set pagesize 22
set linesize 60
set feedback off
ttitle `Monthly Sales|for June'
btitle `Confidential'
column sales_region heading `Sales Region' format A15
column sales_totals heading `Total Sales' format $99,999,999
break on sales_region skip 1

SELECT sales_region, sales_totals from master_sales
WHERE sales_month = `06'

Monthly Sales
For June


Sales Region         Total Sales
------------         -----------
North East           $55,100
                     $43,104
                     $1,004

South East           $72,900

Mid West             $811,460
                     $110,432

Plains               $69,916

                                                                             continues