Microsoft Word - 第3章.doc

Transcription

1 第 3 章 Oracle 数据库常用工具 在数据库系统中, 可以使用两种方式执行命令, 一种方式是通过图形化工具, 另一种方式是直接使用各种命令 图形化工具的特点是直观 简单 容易记忆, 而直接使用命令则需要记忆具体命令的语法形式 但是, 图形工具灵活性比较差, 不利于用户对命令及其选项的理解 ; 而命令则非常灵活, 有利于加深用户对复杂命令选项的理解, 并且可以完成某些图形工具无法完成的任务 在 Oracle 11g 系统中, 提供了用于执行 SQL 语句和 PL/SQL 程序的工具 SQL*Plus 本章先简要地从整体上介绍 SQL*Plus 工具, 然后再介绍一些 SQL*Plus 工具中常用的操作命令 本章知识要点 : 了解 SQL*Plus 的运行环境 ; 熟悉 SQL*Plus 基本的运行环境设置 ; 熟悉 HELP 命令 ; 熟练掌握 DESCRIBE 命令的使用 ; 掌握 PROMPT 命令的使用 ; 掌握 SPOOL 命令的使用 ; 熟练掌握 COLUMN 命令的应用 ; 熟练掌握缓存区命令的使用 ; 熟练掌握在 SQL*Plus 中编写 运行脚本文件 ; 了解企业管理器的配置方法 ; 掌握企业管理器的启动和停止方法 ; 了解企业管理器的功能及基本使用方法 ; 综合使用 SQL*Plus 命令解决实际问题 3.1 SQL*Plus 概述 Oracle 的 SQL*Plus 是与 Oracle 进行交互的客户端工具 在 SQL*Plus 中, 可以运行 SQL*Plus 命令与 SQL 语句 我们通常所说的 DML(Data Manipulation Language) DDL(Data Define Language) 和 DCL(Data Control Language) 语句都是 SQL 语句, 它们执行完后, 都可以保存在一个被称为 SQL Buffer 的内存区域中, 并且只能保存一条最近执行的 SQL 语句 我们可以对保存在 SQL Buffer 中的 SQL 语句进行修改, 然后再次执行,SQL*Plus 一般都与数据库打交道 除了 SQL 语句, 在 SQL*Plus 中执行的其他语句我们称之为 SQL*Plus 命令 它们执

2 第 1 篇基础篇 行完后, 不保存在 SQL Buffer 的内存区域中, 它们一般用来对输出的结果进行格式化显示, 以便于制作报表 SQL*Plus 是 Oracle 数据库管理系统提供的一个工具软件, 它提供一个人机接口, 通过 SQL*Plus 管理和维护数据库, 如常用的查询数据表信息 系统信息和数据文件等 它提供了一系列指令, 通过这些指令可以简化用户的指令或者格式化输出信息 它还提供了编写脚本文件的功能, 可以极大地提高 DBA 管理数据库的效率 SQL*Plus 作为数据库管理工具可以设置友好的环境变量, 以方便 DBA 的管理和维护需求 SQL*Plus 作为与 Oracle 进行交互的常用工具, 具有很强的功能, 主要有 : 数据库的维护, 如启动 关闭等, 这些一般在服务器上操作 执行 SQL 语句执行 PL/SQL 执行 SQL 脚本 数据的导出, 报表 应用程序开发 测试 生成新的 SQL 脚本 供应用程序调用, 如安装程序中进行脚本的安装 用户管理及权限维护等 3.2 启动 退出 SQL*Plus SQL*Plus 是与 Oracle 数据库进行交互的一个非常重要 在服务器端或客户端都可以使用的工具, 同时也是一个通用的 在各种平台上几乎都完全一致的工具 所以 SQL*Plus 的应用非常广泛 而且初学者基本上都是使用 SQL*Plus 和 Oracle 数据库进行交互的 如果要使用 SQL*Plus 与数据库服务器进行交互, 首先要登录到数据库服务器上, 这时在 SQL*Plus 进程和数据库服务器之间将建立一条连接, 它们以客户 / 服务器模式工作 下面介绍几种 Oracle 11g 中 SQL*Plus 的启动和退出方法 方法一 : 在程序组中启动 退出 (1) 依次选择 开始 所有程序 Oracle-OraDb11g_home1 应用程序开发 SQL Plus 命令, 如图 3-1 所示 图 3-1 开始 菜单中的 SQL*Plus 菜单项 (2) 在打开的 SQL Plus 窗口中出现了 请输入用户名 的提示字样, 在 请输入用户名 后输入数据库的 username, 并按 Enter 键 在 输入口令 后输入数据库的登录口令, 并按 Enter 键 当出现 连接到 字样和 SQL> 提示符后表示已经成功连接数据库, 如图 3-2 所示,SQL* Plus 已经准备好接收命令或者语句了 56

3 第 3 章 Oracle 数据库常用工具 图 3-2 连接到默认数据库 或者在 请输入用户名 后直接输入 username/password[@connect_identifier] 而直接登录连接到数据库 其中 [@connect_identifier] 可选 (connect_identifier 是数据库的连接标识符, 其简写的别名就是网络服务名 ) 亦可在 请输入用户名 后输入 username, 在 输入口令 后输入 password[@connect_ identifier] (3) 在 SQL> 提示符后输入命令或者语句, 然后回车, 就可以实现和数据库的交互了 命令和语句不分大小写, 但建议将命令和语句保留字大写 命令后面不需要 ( 但可以 ) 输入分号 ;, 而 SQL 语句的后面需要输入分号来结束 (4) 在 SQL> 提示符后输入 exit 或者 quit 后按 Enter 键即可退出 SQL* Plus 如果要在不退出 SQL*Plus 的情况下断开与数据库服务器的连接, 则输入 DISCONNECT 命令 如果要重新连接, 或者在已经连接的情况下以另一个用户的身份连接, 则使用 CONNECT 命令 这条命令的格式为 : CONNECT 用户名 / 口令 as sysdba 如图 3-3 所示 图 3-3 connect 连接另一个数据库方式如果是 SYS 用户, 则使用 as sysdba 或者 as sysoper 参数 如果是远程登录, 还要在用户名和口令之后输入网络服务名 方法二 : 在安装目录中找到 sqlplus.exe 启动 退出 在 Oracle 的 BIN 目录中找到 sqlplus.exe 双击后启动即可, 如果是按默认的方式安装的, 则 sqlplus.exe 的路径为 D:\app\Administrator\product\11.2.0\dbhome_1\BIN 其他操作同方 57

4 第 1 篇基础篇 法一 方法三 : 在运行中启动 退出 SQL*Plus 依次选择 开始 运行 命令, 在 运行 窗口中的 打开 (O): 右边的文本框中输入 sqlplus 用户名 / 数据库名, 然后单击 确定 按钮即可 例如要连接到数据库 magical 的用户名为 scott, 口令是 tiger, 则在 运行 窗口中启动 SQL*Plus 的方法如图 3-4 所示 退出操作同方法一 图 3-4 在运行窗口中启动 SQL*Plus 方法四 : 在命令行中启动 退出 这种方法的前提是在安装 Oracle 11g 时已经在环境变量 Path 添加了 sqlplus.exe 程序的完整路径 D:\app\Administrator\product\11.2.0\dbhome_1\BIN 在安装时这个环境变量是会自动添加的, 所以无需做调整, 为了证实, 你可以在 Path 变量中找找上述路径是否存在 (1) 依次选择 开始 所有程序 附件 命令提示符 命令 打开命令提示符窗口 (2) 输入 sqlplus 或者 sqlpuls.exe 后按 Enter 键 会出现 请输入用户名 字样, 后续的操作就同方法一了 或者直接输入 sqlplus username/password[@connect_identifier] 连接到数据库, 连接数据库成功后的界面如图 3-5 所示 后续的操作同方法一 58 图 3-5 命令方式启动 SQL*Plus

5 第 3 章 Oracle 数据库常用工具 方法五 : 快捷方式的启动 退出 (1) 在 D:\app\Administrator\product\11.2.0\dbhome_1\BIN( 默认安装情况下, 视自己的安装情况而定 ) 目录下找到 sqlplus.exe (2) 在 sqlplus.exe 图标上单击右键, 在菜单中选择 发送到 桌面快捷方式 命令 (3) 在桌面上找到 sqlplus.exe 的快捷方式, 在其图标上单击右键, 从快捷菜单中选择 属性 命令, 在弹出的窗口中有个 目标, 将目标中的内容改为 D:\app\Administrator\ product\11.2.0\dbhome_1\bin\sqlplus.exe username/password@ 网络服务名 然后依次单击窗口下面的 应用 和 确定 按钮, 如图 3-6 所示 图 3-6 快捷方式启动 SQL*Plus (4) 双击桌面上 SQL*Plus 的快捷方式即可自动连接到指定的数据库 后续的操作同方法一 几种连接方式的优缺点如下 : 命令行方式的优点是可以使用复制 粘贴等功能 ; 缺点是在命令行中带参数启动 SQL*Plus 程序就会在标题栏显示该参数, 不利于保密 快捷方式的优点是无需每次都输入用户名 口令和连接字符串 ; 缺点是容易暴露用户名和口令, 不安全 59

6 第 1 篇基础篇 3.3 预备知识 1. 基表 Oracle 是一种关系型数据库系统, 它以关系的方式组织数据, 这里所说的关系, 我们 称之为基表, 也称为表 一个数据库可以由若干个基表组成, 用户对数据库的操作也就是 对表的操作 Oracle 用基表的形式存储信息, 如表 3-1 所示 每个基表都有一个表名, 以便识别 每个基表都包含一个或一个以上的列, 每列包含一类信息, 并都有一个列名, 以描述保存 在那一列中的信息的种类 表名和列名构成了基表的框架, 也就是基表的分类信息 具体 信息一行一行地存放, 表的每一行表示一组独立的数据, 它由具有不同列值的各列组成 表 3-1 EMP 表 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK Dec ALLEN SALESMAN Feb WARD SALESMAN Feb JONES MANAGER Apr MARTIN SALESMAN Sep BLAKE MANAGER May 基表之间的关系 在数据库中, 数据与数据之间是存在着联系的, 我们可以将某个基表中的信息与其他 基表中的信息建立关系, 例如, 在前面的 EMP 表中, 每个雇员均有部门编号 (DEPTNO), 它对应于 DEPT 表中的部门编号 (DEPTNO), 见表 3-2 表 3-2 DEPT 表 DEPTNO DNAME LOC 40 OPERATIONS BOSTON 30 SALSE CHICAGO 20 RSESARCH DALLAS 10 ACCOUNTING NEW YORK 这样可便于信息的独立组织, 因为我们可以在 EMP 表中存放雇员信息, 而无需在基 表中存放 DEPT 表中有关部门的信息 当然, 我们还可以在多个基表之间建立联系 3.4 SQL*Plus 运行环境 SQL*Plus 在运行时受到一些环境变量的限制, 可以通过对这些环境变量进行更改来设 60

7 第 3 章 Oracle 数据库常用工具 置运行的环境 在 SQL*Plus 中有两类相关的设置信息, 一类是 SQL*Plus 本身的设置信息, 这类信息主要控制 SQL*Plus 的输出格式 ; 另一类是数据库服务器的设置信息, 这类信息 主要来自实例的参数文件 1. 显示设置信息命令 show 使用 show 命令可以显示当前 SQL*PLUS 的环境变量的值 例 3-1 显示当前登录数据库用户的名字 执行如下命令 : SQL> show user 假设登录用户为 SCOTT, 则 USER 为 SCOTT 例 3-2 显示 SQL*Plus 的所有设置信息 执行 show all 命令, 命令执行的结果类似于以下形式 : SQL> show all appinfo 为 OFF 并且已设置为 "SQL*Plus" arraysize 15 autocommit OFF autoprint OFF autorecovery OFF... 如果要显示某个具体的设置信息, 可以在 show 命令之后跟上相关的关键字 例 3-3 显示控制是否统计每个 SQL 命令的运行时间的参数 timing 的状态 (ON 表示统计,OFF 表示不统计 ) SQL> show timing timing OFF 如果要显示数据库服务器的参数设置信息, 可以使用 show parameter 命令, 并在命令 之后指定要显示的参数名称 由于这些信息是从参数文件中读取的, 因此只有特权用户才 可以查看这样的信息 例 3-4 显示当前数据库的名称 执行如下命令 : SQL> show parameter db_name NAME TYPE VALUE db_name string ORCL 在命令执行的结果中包含参数的名称 类型和参数值 由于参数名都是字符串, 在显示参数时, 可以只指定参数名称的一部分, 这样, 系统 将显示所有包含这个字符串的参数 例 3-5 显示所有包含字符串 db_block 的参数设置信息 执行如下命令 : SQL> show parameter db_block NAME TYPE VALUE db_block_buffers integer 0 db_block_checking boolean FALSE db_block_checksum boolean TRUE db_block_size integer

8 第 1 篇基础篇 2. 设置或修改设置信息命令 set 使用 set 命令设置或者修改环境变量的值, 这样可以灵活控制 SQL*Plus 的显示格式 命令语法格式如下 : set system_option value 其中 system_option 和 value 的设置方式如表 3-3 所示 例 3-6 设置每页打印的行数为 30 行 ( 包括 NEWPAGE 设置的空行数 ) 执行如下命令 : SQL>set PAGESIZE 30 注意 : 通过 set 命令设置的环境变量是临时的, 当用户退出 SQL*Plus 后, 用户设置的参数将全部丢失 注意 : 改变后的设置信息只对 SQL*Plus 的当前启动起作用 如果要经常修改这些设置信息, 通常的做法是编写一个脚本文件, 在脚本文件中 指定这些设置信息, 然后在 SQL*Plus 中执行脚本文件 常见的环境变量设置如表 3-3 所示 set arraysize {15 N} 表 3-3 SQL*Plus 运行环境设置 选 项 说 明 set autocommit{on off immediate n} set autoprint {on off} set autorecovery {on off} set autotrace{on off trace [only]}[explain] [statistics] set blockterminator {c on off} set cmdsep{; c on off} set colsep{ _ text} set define 设置 SQL*Plus 一次从数据库中取出的行数, 其取值范围为任意正整数 该参数的值决定 Oracle 何时提交对数据库所做的修改 当设置为 ON 和 IMMEDIATE 时, 当 SQL 命令执行完毕后, 立即提交用户做的更改 ; 而当设置为 OFF 时, 则必须用户使用 COMMIT 命令提交 关于事务处理请参考相关章节 自动打印变量值, 如果 autoprint 设置为 on, 则在过程的执行过程中可以看到屏幕上打印的变量值 ; 设置为 off 时表示只显示 过程执行完毕 这样的提示 设定为 on 时, 将以默认的文件名来记录重做记录, 当需要恢复时, 可以使用 recover automatic database 语句恢复, 否则只能使用 recover database 语句恢复 对正常执行完毕的 sql dml 语句自动生成报表信息 定义表示结束 PL/SQL 块结束的字符 定义 SQL*Plus 的命令行区分字符, 默认值为 off, 也就是说回车键表示下一条命令并开始执行 ; 假如设置为 on, 则命令行区分字符会被自动设定成 ;, 这样就可以在一行内用 ; 分隔多条 SQL 命令 设置列和列之间的分隔字符 默认情况下, 在执行 select 输出的结果中, 列和列之间是以空格分隔的 这个分隔符可以通过使用 SET COLSEP 命令来定义 用于指定一个除字符 & 之外的字符作为定义变量的字符 62

9 第 3 章 Oracle 数据库常用工具 选项说明 on: 显示文件中的每条命令及其执行结果 ; set echo {on off} off: 不显示文件中的命令, 只显示其执行结果 set linesize {80 n} 设置 SQL*Plus 在一行中能够显示的总字符数, 默认值为 80 可以的取值为任意正整数 set long {80 n} 为 LONG 型数值设置最大显示宽度, 默认值为 80 set newpage {1 n none} 设置每页打印标题前的空行数, 默认值为 1 set null text 设置当 SELECT 语句返回 NULL 值时显示的字符串 set numformat format 设置数字的默认显示格式 set pagesize {14 n} 设置每页打印的行数, 该值包括 NEWPAGE 设置的空行数 设置 SQL*Plus 输出结果时是否滚动显示 当取值为 on 时表示 set pause{off on text} 输出结果的每一页都暂停, 用户按下回车键后继续显示 ; 取为 字符串时, 每次暂停都将显示该字符串 显示或打印记录分隔符 其取值为 wrapped 时, 只有在折叠的 set recsep {wrapped each off} 行后面打印记录分隔符 ; 取值为 each 则表示每行之后都打印记录分隔符 ;off 表示不必打印分隔符 set space{1 n} 设置输出结果中列与列之间的空格数, 默认值为 10 设置在执行 SQL 命令之前是否转换大小 取值可以为 mixed( 不 set sqlcase{mixed lower upper} 进行转换 ) lower( 转换为小写 ) 和 upper( 转换为大写 ) set sqlcontinue{> test} set time {off on} set timing {off on} set underline{- c on off} set verify [on off] set wrap {on off} 设置 SQL*Plus 的命令提示符 续表 控制当前时间的显示 取值为 on 时, 表示在每个命令提示符前显示当前系统时间 ; 取值为 off 则不显示系统当前时间 控制是否统计每个 SQL 命令的运行时间 取值为 on 表示为统计,off 则不统计设置 SQL*Plus 是否在列标题下面添加分隔线, 取值为 on 或 off 时分别表示为打开或关闭该功能 ; 还可以设置列标题下面分隔线的样式 用来指定是否输出原值和新值的信息 设置当一个数据项比当前行宽时,SQL*Plus 是否截断数据项的显示 取值为 off 时表示截断,on 表示为超出部分折叠到下一行显示 3.5 使用 SQL*Plus 命令 SQL*PLUS 命令用于设置查询结果的输出格式, 形成复杂的报表, 编辑 SQL 命令, 设置系统变量并可提供帮助信息 SQL*PLus 命令不存在缓冲区中, 不以分号结束, 在输入 SQL*PLUS 命令的过程中, 按 Enter 键表示输入完成, 而不必输入分号 (;) 如果命令输入占满一行, 光标会自动移到下一行, 可以继续输入, 直到按 Enter 键结束输入 以下详细介绍几种典型 SQL*Plus 命令的使用 HELP 命令 SQL*Plus 提供了许多操作命令, 例如常用的有 HELP HOST SHOW 以及 DESCRIBE 等, 如表 3-4 所示 而且每个命令都有大量的选项, 要记住每一个命令的所有选项是很困 63

10 第 1 篇基础篇 难的 不过 SQL*Plus 提供了内建的帮助系统, 用户可以在需要的时候, 随时使用 HELP 命令查询相关的命令信息 HELP[topic] HOST HOST 操作系统命令 CLEAR SCR[EEN] 表 3-4 常用 SQL*Plus 命令 命 令 功 能 SHOW[ALL USER SGA ERRORS REL[EASE] PARAMETERS] DESC[RIBE] 查看命令的使用方法,topic 表示需要查看的命令名称 例如 : HELP DESC 使用该命令可以从 SQL*Plus 环境切换到操作系统环境, 一边执行操作系统命令 执行操作系统命令, 例如 :HOST notepad.exe, 可打开一个记事本 清屏操作 SQL*Plus 帮助系统可以向用户提供下面一些信息 : 命令标题 ; 命令作用描述的文件 ; 命令的缩写形式 ; 命令中使用的强制参数和可选参数 语法格式 : HELP [TOPIC] 查看 SQL*Plus 的所有系统变量值信息 当前是哪个用户在使用 SQL*Plus 显示 SGA 大小 查看错误信息 数据库版本信息以及系统初始化参数信息 查看对象的结构, 这里的对象可以是表 视图 存储过程 函数和包等 例如 :DESC dual 语法说明 : (1) 一对方括号 [ ]: 表示里面的部分为可选项 (2)TOPIC: 表示需要查看的命令名称 例 3-7 试获取所有 SQL 语句和 SQL*Plus 命令的帮助信息 只需在命令提示符下直接输入 help 命令, 其操作过程及执行结果如下 : SQL> HELP HELP ---- Accesses this command line help system. Enter HELP INDEX or? INDEX for a list of topics. You can view SQL*Plus resources at and the Oracle Database Library at HELP? [topic] 可以通过 HELP 命令获得帮助索引, 操作如图 3-7 所示 如果希望获取某一个命令的详细帮助信息, 可以在 HELP 之后输入该命令的名字 例 3-8 试获取所有 DESCRIBE 命令的详细帮助信息 操作方法及执行结果如下 : SQL> HELP describe DESCRIBE 64

11 第 3 章 Oracle 数据库常用工具 Lists the column definitions for a table, view, or synonym, or the specifications for a function or procedure. DESC[RIBE] {[schema.]object[@connect_identifier]} 图 3-7 通过 HELP 命令获得帮助索引 DESCRIBE 命令 DESCRIBE 命令可以返回数据库中所存储的对象的描述 对于表 视图等对象而言, DESCRIBE 命令都可以列出其各个列的名称以及各个列的属性 除此之外,DESCRIBE 还会输出过程 函数和程序包的规范 语法格式 : DESCRIBE object_name 其中 object_name 表示表 视图等对象的名字 例 3-9 试显示表 DEPT 的结构信息 执行如下命令 : SQL>DESCRIBE dept 命令执行结果如下 : NAME NULL? TYPE DEPTNO NOT NULL NUMBER (2) DNAME CHAR (14) LOC CHAR (13) 基表 DEPT 的结构信息包括如下 NAME: 列名 NULL: 标识该列是否允许为空值, 如缺省则表示允许为空值 TYPE: 说明该列的数据类型, 可以是 字符型 CHAR(w),VARCHAR2(w); 65

12 第 1 篇基础篇 数字型 NUMBER(w,d); 日期型 DATE; 原始数据型 RAW 以及 LONG 型等等 其中 w 表示列的宽度,d 表示小数点之后的位数 还可以使用 DESCRIBE 命令获取有关函数 过程以及包描述 例 3-10 显示有关函数 afunc 的信息 执行如下命令 : SQL>DESCRIBE afunc 执行结果如下 : function afunc(f1 CHAR,F2 NUMBER) return NUMBER; 编辑 SQL*Plus 命令 SQL*Plus 提供了一个命令缓冲区, 用来保存最近执行的一条 SQL 语句, 或者一个 PL/SQL 块 用户可以反复执行缓冲区中的内容, 也可以对缓冲区中的内容进行编辑 常见的编辑 SQL*PLUS 命令如表 3-5 所示 表 3-5 常用编辑 SQL*Plus 命令 命 令 缩 写 意 义 APPEND text A text text 行尾增加 CHANGE/old/new C/old/new 在当前行中将 old 改为 new CHANGE/text C/text 从当前行中删除 text CLEAR BUFFER CL BUFF 删除 SQL 缓冲区的所有行 DEL 无 删除当前行 INPUT I 增加一行或多行 INPUT text I text 增加一个由 text 组成的行 LIST L 显示 SQL 缓冲区的所有行, 并将最后一行作为当前行 LIST n L n 或 n 显示一行 LIST * L * 显示当前行 LIST LAST LAST 显示最后一行 LIST m n L m n 显示多行 ( 从 m 到 n) 1. 执行缓冲区内容执行缓冲区中内容的命令有两个 : / 和 run / 命令的作用是执行缓冲区中刚刚输入的或者已经执行的内容 如果是一条 SQL 语句, 它的结束标志是 ;, 遇到分号 ;, 这条 SQL 语句就会执行 如果在 SQL 语句执行后输入 / 命令, 这条 SQL 语句将再执行一次 如果是 PL/SQL 块, 结束标志仍然是 ;, 只是在输入结束后还必须输入 / 命令, 这个块才能执行 如果再次输入 / 命令, 这个块将再次执行 例 3-11 假设先执行了创建表 t2 的 SQL 语句 : 66

13 SQL> CREATE TABLE t2(id number); 表已创建 第 3 章 Oracle 数据库常用工具 现要求用 / 命令将以上创建表 t2 的命令再执行一次 SQL> / create table t2(id number) * ERROR 位于第 1 行 : ORA-00955: 名称已由现有对象使用 上面的例子中, 首先在 SQL*Plus 中执行 CREATE 命令创建表 t2, 然后输入 / 命令再次执行这条 SQL 语句 由于这个表已经创建, 所以出现了错误信息 run 命令与 / 命令一样, 也是再次执行缓冲区中的内容, 只是在执行之前首先显示缓冲区中的内容 例 3-12 在刚才执行了 CREATE 语句后, 使用 run 命令再执行一次 SQL> run 1* create table t2(id number) create table t2(id number) * ERROR 位于第 1 行 : ORA-00955: 名称已由现有对象使用 2. 编辑缓冲区内容 缓冲区真正的意义在于用户可以编辑其中的内容, 这样如果语句执行出错, 用户可以很方便地进行修改, 特别是长的 复杂的 SQL 语句或者 PL/SQL 块 使用最频繁的编辑命令是 edit( 或 ed), 这条命令的作用是打开默认的编辑器 ( 在 Windows 环境中为记事本 ), 并将缓冲区中的内容放在编辑器中, 文件内容以斜杠 (/) 结束 其语法格式如下 : ED[IT] [file_name] 其中,file_name 默认为 afiedt.buf, 也可以指定一个其他的文件 例 3-13 假设在 SQL*Plus 中先执行了 SQL 语句 select eename from emp; : SQL> select eename from emp; select eename from emp * ERROR 位于第 1 行 : ORA-00904: EENAME : 无效的标识符 试用 EDIT 命令将缓冲区内容写入默认的 操作如下 : SQL>edit 已写入文件 afiedt.buf 用户可以在编辑器中修改缓冲区中的内容, 修改完后保存并退出编辑器, 然后在 SQL*Plus 中输入 / 命令, 修改后的内容将在 SQL*Plus 中执行 67

14 第 1 篇基础篇 说明 : 在这个临时文件 afiedt.buf 中并不保存所有已经执行的 SQL 语句或者 PL/SQL 块, 仅当执行 edit 命令时, 才将当前缓冲区中的内容写入这个文件, 文件中以前的内容将被覆盖 3. 显示缓冲区内容 如果要显示缓冲区中的内容, 可以执行 list( 或者 l) 命令, 其用法见表 3-5 list 命令以分行的形式显示缓冲区的内容, 并在每一行前面显示行号 如果要显示某一行的内容, 可以在 list 命令之后指定行号, 这样只显示指定的一行, 并使这一行成为当前行, 而不是显示所有内容 例 3-14 假设在缓冲区中已经有一条 SQL 语句, 我们可以以不同的形式执行 list 命令, 其含义可见表 3-5 的说明 SQL> list 1 SELECT ename 2 FROM emp 3* WHERE deptno=10; SQL> l 2 2* FROM emp SQL> l 3 3* WHERE deptno=10; 说明 : 使用编辑命令时, 如果输入的 SQL 语句超过一行,SQL*Plus 会自动增加行号, 并在屏幕上显示行号 根据行号, 就可以使用编辑命令对指定的行进行相关操作 还有一种简单的方法用来显示某一行的内容 在 SQL*Plus 提示符下直接输入一行的行号, 结果与将行号作为参数的 list 命令是等价的 4. 追加缓冲区内容 向缓冲区追加内容有两种命令方式 :append 和 input 下面具体介绍这两种命令的功能及用法 1)append 命令 append 命令 ( 或者 a) 的作用是在缓冲区中当前行的末尾追加文本 在默认情况下, 最后一行是当前行 如果以某一行的行号作为参数执行了 list 命令, 那么指定的行将成为当前行 append 命令的格式为 : append 文本 append 将把指定的文本追加到当前行的末尾 说明 : 注意追加的文本不需要用引号限定, 否则引号将作为文本的一部分一起被追加 例 3-15 在前面的 SELECT 语句的基础上, 在第一行的末尾追加文本, sal 追加的步骤为 : SQL> l 1 1* SELECT ename SQL> append, sal 1* SELECT ename, sal 68

15 第 3 章 Oracle 数据库常用工具 这样, 缓冲区中第一行的内容由原来的 SELECT ename 变为 SELECT ename,sal, SELECT 语句执行的结果将检索 ename 和 sal 两个列的值 2)input 命令 append 命令的作用是在当前行的末尾追加文本 如果要在缓冲区中增加一行, 就要使用 input 命令 input 命令 ( 或者 i) 的作用是在当前行之后追加一行或者多行 在默认情况下,input 命令在最后一行之后追加文本 如果要在某一行之后追加, 应该先执行 list 命令使该行成为当前行, 然后再追加 使用 input 命令追加文本时, 可以只追加一行, 这时 input 命令的格式为 : input 文本 如果要追加多行, 则需要输入不带参数的 input 命令并回车, 这时行号将变成 ni 的形式, 其中 n 是从当前行号的下一个数字开始的整数, 表示该行内容是追加到缓冲区中的 追加结束后以一个空行和回车符结束 例 3-16 假设当前缓冲区中有一条不完整的 SQL 语句 : SQL> list 1 SELECT ename 2* AND sal>1000; 现在要求显示职工表 EMP 中职工所在部门编号为 10 且月工资在 1000 以上 ( 不包括 1000) 的职工信息 请补充完整以上缓冲区中的 SQL 语句 在第一行之后增加 from 子句和 where 子句, 操作步骤如下 : SQL> list 1 1* SELECT ename SQL> input 2i FROM emp 3i WHERE deptno=10 4i SQL> 当前缓冲区中的内容为 : SQL> list 1 SELECT ename 2 FROM emp 3 WHERE deptno=10 4* AND sal>1000; 注意, 在追加多行时,input 命令为追加的新行重新显示了行号, 即上面的 2i 3i 等 输入结束后, 在下一行直接回车, 这时重新显示 SQL*Plus 提示符, 追加操作便告结束 3)change 命令如果发现缓冲区中的内容有错误, 可以用 edit 命令打开编辑器, 在编辑器中进行修改 还有一种修改方法, 就是 change 命令 change( 或者 c) 命令的作用是在缓冲区中当前行上用新的字符串代替旧的字符串 这条命令的格式为 : change / 新字符串 / 旧字符串 例 3-17 把上面修改后的 SELECT 语句中的最后一个条件 sal>1000 改为 comm is not null 69

16 第 1 篇基础篇 操作的步骤为 : SQL> list 4 4* and sal>1000; SQL> change /sal>1000/comm is not null 4* and comm is not null; 重新显示的结果表明这一行的内容已经被修改 5. 清除缓冲区内容 如果要清空缓冲区中的内容, 可以执行 del 命令 当缓冲区被清空后, 就不能再执行 edit 命令进行编辑, 也不能再执行 list 命令进行显示了 如果只删除缓冲区中的一部分内容, 则通过 edit 和 list 命令可以显示剩下的内容 在默认情况下,del 命令删除缓冲区中当前行的全部内容 但是通过指定参数,del 命令可以删除指定的一行或者多行 del 命令的格式有以下形式 (1)del 开始行号结束行号 : 删除开始行号和结束行号之间的行 (2)del 开始行号 *: 删除开始行号和当前行之间的行 (3)del* 结束行号 : 删除当前行和结束行号之间的行 (4)del last: 删除最后一行 其中开始行号和结束行号是指定的行号, 开始行号必须小于结束行号 符号 * 用来代表当前行, 标识符 last 代表最后一行 例 3-18 删除缓冲区中上面修改后的 SELECT 语句中的所有行 SQL>del 如何对操作系统文件进行读写在 SQL*Plus 中可以对操作系统中的文本文件进行简单的读写访问 例如, 事先将 SQL 语句或者 PL/SQL 块的代码存放在文本文件中, 再把文本文件调入缓冲区中, 使之执行 或者把当前缓冲区中的内容保存到一个文件中, 或者把 SQL 语句 PL/SQL 块的执行结果保存到文件中 以下分别介绍对文本文件进行读和写的命令 1) get 和 start 等命令 (1)@ 命令的作用是将指定的文本文件的内容读到缓冲区中, 并执行它 文本文件可以是本地文件, 也可以是远程服务器上的文件 如果是本地文件,@ 命令的执行格式为 文件名这里的文件名要指定完整的路径, 默认的扩展名是.sql, 如果脚本文件使用了默认的扩展名, 命令中可以省略扩展名 如果是远程文件, 必须将它存放到一个 web 服务器上, 并以 HTTP 或 FTP 方式访问 命令的执行格式为 ( 以 HTTP 为例 服务器 / 命令读取文件时, 文件中可以包含多条 SQL 语句, 每条语句以分号结束 ; 或者 70

17 第 3 章 Oracle 数据库常用工具 可以包含一个 PL/SQL 块 文件被读入缓冲区中以后,SQL*Plus 将按顺序执行文件中的代码, 并将执行结果输出到显示器上 例 3-19 假设在 D:\temp 目录下创建 ( 可用记事本创建 ) 了一个脚本文件 my_1.sql, 文件的内容为 : SELECT ename FROM emp WHERE empno=7902; SELECT dname FROM dept WHERE deptno=10; 命令将这个文件读到缓冲区中 命令执行的格式如下 : D:\temp\my_1 ENAME SMITH DNAME ACCOUNTING 说明 : 如果在 Windows 文件夹中创建的脚本文件名带有空格 ( 例如 D:\Oracle Demo\my_ 1.sql), 命令之后的内容加上一对英文输入状态下的双引号, D:\Oracle 命令还有一个用法, 就是在启动 SQL*Plus 的同时, 将指定的文件读入缓冲区并执行它 命令和文件名一起作为 SQL*Plus 的命令行参数, 格式如下 : sqlplus 用户名 / 文件名 注意, 这种格式与以前提到的使用网络服务的格式是很相似的, 但是仍然有区别, 请注意观察 : sqlplus 用户名 / 网络服务名 由于文件名和网络服务名都表现为字符串, 所以单纯从名字上无法区分到底使用了文件名还是网络服务名 二者的区别在于第一种格式中在用户名 / 口令之后有一个空格, 这时将把后面的参数解释为一个文件, 并把这个文件加载到缓冲区中 在第二种格式中, 用户名 / 口令之后没有空格, 这时将后面的参数解释为网络服务名 (2)get 命令 get 命令相似, 但是它只是把文件加载到缓冲区中, 并不直接执行 get 命令的执行格式为 : get 文件名选项 其中文件名的默认扩展名为.sql, 在 get 命令中可以省略 目前 get 命令只支持本地的操作系统文件 可以使用的选项有两个 :LIST 和 NOLIST 其中 LIST 选项指定将文件的内容读到缓冲区的同时, 还要在显示器上输出, 这是默认选项 ; 选项 NOLIST 使得文件的内容不在显示器上输出 使用 get 命令时还要注意, 在文本文件中只能包含一条 SQL 语句, 而且不能以分号结束 也可以只包含一个 PL/SQL 块, 块以分号结束 和 get 命令时要注意这些格式上的差别 71

18 第 1 篇基础篇 例 3-20 假设在 D:\temp 目录下创建 ( 可用记事本创建 ) 了一个脚本文件 my_2.sql, 文件的内容为 : SELECT ename FROM emp WHERE empno=7902; 要求通过 get 命令把它读入缓冲区, 然后使用 / 命令使之执行 命令执行的格式如下 : SQL> get D:\temp\my_2 1* SELECT ename FROM emp WHERE empno=7902; SQL> / ENAME SMITH start 命令是等价的, 这里不再赘述 2) 写文件命令写文件涉及的命令包括 save 和 spool (1)save 命令 save 命令用于将当前缓冲区中的内容写入一个操作系统文件, 其语法格式为 : SQL>save 文件名选项 这里的文件名要指定完整的路径, 默认的扩展名是.sql; 如果不指定完整的路径, 则在当前目录下产生这个文件 选项指定以什么样的方式写文件 可以使用的选项有以下 3 个 CREATE: 如果文件不存在, 则创建 否则, 命令执行失败 该选项为默认值 APPEND: 如果文件不存在, 则创建 否则, 在文件末尾追加 REPLACE: 如果文件不存在, 则创建 否则删除原文件, 重新创建 例 3-21 假设当前缓冲区中有一条 SELECT 语句, 使用 save 命令将这条语句写入文件 D:\temp\my_3.sql 中 操作如下 : SQL> list 1* SELECT * FROM emp; SQL> save D:\temp\my_3 已创建 file my_3.sql 如果该文件已经存在, 若不指定 APPEND 或 REPLACE 选项, 将会显示错误提示信息 例如 : SQL> save D:\temp\my_3 SP2-0540: 文件 my_3.sql 已经存在 使用 SAVE filename REPLACE 指定 APPEND 或 REPLACE 选项, 则再次保存数据成功, 操作如下 : SQL> save D:\temp\my_3 APPEND 已将 file 附件到 my_3.sql (2)spool 命令 spool 命令用于将命令的执行结果输出到一个操作系统文件 它有以下几种用法 72

19 第 3 章 Oracle 数据库常用工具 spool: 得到当前 spool 的状态, 默认为不可用 spool 文件名 : 启动 spool, 并打开指定的文件 spool off: 关闭 spool, 并将 SQL*Plus 的输出写入文件中 spool out: 关闭 spool, 将 SQL*Plus 的输出写入文件中, 并同时送往打印机 如果在 SQL*Plus 中以命令行的方式执行 spool 命令, 那么从执行 spool 命令并打开文件开始, 此后的所有输出, 包括错误信息, 以及用户的键盘输入, 都将写入指定的文件, 直到遇到 spool off 或者 spool out 但是这些信息的写入是一次性完成, 即在执行 spool off 或者 spool out 的一瞬间, 这些信息才一次全部写入文件, 包括最后执行的 spool off 或者 spool out 命令本身 文件的默认扩展名为.lst, 默认的路径是当前目录 spool 命令通常的用法是生成报表 首先将精心设计的 SQL 语句存放在一个文件中, 在产生输出的语句前后加上 spool 命令, 然后将这个文件读到缓冲区中执行 这样在写入的文件中只有命令执行的结果, 而不包括 SQL 语句本身 例 3-22 假设 D:\temp 下有一个脚本文件, 名为 my_4.sql, 它的内容为 : spool cc SELECT ename, sal FROM emp WHERE deptno=20; spool off 现在将这个文件读到缓冲区中, 并使之执行 my_4 ENAME SAL SMITH 800 JONES 2975 文件中 SQL 语句的执行结果显示在屏幕上, 同时在当前目录下生成了文件 cc.lst, 文件的内容与屏幕上显示的结果完全一致 如何在 SQL*Plus 中使用变量 为了使数据处理更加灵活, 在 SQL*Plus 中可以使用变量 SQL*Plus 中的变量在 SQL*Plus 中的整个启动期间一直有效, 这些变量可以用在 SQL 语句 PL/SQL 块以及文本文件中 在执行这些代码时, 需要先将变量替换为变量的值, 然后再执行 1. 用户自定义的变量用户可以根据需要, 自己定义变量 SQL*Plus 中有两种类型的自定义变量 : 第一类变量不需要定义, 可以直接使用, 在执行代码时,SQL*Plus 将提示用户输入变量的值 ; 第二类变量需要事先定义, 并且需要赋初值 1) 第一类自定义变量第一类变量不需要事先定义, 在 SQL 语句 PL/SQL 块以及脚本文件中可以直接使用 包括两种表达形式 : & 变量名 和 && 变量名 (1)& 变量名这类变量的特点是在变量名前面有一个 & 符号 当执行代码时, 如果发现有这样的变量,SQL*Plus 将提示用户逐个输入变量的值, 当用变量值代替变量后, 才执行代码 73

20 74 第 1 篇基础篇 例 3-23 假设用户构造了一条 SELECT 语句, 在语句中使用了两个变量, 如下 : SELECT ename, sal FROM &table_name WHERE ename='&name'; 则这条语句的执行过程为 : 输入 table_name 的值 : emp 输入 name 的值 : SMITH 原值 1: SELECT ename, sal FROM &table_name WHERE ename='&name'; 新值 1: SELECT ename, sal FROM emp WHERE ename='smith'; ENAME SAL SMITH 800 其中字符串 emp 和 SMITH 是用户输入的变量值 在 SQL*Plus 中首先用变量值代替变量, 生成一个标准的 SQL 语句, 然后再执行这条语句 当为所有的变量都提供了变量值后, 这条语句才能执行 说明 : 在构造这样的 SQL 语句时要注意, 使用变量和不使用变量的语句在形式上是一致的 例如,ename 列的值为字符型, 应该用一对单引号限定, 使用了变量以后, 仍然要用一对单引号限定 (2)&& 变量名上述语句如果需要再次执行, 系统将提示用户再次逐个输入变量的值 为了使用户在每次执行代码时不需要多次输入变量的值, 可以在变量名前加上 && 符号 使用这种形式的变量, 只需要在第一次遇到这个变量时输入变量的值, 变量值将保存下来, 以后就不需要不断地输入了 例 3-24 假设把上述 SELECT 语句改为以下形式 : SELECT ename, sal FROM &&table_name WHERE ename='&&name'; 那么在第一次执行时, 像以前一样需要输入变量的值, 而再次执行时, 就不再需要输入变量的值了, 直接使用以前提供的变量值 以下是第二次以后的执行情况 : SQL> / 原值 1: SELECT ename,sal FROM &&table_name WHERE ename='&&name'; 新值 1: SELECT ename, sal FROM emp WHERE ename='smith';... 2) 第二类自定义变量在 SQL*Plus 中可以使用的第二类自定义变量需要事先定义, 而且需要提供初值 定义变量的命令是 DEFINE 定义变量的格式是: define 变量名 = 变量值 变量经定义后, 就可以直接使用了 实际上, 用 DEFINE 命令定义的变量和使用 & 的变量在本质上是一样的 用 DEFINE 命令定义变量以后, 由于变量已经有值, 所以在使用变量时不再提示用户输入变量的值 如果执行不带参数的 DEFINE 命令, 系统将列出所有已经定义的变量, 包括系统定义的变量和用 & 定义的变量, 以及即将提到的参数变量

21 第 3 章 Oracle 数据库常用工具 例 3-25 使用 DEFINE 命令查看所有已定义的变量 SQL> define DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR) DEFINE _SQLPLUS_RELEASE = " " (CHAR) DEFINE _EDITOR = "NOTEPAD" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release Production With the Partitioning, OLAP, Data Mining and Real Application Testing Options" (CHAR) DEFINE _O_RELEASE = " " (CHAR) DEFINE TABLE_NAME = "emp" (CHAR) DEFINE NAME = "SMITH" (CHAR) 其中最后两个变量就是刚才我们用 & 定义的变量 例 3-26 分别使用 DEFINE 命令定义变量 col_name 和 salary, 并分别将 ename 和 2000 赋值给这两个变量 SQL> define col_name=ename SQL> define salary=2000 在这里定义了两个变量, 然后在 SQL 语句中就可以直接使用这两个变量了 在使用变量时, 仍然用 & 变量名 的形式来引用变量的值 例如 : SQL> SELECT &col_name FROM emp WHERE sal>&salary; 在执行这条语句时, 用 ename 代替变量 col_name, 用 2000 代替变量 salary, 生成一条标准的 SQL 语句 这条语句的执行结果为 : 原值 1: SELECT &col_name FROM emp WHERE sal>&salary; 新值 1: SELECT ename FROM emp WHERE sal>2000; ENAME JONES BLAKE 当一个变量不再使用时, 可以将其删除 undefine 命令用于取消一个变量的定义 删除一个变量的命令格式为 : undefine 变量名 例 3-27 分别使用 UNDEFINE 命令取消上例中定义的变量 col_name 和 salary SQL>undefine col_name SQL>undefine salary 则原先定义的两个变量 col_name 和 salary 不再有效 2. 参数变量 在 SQL*Plus 中, 除了用户自定义的变量外, 还有一类变量, 这就是参数变量 参数变量在使用时不需要事先定义, 可以直接使用 前面我们讲述了 get 命令的用法 这两个命令的作用是将一个文本文件加载到缓冲区中, 使之执行 因为文本文件的内容是固定的, 在执行期间不能被修改, 所以只能执行固定的代码, 这就为灵活地数据操作带来了一定的困难 例如, 要查询某部门中员工的工资情况 部门号事先不确定, 而是根据实际情况临时确定的 这样在文本文件的 SELECT 75

22 第 1 篇基础篇 语句中就不能将部门号指定为一个固定值 解决这个问题的一个办法是使用参数变量 由于部门号是不确定的, 所以在执行文本文件时可以将实际的部门号作为一个参数, 在 SELECT 语句中通过参数变量引用这个参数 参数在 SQL*Plus 的命令行中指定的格式为 文件名参数 1 参数 2 参数 3... 这样在文本文件中就可以用参数变量 &1 &2 &3 分别引用参数 1 参数 2 参数 3... 例如, 要查询某部门中工资大于某个数值的员工姓名, 在构造 SELECT 语句时就不能将部门号和工资这两个列的值指定为固定值, 而是分别用一个参数变量代替 例 3-28 假设在目录 D:\temp 中建立了一个文本文件 my_5.sql, 文件的内容为 : SELECT ename FROM emp WHERE deptno=&1 and sal>&2; 在执行这个文本文件时, 需要为参数变量 &1 和 &2 指定实际的参数值 参数值是在用 get 命令加载文本文件时指定的 例如, 要查询部门 30 中工资大于 2000 的员工, 执行文件 my_5.sql 的命令格式为 : D:\temp\ my_ 这条命令执行的情况为 : 原值 1: SELECT ename FROM emp WHERE deptno=&1 and sal>&2; 新值 1: SELECT ename FROM emp WHERE deptno=30 and sal>2000;... 从命令的执行结果可以看出, 在 SQL*Plus 中首先用实际参数 30 代替参数变量 &1, 用参数 2000 代替参数变量 &2, 生成一条标准的 SQL 语句, 然后才执行这条 SQL 语句 3. 与变量有关的交互式命令 SQL*Plus 还提供了几条交互式命令, 主要包括 prompt accept 和 pause 这几条命令主要用在文本文件中, 用来完成灵活的输入输出 (1)prompt 命令 prompt 命令用来在屏幕上显示指定的字符串 这条命令的格式为 : prompt 字符串 注意这里的字符串不需要单引号限定, 即使是用空格分开的几个字符串 prompt 命令只是简单地把其后的所有内容在屏幕上显示 例 3-29 试用 prompt 命令在屏幕上显示 I'm a programmer SQL> prompt I'm a programmer I'm a programmer (2)accept 命令 accept 命令的作用是接收用户的键盘输入, 并把用户输入的数据存放到指定的变量中, 它一般与 prompt 命令配合使用 accept 命令的格式为 : accept 变量名变量类型 prompt 提示信息选项 其中变量名是指存放数据的变量, 这个变量不需要事先定义, 可直接使用 变量类型 76

23 第 3 章 Oracle 数据库常用工具 是指输入的数据的类型, 目前 SQL*Plus 只支持数字型 字符型和日期型数据的输入 prompt 用来指定在输入数据时向用户显示的提示信息 选项指定了一些附加的功能, 可以使用的选项包括 :hide 和 default hide 的功能是使用户的键盘输入不在屏幕上显示, 这在输入保密信息时非常有用 ;default 为变量指定默认值, 在输入数据时如果直接回车, 则使用该默认值 例 3-30 从键盘输入一个数字型数据到变量 d, 在输入之前显示指定的提示信息, 并为变量指定默认值, 这样如果在输入数据时直接回车, 那么变量的值就是这个默认值 对应的 accept 命令的形式为 : SQL> accept d number prompt 请输入变量 d 的值 : default 0 请输入变量 d 的值 :100 这样变量 d 的值为 100 (3)pause 命令 pause 命令的作用是使当前的执行暂时停止, 在用户输入回车键后继续 一般情况下 pause 命令用在文本文件的两条命令之间, 使第一条命令执行后出现暂停, 待用户输入回车键后继续执行 pause 命令的格式为 : pause 文本 其中文本是在暂停时向用户显示的提示信息 例 3-31 在目录 D:\temp 中建立了一个文本文件 my_6.sql, 演示交互式命令的用法 文本文件 my_6.sql 的功能是统计某个部门的员工工资, 部门号需要用户从键盘输入 文本文件的内容如下 : prompt 工资统计现在开始 accept dno number prompt 请输入部门号 : default 0 pause 请输入回车键开始统计... SELECT ename, sal FROM emp WHERE deptno=&dno; 试执行这个脚本文件 D:\temp\ my_6 工资统计现在开始请输入部门号 :30 请输入回车键开始统计... 原值 1: SELECT ename,sal FROM emp WHERE deptno=&dno 新值 1: SELECT ename,sal FROM emp WHERE deptno= 30 ENAME SAL ALLEN 1600 WARD 1250 MARTIN 1250 BLAKE 2850 如果希望生成一个报表, 那么可以在 SELECT 前后分别加上 spool 命令, 将统计的结果写到一个文件中, 或者发往打印机 SQL*Plus 的报表功能 SQL*Plus 有一个强大的功能, 就是能够根据用户的设计生成美观的报表 实际上, 利 77

24 第 1 篇基础篇 用本章前面介绍的知识已经能够生成一个简单的报表了, 但是如果要生成规范的 美观的报表, 还要学习 SQL*Plus 的其他一些功能 SQL*Plus 的报表功能是利用它的命令来实现的, 与制作报表有关的 SQL*Plus 命令见表 3-6 首先, 用户要根据自己的意图, 设计报表的显示格式, 包括报表的标题 各列的格式等 然后构造查询语句, 决定要对哪些数据进行显示 最后还要决定把报表仅仅显示在屏幕上, 还是存放在文本文件中, 或者送往打印机 表 3-6 常用制作报表命令 命 令 定 义 TTITLE 为报表的每一页设置头标题 BTITLE COLUMN BREAK COMPUTE SET LINESIZE SET PAGESIZE SET NEWPAGE SET HEADSEP 为报表的每一页设置底标题 设置列的标题和格式 将报表中的数据分组显示并设置组间间隔 计算分组数据的汇总值 设置报表每行允许的最大字符数 设置每页的最大行数 设置页与页之间的空行数 设置标题分隔符 一般情况下, 生成一个报表需要许多条命令, 如果每次在生成报表时都输入这么多的命令, 是件很麻烦的事情 通常的做法是把这些命令放到一个文本文件中, 在需要时只把这个文本文件读到缓冲区中, 并使其执行即可 1. 报表的标题设计 报表的标题是利用 SQL*Plus 的两个命令来设计的, 即 TTITLE 和 BTITLE 其中 TTITLE 命令用来设计报表的头部标题 ( 页眉 ), 而 BTITLE 用来设计报表的尾部标题 ( 页脚 ) 它们的命令格式如下: TTITLE [ 位置说明 < 表头 >] [OFF ON] BTITLE [ 位置说明 < 表尾 >] [OFF ON] 其中说明如下 1) 位置说明 : 规定标题在一行中的位置, 关于标题的位置说明可以使用表 3-7 所示的子句 表 3-7 表标题位置说明子句举例说明 COL n COL 72 让标题信息从当前行左边的第 n 个位置开始显示 SKIP n SKIP 2 LEFT LEFT 标题信息靠左放置 CENTER CENTER 标题信息居中放置 RIGHT RIGHT 标题信息靠右放置 打印 n 个空行, 如果 n 未指明, 则打印一个空行 ; 如果 n 为 0, 则不打印空行 ; 如果 n 大于 1, 则为两行文字间加入 n-1 个空行 78

25 第 3 章 Oracle 数据库常用工具 2)< 表头 > < 表尾 >: 指定了标题的内容 一般情况下, 标题可以指定为以下内容 指定的文本 SQL.LNO: 当前的行号 SQL.PNO: 当前的页号 SQL.RELEASE: 当前 Oracle 的版本号 SQL.USER: 当前登录的用户名称 3)OFF ON:OFF 表示关闭标题的显示 ;ON 表示打开标题的显示, 默认为 ON 例 3-32 查询 EMP 表, 假设使用报表标题设置命令对 SQL 语句执行结果所做设置保存在脚本文件 D:\temp\ my_7.sql 中, 脚本文件内容如下 : TTITLE CENTER 部门编号为 30 的职工基本信息 BTITLE CENTER 单位内部信息 SELECT DEPTNO, ENAME, SAL FROM EMP WHERE DEPTNO=30; TTITLE OFF BTITLE OFF 试分析并执行以上脚本文件里面的命令及语句 D:\temp\ my_7 查询结果显示如下 : 部门编号为 30 的职工基本信息 DEPTNO ENAME SAL ALLEN WARD MARTIN BLAKE 2850 单位内部信息已选择 4 行 2. 设置报表尺寸每页报表中都包含表头 列标题 查询的结果和表尾信息 报表尺寸的设置对于这些内容的正确显示都是十分必要的 系统默认的报表尺寸如下 : 每页报表表头空一行 ; 每页输出内容为 14 行 ( 包括表头和表尾之间的所有内容 ); 每行所能显示的字符数为 80 可以通过 SET 命令改变上述设置 1)SET NEWPAGE 命令该命令设置每一页的表头与每一页开始位置之间的空行数, 实际上就是页与页之间的空行数, 命令如下 : SET NEWPAGE 行数如果设置行数为 0, 系统将在每页的日期前产生一个顶部格式字符 ( 通常是 16 进制的 13) 大部分打印机立即响应这个字符并将打印头移至下一页的开始, 即报表打印的起始位置 如果将 NEWPAGE 设置变大,SQL*PLUS 输出的信息行就会减小 而每页的总行数不变 79

26 第 1 篇基础篇 2)SET PAGESIZE 命令该命令设置每页的输出行数, 包括表头 表尾 列标题和查询出的信息 对于一般的打印纸, 该值通常设置为 66 命令如下: SET PAGESIZE 行数 SET PAGESIZE 命令一般与 SET NEWPAGE 配合使用 3)SET LINESIZE 命令该命令控制出现在一行上的最大字符数 命令如下 : SET LINESIZE 字符数 如果一行查询结果的总宽度超过了 LINESZIE 设置的行宽,SQL*Plus 将把多出的列折行输出 LINESIZE 的大小还会影响表头 日期和页码的放置位置, 因为表头的居中显示和居右显示要根据 LINESIZE 的值确定 例 3-33 设置新的报表尺寸 : SQL>SET PAGESIZE 66 SQL>SET NEWPAGE 0 SQL>SET LINESIZE 32 若要恢复系统默认设置, 则执行下列命令 : SQL>SET PAGESIZE 14 SQL>SET NEWPAGE 1 SQL>SET LINESIZE 设置列 COLUMN 使用 SQL*Plus 的 COLUMN 命令可以改变列标题及各列数据的显示格式, 语法格式如下 : column [{colunmn alias}] [options] 语法说明如下 (1)column: 列名 (2)alias: 列的别名 (3)options: 指定用于格式化列或列的别名的一个或多个选项 常用选项如表 3-8 所示, 表中第一行 FORMAT format 的常用 format 格式元素见表 3-9 FOR[MAT] format HEA[DING] text 表 3-8 options 选项常用设置 选 项 说 明 JUS[TIFY][{LEFT CENTER RIGHT}] WRA[PPED] WOR[D_WRAPPED] CLE[AR] TRUNCATED 将列或列名的显示格式设置为由 format 字符串指定的格式, format 可以使用的格式见表 3-9 设置由 text 字符串指定的列标题 将列的输出信息设置为左对齐 居中对齐或右对齐 在输出结果中将一个字符串的末尾换行显示 该选项可能导致单个单词跨越多行 与 WRAPPED 选项类似, 但是单个单词不会跨越多行 清除列的格式化 删除第一行的字符串 80

27 第 3 章 Oracle 数据库常用工具 NULL text PRINT NOPRINT 选 项 说 明 指定列为空值时显示的内容 显示列标题 隐藏列标题 续表 表 3-9 format 格式元素元素说明举例 An 为 [VAR]CHAR 类型的列内容设置宽度 如果内容超过指定的宽度, 则内容自动换行 9 设置 NUMBER 列的显示格式 $ 浮动的货币符号 $9,999 L 本地货币符号 L9999. 小数点位置 , 千位分隔符 9,999 例 3-34 在 D:\temp\ my_8.sql 中有以下内容 : TTITLE LEFT ' 日期 :'_DATE CENTER ' 员工基本信息表 ' RIGHT ' 页 : ' FORMAT 999 SKIP 2 SQL.PNO BTITLE CENTER ' 谢谢使用报表!' SET ECHO OFF// 禁止 SQL*Plus 显示脚本中的 SQL 语句和命令 SET VERIFY OFF// 用来禁止显示验证信息 SET PAGESIZE 30 SET LINESIZE 120 CLEAR COLUMNS COLUMN empno HEADING ' 职工编号 ' FORMAT 9999 COLUMN ename HEADING ' 职工姓名 ' FORMAT A10 COLUMN hiredate HEADING ' 受雇日期 ' JUSTIFY CENTER COLUMN sal HEADING ' 职工工资 ' FORMAT $99, SELECT empno, ename, hiredate, sal from emp; CLEAR COLUMNS TTITLE OFF BTITLE OFF 试分析并执行以上脚本文件里面的命令及语句 SQL> start D:\temp\ my_8 查询结果显示如下 : 日期 :30- Dec -13 员工基本信息表 页 : 1 职工编号 职工姓名 ' 受雇日期 职工工资 SMITH 17-Dec-80 $ ALLEN 20-Feb-81 $1, WARD 22-Feb-81 $1, JONES 2-Apr-81 $2, MARTIN 28-Sep-81 $1, BLAKE 1-May-81 $2, 已选择 6 行 A5 81

28 第 1 篇基础篇 82 对于 COLUMN 命令, 有以下几点说明 (1) 若想显示某一列的显示属性, 可以使用命令 : COLUMN 列名 (2) 若想显示所有列的显示属性, 则使用命令 : COLUMN (3) 如果想将某列的显示属性重置成默认形式, 可以使用 COLUMN 命令的 CLEAR 子句 : COLUMN 列名 CLEAR (4) 若希望将所有列的显示属性重新置成默认的形式, 则使用下列命令 : SQL>CLEAR COLUMNS columns cleared. 4. 计算小计 当在 SELECT 命令中使用 ORDER BY 子句时, 会将数据按某一列的值排序, 该列值相同的各行数据将会排列在一起输出 为了使输出的内容更为有用和清晰, 可以使用 BREAK 和 COMPUTE 命令, 使用 BREAK 命令, 可以将报表中的信息分成若干组, 然后就可以使用 COMPUTE 命令分别计算各组的汇总值 BREAK 命令和 COMPUTE 命令的语法格式如下 : BREAK [ON 列名 ] SKIP n COMPUTE function OF 列名 1 列名 2 ON 列名 语法说明如下 (1)SKIP n: 表示在指定列的变化之前插入 n 个空行 (2)function: 表示执行的汇总操作 常用的汇总操作函数如表 3-10 所示 使用 COMUTE 的函数, 可以计算 COMPUTE 命令的 OF 和 ON 之间的所有列或列表达式的汇总值, 计算结果分别显示在各个分组之后, 计算的内容显示在第一列上 表 3-10 COMPUTE 命令中使用的函数 函 数 说 明 SUM 计算总和 MIN MAX AVG STD VAR COUNT NUMBER 计算最小值 计算最大值 计算平均值 计算标准偏差 计算协方差 计算非空值的总个数 计算行数 例 3-35 列出 30 号部门的雇员及他们工资的平均值和总和, 所有命令存放在 D:\temp\ my_9.sql 中 BREAK ON DEPTNO COMPUTE AVG SUM OF SAL ON DEPTNO

29 SELECT DEPTNO,ENAME,SAL FROM EMP WHERE DEPTNO=30 ORDER BY DEPTNO, SAL; 执行该脚本文件 : 第 3 章 Oracle 数据库常用工具 SQL>@ D:\temp\ my_9 DEPTNO ENAME SAL 30 ALLEN 1600 WARD 1250 MARTIN 1250 BLAKE 2850 avg sum 6950 已选择 4 行 对于 BREAK 命令和 COMPUTE 命令, 有以下几点说明 (1) 若想显示 BREAK 命令的当前设置, 可以输入下列命令 : SQL>BREAK (2) 如果希望清除 BREAK 命令的设置, 可以输入命令 : SQL>CLEAR BREAKS (3) 如果需要查看已经定义的 COMPUTE 命令, 可以输入命令 : SQL>COMPUTE (4) 可以使用带 COMPUTES 子句的 CLEAR 清除 COMPUTE 的设置 : SQL>CLEAR COMPUTES Computes cleared COMPUTE 命令与 BREAK 命令密切相关 COMPUTE 命令一般是计算由 BREAK ON 具体指定的那一部分内容的汇总值 COMPUTE 命令必须有相应的 BREAK 命令, 且关键字 ON 后面的内容应该一致 反之,BREAK 命令却可以单独使用, 不必有 COMPUTE 命令对应, 但使用这两个命令必须遵循以下原则 : 每一个 BREAK ON 必须要有一个相关的 ORDER BY 子句 每一个 COMPUTE 必须要有一个相关的 BREAK ON 正确地掌握这两条原则就可以顺利地使用这两条命令 一旦定义了 COMPUTE 和 BREAK 命令, 它们将一直有效, 直到它们被重新定义或清除 用户退出 SQL*plus, 本次设置的这两条命令也将失败 3.6 企业管理器 (OEM) Oracle 11g 企业管理器简称 OEM(Oracle Enterprise Manager) 是一个功能全面的图形界面管理工具, 可管理本地数据库环境和网络环境, 其主要功能包括 : 83

30 第 1 篇基础篇 数据中心管理 ; 生命周期管理 ; 存储管理 ; 有效性管理 ; 性能管理 ; 故障管理 Oracle 11g 企业管理器与之前 9i 版本的区别在于,11g 是 B/S 结构,9i 为 C/S 结构 以下简要介绍 OEM 的启动和使用方法 OEM 的启动 在成功安装完 Oracle 11g 后,OEM 也就被安装完毕, 启动 Oracle 11g 的 OEM 只需在浏览器中输入其 URL 地址 通常为 然后连接主页即可 ; 也可以在 开始 菜单的 Oracle 程序组 中选择 Database Control - orcl 菜单命令来启动 Oracle 11g 的 OEM 工具, 如图 3-8 所示 图 3-8 打开 OEM 如果是第一次使用 OEM, 启动 Oracle 11g 的 OEM 后, 需要安装 信任证书 或者直接选择 继续浏览此网站 即可 然后就会出现 OEM 的登录页面, 用户需要输入登录用户名 ( 如 SYSTEM SYS 和 SCOTT 等 ) 和登录口令, 如图 3-9 所示 图 3-9 登录 OEM 84

31 第 3 章 Oracle 数据库常用工具 在输入用户名和口令后, 单击 登录 按钮, 若用户名和口令都正确, 就会出现 数据库实例 的 主目录 属性页, 如图 3-10 和图 3-11 所示, 用于从数据库层面监控表空间占用情况以及 Oracle 告警日志监控管理 图 3-10 主目录 页面 图 3-11 表空间信息 85

32 第 1 篇基础篇 OEM 的使用 以下分别介绍 主目录 页面后面的 性能 可用性 服务器 方案 数据移动 以及 软件和支持 5 个属性页的基本功能 性能 属性页为数据库对系统性能包括 CPU 内存和 I/O 等进行监控的界面, 同时提供对 AWR/ADDM 进行管理 报告生成等功能, 如图 3-12 图 3-13 图 3-14 和图 3-15 所示 图 3-12 性能 界面 86 图 3-13 监控信息

33 第 3 章 Oracle 数据库常用工具 图 3-14 报告信息 图 3-15 AWR/ADDM 管理信息 可用性 属性页主要用于对系统备份策略 路径等进行设置, 如图 3-16 所示 87

34 第 1 篇基础篇 图 3-16 可用性 界面 服务器 属性页主要对服务器进行管理, 包括 :(1) 存储管理 : 对表空间进行管理 ( 创建 扩表空间 ) 和 ASM 管理 ;(2)Oracle 调度管理 :JOB 管理 ;(3) 数据库配置管理 : 初始化参数管理和内存指导等 ;(4) 统计信息管理 :AWR 管理和 AWR 基线管理 ;(5) 安全性管理 : 用户 角色 概要文件 审计和虚拟专用数据库等 ;(6) 查询优化管理 ;(7) 更改数据库 : 添加 / 删除 INSTANCE 等 如图 3-17 所示 图 3-17 服务器 界面 88

35 第 3 章 Oracle 数据库常用工具 方案 属性页主要涉及方案管理, 包括对表 索引视图 同义词 序列 存储过程等数据库对象及程序进行管理, 如图 3-18 所示 图 3-18 方案 界面 数据移动 属性页涉及管理数据移动, 包括 EXP/IMP AWR 数据倒出 / 加载 传输表空间和克隆数据库等, 如图 3-19 所示 图 3-19 数据移动 界面 89

36 第 1 篇基础篇 软件和支持 属性页说明 OEM 支持对数据库补丁的安装管理, 如图 3-20 所示 图 3-20 软件和支持 界面 3.7 实例分析 在 D 盘根目录下用记事本基于表 3-1 按以下要求创建报表脚本文件 emp.sql 1. 在页眉左边以 日期 : 形式显示当前日期, 中间位置显示 员工基本信息 2. 在页脚中间位置显示 谢谢使用报表!, 右边以 页 : 形式显示页码, 页码的 NUMBER 格式为 禁止显示脚本中的 SQL 语句和命令 4. 禁止显示验证信息 5. 设置一页显示 30 行数据, 每行显示 100 个字符 6. 清除缓存中原来定义的所有字段的格式信息 7. EMPNO 列设置标题为 员工编号,NUMBER 格式为 9999;ENAME 列设置标题为 员工姓名, 格式设置为 30 个字符 ;HIREDATE 列设置标题为 聘用日期, 且居中显示 ;DEPTNO 列设置标题为 部门编号,NUMBER 格式为 清除缓存中的所有字段的格式信息 9. 显示完毕关闭页眉和页脚的显示 输出以上设置效果 参考解答 : TTITLE LEFT ' 日期 :'_DATE CENTER ' 员工基本信息 ' BTITLE CENTER ' 谢谢使用报表!' RIGHT ' 页 :' FORMAT 999 SQL.PNO 90

37 第 3 章 Oracle 数据库常用工具 SET ECHO OFF// 禁止 SQL*Plus 显示脚本中的 SQL 语句和命令 SET VERIFY OFF// 用来禁止显示验证信息 SET PAGESIZE 30 SET LINESIZE 100 CLEAR COLUMNS COLUMN empno HEADING ' 职工编号 ' FORMAT 9999 COLUMN ename HEADING ' 职工姓名 ' FORMAT A30 COLUMN hiredate HEADING ' 受雇日期 ' JUSTIFY CENTER COLUMN sal HEADING ' 职工工资 ' FORMAT $99, SELECT empno, ename, hiredate, deptno from emp; CLEAR COLUMNS TTITLE OFF BTITLE OFF 在 SQL*Plus 中使用 START 命令运行该脚本文件 : SQL> start D:\emp.sql 日期 :3- Feb -14 员工基本信息 职工编号 职工姓名 ' 受雇日期 部门编号 SMITH 17-Dec ALLEN 20-Feb WARD 22-Feb JONES 2-Apr MARTIN 28-Sep BLAKE 1-May 谢谢使用报表! 页 : 本章小结 本章介绍了 SQL*Plus 工具的主要功能, 启动和退出 SQL*Plus 的 5 种典型方法, 如何显示和设置 SQL*Plus 的运行环境, 重点介绍了常用 SQL*Plus 命令的使用方法, 最后简要介绍了 Oracle 企业管理器 (OEM) 的环境配置和基本操作方法 通过本章的学习, 使读者能够熟练掌握 SQL*Plus 的启动和退出方法, 能够根据需要熟练应用 SQL*Plus 命令解决相关问题, 了解基本报表样式设置方法 读者借助 SQL*Plus 命令解决相关问题时, 应养成在脚本文件中书写 SQL*Plus 命令的好习惯, 这样便于检查 修改和重复利用 熟悉借助 OEM 图形用户界面完成一些 SQL*Plus 命令的效果 此外, 还要注意以下两组命令之间的区别 :run 命令 / 命令和 start 命令 ;append 命令和 input 命令 3.9 习题与实践练习 一 填空题 1. SQL*Plus 工具中, 可以运行 和 2. 在 SQL*Plus 工具中, 可以使用 命令编辑缓冲区或文件内容 ; 还可以使用 命令复制输出结果到文件 3. 使用 命令可以显示表的结构信息 91

38 第 1 篇基础篇 4. 使用 SQL*Plus 的命令可以将文件检索到缓冲区, 并且不执行 5. 当设置了多个列的显示属性后, 如果清除设置的显示属性, 可以使用命令, 而当要清除某列具体的显示属性时, 需要使用命令 6. 使用命令可以将缓冲区中的 SQL 命令保存到一个文件中, 并且可以使用该命令运行这个文件 7. 要设置 SQL*Plus 显示数字时的格式可以使用命令, 而设置某个数字列的显示格式要使用命令 二 选择题 1. 关于 SQL*Plus 的叙述正确的是 ( ) A.SQL*Plus 是 Oracle 数据库的专用访问工具 B.SQL*Plus 是标准的 SQL 访问工具, 可以访问各类关系型数据库 C.SQL*Plus 是访问 Oracle 数据库的唯一对外接口 D. 以上所述都不正确 2. SQL*Plus 在 Oracle 数据库系统中的作用, 以下叙述正确的是 ( ) A. 是 Oracle 数据库服务器的主要组成部分, 是服务器运行的基础构件 B. 是 Oracle 数据库系统底层网络通信协议, 为所有的 Oracle 应用程序提供一个公共的通信平台 C. 是 Oracle 客户端访问服务器的一个工具, 通过它可以向服务器发送 SQL 命令 D. 以上所述都不正确 3. 在 SQL*Plus 中显示 EMP 表结构的命令是 ( ) A.LIST EMP B.DESC EMP C.SHOW DESC EMP D.SHOW STRUCTURE EMP 4. 将 SQL*Plus 的显示结果输出到 d:\data1.txt 文件中的命令是 ( ) A.write to d:\data1.txt B.spool to d:\data1.txt C.spool on d:\data1.txt D.spool d:\data1.txt 5. 在 SQL*Plus 中执行刚输入的一条命令用 ( ) A. 正斜杠 (/) B. 反斜杠 (\) C. 感叹号 (!) D. 句号 (.) 6. 如何设置 SQL*Plus 操作界面的行宽可以容纳 1000 个字符?( ) A.set size 1000 B.set line 1000 C.set numformat 1000 D.set page

39 第 3 章 Oracle 数据库常用工具 7. 当用 SQL*Plus 已经登录到某一数据库, 此时想登录到另一数据库, 应该用命令 ( ) A.CONN B.DISC C.LOGIN D.LOGON 8. 使用 SQL*Plus 中的 ( ), 可以将文件中的内容检索到缓冲区, 并且不执行 A.SAVE 命令 B.GET 命令 C.START 命令 D.SPOOL 命令 9. 如果要设置 SQL*Plus 每页打印的数量, 则可以使用如下的哪个命令?( ) A.SET PAGE B.PAGESIZE C.SET PAGESIZE D.SIZE 10. 如果希望控制列的显示格式, 可以使用下面的 ( ) 命令 A.SHOW B.DEFINE C.SPOOL D.COLUMN 三 简答题 1. 在数据库系统中, 可以使用哪两种方式执行命令? 各有何特点? 2. 简述 SQL*Plus 工具的主要功能 3. 简述常用启动 SQL*Plus 工具的操作方法 4. 基于表 3-1 的数据完成如下操作 (1) 试用 SQL*Plus 命令显示 EMP 表的结构 (2) 写出执行以下 SQL 语句的结果 : SQL>SELECT EMPNO, ENAME, JOB, SAL 2 FROM EMP WHERE SAL < 2500; (3) 试用 SQL*Plus 命令列出缓冲区的内容 (4) 假设将 (1) 中的 SQL 语句错误地输入为 : SQL>SELECT EPNO, ENAME, JOB, SAL 2 FROM EMP WHERE SAL < 2500; 试用 SQL*Plus 命令修改以上错误 (5) 试用 RUN( 或 /) 命令运行当前 SQL 语句 (6) 将缓冲区中的内容使用 SQL*Plus 命令保存到 D:\SQL_PLUS\empinfo 文件中 四 上机操作题 1. 在 SQL*Plus 环境中用普通用户 SCOTT 连接 Oracle 数据库 2. 用 Show user 命令显示当前连接数据库的用户 3. 基于表 3-1 的数据, 完成如下操作 (1) 练习 SQL 语句 : SELECT * FROM EMP; (2) 用 LIST 显示缓冲区的内容 (3) 假设将 (1) 中的 SQL 语句误输入为 : SELECT * FROM EMPLOY; 试用 CHANGE 命令修改当前行 (4) 若要求在 (1) 中 SQL 语句后第一行后面增加一行 ORDER BY SAL, 请用 INPUT 93

40 第 1 篇基础篇 命令完成 (5) 使用 RUN( 或 /) 命令执行添加一行后的 SQL 语句 (6) 试用 DEL 命令删除刚才 SQL 语句中添加的一行信息 4. 对表 3-1 的输出信息进行以下格式设置 (1)EMPNO 列设置标题为 员工编号,NUMBER 格式为 9999;ENAME 列设置标题为 员工姓名, 格式设置为 20 个字符 ;HIREDATE 列设置标题为 聘用日期, 并且标题均居中显示 (2) 设置一页显示 30 行数据, 每行显示 100 个字符 输出以上设置效果 94