■■用 iSQL*Plus 生成易读的输出
■替换变量
用 iSQL*Plus，你可以创建有提示的报表，使用替换变量提示用户提供他们自己的值来限制返回数据的范围。你可以在命令文件或在单个的 SQL 语句中嵌入替换变量。一个变量可以被认为是一个容器，在其中值被临时存储，当语句运行时，值被替换。
iSQL*Plus 替换变量用于：
:: 临时存储变量
-单 & 符号
-双 & 符号
-DEFINE 命令
:: 在 SQL 语句之间传递变量
:: 动态改变页眉和页脚
■替换变量
在 iSQL*Plus 中，你可以使用单个的 & 替换变量临时存储值。
你可以用 DEFINE 命令在 iSQL*Plus 中预先确定变量。DEFINE 创建并指定一个只到变量。
限制数据范围的例子
报告只获得当前的范围或指定的日期范围
报告涉及的数据只与用户请求的报告有关
只显示给定部门中的人员
其他的交互式作用
交互作用不限制用户直接与 WHERE 子句的交互。同样的规则可以用于完成其他目的。例如：
动态改变页眉和页脚
从文件而不是从人那里获得输入值
从一个 SQL 语句到另一个 SQL 语句传递值
iSQL*Plus 不支持用户输入的确认检查。
注释
替换变量可以用在 SQL 和 iSQL*Plus 命令的任何地方，除了在命令提示符后作为第一个单词。
■使用 & 替换变量
用一个带有前缀 & 的变量提示用户输入一个值
eg:
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num;
Single-Ampersand Substitution Variable(& 替换变量)
When running a report, users often want to restrict the data returned dynamically. iSQL*Plus provides this flexibility by means of user variables. Use an ampersand (&) to identify each variable in your SQL statement. You do not need to define the value of each variable.
Notion Description
&user_variable Indicates a variable in a SQL statement;if the variable does not exist,
iSQL*Plus prompts the user for a value(iSQL*Plus discards a new variable once it is used.)
The example on the slide creates an iSQL*Plus substitution variable for an employee number. When the statement is executed, iSQL*Plus prompts the user for an employee number and then displays the employee number, last name, salary, and department number for that employee.
With the single ampersand, the user is prompted every time the command is executed, if the variable does not exist.
When iSQL*Plus detects that the SQL statement contains an &, you are prompted to enter a value for the substitution variable named in the SQL statement.
Once you enter a value and click the Submit for Execution button, the results are displayed in the output area of your iSQL*Plus session.
･替换变量的字符和数字值
日期和字符值放在单引号之间
eg:
SELECT last_name, department_id, salary*12
FROM employees
WHERE job_id = '&job_title' ;
Specifying Character and Date Values with Substitution Variables
In a WHERE clause, date and character values must be enclosed within single quotation marks. The same rule applies to the substitution variables.
Enclose the variable in single quotation marks within the SQL statement itself.
The slide shows a query to retrieve the employee names, department numbers, and annual salaries of all employees based on the job title value of the iSQL*Plus substitution variable.
Note: You can also use functions such as UPPER and LOWER with the ampersand. Use UPPER('&job_title') so that the user does not have to enter the job title in uppercase.
･指定列名、表达式和文本
用替换变量可以提供：
WHERE 条件
ORDER BY 子句
列表达式
表名
输入 SELECT 语句
Specifying Column Names, Expressions, and Text
Not only can you use the substitution variables in the WHERE clause of a SQL statement, but these variables can also be used to substitute for column names, expressions, or text.
eg:
Display the employee number and any other column and any condition of employees.
SELECT employee_id, &column_name
FROM employees
WHERE &condition;
If you do not enter a value for the substitution variable, you will get an error when you execute the preceding statement.
Note: A substitution variable can be used anywhere in the SELECT statement, except as the first word entered at the command prompt.
･指定列名、表达式和文本
SELECT employee_id, last_name, job_id,
&column_name
FROM employees
WHERE &condition
ORDER BY &order_column ;
Specifying Column Names, Expressions, and Text (continued)
The slide example displays the employee number, name, job title, and any other column specified by the user at run time, from the EMPLOYEES table. You can
also specify the condition for retrieval of rows and the column name by which the resultant data has to be ordered.
･定义替换变量
:: 可以用 iSQL*Plus DEFINE 命令预先定义变量
DEFINE variable = value 创建一个 CHAR 数据类型的用户变量
:: 如果需要预先定义一个带空格的变量，在使用 DEFINE 命令时，必须将变量放在单引号中
:: 一个已定义的变量在本会话期间有效
Defining Substitution Variables
You can predefine user variables before executing a SELECT statement. iSQL*Plus provides the DEFINE command for defining and setting substitution variables:
Command Description
DEFINE variable=value Creates a user variable with the CHAR data and assigns a value to it
DEFINE variable Displays the variable,its value,and its data type
DEFINE Displays all user variables with their values and data types
･DEFINE 和 UNDEFINE 命令
:: 一个变量保持其定义，直到：
- 使用 UNDEFINE 命令清除它
- 退出 iSQL*Plus
:: 可以用 DEFINE 命令检验所做的改变
SQL> define tes='hha'
SQL> define tes
DEFINE TES = "hha" (CHAR)
SQL> undefine tes
SQL> define tes
SP2-0135: 符号 tes 未定义
SQL>
The DEFINE and UNDEFINE Commands
Variables are defined until you either:
Issue the UNDEFINE command on a variable
Exit iSQL*Plus
When you undefine variables, you can verify your changes with the DEFINE command. When you exit iSQL*Plus, variables defined during that session are lost.
･使用带 & 替换变量的 DEFINE 命令
:: 使用DEFINE命令创建替换变量
DEFINE employee_num = 200
:: 在 SQL 语句中用一个带 & 前缀的变量来替换
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;
Using the DEFINE Command
The example on the slide creates an iSQL*Plus substitution variable for an employee number by using the DEFINE command, and at run time displays the mployee number, name, salary, and department number for that employee.
Because the variable is created using the iSQL*Plus DEFINE command, the user is not prompted to enter a value for the employee number. Instead, the defined
variable value is automatically substituted in the SELECT statement.
The EMPLOYEE_NUM substitution variable is present in the session until the user undefines it or exits the iSQL*Plus session.
■使用 && 替换变量
如果你想要重复使用变量的值而不需要每次提示用户输入，可以使用 &&
SELECT employee_id, last_name, job_id, &&column_name
FROM employees
ORDER BY &column_name;
Double-Ampersand Substitution Variable
You can use the double-ampersand (&&) substitution variable if you want to reuse the variable value without prompting the user each time. The user will see the prompt for the value only once. In the example on the slide, the user is asked to give the value for variable column_name only once. The value supplied by the user (department_id) is used both for display and ordering of data.
iSQL*Plus stores the value supplied by using the DEFINE command; it will use it again whenever you reference the variable name. Once a user variable is in place, you need to use the UNDEFINE command to delete it.
■使用 VERIFY 命令
用 VERIFY 命令来交替显示，替换变量在 iSQL*Plus 执行替换之前和之后的值
eg:
SQL> set verify on
SQL> create table test1 (t number);
表已创建。
SQL> edit;
已写入 file afiedt.buf
1 begin
2 for i in 1..10
3 loop
4 insert into test1 values(i);
5 end loop;
6 commit;
7* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> select * from test1;
T
----------
1
2
3
4
5
6
7
8
9
10
已选择10行。
SQL> select * from test1 where t=&t;
输入 t 的值: 12
原值 1: select * from test1 where t=&t
新值 1: select * from test1 where t=12
未选定行
SQL> set verify off;
SQL> select * from test1 where t=&t;
输入 t 的值: 10
T
----------
10
The VERIFY Command
To confirm the changes in the SQL statement, use the iSQL*Plus VERIFY command. Setting SET VERIFY ON forces iSQL*Plus to display the text of a command before and after it replaces substitution variables with values.
The example on the slide displays the old as well as the new value of the EMPLOYEE_ID column.
■定制 iSQL*Plus 环境
:: 用SET命令控制当前会话 SET system_variable value
:: 用 SHOW 命令检验所做的设置
SET ECHO ON
SHOW ECHO
echo ON
Customizing the iSQL*Plus Environment
You can control the environment in which iSQL*Plus is currently operating by using the SET commands.
Syntax
SET system_variable value
In the syntax:
system_variable is a variable that controls one aspect of the session environment
value is a value for the system variable
You can verify what you have set by using the SHOW command. The SHOW command on the slide checks whether ECHO had been set on or off.
To see all SET variable values, use the SHOW ALL command.
For more information, see iSQL*Plus User’s Guide and Reference, “Command Reference.”
･SET 命令变量
･SET Command variables
SET Variable and Values Discription
ARRAY[SIZE]{20|n} Sets the database data fetch size
FEED[BACK]{6|n|OFF|ON} Displays the number of records returned by a query when the query selects at least n records
HEA[DING] {OFF|ON} Determines wheather column headings are displayed in reports
LONG{80|n} Sets the maximum width for dispalying LING vaues
Note: The value n represents a numeric value. The underlined values indicate default values. If you enter no value with the variable, iSQL*Plus assumes the default value.
■iSQL*Plus 格式化命令
COLUMN [column option]
TTITLE [text | OFF | ON]
BTITLE [text | OFF | ON]
BREAK [ON report_element]
Guidelines
:: All format commands remain in effect until the end of the iSQL*Plus session or until the format setting is overwritten or cleared.
:: Remember to reset your iSQL*Plus settings to the default values after every report.
:: There is no command for setting an iSQL*Plus variable to its default value; you must know the specific value or log out and log in again.
:: If you give an alias to your column, you must reference the alias name, not the column name.
･COLUMN 命令
控制一个列的显示：
COL[UMN] [{column|alias} [option]]
:: CLE[AR]: 清除任何列格式
:: HEA[DING] text: 设置列标题
:: FOR[MAT] format: 用一个格式化模板改变列的显示
:: NOPRINT | PRINT
:: NULL
eg:
使用 COLUMN 命令
:: 创建列标题
COLUMN last_name HEADING 'Employee|Name'
COLUMN salary JUSTIFY LEFT FORMAT $99,990.00
COLUMN manager FORMAT 999999999 NULL 'No manager'
::显示当前对LAST_NAME列的设置
COLUMN last_name
::清除对LAST_NAME 列的设置
COLUMN last_name CLEAR
･COLULMN格式化模板
COLUMN Format Models
The slide displays sample COLUMN format models.
The Oracle server displays a string of pound signs (#) in place of a whole number whose digits exceed the number of digits provided in the format model. It also displays pound signs in place of a value whose format model is alphanumeric but whose actual value is numeric.
･使用 BREAK 命令
用BREAK命令禁止重复值
BREAK ON job_id
BREAK 命令
用 BREAK 命令来把行分成几个部分，并且禁止完全相同的值。为了确保 BREAK 命令有效，使用子句定制那些你正在阻断的列。
语法
BREAK on column[|alias|row]
在语法中：
column[|alias|row 禁止重复显示一个给定列的值
･使用 CLEAR 命令清除所有 BREAK 设置：
CLEAR BREAK
･使用TTITLE和BTITLE命令
语法:
TTI[TLE]|BTI[TLE] [text|OFF|ON]
::显示页眉页脚
TTI[TLE] [text|OFF|ON]
::设置报告页眉
TITLE 'Salary|Report'
::设置报告页脚
BTITLE 'Confidential'
The TTITLE and BTITLE Commands
Use the TTITLE command to format page headers and the BTITLE command for footers. Footers appear at the bottom of the page.
The syntax for BTITLE and TTITLE is identical. Only the syntax for TTITLE is shown. You can use the vertical bar (|) to split the text of the title across
several lines.
Syntax
TTI[TLE]|BTI[TLE] [text|OFF|ON]
In the syntax:
text represents the title text (enter single quotes if the text is more than one word).
OFF|ON toggles the title either off or on. It is not visible when turned off.
The TTITLE example on the slide sets the report header to display Salary centered on one line and Report centered below it. The BTITLE example sets the
report footer to display Confidential. TTITLE automatically puts the date and a page number on the report.
Note:The slide gives an abridged syntax for TTITLE and BTITLe.Various options for TTItLe and BTITLE are covered in another SQL course.
■创建脚本文件来运行一个报告
1. 创建并且测试 SQL SELECT 语句
2. 保存 SELECT 语句到脚本文件中
3. 装载脚本文件到一个编辑器中
4. 在 SELECT 语句的前加格式化命令
5. 检验跟随 SELECT 语句的终止字符semicolon (;) or a slash (/).
6. 在 SELECT 语句之后清除格式命令
7. 保存脚本文件
8. 装载脚本文件到 iSQL*Plus 的文本窗口中，单击执行按钮
Guidelines
:: You can include blank lines between iSQL*Plus commands in a script.
:: If you have a lengthy iSQL*Plus or SQL*Plus command, you can continue it on the next line by ending the current line with a hyphen (-).
//一行写不下继续写
:: You can abbreviate iSQL*Plus commands. //可以缩短iSQL*Plus命令.
:: Include reset commands at the end of the file to restore the original iSQL*Plus environment. //在每个命令的最后恢复原始iSQL*Plus环境.
Note: REM represents a remark or comment in iSQL*Plus. //备注
eg;
SET FEEDBACK OFF
TTITLE 'Employee|Report'
BTITLE 'Confidential'
BREAK ON job_id
COLUMN job_id HEADING 'Job|Category'
COLUMN last_name HEADING 'Employee'
COLUMN salary HEADING 'Salary' FORMAT $99,999.99
REM ** Insert SELECT statement
SELECT job_id, last_name, salary
FROM employees
WHERE salary < 15000
ORDER BY job_id, last_name
/
REM clear all formatting commands ...
SET FEEDBACK ON
COLUMN job_id CLEAR
COLUMN last_name CLEAR
COLUMN salary CLEAR
CLEAR BREAK
Summary
In this lesson, you should have learned about substitution variables and how useful they are for running reports. They give you the flexibility to replace values in a WHERE clause, column names, and expressions.
You can customize reports by writing script files with:
Single ampersand substitution variables
Double ampersand substitution variables
The DEFINE command
The UNDEFINE command
Substitution variables in the command line
You can create a more readable report by using the following commands:
COLUMN
TTITLE
BTITLE
BREAK
