090-094

Previous

Table of Contents

Next

Table 3.4 lists the format model elements for numerical values. SQL*Plus will always round to the specified number of significant digits. If a column has no format model, the column s width defaults to the value of the numwidth SQL setting ( numwidth is discussed later in this chapter).

Table 3.4 Valid number format models.

Element	Explanation
9	Represents a digit. If no digit occupies this space, it will be blank.
	Represents a digit. If no digit occupies this space, a leading 0 will be used.
$	Prefixes a value with a dollar sign.
B	Displays a value of zero as blank, not 0.
MI	Displays a negative sign (-) after a negative value.
PR	Displays a negative value in angle brackets (<>).
,	Displays a comma in this position.
.	Displays a period in this position.
V	Multiplies the value by n , where n is the number of digits following V .
E	Displays the value in scientific notation (the format model must contain four Es).

All of these examples contain valid formats for numbers :

 column account_balance format 9,999.99 column account_balance format 
 column account_balance format $999,999.99 column account_balance format $000,000.09 column account_balance format $099999.99 column variance format 9.999EEEE column account_balance format $999999.99MI 
 0,000.09 column account_balance format 9999.99 column variance format 9.999EEEE column account_balance format 9999.99MI 

The compute Command

The compute command allows basic mathematical operations to be carried out when a break event is reached. This command works only with the break command. The following eight types of operations can be performed with the compute command:

    avg ”Calculates the average value for the column. This may only be used for columns of a numerical datatype.

    count ”Counts all non- NULL values in the column. This can be used for columns of any datatype.

    max ”Determines the highest value of the column. This may only be used for numerical and character datatypes.

    min ”Determines the lowest value of the column. Like max , this may only be used for numerical and character datatypes.

    number ”Counts all rows. Like count , this may be used on columns of any datatype.

    std ”Determines the standard deviation of the column from the mean. This may only be used on columns of a numerical datatype.

    sum ”Reports the total of the values in the column. This may only be used on columns of a numerical datatype.

    variance ”Calculates the variance of the column. This may only be used on columns of a numerical datatype.

More than one operation can be performed on any given column, as shown in this example:

 compute avg number max of length on type; 

The values that compute generates can be stored in variables , as follows :

 compute avg of length on type into avglength; 

When used alone, the compute command displays all the compute operations that are currently in effect.

The define Command

The define command allows you to see the status of variables inside SQL*Plus. This is typically used with variables that have been created using the accept command. Typing define alone will show the status of the variables that are currently available. You may also type define <variable> to see the status of a particular variable.

The host Command

The host command allows you to execute a command at the OS level from inside SQL*Plus. When the command has finished executing, control returns to SQL*Plus.

The pause Command

The pause command allows you to halt a script and require the user to press a carriage return, typically as acknowledgment of a status or error message that the script has written.

The remark Command

The remark command (more commonly known as rem ) is used at the start of a line and instructs SQL*Plus to proceed to the next line. This command is still used, although it has been supplanted to some extent by the use of single and multiple line comments.

Previous

Table of Contents

Next