Oracle编程入门经典第9章掌握SQL*Plus

2013-04-25 09:47 夜雨瞳阅读(1207) 评论(0) 编辑收藏举报

9.1 高级启动选项... 1

9.1.1 LOGON.. 2

9.1.2 设置描述文件... 2

9.1.3 START. 4

试验：调用SQL*Plus. 6

工作原理... 6

9.1.4 GUI SQL*Plus的启动选项... 6

9.2 日常使用SQL*Plus. 7

9.3 SQL*Plus命令入门... 8

9.3.1 COLUMN.. 8

9.3.2 DESCRIBE. 9

9.3.3 PROMPT. 11

9.3.4 ACCEPT. 11

9.3.5 SHOW.. 14

9.3.6 VARIABLE. 16

9.4 内建的SQL*Plus HELP. 18

9.4.1 内建HELP. 18

9.4.2 安装HELP. 20

9.4.3 输入新的HELP主题... 20

9.5 使用SQL*Plus缓冲区... 20

9.5.1 设置用户编辑器... 20

9.5.2 使用SQL*Plus进行编辑... 21

9.6 脚本... 23

9.6.1 START，@，@@... 23

9.6.2 HOST命令... 25

9.7 小结... 25

在本意中，我们将要讨论：

SQL*Plus可以使用的高级启动选项，包括站点描述文件和用户描述文件
怎样定制用户的SQL*Plus脚本和输出
SQL*Plus的HELP命令
SQL*Plus缓冲

9.1 高级启动选项

打开命令窗口，并且输入命令sqlplus –

C:\Documents and Settings\zhyongfeng>sqlplus -SQL*Plus: Release 9.2.0.1.0 - Production
用法: SQLPLUS [ [<option>] [<logon>] [<start>] ]
其中  <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
<start> ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
"-H"  显示  SQL*Plus  的版本标帜和使用语法
"-V"  显示  SQL*Plus  的版本标帜
"-L"  只尝试登录一次
"-M <o>"  使用  HTM标志选项  <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode

就如用户所见，这里有大量可用选项。我们依次快速讨论它们：

-或者-H：使用破折号（或者-H）作为启动选项将会告诉SQL*Plus输出版本标题以及使用信息。
-V：这个选项可以告诉SQL*Plus输出版本标题，以便告诉用户可以知道用户正在运行的SQL*Plus的版本。
-M <o>：这个选项用于标记。当使用SQL*Plus的时候，用户可以要求工具使用HTML标记输出数据，来代替普通的文本。对于一些用户，这可能是一种从SQL*Plus中方便生成HTML标签数据的方法。
-R <n>：R代表RESTRICT。通过使用这个选项，用户就能够禁用用来与操作系统进行交互的特定SQL*Plus命令。这里有三个层次，随着编号的增加会有起来越多的限制。限制会禁用如下命令：
- 层次1：EDIT、HOST以及!
- 层次2：EDIT、HOST、!、SAVE、SPOOL、STORE
- 层次3：EDIT、GET、HOST、!、SAVE、SPOOL、START、@、@@、STORE

为了展示这些限制，我们首先来打开一个SQL*Plus会话，输入dir命令：

C:\Documents and Settings\zhyongfeng>sqlplus scott/tiger@newdb
SQL*Plus: Release 9.2.0.1.0 - Production o星期一  4 月  8 11:29:13 2013
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng
连接到:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
SQL> host
Microsoft Windows XP [版本  5.1.2600]
(C)  版权所有  1985-2001 Microsoft Corp.

由于没有规定限制选项，所以我们能够随意与操作系统进行交互（如输入host mstsc，进行远程桌面连接）。然而，如果我们打开另一个会话，并且使用如下命令：

C:\Documents and Settings\zhyongfeng>sqlplus -R 3 scott/tiger@newdb
SQL*Plus: Release 9.2.0.1.0 - Production o星期一  4 月  8 11:29:35 2013
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
SQL> host
SP2-0738:  限制的命令  "host"  不可用
SQL>

-S：SILENT模式。这个选项会禁止向屏幕回送提示以及与SQL*Plus有关的信息。

9.1.1 LOGON

如果用户启动SQL*Plus，但是没有规定登录方法，那么就会提示用户输入用户名和密码，要注意，密码不会回显到屏幕：

C:\Documents and Settings\zhyongfeng>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production o星期一  4 月  8 11:37:21 2013
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
请输入用户名: scott
请输入口令:
连接到:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
SQL>

9.1.2 设置描述文件

SQL*Plus最重要但是也是最常被忽略的特性就是描述文件（profiles）。描述文件只是包含了SQL*Plus设置、SQL查询和/或者PL/SQL程序块的SQL文件。它可以让用户设置SQL*Plus环境来满足用户的需求。

站点描述文件

站点描述文件（stie profile）是由数据库管理员建立的SQL*Plus脚本，它会在数据库服务器上使用SQL*Plus的用户建立默认设置。

这要通过在执行SQL*Plus的计算机建立名为glogin.sql的文件来实现。这个文件可以位于如下位置：

Unix：$ORACLE_HOME/sqlplus/admin/glogin.sql
Windows：%ORACLE_HOME%/sqlplus/admin/glogin.sql

Glogin.sql站点描述文件的示例如下所示：

---- Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved.
---- NAME
--  glogin.sql
---- DESCRIPTION
--  SQL*Plus global login startup file.
----  Add any sqlplus commands here that are to be executed when a user
--  starts SQL*Plus on your system
---- USAGE
--  This script is automatically run when SQL*Plus starts
---- For backward compatibility
SET PAGESIZE 14
SET SQLPLUSCOMPATIBILITY 8.1.7
-- Used by Trusted Oracle
COLUMN ROWLABEL FORMAT A15
-- Used for the SHOW ERRORS command
COLUMN LINE/COL FORMAT A8
COLUMN ERROR FORMAT A65 WORD_WRAPPED
-- Used for the SHOW SGA command
COLUMN name_col_plus_show_sga FORMAT a24
-- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE
Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng
-- Defaults for SET AUTOTRACE EXPLAIN report
COLUMN id_plus_exp FORMAT 990 HEADING i
COLUMN parent_id_plus_exp FORMAT 990 HEADING p
COLUMN plan_plus_exp FORMAT a60
COLUMN object_node_plus_exp FORMAT a8
COLUMN other_tag_plus_exp FORMAT a29
COLUMN other_plus_exp FORMAT a44

要记住，由于这些文件位于服务器上，所以其它计算机上的SQL*Plus客户程序不能执行这个脚本。只有从glogin.sql文件所处计算机上执行的SQL*Plus客户程序才会使用全局站点描述文件。

用户描述文件

用户描述文件是个性化的描述文件，它与站点描述文件类似，但是能够针对各个用户考虑。如果在SQL*Plus当前工作目录下没有login.sql，SQL*Plus就会依次寻找SQLPATH环境变量中的各个目录。只要SQL*Plus找到名为login.sql的文件，搜索就会结束。如果没有这个文件，SQL*Plus就不去执行这个文件。

9.1.3 START

START命令可以让用户有能力从文件中，或者从统一资源定位符（URI）运行脚本。START选项的语法如下所示：

@{ uri | filename[.ext] } [arg…]
@@ filename[.ext] [arg…]
sta[rt] { uri | filename[.ext] } [arg…]

在这里，我们应该注意到由W3C（Worldwide Web Consortium）发布的统一资源定位符的定义，如下所示：

“统一资源定位符（UIR）是短字符串，它可以标识Web中的资源：文档、图像、可下载文件、服务、电子邮件信箱以及其它资源。它们可以在各种命名模式和方法下使用资源，例如HTTP、FTP以及采用相同的简单方式寻址的Internet邮件。”

在W3C的Web站点http://www.w3.org/Addressing/上可以找到更多有关URI和RUL命令和寻址的信息。

简而言之，URI为SQL*Plus提供了运行在远程Web上存储脚本的能力。在SQL*Plus版本9.0.1中，HTTP、FTP和GOPHER协议都在URI中得到支持。

注意：

要注意，这种支持当前只能够在Windows平台上得到。

我们能够使用SQL*Plus去分析表，找到数据库中所有列的名称、数据类型、长度、精度。为了完成这项工作，我们要执行如下步骤：

启动SQL*Plus
为我们进行数据库认证（如下所示，启动和认证能够在一个单独的步骤中完成）。
输入DESCRIBE(DESC)命令
检查结果
退出工具

连接到:

连接到: 
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - ProductioCopyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng
SQL> desc employees
名称  是否为空?  类型
----------------------------------------- -------- ------------EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL> exit

这个过程并不困难，但是它明显很“乏味”。与此相反，我们来编写一个Shell脚本，为我们完成所有重复的工作，而我们只需输入绝对必要的信息。将如下脚本保存到desc.sql文件中：

describe &1
exit

在这个脚本中，我们要在&1上执行DESCRIBE命令。&字符在SQL*Plus中用来表示变量。在这个例子中。&1表示从命令行传递给脚本的第一个参数。我们能够使用&2、&3等，为多个命令参数使用相同的方法。

为了能够在Windows平台上执行这个脚本，可以将如下内容保存到名为desc.cmd的文件中：

sqlplus –S %1 @desc.sql %2

在UNIX中，可以将如下Shell脚本保存到名为desc的文件中：

sqlplus –S $1 @desc.sql $2

在这个desc文件中，我们使用了-S选项，以便可以抑止SQL*Plus标题和工具输出，我们传递给desc.sql的第一个参数包括了用户名和密码。我们为这2个值使用了一个参数原因是，可以让用户能够与远程数据库进行连接，并且使用如下方式调用脚本：

desc scott/tiger@newdb employees

如下所示：

C:\Documents and Settings\zhyongfeng>desc hr/hr@newdb employees
C:\Documents and Settings\zhyongfeng>sqlplus -S hr/hr@newdb @desc.sql employees
名称  是否为空?  是否为空?
----------------------------------------- -------- ---------------------------EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME    VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)  
6
Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
C:\Documents and Settings\zhyongfeng>

将以上desc.sql与desc.cmd放在C:\Documents and Settings\zhyongfeng文件下。

试验：调用SQL*Plus

(1) 我们首先要写Shell脚本中的SQL脚本。将如下脚本为versions.sql：

select banner from v$version
/
exit

(1) 现在，我们来编写Shell脚本，并且将它保存为versions.cmd（UNIX上为versions）：

sqlplus -S hr/hr@newdb @versions.sql

用户必须提供用户自己的用户名和密码，来代替hr/hr@neweb

(1) 现在调用这个脚本，如下所示：

工作原理

在这个例子中，versions脚本会调用SQL*Plus，连接数据库，并且使用-S选项抑制SQL*Plus消息，然后再调用SQL脚本。

9.1.4 GUI SQL*Plus的启动选项

即使GUI SQL*Plus工具会从Windows中的图标启动，启动选项也与工具的命令行版本的选项完全相同。唯一的区别是，在SQL*Plus的GUI模式中，可以使用菜单来执行常见的任务，例如将文件载入到SQL*Plus缓存，将脚本保存到文件系统中，调用定制的文本编辑器，等等。例如，在命令版本中，为了设置查询结果中的行的宽度，用户要使用命令：

SET LINESIZE N

在GUI中，用户就可以简单使用Options | Environment 菜单打开Environment对话框，在那里用户就可以设定所有的SQL8Plus环境设置，见图9-1。

图9-1 GUI SQL*Plus

打开DOS窗口，输入命令sqlplusw打开SQL*Plus
现在，使用HR用户账号连接数据库
使用如下命令运行我们本章前面建立的SQL脚本desc.sql

@desc.sql

若以sqlplusw –R 3打开SQL*Plus，则会受到限制（以sqlplusw则能完全打开）：

SQL*Plus: Release 9.2.0.1.0 - Production o星期一  4 月  8 17:58:17 2013
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
请输入用户名: hr
请输入口令: **
连接到: 
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
SQL> @c:\desc.sql employees
SP2-0738:  限制的命令  "@ (START)"  不可用
SQL>

9.2 日常使用SQL*Plus

我们来讨论SQL*Plus所提供的，可以支持用户民数据库进行日常交互的特性。我们会讨论怎自定义工具的输出，怎样请求工具帮助。不仅如此，我们还要考虑怎样使用功能强大的SQL*Plus缓冲器，并且将一些用户可以在SQL*Plus中使用，能够更多了解用户Oracle数据库的有用脚本打包。

9.3 SQL*Plus命令入门

9.3.1 COLUMN

列标题

默认情况下，查询中的列标题是从数据库中选择的列的名称。当用户在查询中使用表达式建立一个列的时候，就会使用表达式本身作为标题。考虑如下示例：

SQL> select ename,sal,sal*1.1 raise from emp;
ENAME SAL RAISE
---------- ---------- ----------SMITH 800 880
ALLEN 1600 1760
WARD 1250    1375
JONES 2975 3272.5
MARTIN 1250 1375
BLAKE 2850 3135
CLARK 2450 2695
KING 5000 5500
TURNER 1500 1650
JAMES 950 1045
FORD    3000 3300
ENAME SAL RAISE
---------- ---------- ----------
MILLER 1300 1430
已选择 12 行。

如果我们想要扩展这些列标题，让不熟悉EMP表的用户更可以容易地理解它们，我们就可以使用COLUMN命令，如下所示：

SQL> column ename format a13 heading "Employee Name"
SQL> column sal heading "Salary"
SQL> column raise heading "Raise"
SQL> select ename,sal,sal*1.1 raise from emp;
Employee Name Salary Raise
------------- ---------- ----------
SMITH 800 880
ALLEN 1600 1760 
WARD    1250 1375
JONES 2975 3272.5
MARTIN 1250 1375
BLAKE 2850 3135
CLARK 2450 2695
KING 5000 5500
TURNER 1500 1650
JAMES    950 1045
FORD 3000 3300
Employee Name Salary Raise
------------- ---------- ----------MILLER 1300 1430

MILLER 1300 1430
已选择 12 行。

数字格式化

在COLUMN命令中还有许多选项可以配置数字列格式。如下表：

表9-1 格式掩码

字符	示例	解释
9	9999	格式掩码中的每个字符都代表了查询结果中所返回数字的点位符
0	999.00	每个0都表示了将要在查询结果中占用一个数位的数字。这个字符通常用于前导或者收尾的0
$	$9999	这个字符会使用美元符号作为数字的前缀
S	S9999	为数字显示符号类型。可以用于查询结果中的正负数字
PR	9999PR	在尖括号串显示负数
D or .	99D99，99.99	在D或者.的位置上放置小数点
，	9，999	在，的位置上放置逗号
RN or rn	RN	根据格式掩码的大小写，以大写或者小写的方式显示罗马数字
DATE	date	假定数字是格式化的日期，以MM/DD/YY的格式显示结果值

其它列选项

COLUMN命令还有其它一些可选命令，为了完整起见，我们在这里将其列出：
CLEAR。这个选项会清除特定列的列设置
CLEAR COLUMNS。这个选项会为所有的列重置列设置。
JUSTIFY { LEFT | CENTER | RIGHT }。使用调整命令将会导致列的标题根据用户传递的方向进行调整（左、中或者右）。
NEW_VALUE <VARIABLE>。这个选项为列值赋予一个由传递给命令的名称所标识的变量。
PRINT | NOPRINT。这个选项可以控制是否在查询输出中显示列和它的标题。
ON | OFF。这个选项控制SQL*Plus是否使用COLUMN命令所规定的列属性。

9.3.2 DESCRIBE

DESCRIBE命令会返回数据库中存储对象的描述。对于表、视图和指向表（或者视图）的同义语，DESCRIBE都可以列出各个列以及各个列的属性。DESCRIBE还会输出过程、函数以及程序包的规范。

DESCRIBE命令的语法是：

DESC[RIBE] { [schema.]object[@connect_identifier]}

我们已经在前面章节中的几个地方使用了DESCRIBE命令，在这里，我们会提供一个简要的回顾。

DESCRIBE函数也能够用于描述数据库中的PL/SQL单元。过程、函数和程序包都能够通过SQL*Plus描述，这在编写使用其它程序单元的PL/SQL代码时会很有帮助。

在以下的代码示例中，我们在DBMS_OUTPUT上使用了DESCRIBE，它是SQL* Plus中用来向屏幕输出数据库程序包：

SQL> desc dbms_output
PROCEDURE DISABLE
PROCEDURE ENABLE
参数名称  类型  输入/输出默认值?
------------------------------ ----------------------- ------ --------BUFFER_SIZE NUMBER(38) IN DEFAULT
PROCEDURE GET_LINE
参数名称    类型  输入/输出默认值?
------------------------------ ----------------------- ------ --------LINE VARCHAR2 OUT
STATUS NUMBER(38) OUT
PROCEDURE GET_LINES
参数名称  类型  输入/输出默认值?
------------------------------ ----------------------- ------ --------LINES TABLE OF VARCHAR2(255) OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
参数名称  类型  输入/输出默认值?
------------------------------ ----------------------- ------ --------A    VARCHAR2 IN
PROCEDURE PUT
参数名称  类型  输入/输出默认值?
------------------------------ ----------------------- ------ --------A NUMBER IN
PROCEDURE PUT_LINE
参数名称    类型  输入/输出默认值?
------------------------------ ----------------------- ------ --------A VARCHAR2 IN
PROCEDURE PUT_LINE
参数名称  类型  输入/输出默认值?
------------------------------ ----------------------- ------ --------A NUMBER IN

9.3.3 PROMPT

PROMPT命令会在屏幕上输出一行数据。这非常有助于在存储脚本中向用户传送信息。例如，如果用户要编写一个查询，提供用户要看到的数据的纯文本描述，用户就可以使用PROMPT命令完成这项工作。将如下脚本存储到名为usernames.sql的文件中：

prompt
prompt This is a list of users and their default
prompt Tablespaces
select username,default_tablespace
from dba_users
/

现在使用START或者@命令在SQL*Plus中运行文件。

您必须作为能够访问DBA_USERS视图的用户运行这个文件。

SQL> @c:\usernames.sql
This is a list of users and their default
Tablespaces
USERNAME DEFAULT_TABLESPACE
------------------------------ -------------------SYS SYSTEM
SYSTEM SYSTEM
DBSNMP SYSTEM
HR    EXAMPLE
SCOTT USERS
OUTLN SYSTEM
WMSYS SYSTEM
ORDSYS SYSTEM
ORDPLUGINS SYSTEM
MDSYS SYSTEM
CTXSYS    DRSYS
USERNAME DEFAULT_TABLESPACE
------------------------------ -------------------XDB XDB
ANONYMOUS XDB
WKSYS DRSYS
WKPROXY    DRSYS
已选择 15 行。

9.3.4 ACCEPT

如果所交互的用户正在运行要将输入数据赋予变量的脚本时，就可以使用ACCEPT命令。ACCEPT可以让作者规定变量的数据类型、输入数据的格式，以及当用户执行未规定值的脚本时的默认值。可以使用HIDE来隐藏正在输入SQL*Plus的值，这有助于规定密码这样的数据。ACCEPT命令的语法如下所示：

ACC[EPT] variable [NUM[BER] | CHAR | DATE] [FOR [MAT] format]
[DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]

数据类型、format、default、ROMPT和HIDE都是编写ACCEPT命令时的可选设置。然而，如果规定了数据类型或/或者格式，SQL*Plus就会只接受符合数据类型和/或者格式掩码的合法值。这可以进一步确保正在接受数据的脚本的作者能够在所使用变量的上下文中工作。考虑如下脚本：

prompt CREATE NEW EMPLOYEE RECORD
prompt
prompt Enter the employee's information:
prompt
accept l_ename char format a10 prompt 'Last name:'
accept l_empno number format '9999' prompt 'Employee #:'
accept l_sal number format '99999.99' prompt 'Salary [1000]:' default '1000.00'
accept l_comm number format '99999.99' prompt 'Commission %[0]:'default '0'
accept l_hired date format 'mm/dd/yyyy' prompt 'Hire date (mm/dd/yyyy):'
prompt List of available jobs:
select distinct job 
from emp
order by job
/
accept l_job char format a9 prompt 'Job:'
prompt List of managers and employee numbers:
select empno,ename
from emp
order by ename
/
accept l_mgr number format '9999' prompt 'Manager”s Employee #:'
prompt List of department numbers and names:
select deptno,dname
from dept
order by deptno
/
accept l_dept number format '99' prompt 'Department #:'
insert into emp(empno,ename,job,mgr,hiredate,sal,comm.,deptno)
values(&l_empno,'&l_ename','&l_job',&l_mgr,
todate('&l_hired','mm/dd/yyyy'),&l_sal,&l_comm,&l_dept)
/

如果用户输入错误数据（错误数据格式），SQL*Plus就会显示错误代码和消息通知用户：

SQL> @c:/new_emp.sql
CREATE NEW EMPLOYEE RECORD
Enter the employee's information:
Last name:Ellison
Employee #:1
Salary [1000]:0
Commission %[0]:99
Hire date (mm/dd/yyyy):05-MAY-1973
SP2-0685:  日期"05-MAY-1973"无效或格式不匹配"mm/dd/yyyy"
Hire date (mm/dd/yyyy):05-05-1975
List of available jobs:
JOB
---------ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
Job:GOTOWORK
List of managers and employee numbers:
EMPNO ENAME
---------- ----------7499 ALLEN
7698 BLAKE
7782 CLARK
7902 FORD
7900 JAMES
7566 JONES
7839 KING
7654 MARTIN
7934 MILLER
7369 SMITH
7844 TURNER
EMPNO ENAME
---------- ----------7521 WARD
已选择 12 行。

9.3.5 SHOW

SHOW命令有助于找出与大量不同内容有关的更多信息。包括：

SQL*Plus环境设置
PL/SQL代码或者Oracle正在编译的数据库中的错误
init.ora参数

SQL*Plus环境设置

通过使用SHOW命令，用户就可以快速而方便的了解到SQL*Plus环境的当前设置。当用户不知道是否可以向文件输出数据的时候，为了能够快速地查看SPOOL的配置，我们就可以使用如下命令：

SQL> show spool
spool OFF

查看SHOW命令可以使用的所有SQL*Plus环境设置，可以使用SHOW ALL。

SQL> show all

编译PL/SQL或者数据库对象中的错误

当使用SQL*Plus编写PL/SQL，或者建立特定的数据库对象时，默认情况下，SQL*Plus不会显示任何详细的错误消息。必须使用SHOW ERRORS命令明确请求它们。

SHOW ERRORS命令可以应用如下对象类型：

过程
函数
程序包
程序包体
触发器
视图
类型
类型体
维数
Java源
Java类

以下代码展示了正在编译出现了故障的PL/SQL过程：

SQL> CREATE OR REPLACE PROCEDURE show_emp(p_empno IN NUMBER)
2 IS
3 BEGIN
4 FOR c1 IN (SELECT * FROM emp WHERE empno=p_empno) LOOP
5 dbms_output.put_line('Name:'|| c1.ename);
6 dbms_output.put_line('Job:'|| c1.job);
7 dbms_output.put_line('Salary:'|| c1.sal);
8 dbms_output.put_line('Commission:'|| c1.comm);
9    END;
10 END show_emp;
11 /
警告:  创建的过程带有编译错误。

这段代码在Oracle中编译会导致编译错误。然而，错误消息却未能提供大量信息，用SHOW ERRORS命令可以获得很大的帮助。SHOW ERRORS的默认行为当前正在执行的SQL*Plus会话中最近编译的对象显示错误。因此，如果用户尝试向Oracle中编译其它代码之前执行SHOW ERRORS命令，就会显示PROCEDURE SHOW_EMP的错误：

SQL> show errors;
PROCEDURE SHOW_EMP  出现错误:
LINE/COL ERROR
-------- ----------------------------------------
9/11 PLS-00103:  出现符号  ";"在需要下列之一时?
loop
符号  "loop"  被替换为  ";"  后继续。

然而，如果过程代码与其它的语句同时出现在脚本中，当SQL*Plus显示没有太多帮助的错误消息时，用户可以采用2种方法来查看错误：

用户可以在每个CREATE语句之后放置SHOW ERRORS消息。
用户可以等到整个脚本执行完毕，使用SHOW ERRORS PROCEDURE SHOW_EMP语法来查看可能在用户脚本中出现问题的特定对象的错误。

如果没有错误，SQL*Plus就会输出如下文本：

SQL> CREATE OR REPLACE PROCEDURE show_emp(p_empno IN NUMBER)
2 IS
3 BEGIN
4 FOR c1 IN (SELECT * FROM emp WHERE empno=p_empno) LOOP
5 dbms_output.put_line('Name:'|| c1.ename);
6 dbms_output.put_line('Job:'|| c1.job);
7 dbms_output.put_line('Salary:'|| c1.sal);
8 dbms_output.put_line('Commission:'|| c1.comm);
9 END LOOP;
10 END show_emp;
11 /
过程已创建。
SQL> show errors;
没有错误。

init.ora参数

Oracle中的数据库管理员需要能够快速访问它们数据库的init.ora参数。通过使用SQL*Plus，就可以很容易地看到数据库中当前init.ora参数的值。只需使用SHOW PARAMETERS命令，如下所示：

要注意，在这个例子中，您必须作为具有词典视图V_$PARAMETERS上SELECT特权的用户进行连接。

QL> show parameters block;
NAME TYPE VALUE
------------------------------------ ----------- ------db_block_buffers integer 0 
16
Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng
db_block_checking boolean FALSE
db_block_checksum    boolean TRUE
db_block_size integer 8192
db_file_multiblock_read_count integer 16

通过使用SHOW PARAMETERS BLOCK命令，SQL*Plus可以在屏幕之后执行查询，并且在屏幕上显示结果：

SQL> -- show parameters db_ is like running the following query:
SQL> SELECT NAME name_col_plus_show_param,
2 TYPE,
3 decode(TYPE,1,'boolean',2,'string',3,'integer',4,'file',6,'big integer','unknown') TYPE,
4 VALUE value_col_plus_show_param
5 FROM v$parameter
6 WHERE upper(NAME) LIKE upper('%block%')
7 ORDER BY name_col_plus_show_param,ROWNUM
8 /
NAME TYPE TYPE VALUE
------------------------------------ ---------- ----------- ------------------------------db_block_buffers    3 integer 0
db_block_checking 1 boolean FALSE
db_block_checksum 1 boolean TRUE
db_block_size 3 integer 8192
db_file_multiblock_read_count    3 integer 16

9.3.6 VARIABLE

使用VARIABLE命令可以建立能够在用户的SQL*Plus会话期间，在PL/SQL代码中使用的绑定变量。绑定变量可能为许多类型，包括：

NUMBER
CHAR
CHAR(n [CHAR|BYTE])
NCHAR
NCHAR(n)
VARCHAR2(n[CHAR|BYTE])
NVARCHAR2(n)
CLOB
NCLOB
REFCURSOR

注意：

要注意，DATE被特别排除在了这个列表之外。

绑定变量提供了一种有效方式，可以在正在执行的SQL*Plus会话中，在SQL语句以及PL/SQL程序块之间存储状。在PL/SQL程序块的范围内声明的变量只能够在PL/SQL程序块的执行期间存在。因此，在PL/SQL程序块中指定的局域PL/SQL变量不能够在其它的PL/SQL程序块中引用。绑定变量为此提供了解决方案。

这里是一个在SQL*Plus会话中使用绑定变量的简单示例。考虑如下名为deptemps.sql的脚本：

SQL> @c:\deptemps.sql
已连接。
Department Names
------------------------------
Accounting
Administration
Benefits
Construction
Contracting
Control And Credit
Corporate Tax
Executive
Finance
Government Sales
Human Resources
Department Names
------------------------------
IT
IT Helpdesk
IT Support
Manufacturing
Marketing
NOC
Operations
Payroll
Public Relations
Purchasing
Recruiting
Department Names
------------------------------
Retail Sales
Sales
Shareholder Services
Shipping
Treasury
已选择 27 行。
Enter the department name to report on :Finance
PL/SQ过程已成功完成。 
18
Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng
Department Information
Id Name Manager location
------ --------------- --------------- --------------------
100 Finance Greenberg Seattle Washington
Department Employees
Job First Name Last Name Phone Number
-------------------- ------------ ------------ ------------
Finance Manager Nancy Greenberg 515.124.4569
Accountant Luis Popp 515.124.4567
Jose Manuel Urman 515.124.4469
Ismael Sciarra 515.124.4369
John Chen 515.124.4269
Danie  Faviet 515.124.4169
已选择 6 行。

这里只是最常使用的SQL*Plus命令的简要概述。用户还可以使用许多其它命令，在接下来的章节中，我们将要继续学习怎样使用SQL*Plus，找到更多有关它们的信息。

9.4 内建的SQL*Plus HELP

9.4.1 内建HELP

SQL*Plus的HELP命令是用户的朋友。试着在SQL提示符下输入如下命令：

SQL> help index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/    DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE  REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RU  WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET
CONNECT PASSWORD SHOW

如果用户用户收到错误：

SQL> help index
SP2-0171:HELP not accessible.

就意味着在用户的数据库中没有安装帮助系统。为了解决这个问题，可以阅读以下题为“安装HELP”的部分。

HELP索引是可以在帮助系统中使用的单词列表。简单输入help[topic]就可以查看到特定单词的注解：

SQL> help column
COLUMN
------Specifies display attributes for a given column, such as:
- column heading text
- column heading alignment
- data format
- column data wrapping
Also lists the current display attributes for a single column
or all columns.
COL[UMN] [{column | expr} [option ...] ]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | C[ENTRE] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

SQL*Plus帮助系统通常可以显示各个命令的相同属性。这里是用户可能会看到的内容：

命令的标题
命令的文本描述
命令的简写（例如，COL可以代替COLUMN）
可以向命令传递的强制参数和可选参数

9.4.2 安装HELP

帮助由SQL*Plus工具提供，但是它要依赖所连接的数据库配置了帮助系统。默认情况下，它安装在数据库上。

用户可以发现，SQL*Plus帮助只是一个SYSTEM所拥有的称为HELP的表。当用户在SQL提示符下使用HELP命令时，SQL*Plus就要在后台对数据库进行查询，并且在工具中向用户显示结果。例如，如果用户使用HELP INDEX，它就等价于SQL*Plus使用了如下查询：

select * from system.help where topic=upper(‘index’);

如果数据库没有在SYSTEM模式中安装HELP表那么当SQL*Plus进行查询的时候，就会获得指出表不存在的错误信息，然后工具就会向用户返回错误：

SP2-0171:HELP not accessible.

如果用户数据库没有安装HELP，那么装配它也很容易。进入

D:\oracle\ora92\sqlplus\admin\help\
@hlpbld.sql helpus.sql

hlpbld.sql文件将会建立HELP表，并且执行脚本，将帮助文件载入表中。

9.4.3 输入新的HELP主题

由于构成帮助系统的数据存储在表中，所以用户可以修改表中的记录，改变HELP命令的结果。在Oracle 9i中，许多以前版本的SQL*Plus命令现在都已经过时。这些过时的命令（以及它的替代命令）都列在了SQL*Plus的帮助表项中，并且在《SQL*Plus Users Guide and Reference》的附录F中提供了进一步的解释（从Oracle 8.1.5以来）。

在这个例子中，我们将要获取《SQL*Plus Users Guide and Reference》中给出的解释，并且将这些数据输入到我们的SQL*Plus帮助索引中。这可以辅助SQL*Plus帮助的用户，帮助他们配置他们的SQL*Plus环境。

9.5 使用SQL*Plus缓冲区

SQL*Plus会在缓冲区（buffer），或者存储内存区域（stored memory area）存储用户最近运行的命令。通过缓冲区中存储这些命令，用户就能够重新调用，编辑，并且运行它们最近输入的SQL语句。

有2种方法可以修改在缓冲区中存储的命令。用户可以将缓冲区的内容传递给Notepad或者vi这样的编辑器，或者用户也可以使用SQL*Plus的默认编辑器。这些操作并不互斥；用户可以在Notepad中编辑整个脚本，保存脚本，并且随后还可以使用SQL*Plus命令修改缓冲区的内容。很容易就可以知道用户并没有受限制于一种操作方式。首先，我们要讨论如何使用选择的编辑器，在SQL*Plus之外修改缓冲区的内容。

9.5.1 设置用户编辑器

为了在SQL*Plus工具中利用vi或者Notepad作为用户的编辑器，可以使用DEFINE命令，如下所示：

SQL> define_editor=vi
SQL> -- or perhaps --
SQL> define_editor=notepad

可以注意到，就如用户在回顾以上的用户描述文件所示的login.sql文件时可以看到的，您可以在用户描述文件中包含DEFINE_EDITOR设置。这就可以避免用户在每次启动SQL*Plus的时候都要手工对其进行设置。一旦设置完毕，用户就可以使用EDIT命令修改最近使用的SQL语句，或者输入到SQL*Plus中的PL/SQL程序块。在缓冲区中没有内容的时候使用EDIT命令将会导致如下错误：

SQL> clear buffer - - this command clears the contents of the SQL*Plus buffer
buffer cleared
SQL> edit
SP2-0107:Nothing to save error.

通常，当用户调用EDIT命令的时候，SQL*Plus会获取最近的SQL语句，或者输入到SQL*Plus中的PL/SQL程序块，并且将其它写入到当前工作目录下的名为afiedt.buf的文件中。

要注意，一些操作系统具有不同的SQL*Plus编辑文件名称。这些文件名称可以使用SET EDITFILE命令进行修改。

文件的内容可以适当地修改。在退出编辑工具的时候，会将缓冲文件的内容自动读回到SQL*Plus的缓冲区中，并且使用/字符或者RUN命令运行。

不要混淆SQL*Plus缓冲区和编辑文件之间的差异。缓冲区是用户能够使用SQL*Plus命令进行修改的内在中的区域。只有当用户调用EDIT命令的时候，才会使用编辑文件（afiedt.buf）。

这里有一些SQL*Plus命令，在使用外部编辑器的时候会有所帮助：

ED[IT] [filename[.ext]]——如上所述，EDIT命令可以用来调用应用，并且将SQL*Plus缓冲区的内容传递给它。如果忽略文件名参数，就会将afiedt.buf文件的内容传递给编辑器。
SAV[E] filename[.exe]] [CREATE|REPLACE|APPEND]——SAVE命令可以用于将SQL*Plus缓冲区的内容保存到文件中。filename指出了将要存储内容的文件名称。有一个可选的参数可以用来规定SAVE命令的内容。
- CREATE——用来生成新文件。如果文件已经存在，这个选项就会失败。当用户想要保存文件名还没有存在的文件时，就可以使用这个选项。
- REPLACE——用来建立新文件的文件名，或者在文件名已经存在的情况下覆写文件。当文件已经存在，但是用户不介意覆写它的时候，就可以使用这个选项。
- APPEND——当需要将SQL*Plus缓冲区的内容增加到文件的末尾时，就可以使用这个选项。这个选项可以用于需要将SQL语句或者PL/SQL程序块保存到文件中，但是又要保留文件的内容的时候。

9.5.2 使用SQL*Plus进行编辑

并不总是有必要使用外部的编辑器来编辑SQL*Plus缓冲区中的命令。在进行简单改变的时候，用户可以选择使用SQL*Plus工具，以及它内建的编辑命令去修改缓冲区中的内容。这里是可以使用的命令的简短描述：

L[IST] [terms]——这个命令会列出SQL*Plus中的内容。使用星号（*）标记的行是SQL*Plus缓冲区的当前行。用户能够选择传递条件（terms），指出用户要列出SQL*Plus缓冲区的哪些部分。忽略LIST命令中的条件将会显示所有的行。条件如下所示：
- n——传递单独的行号只会向用户显示这一行。
- *——使用星号会列出当前行。
- LAST——传递单词LAST将会列出缓冲区中的最后一行。
- n n2——传递2个使用空格的行编号，将会显示这2个行及其之间的内容。这种情况下，能够使用星号（*）和保留字LAST代替n和/或者n2。这将会有效地使用缓冲区中的当前行和/或者缓冲区的最后行显示一定的范围。
A[PPEND] text——当用户希望向SQL*Plus缓冲区中的行的末尾增加文件的时候，就可以使用Append。文本将会增加到当前行。
C[HANGE] separator OLD [separator NEW]——CHANGE命令是一种非常灵活地执行表达式替换的方式。只需将需要修改的文本规定为OLD参数
DEL[ETE] [terms]——使用DELETE命令可以将行从用户的SQL语句中删除。用户可以向DELETE命令传递我们在以上的LIST命令中讨论那些条件。
I[INPUT] text——INPUT命令可以用来将行输入到SQL*Plus缓冲区的当前行之后，新行的内容就是传递给INPUT命令的文本。

试验：使用SQL*Plus编辑器

(1) 我们在这里要做的第一件事情就是向SQL*Plus输入一个非法查询。

SQL> select department_id,department_name
2 from departmens;
from departmens
*
ERROR  位于第  2  行:
ORA-00942:  表或视图不存在

(2) 现在，我们使用LIST命令，分析语句，寻找错误。执行没有参数的LIST将会显示SQL*Plus缓冲区的所有内容：

SQL> list
1 select department_id,department_name
2* from departmens

SQL*Plus输出了缓冲区的内容，用户可以在第二行的行编号2之后看到一个星号。这告诉我们第2行是当前的行。这时使用CHANGE或者INPUT命令都会影响这个行。

(3) 我们可以看到表名称DEPARTMENS拼写错误。我们现在可以使用CHANGE命令修复这个错误。

SQL> change.mens.ments
2* from departments

(4) 现在，我们来使用INPUT命令，向语句中增加一个WHERE子句以及一个ORDER BY子句：

SQL> l
1 select department_id,department_name
2* from departments
SQL> input where department_id between 10 and 40
SQL> l
1 select department_id,department_name
2 from departments
3* where department_id between 10 and 40
SQL> input order by 2
SQL>23
Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng
1 select department_id,department_name
2 from departments
3 where department_id between 10 and 40
4* order by 2

(5) 最后，通过使用SQL*Plus执行字符/，用户就可以执行SQL*Plus缓冲区的内容：

SQL> /
DEPARTMENT_ID DEPARTMENT_NAME
------------- ---------------10 Administration
40 Human Resources
20 Marketing
30 Purchasing

9.6 脚本

用户不必在每次使用的时候都编写常用的SQL语句和PL/SQL程序块，而是可以将它们保存到称为脚本的文件中。这些脚本特别为反复执行的各种任务而设计。

9.6.1 START，@，@@

我们在前面已经看到了怎样使用START或者@命令调用脚本。概括而言，START和@命令基本上使用了相同的工作方式。用户可以传递一个文件名作为第一个参数，并且在命令行参数的位置2（及其以后）传递用户正在运行的脚本所希望的参数。

如果要组合必须执行其它许多文件的安装脚本，这样的复杂脚本，就会十分复杂。就我们目前已经了解到的，如果我们要运行在当前工作目录以外的其它目录中的脚本，我们就会遇到问题。考虑如下2个文件:

第一个文件称为depts.sql

accept did prompt 'Enter Department Id:' default 10
select *
from departments 
where department_id=&did
/
@emps.sql &did

第二个文件称为emps.sql：

select first_name,last_name,job_id
from employees
where department_id=&1
/

现在，如果调用2个脚本都在当前工作目录中，从SQL*Plus中调用depts.sql就会生成正确的结果：

SQL> @depts
Enter Department Id:100
原值  3: where department_id=&did
新值  3: where department_id=100
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------100 Finance 108 1700
原值  3: where department_id=&1
新值  3: where department_id=100
FIRST_NAME LAST_NAME JOB_ID
-------------------- ------------------------- ----------Nancy Greenberg FI_MGR
Daniel Faviet FI_ACCOUNT
John Chen FI_ACCOUNT
Ismael Sciarra FI_ACCOUNT
Jose Manue  Urman FI_ACCOUNT
Luis Popp FI_ACCOUNT
已选择 6 行。

在这个例子中，脚本都存储在文件系统的公共目录中。然而，我们更希望根据脚本的目的，将它们放到合适的目录中。我们将这2个脚本移动到d:\oracle\samples的目录中。

在移动文件后，运行脚本就会导致错误：

SQL> @depts
Enter Department Id:
原值  3: where department_id=&did
新值  3: where department_id=10
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------10 Administration 200 1700
SP2-0310:  无法打开文件"emps.sql"

p这是因为在depts.sql的第8行，脚本指示SQL*Plus去执行当前工作目录下的emps.sql文件。由于我们目前处于d:\oracle\working目录，所以它无法找到我们已经移动到d:\oracle\samples目录中的emps.sql文件，这就是@@命令的用武之地。

当要用户要调用随后会调用其它脚本的脚本时，用户就可以使用@@命令告诉SQL*Plus，相对于正在运行的文件寻找文件。所以，如果我们使用如下命令：

@@d:\oracle\samples\depts.sql

SQL*Plus就会寻找到emps.sql文件。

9.6.2 HOST命令

在SQL*Plus中，用户可以从工具的内部在操作系统上执行命令。这在很多情况下都会很有帮助，所以，我们来讨论一些使用HOST命令的示例。

在SQL*Plus中使用没有参数的HOST（或者在Uinx上使用！，在VMS上使用$）将会用户带到操作系统外壳中。在Windows XP上，使用HOST会将用户带入DOS外壳中：

SQL> host
Microsoft Windows XP [版本  5.1.2600]
(C)  版权所有  1985-2001 Microsoft Corp.
D:\OracleCommand>

9.7 小结

在本意中，我们分析了SQL*Plus，以及它作为数据库访问工具所提供的功能。SQL*Plus不是最好的查看工具，它不能够选择生成图形或图表，但是它可以在编写SQL语句、编辑SQL和PL/SQL、以及执行启动和关闭数据库这样的数据库管理任务时，提供很高的效率。

文章根据自己理解浓缩，仅供参考。

摘自：《Oracle编程入门经典》清华大学出版社 http://www.tup.com.cn

刷新页面返回顶部

夜雨瞳 <strong>人致力于一个目标，一种观念......是人在生活过程中追求完整之需要的一种表现 --- 罗曼.罗兰</strong>

Oracle编程入门经典第9章掌握SQL*Plus

9.1 高级启动选项

9.1.1 LOGON

9.1.2 设置描述文件

9.1.3 START

试验：调用SQL*Plus

工作原理

9.1.4 GUI SQL*Plus的启动选项

9.2 日常使用SQL*Plus

9.3 SQL*Plus命令入门

9.3.1 COLUMN

9.3.2 DESCRIBE

9.3.3 PROMPT

9.3.4 ACCEPT

9.3.5 SHOW

9.3.6 VARIABLE

9.4 内建的SQL*Plus HELP

9.4.1 内建HELP

9.4.2 安装HELP

9.4.3 输入新的HELP主题

9.5 使用SQL*Plus缓冲区

9.5.1 设置用户编辑器

9.5.2 使用SQL*Plus进行编辑

9.6 脚本

9.6.1 START，@，@@

9.6.2 HOST命令

9.7 小结

About

夜雨瞳 <strong>人致力于一个目标，一种观念......是人在生活过程中追求完整之需要的一种表现 --- 罗曼.罗兰</strong>

Oracle编程入门经典 第9章 掌握SQL*Plus

9.1 高级启动选项

9.1.1 LOGON

9.1.2 设置描述文件

9.1.3 START

试验：调用SQL*Plus

工作原理

9.1.4 GUI SQL*Plus的启动选项

9.2 日常使用SQL*Plus

9.3 SQL*Plus命令入门

9.3.1 COLUMN

9.3.2 DESCRIBE

9.3.3 PROMPT

9.3.4 ACCEPT

9.3.5 SHOW

9.3.6 VARIABLE

9.4 内建的SQL*Plus HELP

9.4.1 内建HELP

9.4.2 安装HELP

9.4.3 输入新的HELP主题

9.5 使用SQL*Plus缓冲区

9.5.1 设置用户编辑器

9.5.2 使用SQL*Plus进行编辑

9.6 脚本

9.6.1 START，@，@@

9.6.2 HOST命令

9.7 小结

About

Oracle编程入门经典第9章掌握SQL*Plus