Microsoft Word - 第3章.doc

Size: px

Start display at page:

Download "Microsoft Word - 第3章.doc"

巾祁
5 years ago
Views:

1 第 3 章使用 SQL * Plus 工具 SQL*Plus 是 Oracle 系统的支持工具之一, 它用于运行 SQL 语句和 PL/SQL 块, 并且也用于跟踪调试 SQL 语句和 PL/SQL 块通过它, 用户可以连接位于相同服务器上的数据库, 也可以连接位于网络中不同服务器上的数据库本章将讲述 SQL*Plus 工具的使用以及常用的一些 SQL*Plus 操作命令掌握 SQL*Plus 的主要功能熟练使用 SQL*Plus 常用指令掌握变量的声明和使用掌握格式化查询结果命令的使用掌握报表的设计和数据统计 3.1 SQL * Plus 概述 SQL*Plus 工具是随 Oracle 数据库服务器或客户端的安装而自动进行安装的管理与开发工具,Oracle 数据库中所有的管理操作都可以通过 SQL*Plus 工具完成, 同时开发人员利用 SQL*Plus 可以测试运行 SQL 语句和 PL/SQL 程序本节将简单介绍 SQL*Plus 的主要功能以及连接 / 断开数据库的方式 SQL * Plus 的主要功能 SQL*Plus 工具主要用于数据库的查询和数据处理利用 SQL*Plus 可以将 SQL Server 和 Oracle 专有的 PL/SQL 结合起来进行数据查询和处理 SQL*Plus 是一个最常用的工具, 具有很强的功能, 主要有 : (1) 数据库的维护, 如启动关闭等, 这一般在服务器上操作 (2) 执行 SQL 语句和 PL/SQL 块 (3) 执行 SQL 脚本 (4) 数据的导出报表 (5) 应用程序开发测试 SQL 和 PL/SQL (6) 生成新的 SQL 脚本 (7) 供应用程序调用, 如安装程序中进行脚本的安装

2 使用 SQL * Plus 工具第 3 章 (8) 用户管理及权限维护等在 SQL*Plus 中可以执行 SQL 语句 PL/SQL 程序和 SQL*Plus 命令 (1)SQL 语句 SQL 语句是以数据库对象为操作对象的语言, 主要包括 DDL DML 和 DCL (2)PL/SQL 程序 PL/SQL 语句同样是以数据库对象为操作对象, 但所有的 PL/SQL 语句的解释均由 PL/SQL 引擎来完成使用 PL/SQL 语句可以编写存储过程触发器和包等数据库永久对象 (3)SQL*Plus 命令 SQL*Plus 命令主要用来格式化查询结果, 设置选择, 编辑以及存储 SQL 命令, 设置查询结果的显示格式, 并且可以设置环境选项, 还可以编辑交互语句, 可以与数据库进行对话 SQL * Plus 连接与断开数据库通过 SQL*Plus 工具可以很方便地连接与断开数据库, 下面介绍两种使用 SQL*Plus 连接数据库的方式 1. 启动 SQL * Plus, 命令行方式要从命令行启动 SQL*Plus, 可以使用 sqlplus 命令 sqlplus 命令的语法格式如下 : 47 sqlplus [username] [AS { SYSDBA SYSOPER SYSASM [NOLOG] 语法说明如下 : (1)username: 指定数据库的用户名 (2)password: 指定该数据库用户的密码 (3)@connect_identifier: 指定要连接的数据库 (4)AS: 指定所使用的管理权限, 权限的可选值有 SYSDBA SYSOPER 和 SYSASM (5)SYSOPER: 具有 SYSOPER 权限的管理员可以启动和关闭数据库, 执行联机和脱机备份, 归档当前重做日志文件, 连接数据库 (6)SYSDBA:SYSDBA 权限包括 SYSOPER 的所有权限, 另外还能够创建数据库, 并且授权 SYSDBA 或 SYSOPER 权限给其他数据库用户 (7)SYSASM: SYSASM 权限是 Oracle Database 11g 的新增特性, 是 ASM 实例所特有的, 用来管理数据库存储 (8)NOLOG: 不记入日志文件下面以 system 用户为例, 连接数据库, 在 DOS 命令窗口中输入 sqlplus system/oracle, 按 Enter 键后提示连接到数据库, 如图 3-1 所示 2. 启动 SQL * Plus, 连接到默认数据库启动 SQL * Plus 连接到默认数据库方法的具体步骤如下 :

system) 之后按 Enter 键, 登录窗口紧接着会提示输入口令信息, 输入口令之后按 Enter 键, 如果用户名和口令均正确, 则显示如图

3 Oracle 11g 基础教程与实验指导图 3-1 通过命令行连接数据库 (1) 执行开始程序 Oracle-oraDb11g_home1 应用程序开发 SQL Plus 命令, 出现如图 3-2 所示的登录窗口 48 图 3-2 SQL*Plus 登录窗口 (2) 在登录窗口中输入用户名 ( 如 system) 之后按 Enter 键, 登录窗口紧接着会提示输入口令信息, 输入口令之后按 Enter 键, 如果用户名和口令均正确, 则显示如图 3-3 所示登录成功的窗口图 3-3 SQL*zlus 登录成功另外, 也可以用户名和口令一起输入, 格式为 : 用户名 / 口令, 如 system/oracle, 只

4 使用 SQL * Plus 工具第 3 章是这种方式有一个缺点, 就是会显示口令信息 3. 使用 SQL * Plus 命令连接与断开数据库在 SQL*Plus 中连接数据库时, 可以使用 CONNECT( 可简写为 CONN) 命令指定不同的登录用户连接数据库后,SQL*Plus 维持数据库会话 CONNECT 命令的语法格式如下 : CONN[ECT] [{user_name [/password] [@connect_identifier]} [AS {SYSOPER SYSDBA SYSTEM}]] 如果需要断开与数据库的连接, 可以使用 DISCONNECT( 可简写为 DISCONN) 命令, 该命令可以结束当前会话, 但是保持 SQL*Plus 运行要退出 SQL*Plus, 关闭 SQL*Plus 窗口, 可以执行 EXIT 或者 QUIT 命令 3.2 使用 SQL * Plus 命令通常所说的 DML DDL DCL 语句都是 SQL*Plus 语句, 它们执行完后, 都可以保存在一个称为 SQL BUFFER 的内存区域中, 并且只能保存一条最近执行的 SQL 语句除了 SQL*Plus 语句, 在 SQL*Plus 中执行的其他语句称为 SQL*Plus 命令 SQL*Plus 命令执行完后, 不保存在 SQL BUFFER 的内存区域中, 它们一般用来对输出的结果进行格式化显示, 以便于制作报表使用 DESCRIBE 命令查看表结构表结构包括一个数据库表的名称有哪些字段以及哪些字段是主键等信息表结构可以通过使用 DESCRIBE 命令在数据库中查询, 该命令的语法格式如下 : DESCRIBE {[schema.]object[@connect_identifer]} 其中,schema 表示指定对象所属的用户名 ;object 表示对象的名称, 如表名或视图名 ;@connect_identifer 表示数据库连接字符串技巧 DESCRIBE 也可以简写为 DESC 下面使用 DESCRIBE 命令查看 scott 用户下的 emp 表的结构, 结果如下 : SQL> DESC emp 名称是否为空? 类型 EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9)

5 Oracle 11g 基础教程与实验指导 MGR HIREDATE SAL COMM DEPTNO NUMBER(4) DATE NUMBER(7,2) NUMBER(7,2) NUMBER(2) 使用 DESCRIBE 命令查看表结构时, 如果指定的表不存在, 则提示信息 :object tablename( 表名 )does not exist 如果指定的表存在, 则显示该表的结构如上面的结果所示, 显示表结构时, 按照名称是否为空? 类型这三列进行显示其中, 名称表示列的名称 ; 是否为空? 表示对应列的值是否可以为空, 如果可以为空, 则不显示任何内容, 如果不能为空, 则为 NOT NULL; 类型表示该列的数据类型, 并且显示其精度执行 SQL 脚本 50 在工作需要的时候, 常常会写很多命令, 而这些命令如果一条一条的输入再执行, 则会降低工作效率, 浪费劳力, 这时, 执行 SQL 脚本文件可以解决这一难题下面介绍执行 SQL 脚本方法的使用执行 SQL 脚本的语法格式如下 : SQL>@full_path\file_name 其中,full_path 表示脚本文件的路径 ;file_name 表示脚本文件的文件名例 : SQL>@D: \test.sql 在 SQL*Plus 中输入以上命令之后, 按 Enter 键, 开始执行 SQL 命令将 test.sql 文件的内容读入 SQL*Plus 缓冲区, 然后执行缓冲区中的内容命令的使用方法, 该命名执行后的结果如下 : 名称是否为空? 类型 EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) 其中,test.sql 文件的路径为 F:\test.sql,test.sql 文件的内容为 :

6 使用 SQL * Plus 工具第 3 章 desc emp; desc 命令分别执行上面两条命令, 两条命令的结果均会显示在窗口中提示执行 SQL 脚本还可以使用 start 命令,start 命令, 如 start F: \test.sql \test.sql 使用 SAVE 命令保存缓冲区内容到文件当执行完一条 SQL 语句后, 该语句就会被存入缓冲区, 而把以前存在缓冲区的语句覆盖, 也就是说, 缓冲区只能存放刚刚执行完的 SQL 语句使用 SAVE 命令可以将缓冲区中的内容保存到文件中,SAVE 命令的语法格式如下 : SAV[E] [FILE] file_name [CRE[ATE] REP[LACE] APP[END]] 参数参数说明如表 3-1 所示 file_name CREATE REPLACE APPEND 说明表 3-1 SAVE 命令参数表表示将 SQL*Plus 缓冲区的内容保存到文件名 file_name 的文件中表示创建一个 file_name 文件, 该选项为默认值表示如果 file_name 文件已经存在, 则覆盖 file_name 文件的内容 ; 如果该文件不存在, 则创建该文件如果 file_name 文件已经存在, 则将缓冲区中的内容追加到 file_name 文件的末尾 ; 如果该文件不存在, 则创建该文件 51 下面通过一个示例介绍 SAVE 命令的使用方法执行下面的 SQL 语句 : SELECT ename FROM emp; 此时, 缓冲区的内容就是上面的 SQL 语句, 使用 SAVE 命令将 SQL*Plus 缓冲区中的 SQL 语句保存到一个名为 test2.sql 的文件中, 代码如下 : save F: \test2.sql; 注意在 SAVE 命令中, 如果没有为文件指定路径, 则会默认保存到 Oracle 安装路径的 product\11.2.0\dbhome_1\bin 目录下

7 Oracle 11g 基础教程与实验指导使用 GET 命令读取脚本文件到缓冲区前面介绍了使用 SAVE 命令将缓冲区的内容保存到一个文件中, 而使用 GET 命令可以将文件中的内容读取到缓冲区 GET 命令的语法格式如下 : GET[FILE] file_name[list NOLIST]; 语法说明如下 : (1)file_name: 一个指定文件, 将该文件的内容读入 SQL*Plus 缓冲区中 (2)LIST: 列出缓冲区中的语句 (3)NOLIST: 不列出缓冲区中的语句下面通过一个示例介绍 GET 命令的具体使用方法将节中保存的 test2.sql 文件的内容读入到缓冲区中, 并获取执行结果, 代码如下 : 52 SQL> GET F: \test2.sql 1* SELECT ename FROM emp WHERE ename='smith' SQL> RUN 1* SELECT ename FROM emp WHERE ename='smith' ENAME SMITH 注意使用 GET 命令时, 如果 file_name 指定的文件在 Oracle 的安装目录 product\11.2.0\dbhome_1\bin 下, 则只需要指出文件名 ; 如果不在这个目录下, 则必须指定完整的路径名使用 EDIT 命令编辑缓冲区内容或文件使用 EDIT 命令, 可以编辑缓冲区的内容这样, 如果语句执行出错, 用户可以很方便地进行修改, 特别是长的复杂的 SQL 语句 EDIT 命令可以将 SQL*Plus 缓冲区中的内容复制到一个文件名为 afied.buf 的文件中, 可以使用文本编辑器打开这个文件, 该命令的语法格式如下 : ED[IT] [file_name] 其中,file_name 默认为 afied.buf, 也可以指定一个其他的文件下面通过一个示例介绍 EDIT 命令的具体使用方法在 SQL*Plus 中, 使用 EDIT 命令将缓冲区中的内容复制到 afied.buf 文件中

8 使用 SQL * Plus 工具第 3 章 SQL> SELECT ENAME FROM emp WHERE ename='smith'; ENAME SMITH SQL> EDIT 已写入 file afiedt.buf 在 SQL*Plus 中输入 EDIT 命令之后, 将打开一个记事本文件 afiedt.buf, 在该文件中显示缓冲区中的内容, 如图 3-4 所示图 3-4 使用 EDIT 命令编辑缓冲区内容对 afiedt.buf 文件中的内容进行编辑, 将文件内容修改为 : SELECT ename FROM emp WHERE ename= JONES 53 当退出编辑器时, 该文件的内容将被复制到 SQL*Plus 缓冲区中这时可以使用斜杠 (/) 运行刚才修改过的查询语句, 代码如下 : 1* SELECT ename FROM emp WHERE ename='jones' SQL> / ENAME JONES 使用 SPOOL 命令复制输出结果到文件使用 SPOOL 命令可以将 SQL*Plus 中的输出结果复制到一个指定的文件中, 直到使用 SPOOL OFF 命令为止 SPOOL 命令的语法格式如下 : SPO[OL] [file_name[cre[ate] REP[LACE] APP[END]] OFF OUT] 语法说明如下 : (1)file_name: 指定一个操作系统文件 (2)CREATE: 创建一个指定的文件名为 file_name 的文件 (3)REPLACE: 如果指定的文件已经存在, 则替换该文件 (4)APPEND: 将内容追加到一个已经存在的文件末尾 (5)OFF: 停止将 SQL*Plus 中的输出结果复制到 file_name 文件中, 并关闭该文件

9 Oracle 11g 基础教程与实验指导 (6)OUT: 启动该功能, 将 SQL*Plus 中的输出结果复制到指定的文件名为 file_name 的文件中例如, 使用 SPOOL 命令将 SQL*Plus 中的输出结果保存到 F:\spool.txt 文件中, 代码如下 : SQL> SPOOL F: \spool.txt 然后执行 SQL 语句 : SQL> SELECT * FROM emp; 执行 SPOOL OFF 命令, 在该命令之后所操作的任何语句, 将不再保存其执行结果 SQL> SPOOL OFF; 打开 F: \spool.txt 文件, 该文件内容是上述 SQL 语句的执行结果 3.3 变量 54 在 Oracle 数据库中, 可以使用变量来编写通用的 SQL 语句由于这些变量通常用来替代值, 因此称为替代变量替换变量有以下两种类型 : 1. 临时变量临时变量只在使用它的 SQL 语句中有效, 值不能保留 2. 已定义变量已定义变量会一直保留到被显示地删除重定义或退出 SQL*Plus 为止临时变量在 SQL 语句中, 可以使用字符 & 定义临时变量, 后面跟要定义的变量名例如, &v_number 就定义了一个名为 v_number 的变量在 SQL*Plus 中输入以下 SQL 语句 : SQL> SELECT deptno,dname FROM dept WHERE deptno=&v_deptno; 在上面的 SQL 语句中定义了一个名为 v_deptno 的变量, 按回车键后将会显示以下信息 : 输入 v_deptno 的值 : SQL*Plus 提示用户为 v_deptno 变量输入一个值, 然后在 SELECT 语句的 WHERE 子句中使用这个变量值, 代码如下 : 原值新值 1: SELECT deptno,dname FROM dept WHERE deptno=&v_deptno 1: SELECT deptno,dname FROM dept WHERE deptno=10

10 使用 SQL * Plus 工具第 3 章 DEPTNO DNAME ACCOUNTING 由上面的示例可以看出, 执行替换的行号为 1, 变量 v_deptno 被替换为数字控制输出行原值和新值的输出可以使用 SET VERIFY 命令控制如果输入 SET VERIFY OFF 命令, 就会禁止显示原值和新值, 下面使用斜杠 (/) 再次运行上面的 SQL 语句,SQL*Plus 就会提示为变量 v_deptno 输入一个新值, 代码如下 : SQL> SET VERIFY OFF SQL> / 输入 v_deptno 的值 : 10 DEPTNO DNAME ACCOUNTING 如上面代码所示, 使用 SET VERIFY OFF 命令之后, 执行 SQL 语句时原值和新值行没有输出, 直接输出了 SQL 语句的执行结果如果想重新显示这些行, 可以使用 SET VERIFY ON 命令, 这里不再详细介绍该命令的使用方法 2. 修改变量定义字符 55 在 SQL 语句中, 通常使用字符 & 定义临时变量, 也可以指定使用其他字符, 这时就需要使用 SET DEFINE 命令, 下面的示例显示了如何使用 SET DEFINE 命令将变量定义字符设置为字符 # SQL> SET DEFINE '#' SQL> SELECT deptno,dname FROM dept WHERE deptno=#v_deptno; 输入 v_deptno 的值 : 10 DEPTNO DNAME ACCOUNTING 定义变量已定义变量是指具有明确定义的变量, 该变量的值会一直保留到被显式地删除重定义或退出 SQL*Plus 为止可以使用 DEFINE 命令定义变量, 在使用完变量后, 可以使用 UNDEFINE 命令将其删除 1. 使用 DEFINE 命令定义并查看变量 DEFINE 命令既可以用来定义一个新变量, 也可以用来查看已经定义的变量该命令的语法格式有以下 3 种 :

11 Oracle 11g 基础教程与实验指导 (1)DEF[INE]: 显示所有已定义变量 (2)DEF[INE] variable: 显示指定变量的名称值和数据类型 (3)DEF[INE] variable=value: 创建一个 CHAR 类型的变量, 并为该变量赋初始值例如, 定义一个名为 v_ename 的变量, 并为其赋初始值为 SMITH, 代码如下 : SQL> DEF v_ename='smith' 使用 DEFINE 命令加上变量名就可以查看该变量的定义, 代码如下 : SQL> DEF v_ename DEFINE v_ename = "SMITH" (CHAR) 提示单独使用 DEFINE 命令, 可以查看当前会话的所有变量已定义变量可以用来指定一个元素, 下面的查询使用到了之前定义的变量 v_ename, 并在 WHERE 子句中引用该变量的值 56 SQL> SELECT ename 2 FROM emp 3 WHERE ename='&v_ename'; 原值 3: WHERE ename='v_ename' 新值 3: WHERE ename='smith' ENAME SMITH 由上面结果可以看出, 这一次并没有提示用户输入 v_ename 的值, 而是直接使用了该变量已经设置好的值 2. 使用 ACCEPT 命令定义并设置变量 ACCEPT 命令用于等待用户为变量输入一个值该命令既可以为现有的变量设置一个新值, 也可以定义一个新变量, 并使用一个值对该变量进行初始化 ACCEPT 命令还可以为变量指定数据类型 ACCEPT 命令的语法格式如下 : ACCEPT variable_name [type] [FOR[MAT]format] [DEF[AULT]default] [PROMPT text NOPR[EMPT]] [HIDE] 语法说明如下 : (1)variable_name: 指定为变量分配的名字 (2)type: 指定变量的数据类型, 可以使用的类型有 CHAR NUMBER 和 DATE 默认情况下, 变量的类型为 CHAR (3)FORMAT: 指定变量的格式, 包括 A15(15 个字符 ) 9999( 一个 4 位数字 ) 和 DD-MON-YYYY( 日期 )

12 使用 SQL * Plus 工具第 3 章 (4)DEFAULT: 为变量指定一个默认值 (5)PROMPT: 在用户输入数据之前显示的文本消息 (6)HIDE: 隐藏用户为变量输入的值下面通过一个示例介绍 ACCEPT 命令的使用方法在该示例中, 使用 ACCEPT 命令定义一个名为 v_deptno 的变量, 该变量为两位数字, 并在查询语句中引用该变量, 代码如下 : SQL> ACCEPT v_deptno NUMBER FORMAT 99 PROMPT'please insert the deptno:' please insert the deptno:10 SQL> SELECT * 2 FROM dept 3 WHERE deptno=&v_deptno; 原值 3: WHERE deptno =&v_deptno 新值 3: WHERE deptno 10 DEPTNO DNAME LOC ACCOUNTING NEW YORK 在上面代码中并没有将输入的变量值 10 隐藏, 在实际的开发应用中, 为了安全起见, 通常会隐藏用户输入的值, 可以通过在 ACCEPT 命令的末尾加上 HEDE 来实现使用 UNDEFINE 命令删除变量 UNDEFINE 命令用于删除变量例如, 使用 UNDEFINE 命令删除变量 v_deptno, 代码如下 : UNDEFINE v_deptno 3.4 练习 3-1: 使用多个变量动态获取部门信息在实际应用中变量的使用是很普遍的, 例如, 当需要查询不同的部门信息时, 只需要输入一个新的部门值即可在 SQL 语句中定义了 3 个变量, 分别需要输入一个列名 ( 变量名为 v_deptno) 一个表名( 变量名为 v_table) 和一个列值 ( 变量名为 v_value): SQL> SELECT &v_deptno,dname 2 FROM &v_table 3 WHERE &v_deptno=&v_value; 输入 v_deptno 的值 : deptno 输入 v_table 的值 : dept 输入 v_deptno 的值 : deptno 输入 v_value 的值 : 10 DEPTNO DNAME ACCOUNTING

13 Oracle 11g 基础教程与实验指导由上面的示例可以看出, 需要输入两次相同的变量 (v_deptno) 的值, 为了避免重复输入相同的变量, 可以使用 && 定义变量, 上面的示例可以修改为 : SQL> SELECT && v_deptno,dname 2 FROM &v_table 3 WHERE && v_deptno =&v_value; 输入 v_deptno 的值 : deptno 输入 v_table 的值 : dept 输入 v_value 的值 : 10 DEPTNO DNAME ACCOUNTING 以上介绍了临时变量的使用方法, 使用变量为编写其他可以运行的脚本提供了很多灵活性, 可以为用户提供一个脚本, 用户只需要输入变量值即可 3.5 格式化查询结果 58 SQL*Plus 提供了大量的命令用于格式化查询结果, 使用这些命令可以格式化列, 设置一页显示多少行数据, 设置一行显示多少个字符和创建简单报表, 并为报表添加标题和底标题, 在报表中显示当前日期和页号, 也可以为报表添加新的统计数据等常用的格式化查询结果命令有 COLUMN COMPUTE BREAK BTITLE TTITLE 等格式化列在 SQL*Plus 中, 经常使用 COLUMN 命令对所输出的列进行格式化, 即按照一定的格式进行显示 COLUMN 命令的语法格式如下 : COLUMN {column alias} [options] 语法说明如下 : (1)column: 指定列名 (2)alias: 指定要格式化的列的别名 (3)options: 指定用于格式化列或别名的一个或多个选项在 COLUMN 命令中, 可以使用很多选项, 表 3-2 列出了其中的部分选项表 3-2 COLUMN 命令选项 FOR[MAT] format HEA[DING]heading JUS[TIFY] [{LEFT CENTER RIGHT}] 说明将列或列名的显示格式设置为由 format 字符串指定的格式将列或列名的标题中的文本设置为由 heading 字符串指定的格式将列输出设置为左对齐居中或右对齐

14 使用 SQL * Plus 工具第 3 章选项 WRA[PPED] 说明续表在输出结果中将一个字符串的末尾换行显示, 该选项可能导致单个单词跨越多行 WOR[D_WRAPPED] 与 WRAPPED 选项类似, 不同之处在于单个单词不会跨越多行 CLE[AR] 清除列的任何格式化 ( 将格式设置回默认值 ) 表 3-2 中的 format 字符串可以使用很多格式化参数, 可以指定的参数取决于该列中保存的数据如果列中包含字符, 可以使用 Ax 对字符进行格式化, 其中 x 指定了字符宽度, 如 A13 就是将宽度设置为 13 个字符 ; 如果列中包含数字, 可以使用数字格式, 如 $99.99 就是在数字前加美元符号 ; 如果列中包含日期, 可以使用日期格式, 如 MM-DD-YYYY 设置的格式就是一个两位的月份 (MM) 一个两位的日(DD) 一个 4 位的年份 (YYYY) 下面通过一个示例介绍 COLUMN 命令的使用方法该示例查询 emp 表中的 eanme empno 和 sal 列, 并分别对它们进行格式化, 代码如下 : SQL> COLUMN empno HEADING " 员工编号 " SQL> COLUMN ename HEADING " 雇员名 " FORMAT A13 SQL> COLUMN empno FORMAT 9999 SQL> COLUMN sal HEADING " 工资 " FORMAT $ SQL> SELECT empno,ename,sal 2 FROM emp; 员工编号雇员名工资 SMITH $ ALLEN $ WARD $ JONES $ JAMES $ FORD $ MILLER $ 已选择 14 行 59 由上面的示例可以看出, 使用 COLUMN 命令不仅可以对列的值进行格式化, 还可以修改列名, 使用别名来显示, 从而使查询结果更加简明直观在该示例中, 输出结果中标题显示了两次, 为了让结果看起来更美观, 只让标题显示一次, 可以通过设置页面显示行数据来实现,3.5.2 节将介绍如何设置页面显示行数据设置每页显示的数据行每页中显示的数据可以使用 SET PAGESIZE 命令来设置这个命令的具体功能是设置 SQL*Plus 输出结果中一页应该显示的行数, 超过这个行数之后,SQL*Plus 就会再次

15 Oracle 11g 基础教程与实验指导显示标题 SET PAGESIZE 命令的语法格式如下 : SET PAGESIZE n 其中, 参数 n 表示每一页显示的行数, 最大只可以为 , 默认值为 14 下面使用 SET PAGESIZE 命令将页面大小设置为 20 行, 再次执行节中的示例 : SQL> SET PAGESIZE 20 SQL> / 员工编号雇员名工资 SMITH $ ALLEN $ WARD $ ADAMS $ FORD $ MILLER $ 已选择 14 行 60 上面示例中, 设置了该页显示 20 行数据, 故只在顶部显示了一次标题, 这样的输出结构看起来更加简明提示 SQL*Plus 中的页并不是仅仅由输出数据行构成, 而是由 SQL*Plus 显示到屏幕上的所有输出结果构成, 包括标题和空行等设置每行显示的字符数一行中显示的字符数可以使用 SET LINESIZE 命令来设置, 默认值为 80 如果设置的值比较小, 那么表中每行数据有可能在屏幕上需要分多行显示 ; 如果设置的值比较大, 则表中每行数据就可以在屏幕的一行中进行显示 LINESIZE 命令的语法格式如下 : SET LINESIZE n 其中,n 表示屏幕上一行数据中显示的字符数, 有效范围是 1~ 下面以设置一行显示 20 个字符为例, 查询 emp 表中雇员编号和雇员姓名示例代码如下 : SQL> SET LINESIZE 20 SQL> SELECT empno,ename 2 FROM emp; 员工编号雇员名

16 使用 SQL * Plus 工具第 3 章 7369 SMITH 7499 ALLEN 7521 WARD JONES 7654 MARTIN 上面示例中, 由于一行显示的字符数为 20, 因此所有超出的部分换行显示提示如果不再使用列的格式化, 可以使用 COLUMN 命令加上 CLEAR 选项来清除,CLEAR columns 为清除所有列的格式化 3.6 创建简单报表所谓报表就是用表格图表等格式来动态显示数据计算机上报表的主要特点是数据动态化和格式多样化, 并且实现报表数据和报表格式的完全分离, 用户可以只修改数据或者只修改格式报表的标题设计报表的标题是利用 SQL*Plus 的两个命令来设计的, 即 TTITLE 和 BTITLE 其中, TTITLE 命令用来设计报表的头部标题, 而 BTITLE 命令用来设计报表的尾部标题 TTITLE 命令设计的头部标题显示在报表每页的顶部设计头部标题时, 要指定显示的信息和显示的位置, 还可以使标题分布在多行之中用 TTITLE 命令设计头部标题的操作是比较复杂的, 这条命令的语法格式如下 : TTI[TLE] [printspec [text variable]...] [OFF ON] 语法说明如下 : (1)printspec: 指定出现在报表中每一个页面顶端的页眉, 其可选值有 LEFT CENTER RIGHT BOLD FORMAT text COL n S[KIP] [n] 和 TAB n (2)OFF: 取消设置 (3)ON: 启用设置下面是使用 TTITLE 命令设置页面的示例 SQL>TTITLE RIGHT ' 日期 :'_DATE CENTER ' 生成报表 'SQL.USER'user' SQL>RIGHT ' 页 :'FORMAT 999 SQL.PNO SKIP 2

17 Oracle 11g 基础教程与实验指导其中,_DATE 表示当前日期 ;SQL.USER 表示显示当前用户 ;SQL.PNO 表示显示当前页 (FORMAT 用来格式化数字 );CENTER 和 RIGHT 表示文本的对齐方式 ;SKIP2 表示跳过两行 BTITLE 命令的用法与 TTITLE 命令是一样的, 区别在于 BTITLE 命令用来设计尾部标题, 显示的位置在报表每页的底部 BTITLE 命令的语法格式如下 : BTI[TLE] [printspec[text variable]...] [OFF ON] 下面的示例演示了 BTITLE 命令的使用 BTITLE CENTER " 谢谢使用该表 " RIGHT " 页 :"FORMAT 999 SQL.PNO 查询 scott 用户下的 emp 表中的数据并以报表的信息显示出来在设计报表时, 需要使用 TTITLE 和 BTITLE 命令设置页眉和页脚信息, 其具体的操作步骤如下 : (1) 在 F:\SQL 文件夹下创建 report.sql 文件, 该脚本中包含了 TTITLE 和 BTITLE 命令, 脚本内容如下 : 62 TTITLE RIGHT ' 日期 :' _DATE CENTER ' 生成报表 ' SQL.USER BTITLE CENTER ' 谢谢使用该表 ' RIGHT ' 页 :' FORMAT 999 SQL.PNO SET PAGESIZE 20 SET LINESIZE 120 COLUMN ename HEADING " 员工姓名 " FORMAT A20 COLUMN sal HEADING " 工资 " FORMAT $ COLUMN hiredate HEADING " 入职时间 " COLUMN empno HEADING " 员工编号 " SELECT empno,ename,hiredate sal FROM emp; TTITLE OFF BTITLE OFF (2) 运行 F:\SQL\report.sql 文件, 生成报表, 代码如下生成报表 SCOTT 日期 :16-8 月 -12 员工编号员工姓名入职时间工资 SMITH 月 -80 $ ALLEN 20-2 月 -81 $ FORD 月 -81 $ MILLER 23-1 月 -82 $ 谢谢使用该表页 : 1 已选择 14 行在上面的示例中, 首先使用 TTITLE 和 BTITLE 设置了报表的页眉和页脚, 然后使用 COLUMN 格式化列信息, 最后使用 TTITLE OFF 和 BTITLE OFF 关闭了设置的页眉

18 使用 SQL * Plus 工具第 3 章和页脚, 从而其他报表中不适用该页眉页脚的设置统计数据 BREAK 和 COMPUTE 命令可以结合使用, 用来为列添加小计 BREAK 子句可以使 SQL*Plus 根据列值的范围分隔输出结果,COMPUTE 子句可以使 SQL*Plus 计算一列的值 BREAK 命令的语法格式如下 : BRE[AK] [ON column_name] SKIP n 语法说明如下 : (1)column_name: 对哪一列执行操作 (2)SKIP n: 在指定列的值变化之前插入 n 个空行 COMPUTE 命令的语法格式如下 : COMP[UTE] function LABEL label OF column_name ON break_column_name 语法说明如下 : (1)Function: 执行的操作, 如 SUM( 求和 ) MAXIMUN( 最大值 ) MINIMUN ( 最小值 ) AVG( 平均值 ) COUNT( 非空值的列数 ) NUMBER( 行数 ) VARIANCE ( 方差 ) 以及 STD( 均方差 ) 等 (2)LABEL: 指定显示结果时的文本信息下面使用 BREAK 命令和 COMPUTE 命令计算不同部门的工资总数首先在 F:\SQL 文件夹下新建文件 sum.sql, 该脚本的内容如下 : BREAK ON deptno COMPUTE SUM OF sal ON deptno COLUMN deptno HEADING " 部门号 " FORMAT 99 COLUMN ename HEADING " 员工姓名 " FORMAT A20 COLUMN sal HEADING " 工资 " SELECT deptno,ename,sal FROM emp ORDER BY deptno; 63 命令执行 sum.sql 脚本, 执行结果如下 : 部门号员工姓名工资 CLARK $ KING $ MILLER $ ****** sum $ JONES $

19 Oracle 11g 基础教程与实验指导 30 MARTIN $ ****** sum $ 已选择 14 行当 deptno 有了新值后,SQL*Plus 会对输出结果重新进行分隔, 并对 deptno 相同行的 sal 列进行求和,deptno 列相同的行只会显示一次 deptno 的值 3.7 练习 3-2: 使用报表统计各部门的最高工资 64 在 3.6 节, 介绍了如何使用 TTITLE 和 BTITLE 命令创建报表以及如何使用 BREAK 和 COMPUTE 命令统计数据, 下面把这两种命令结合起来使用创建一个报表, 并统计各部门的最高工资 (1) 在 F:\SQL 文件夹下创建生成报表的脚本文件 max_sal.sql (2) 在 max_sal.sql 脚本文件中, 首先使用 TTITLE 和 BTITLE 命令设置报表的页眉和页脚信息, 并使用 SET PAGESIZE 命令设置每页显示 50 行数据, 使用 SET LINESEIZ 命令设置每行显示 100 个字符, 代码如下 : TTITLE RIGHT ' 日期 :' _DATE CENTER ' 使用报表统计各部门的最高工资 ' BTITLE CENTER '' RIGHT ''FORMAT 999 SKIP 2 SQL.PNO SET PAGESIZE 50 SET LINESIZE 100 (3) 使用 COLUMN 命令格式化列的信息, 在格式化列之前, 需要使用 CLEAR 命令清除列的格式, 代码如下 : CLEAR COLUMNS COLUMN empno HEADING ' 员工编号 ' FORMAT 9999 COLUMN ename HEADING ' 员工姓名 ' FORMAT A10 COLUMN deptno HEADING ' 部门编号 ' FORMAT 9999 COLUMN sal HEADIN ' 工资 'FORMAT $999, (4) 使用 BREAK 命令根据 deptno 列值的不同分隔输出, 并使用 COMPUTE 命令根据 deptno 列统计 sal 的最大值, 代码如下 : BREAK ON deptno COMPUTE MAXIMUM OF sal ON deptno SELECT deptno,empno,ename,sal FROM emp ORDER BY deptno; (5) 在 SQL*Plus 中使用 scott 用户登录, 当连接成功后, 使用 START 命令运行 max_sal.sql 文件, 运行结果如下 : SQL> START F:\SQL\max_sal.sql 使用报表统计各部门的最高工资日期 :17-8 月 -12 部门编号员工编号员工姓名工资 CLARK $2,450.00

20 使用 SQL * Plus 工具第 3 章 7839 KING $5, MILLER $1, 最高工资 $5, JONES $2, FORD $3, 最高工资 $3, WARD $1, TURNER $1, 最高工资 $2, 谢谢使用该表页 :1 已选择 14 行在上述的输出结果中, 输出了页眉和页脚信息, 每个数据列的内容都按照指定的格式进行显示, 使用 BREAK 命令根据 deptno 列值的不同进行分隔输出, 并使用 COMPUTE 命令, 从而统计了每个部门的最高工资 3.8 扩展练习 1. 格式化输出结果使用 SET PAGESIZE 命令将页面大小设置为 30 行, 并使用 SET LINESIZE 命令来设置每行显示的字符数为 20, 然后使用这种设置格式化查询当前用户下的所有表并显示出来使用报表统计各部门的平均工资使用报表统计各部门的平均工资, 要求在报表中显示当前时间, 结果如下 : 使用报表统计各部门的平均工资日期 :30-8 月 -12 部门编号员工编号员工姓名工资 CLARK $2, KING $5, MILLER $1, ******** avg $2, JONES $2, FORD $3, ******** avg $2, WARD $1, TURNER $1, ALLEN $1, JAMES $ 谢谢使用该表页 :1 已选择 14 行

sql> startup mount 改变数据库的归档模式 sql> alter database archivelog # 打开数据库 sql> alter database open 禁止归档模式 sql> shutdown immediate sql>startup mount sql> al

sql> startup mount 改变数据库的归档模式 sql> alter database archivelog # 打开数据库 sql> alter database open 禁止归档模式 sql> shutdown immediate sql>startup mount sql> al RMAN sql> sqlplus / as sysdba 查看数据库版本 sql> select * from v$version; 查看数据库名称 sql> show parameter db_name; 一使用 RMAN 时, 需要将数据库设置成归档模式 sql> conn / as sysdba; sql> show user 查看数据库是否为归档模式 sql> archive log list