Oracle 11g 数据库管理员指南-电子书-迷你版


数据库技术丛书 Oracle 11g数据库 管理员指南 刘宪军编著Oracle 11g是Oracle公司最新推出的数据库版本。本书从实用的角度出发,系统地介绍了 Oracle 11g的使用和管理,并对它的体系结构和常规管理进行了重点描述。本书对深奥的理论知 识不作过多的讨论,重点突出实用性,在每章中都提供了许多实用的例子,力求帮助读者更好 地使用Oracle。 从内容组织形式上来看,本书分为四大部分。第一部分介绍了Oracle 11g的使用基础,包 括SQL语言基础、SQL*Plus的使用和PL/SQL编程,其中对Oracle特有的PL/SQL进行了比较深 入的描述。第二部分是本书的重点,介绍了Oracle数据库管理(DBA)的各个方面,其中对 Oracle的体系结构进行了重点介绍。第三部分介绍了Oracle的自动文件管理和自动存储管理。第 四部分介绍了数据库的备份与恢复,其中对RMAN进行了重点介绍。 本书不仅可以作为Oracle数据库技术人员的参考手册,还可以作为培训中心的培训教材。 封底无防伪标均为盗版 版权所有,侵权必究 本书法律顾问北京市展达律师事务所 图书在版编目(CIP)数据 Oracle 11g数据库管理员指南 / 刘宪军编著. —北京:机械工业出版社, 2010.6 (数据库技术丛书) ISBN 978-7-111-30935-2 Ⅰ. O⋯Ⅱ. 刘⋯ Ⅲ . 关系数据库-数据库管理系统,Oracle 11g Ⅳ. TP311.138 中国版本图书馆CIP数据核字(2010)第106170号 机械工业出版社(北京市西城区百万庄大街22号 邮政编码 100037) 责任编辑:陈佳媛 印刷 2010年8月第1版第1次印刷 186mm×240mm· 23.75印张 标准书号:ISBN 978-7-111-30935-2 定价:49.00元 凡购本书,如有缺页、倒页、脱页,由本社发行部调换 客服热线:(010)88378991;88361066 购书热线:(010)68326294;88379649;68995259 投稿热线:(010)88379604 读者信箱:hzjsj@hzbook.com前言 目前全世界发展势头最猛的IT巨头可能要数Oracle公司了。几年前,Oracle收购了BEA公 司,成功地将Weblogic这个优秀的应用服务器软件收归己有。不久前,Oracle又将SUN公司纳 入自己旗下,从此有了自己的小型机和UNIX操作系统,这无疑为Oracle插上了腾飞的双翼。然 而,Oracle公司最引人注目的还是它的数据库产品。 在15年前,大家可能想不到Oracle公司会有今天的发展成果,当时的数据库市场是Sybase 的天下,Oracle只是一个名不见经传的小公司。如今Oracle数据库产品以其出色的安全性、稳 定性和优异的性能稳居数据库产品的榜首,占领了数据库市场的大部分份额,在银行、保险、 通信、政府等应用领域具有绝对的话语权。 在一个企业应用系统中,核心部分是数据。对一个企业而言,数据就是生命。要管理重要 的数据库,就需要高水平的数据库管理员。无论在国内还是国外,数据库管理员的待遇都是很 丰厚的。然而Oracle软件毕竟是一个非常复杂的数据库产品,如果只掌握它的皮毛,你只能做 一些初级的技术工作,根本无法管理数据库,你就失去了升职、深造的机会。不仅如此,如果 不小心把数据搞丢了,还要承担法律责任。 想必大家都听过“庖丁解牛”的故事吧?庖丁在解牛的时候,手所接触的地方,肩所靠的 地方,脚所踩的地方,膝盖所顶的地方,都发出皮骨相离声,刀子刺进去时响声更大,这些声 音没有不合乎音律的。正当你在欣赏这种音律时,牛肉已经被干净利索地解了下来,而庖丁 “提刀而立,为之四顾,为之踌躇满志,善刀而藏之”。究其原因,只有“依乎天理”,才能做 到“以神遇而不以目视,官知止而神欲行”,从而“批大 ,道大 ,因其固然”。一般的厨 工每月换一把刀,是因为他们用刀子去砍骨头,而庖丁的刀已经用了19年,还像新的一样。 学习Oracle也是一样的,只有掌握Oracle的脉络,那么在解决实际问题的时候才能像庖丁 那样,做到得心应手,游刃有余。在客户现场,当几个工程师(可能包括原厂工程师)正在为 解决一个问题争得面红耳赤的时候,如果你三下五除二帮客户解决了问题,你也能体会到庖丁 那种“踌躇满志”的感觉。 本书的编写目的,就是试图使读者掌握Oracle的脉络,而不是教大家怎样学SQL语句。本 书既不是对英文资料的翻译,也不是对Oracle产品用法的直白描述,而是对作者多年从事技术 支持和培训工作的经验总结。书中列举了很多实际的例子,都是作者在客户现场遇到过并亲手 解决的实际案例,希望这些例子对大家有所帮助。 读者在学习Oracle时,可以先学习怎样在UNIX/Linux系统中安装Oracle软件,然后学习怎 样创建数据库,接着应该学习SQL和PL/SQL。接下来要重点学习的内容是Oracle的体系结构, 只有掌握了这部分内容,才有可能对数据库进行管理、备份与恢复以及性能优化。以后如果有 机会配置RAC集群环境,或者利用DataGuard配置数据库的异地容灾,读者就能体会到,一旦掌握了Oracle体系结构,对自己的帮助是多么大。 现在的企业应用系统都越来越复杂,涉及好多种技术。要想管理好数据库,只掌握少数的 两三种技术是不够的。就数据库而言,目前的运行环境一般都是UNIX/Linux,管理员应该至少 掌握一种UNIX,还应该了解Java、网络和一些存储方面的知识。 在本书编写之前不久,Oracle公司发布了Oracle 11g的最新版本的产品11.2。根据以往的情 况来看,Oracle每个版本的产品都有两个次版本,其中第二个次版本是最稳定、用户使用时间 最长的产品。如Oracle 10g有两个次版本10.1和10.2,目前很多企业用户还在使用10.2这个版本。 本书的内容力求体现Oracle的最新特点。 面对技术发展的迅猛势头,作者也感觉才疏学浅。本书难免有疏漏和不足的地方,敬请读 者朋友批评指正。 作者 2010年5月 IV前言 第一部分 Oracle使用基础 第1章 SQL语言基础⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 1 1.1 查询语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 2 1.1.1 查询语句的基本用法 ⋯⋯⋯⋯⋯⋯ 2 1.1.2 查询语句中的条件 ⋯⋯⋯⋯⋯⋯⋯ 6 1.1.3 查询语句中的单行函数⋯⋯⋯⋯⋯⋯ 8 1.1.4 分组函数与分组统计 ⋯⋯⋯⋯⋯⋯ 14 1.1.5 数据的排序⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 17 1.1.6 多表查询 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 18 1.1.7 子查询 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 20 1.2 DML语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 22 1.2.1 INSERT语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 22 1.2.2 DELETE语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 23 1.2.3 UPDATE语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 24 1.3 事务控制语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 24 1.4 DDL语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 27 1.4.1 表的创建 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 27 1.4.2 如何修改表的结构 ⋯⋯⋯⋯⋯⋯⋯ 29 1.4.3 其他DDL命令⋯⋯⋯⋯⋯⋯⋯⋯⋯ 31 1.5 约束 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 32 1.5.1 约束的类型⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 32 1.5.2 如何在创建表时指定约束 ⋯⋯⋯⋯ 33 1.5.3 如何在创建表之后指定约束 ⋯⋯⋯ 35 1.5.4 约束的维护⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 36 1.6 视图 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 37 1.6.1 视图的创建、修改和删除 ⋯⋯⋯⋯ 38 1.6.2 如何对视图进行访问 ⋯⋯⋯⋯⋯⋯ 40 1.6.3 复杂视图 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 41 1.7 索引 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 42 1.7.1 索引的基本概念 ⋯⋯⋯⋯⋯⋯⋯⋯ 42 1.7.2 索引的创建、修改和删除 ⋯⋯⋯⋯ 45 1.7.3 索引信息的查询 ⋯⋯⋯⋯⋯⋯⋯⋯ 46 1.8 序列 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 46 1.8.1 序列的创建、修改和删除 ⋯⋯⋯⋯ 46 1.8.2 序列的使用⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 48 1.8.3 序列信息的查询 ⋯⋯⋯⋯⋯⋯⋯⋯ 50 1.9 同义词 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 50 1.9.1 同义词的概念和类型 ⋯⋯⋯⋯⋯⋯ 50 1.9.2 同义词的创建与删除 ⋯⋯⋯⋯⋯⋯ 51 1.9.3 同义词信息的查询 ⋯⋯⋯⋯⋯⋯⋯ 52 第2章 SQL*Plus用法指南 ⋯⋯⋯⋯⋯⋯⋯ 53 2.1 SQL*Plus的基本用法 ⋯⋯⋯⋯⋯⋯⋯ 53 2.1.1 登录与退出⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 53 2.1.2 如何获取帮助信息 ⋯⋯⋯⋯⋯⋯⋯ 55 2.1.3 如何修改SQL*Plus的设置信息 ⋯⋯ 55 2.1.4 实例的启动与关闭 ⋯⋯⋯⋯⋯⋯⋯ 57 2.2 SQL*Plus中的缓冲区 ⋯⋯⋯⋯⋯⋯⋯ 58 2.2.1 执行缓冲区中的内容 ⋯⋯⋯⋯⋯⋯ 58 2.2.2 编辑缓冲区的内容 ⋯⋯⋯⋯⋯⋯⋯ 59 2.2.3 如何对操作系统文件进行读写 ⋯⋯ 61 2.3 如何在SQL*Plus中使用变量⋯⋯⋯⋯⋯ 64 2.3.1 用户自定义的变量 ⋯⋯⋯⋯⋯⋯⋯ 64 2.3.2 参数变量 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 65 2.3.3 与变量有关的交互式命令 ⋯⋯⋯⋯ 66 2.4 SQL*Plus的报表功能 ⋯⋯⋯⋯⋯⋯⋯ 67 2.4.1 报表的标题设计 ⋯⋯⋯⋯⋯⋯⋯⋯ 68 2.4.2 报表显示格式的设计 ⋯⋯⋯⋯⋯⋯ 69 2.4.3 如何对特定列进行统计 ⋯⋯⋯⋯⋯ 72 第3章 PL/SQL编程⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 75 3.1 PL/SQL概述 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 75 3.2 PL/SQL中的变量 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 76 3.2.1 变量的定义与使用 ⋯⋯⋯⋯⋯⋯⋯ 76 3.2.2 如何在PL/SQL中定义类型 ⋯⋯⋯⋯ 78 3.3 PL/SQL中的流控制 ⋯⋯⋯⋯⋯⋯⋯⋯ 81 目录3.3.1 IF语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 82 3.3.2 LOOP语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 83 3.3.3 WHILE语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 83 3.3.4 FOR语句 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 84 3.4 PL/SQL如何访问数据库 ⋯⋯⋯⋯⋯⋯ 85 3.4.1 如何对数据进行查询 ⋯⋯⋯⋯⋯⋯ 85 3.4.2 如何使用DML语句 ⋯⋯⋯⋯⋯⋯⋯ 87 3.5 子程序设计 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 88 3.5.1 如何使用过程 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 88 3.5.2 如何使用函数 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 91 3.5.3 函数与过程的重载 ⋯⋯⋯⋯⋯⋯⋯ 93 3.5.4 函数与过程的递归调用 ⋯⋯⋯⋯⋯ 95 3.6 存储过程与存储程序 ⋯⋯⋯⋯⋯⋯⋯ 97 3.6.1 存储过程 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 98 3.6.2 存储函数 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 100 3.6.3 程序包 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 101 3.6.4 系统预定义程序包 ⋯⋯⋯⋯⋯⋯ 104 3.6.5 与存储程序有关的数据字典 ⋯⋯⋯ 110 3.7 异常处理⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 113 3.7.1 异常处理程序 ⋯⋯⋯⋯⋯⋯⋯⋯ 113 3.7.2 预定义的异常 ⋯⋯⋯⋯⋯⋯⋯⋯ 114 3.7.3 非预定义异常 ⋯⋯⋯⋯⋯⋯⋯⋯ 117 3.7.4 用户自定义的异常 ⋯⋯⋯⋯⋯⋯ 118 3.7.5 异常的传递 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 121 3.8 游标的应用 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 124 3.8.1 隐式游标 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 124 3.8.2 显式游标 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 125 3.8.3 带参数的游标 ⋯⋯⋯⋯⋯⋯⋯⋯ 130 3.8.4 如何通过游标修改表中的数据 ⋯⋯ 132 3.9 触发器 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 133 3.9.1 触发器的使用 ⋯⋯⋯⋯⋯⋯⋯⋯ 134 3.9.2 语句级触发器 ⋯⋯⋯⋯⋯⋯⋯⋯ 135 3.9.3 行触发器 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 137 3.9.4 视图上的触发器 ⋯⋯⋯⋯⋯⋯⋯ 141 3.9.5 与触发器有关的数据字典 ⋯⋯⋯⋯ 144 第二部分 Oracle DBA 第4章 Oracle体系结构 ⋯⋯⋯⋯⋯⋯⋯⋯ 147 4.1 实例的体系结构 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 148 4.1.1 实例的概念 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 148 4.1.2 实例的组成 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 148 4.2 实例的内存结构 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 149 4.2.1 数据库高速缓存 ⋯⋯⋯⋯⋯⋯⋯ 150 4.2.2 重做日志缓冲区 ⋯⋯⋯⋯⋯⋯⋯ 153 4.2.3 共享池 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 153 4.2.4 Java池 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 155 4.2.5 PGA ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 155 4.3 实例中的后台进程 ⋯⋯⋯⋯⋯⋯⋯⋯ 156 4.3.1 DBWR进程 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 157 4.3.2 LGWR进程 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 158 4.3.3 CKPT进程 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 160 4.3.4 SMON进程 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 161 4.3.5 PMON进程 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 162 4.3.6 ARCH进程 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 162 4.4 实例的内存结构管理 ⋯⋯⋯⋯⋯⋯⋯ 163 4.4.1 自动内存管理 ⋯⋯⋯⋯⋯⋯⋯⋯ 163 4.4.2 自动共享内存管理 ⋯⋯⋯⋯⋯⋯ 164 4.4.3 手工共享内存管理 ⋯⋯⋯⋯⋯⋯ 164 4.5 数据库的连接模式 ⋯⋯⋯⋯⋯⋯⋯⋯ 165 4.5.1 专用数据库连接模式 ⋯⋯⋯⋯⋯ 165 4.5.2 共享数据库连接模式 ⋯⋯⋯⋯⋯ 166 4.5.3 如何设置共享连接模式⋯⋯⋯⋯⋯ 167 4.6 数据库的逻辑结构 ⋯⋯⋯⋯⋯⋯⋯⋯ 168 4.6.1 表空间 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 169 4.6.2 段 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 171 4.6.3 区 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 172 4.6.4 数据块 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 172 4.7 数据库的物理结构 ⋯⋯⋯⋯⋯⋯⋯⋯ 172 4.7.1 数据文件 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 173 4.7.2 控制文件 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 173 4.7.3 重做日志文件 ⋯⋯⋯⋯⋯⋯⋯⋯ 174 4.7.4 跟踪文件和警告文件 ⋯⋯⋯⋯⋯ 174 4.8 特权用户与口令文件 ⋯⋯⋯⋯⋯⋯⋯ 175 4.9 数据字典视图与动态性能视图 ⋯⋯⋯ 176 4.9.1 数据字典视图 ⋯⋯⋯⋯⋯⋯⋯⋯ 176 4.9.2 动态性能视图 ⋯⋯⋯⋯⋯⋯⋯⋯ 177 4.10 初始化参数 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 178 VI4.10.1 参数文件⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 178 4.10.2 初始化参数的查看 ⋯⋯⋯⋯⋯⋯ 179 4.10.3 初始化参数的修改 ⋯⋯⋯⋯⋯⋯ 179 第5章 数据库的创建⋯⋯⋯⋯⋯⋯⋯⋯⋯ 181 5.1 数据库的规划 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 181 5.1.1 SGA的规划 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 181 5.1.2 数据文件的规划 ⋯⋯⋯⋯⋯⋯⋯ 182 5.1.3 控制文件的规划 ⋯⋯⋯⋯⋯⋯⋯ 183 5.1.4 重做日志文件的规划 ⋯⋯⋯⋯⋯ 183 5.1.5 参数文件的规划 ⋯⋯⋯⋯⋯⋯⋯ 183 5.2 如何利用DBCA创建数据库⋯⋯⋯⋯⋯ 184 5.3 如何利用命令行创建数据库 ⋯⋯⋯⋯ 195 5.3.1 编辑文本参数文件 ⋯⋯⋯⋯⋯⋯ 195 5.3.2 实例的管理 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 196 5.3.3 口令文件的创建 ⋯⋯⋯⋯⋯⋯⋯ 198 5.3.4 数据库的创建 ⋯⋯⋯⋯⋯⋯⋯⋯ 200 5.3.5 如何创建数据字典视图⋯⋯⋯⋯⋯ 202 5.3.6 如何创建默认的profile ⋯⋯⋯⋯⋯ 202 5.3.7 如何创建SCOTT模式 ⋯⋯⋯⋯⋯ 202 5.3.8 如何创建服务器参数文件 ⋯⋯⋯⋯ 203 5.4 数据库服务器的启动和关闭 ⋯⋯⋯⋯ 203 5.5 如何利用NET Manager配置客户端与 服务器端的通信 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 205 5.5.1 监听器的创建 ⋯⋯⋯⋯⋯⋯⋯⋯ 205 5.5.2 监听器的管理 ⋯⋯⋯⋯⋯⋯⋯⋯ 206 5.5.3 Oracle客户端的配置 ⋯⋯⋯⋯⋯⋯ 207 第6章 表空间的管理⋯⋯⋯⋯⋯⋯⋯⋯⋯ 209 6.1 表空间的结构 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 209 6.1.1 区管理方式 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 209 6.1.2 段管理方式 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 210 6.1.3 数据文件 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 210 6.2 本地管理表空间的管理 ⋯⋯⋯⋯⋯⋯ 211 6.2.1 本地管理表空间的创建 ⋯⋯⋯⋯⋯ 211 6.2.2 表空间信息的查询 ⋯⋯⋯⋯⋯⋯ 212 6.2.3 表空间的删除 ⋯⋯⋯⋯⋯⋯⋯⋯ 213 6.3 大文件表空间的管理 ⋯⋯⋯⋯⋯⋯⋯ 213 6.3.1 大文件表空间的支持 ⋯⋯⋯⋯⋯ 213 6.3.2 大文件表空间的创建 ⋯⋯⋯⋯⋯ 214 6.3.3 大文件表空间的修改 ⋯⋯⋯⋯⋯ 214 6.4 临时表空间的管理 ⋯⋯⋯⋯⋯⋯⋯⋯ 214 6.4.1 临时表空间的创建 ⋯⋯⋯⋯⋯⋯ 215 6.4.2 临时表空间组 ⋯⋯⋯⋯⋯⋯⋯⋯ 215 6.5 UNDO表空间的管理 ⋯⋯⋯⋯⋯⋯⋯ 216 6.5.1 UNDO表空间的创建⋯⋯⋯⋯⋯⋯ 217 6.5.2 UNDO表空间的切换⋯⋯⋯⋯⋯⋯ 217 6.6 表空间的扩展 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 218 6.6.1 如何添加新的数据文件⋯⋯⋯⋯⋯ 219 6.6.2 如何扩展数据文件 ⋯⋯⋯⋯⋯⋯ 219 6.7 表空间的维护 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 220 6.7.1 表空间的联机与脱机 ⋯⋯⋯⋯⋯ 220 6.7.2 数据文件的联机与脱机⋯⋯⋯⋯⋯ 222 6.7.3 表空间的读写权限 ⋯⋯⋯⋯⋯⋯ 222 6.7.4 数据文件的移动和重命名 ⋯⋯⋯⋯ 223 第7章 存储空间管理⋯⋯⋯⋯⋯⋯⋯⋯⋯ 225 7.1 段的管理⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 225 7.1.1 段的类型 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 225 7.1.2 段的空间管理 ⋯⋯⋯⋯⋯⋯⋯⋯ 228 7.2 区的管理⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 229 7.2.1 区的分配 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 229 7.2.2 区的回收 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 230 7.3 数据块的管理 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 230 7.3.1 数据块的组成 ⋯⋯⋯⋯⋯⋯⋯⋯ 230 7.3.2 数据块的空间管理 ⋯⋯⋯⋯⋯⋯ 231 第8章 控制文件管理⋯⋯⋯⋯⋯⋯⋯⋯⋯ 234 8.1 控制文件的规划 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 234 8.1.1 控制文件的镜像 ⋯⋯⋯⋯⋯⋯⋯ 234 8.1.2 控制文件的存储位置 ⋯⋯⋯⋯⋯ 235 8.2 控制文件的重新创建 ⋯⋯⋯⋯⋯⋯⋯ 236 8.2.1 如何增加新的控制文件⋯⋯⋯⋯⋯ 236 8.2.2 如何重新创建控制文件⋯⋯⋯⋯⋯ 236 8.3 控制文件的备份与删除 ⋯⋯⋯⋯⋯⋯ 240 8.4 控制文件信息的查询 ⋯⋯⋯⋯⋯⋯⋯ 240 8.4.1 查询控制文件的位置和名称 ⋯⋯⋯ 241 8.4.2 查询控制文件中记录的信息 ⋯⋯⋯ 241 第9章 重做日志管理⋯⋯⋯⋯⋯⋯⋯⋯⋯ 243 9.1 重做日志的规划 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 244 VII9.1.1 重做日志缓冲区的规划⋯⋯⋯⋯⋯ 245 9.1.2 重做日志文件组的规划⋯⋯⋯⋯⋯ 245 9.1.3 如何对重做日志文件进行规划 ⋯⋯ 246 9.2 重做日志文件的管理 ⋯⋯⋯⋯⋯⋯⋯ 247 9.2.1 增加重做日志组 ⋯⋯⋯⋯⋯⋯⋯ 247 9.2.2 增加日志成员 ⋯⋯⋯⋯⋯⋯⋯⋯ 248 9.2.3 修改重做日志文件的存储位置和 名称 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 248 9.2.4 删除重做日志文件 ⋯⋯⋯⋯⋯⋯ 249 9.2.5 重做日志文件的清空 ⋯⋯⋯⋯⋯ 250 9.2.6 重做日志的切换 ⋯⋯⋯⋯⋯⋯⋯ 250 9.2.7 重做日志信息的查询 ⋯⋯⋯⋯⋯ 251 9.3 归档日志的管理 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 252 9.3.1 数据库的日志模式 ⋯⋯⋯⋯⋯⋯ 252 9.3.2 切换日志模式 ⋯⋯⋯⋯⋯⋯⋯⋯ 253 9.3.3 设置归档位置 ⋯⋯⋯⋯⋯⋯⋯⋯ 254 9.3.4 归档信息的查询 ⋯⋯⋯⋯⋯⋯⋯ 255 9.4 如何对重做日志进行分析 ⋯⋯⋯⋯⋯ 256 9.4.1 如何创建字典文件 ⋯⋯⋯⋯⋯⋯ 256 9.4.2 如何创建分析列表 ⋯⋯⋯⋯⋯⋯ 257 9.4.3 如何开始日志分析 ⋯⋯⋯⋯⋯⋯ 257 9.4.4 如何查看日志分析结果⋯⋯⋯⋯⋯ 258 9.4.5 如何结束日志分析 ⋯⋯⋯⋯⋯⋯ 259 第10章 基本数据库对象管理 ⋯⋯⋯⋯⋯ 260 10.1 表的管理 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 260 10.1.1 表的结构⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 260 10.1.2 表的创建⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 262 10.1.3 表的修改⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 264 10.1.4 表的删除⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 266 10.2 分区表的管理 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 267 10.2.1 分区的概念 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 268 10.2.2 范围分区⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 268 10.2.3 列表分区⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 269 10.2.4 散列分区⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 270 10.2.5 复合分区⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 270 10.3 索引的管理 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 271 10.3.1 索引概述⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 272 10.3.2 反向索引⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 272 10.3.3 位图索引⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 273 10.3.4 基于函数的索引 ⋯⋯⋯⋯⋯⋯⋯ 274 10.3.5 分区索引⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 275 10.3.6 索引的维护 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 275 10.4 簇的管理 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 276 10.4.1 簇的创建⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 277 10.4.2 簇的修改⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 278 10.4.3 簇的删除⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 278 10.4.4 簇信息的查询 ⋯⋯⋯⋯⋯⋯⋯⋯ 279 10.5 索引组织表的管理 ⋯⋯⋯⋯⋯⋯⋯ 279 10.5.1 索引组织表的概念 ⋯⋯⋯⋯⋯⋯ 279 10.5.2 索引组织表的创建 ⋯⋯⋯⋯⋯⋯ 280 10.5.3 索引组织表的维护 ⋯⋯⋯⋯⋯⋯ 281 第11章 用户与权限管理 ⋯⋯⋯⋯⋯⋯⋯ 282 11.1 用户管理 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 282 11.1.1 数据库中有哪些用户 ⋯⋯⋯⋯⋯ 282 11.1.2 如何创建用户 ⋯⋯⋯⋯⋯⋯⋯⋯ 283 11.1.3 如何修改用户的信息 ⋯⋯⋯⋯⋯ 284 11.1.4 如何删除用户 ⋯⋯⋯⋯⋯⋯⋯⋯ 285 11.2 用户权限的管理 ⋯⋯⋯⋯⋯⋯⋯⋯ 285 11.2.1 系统权限的管理 ⋯⋯⋯⋯⋯⋯⋯ 286 11.2.2 对象权限的管理 ⋯⋯⋯⋯⋯⋯⋯ 288 11.2.3 权限信息的查询 ⋯⋯⋯⋯⋯⋯⋯ 291 11.3 角色的管理 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 292 11.3.1 角色的创建和删除 ⋯⋯⋯⋯⋯⋯ 293 11.3.2 角色中权限的添加和删除 ⋯⋯⋯ 294 11.3.3 角色的分配和回收 ⋯⋯⋯⋯⋯⋯ 295 11.3.4 角色信息的查询 ⋯⋯⋯⋯⋯⋯⋯ 295 11.4 PROFILE的管理 ⋯⋯⋯⋯⋯⋯⋯⋯ 296 11.4.1 PROFILE的创建与删除 ⋯⋯⋯⋯ 296 11.4.2 如何利用PROFILE对用户口令 进行控制⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 297 11.4.3 如何利用PROFILE对用户使用 资源进行控制 ⋯⋯⋯⋯⋯⋯⋯⋯ 298 11.4.4 默认的PROFILE ⋯⋯⋯⋯⋯⋯⋯ 299 第三部分自动文件管理和自动存储管理 第12章 自动文件管理 ⋯⋯⋯⋯⋯⋯⋯⋯ 301 12.1 如何激活自动文件管理功能 ⋯⋯⋯⋯ 301 VIII12.2 文件的命名规则 ⋯⋯⋯⋯⋯⋯⋯⋯ 302 12.3 如何创建OMF数据库 ⋯⋯⋯⋯⋯⋯ 302 12.4 如何创建OMF表空间 ⋯⋯⋯⋯⋯⋯ 304 12.5 如何创建OMF控制文件⋯⋯⋯⋯⋯⋯ 305 12.6 如何创建OMF重做日志文件 ⋯⋯⋯⋯ 306 第13章 自动存储管理 ⋯⋯⋯⋯⋯⋯⋯⋯ 307 13.1 ASM实例 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 307 13.2 磁盘组的管理 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 309 13.3 如何使用ASM磁盘组 ⋯⋯⋯⋯⋯⋯ 312 第四部分备份与恢复 第14章 数据库的导入与导出 ⋯⋯⋯⋯⋯ 315 14.1 导入导出工具的用法⋯⋯⋯⋯⋯⋯⋯ 316 14.2 表的导入与导出 ⋯⋯⋯⋯⋯⋯⋯⋯ 317 14.3 用户模式的导入与导出 ⋯⋯⋯⋯⋯⋯ 319 14.4 数据库的导入与导出⋯⋯⋯⋯⋯⋯⋯ 319 14.5 表空间的导入与导出⋯⋯⋯⋯⋯⋯⋯ 319 第15章 数据库的常规备份与恢复 ⋯⋯⋯ 322 15.1 备份与恢复的相关概念 ⋯⋯⋯⋯⋯⋯ 322 15.1.1 冷备份与热备份 ⋯⋯⋯⋯⋯⋯⋯ 322 15.1.2 物理备份与逻辑备份 ⋯⋯⋯⋯⋯ 322 15.1.3 完全备份与增量备份 ⋯⋯⋯⋯⋯ 322 15.1.4 备份策略⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 323 15.1.5 完全恢复与不完全恢复 ⋯⋯⋯⋯ 324 15.1.6 日志模式对备份与恢复的影响 ⋯ 324 15.1.7 哪些情况将导致数据丢失 ⋯⋯⋯ 324 15.1.8 哪些文件需要备份 ⋯⋯⋯⋯⋯⋯ 325 15.2 控制文件的备份与恢复 ⋯⋯⋯⋯⋯⋯ 326 15.3 重做日志文件的备份与恢复 ⋯⋯⋯⋯ 326 15.4 数据文件的备份 ⋯⋯⋯⋯⋯⋯⋯⋯ 326 15.5 数据库的完全恢复 ⋯⋯⋯⋯⋯⋯⋯ 327 15.6 两个实际的备份与恢复的例子 ⋯⋯⋯ 328 15.6.1 模拟数据文件损坏的例子 ⋯⋯⋯ 328 15.6.2 模拟磁盘损坏的例子 ⋯⋯⋯⋯⋯ 329 15.7 Flashback技术在数据库恢复中的应用⋯ 329 15.7.1 回收站的应用 ⋯⋯⋯⋯⋯⋯⋯⋯ 330 15.7.2 Flashback技术在表上的应用 ⋯⋯ 331 15.7.3 Flashback技术在数据库恢复中 的应用 ⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯ 331 第16章 如何利用RMAN对数据库进行 备份与恢复⋯⋯⋯⋯⋯⋯⋯⋯⋯ 332 16.1 RMAN的基本结构 ⋯⋯⋯⋯⋯⋯⋯ 332 16.2 RMAN的配置 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 334 16.2.1 如何配置RMAN客户端的连接 ⋯ 334 16.2.2 恢复目录的创建 ⋯⋯⋯⋯⋯⋯⋯ 334 16.3 如何利用RMAN对数据库进行备份 ⋯ 335 16.3.1 通道的设置 ⋯⋯⋯⋯⋯⋯⋯⋯⋯ 335 16.3.2 存储脚本的用法 ⋯⋯⋯⋯⋯⋯⋯ 336 16.3.3 控制文件的备份 ⋯⋯⋯⋯⋯⋯⋯ 337 16.3.4 参数文件的备份 ⋯⋯⋯⋯⋯⋯⋯ 338 16.3.5 归档日志文件的备份 ⋯⋯⋯⋯⋯ 338 16.3.6 非归档模式下数据文件的备份 ⋯ 339 16.3.7 归档模式下数据文件的备份 ⋯⋯ 339 16.3.8 备份集的备份 ⋯⋯⋯⋯⋯⋯⋯⋯ 341 16.4 如何对数据库进行完全恢复 ⋯⋯⋯⋯ 342 16.4.1 如何对备份文件进行校验 ⋯⋯⋯ 342 16.4.2 如何对数据文件进行恢复 ⋯⋯⋯ 342 16.5 两个实际的例子 ⋯⋯⋯⋯⋯⋯⋯⋯ 344 16.5.1 模拟数据文件损坏的例子 ⋯⋯⋯ 344 16.5.2 模拟磁盘损坏的例子 ⋯⋯⋯⋯⋯ 345 16.6 如何对坏块进行恢复⋯⋯⋯⋯⋯⋯⋯ 346 16.6.1 什么叫块介质恢复 ⋯⋯⋯⋯⋯⋯ 346 16.6.2 如何进行块介质恢复 ⋯⋯⋯⋯⋯ 347 16.7 如何对数据进行跨平台移植 ⋯⋯⋯⋯ 347 16.7.1 字节存储次序相同时的移植 ⋯⋯ 348 16.7.2 字节存储次序不同时的移植 ⋯⋯ 349 附录A Oracle 11g在AIX下的安装 ⋯⋯ 351 附录B Oracle 11g在Linux下的安装 ⋯⋯ 360 附录C Oracle 11g在Solaris下的安装 ⋯ 364 IX第1章 SQL语言基础 SQL是结构化查询语言(Structured Query Language)的缩写,它是目前关系数据库系统中 通用的标准语言。 SQL最早在20世纪70年代由Boyce和Chamberlin提出,并首先在IBM公司的数据库管理系统 System R上实现,随后又在IBM的DB2上实现,并获得了巨大的成功。后来美国标准化组织和 国际标准化组织先后将SQL作为关系数据库系统的标准语言,从此,SQL得到了发展的机会。 到目前为止,包括Oracle、Sybase、Informix等在内的几乎所有大型数据库系统都支持SQL。 SQL在字面上虽然称为结构化查询语言,实际上它还包括数据操纵、数据定义、事务控制、 安全控制等一系列命令。SQL操作的基本对象是表,也就是关系。它可以对表中的数据进行查 询、增加、删除、修改等常规操作,还可以维护表中数据的一致性、完整性和安全性,能够满 足从单机到分布式系统的各种应用需求。 SQL是一种非过程化的语言,用户在使用SQL操作数据时,只需要告诉系统做什么,而不 需要关心怎么做,系统会根据用户的意图自动完成相应的操作。由于SQL的这一特点,它被人 们称为“第四代语言”(4GL),以区别于面向过程的高级语言。 用SQL语言编写的SQL语句有两种执行方式,一种是联机交互方式,SQL语句在一定的平 台上执行,例如数据库管理系统提供的实用程序。这个执行平台将SQL语句提交给数据库服务 器,并将从数据库服务器返回的执行结果显示给用户。另一种方式是嵌入方式,用户在用 C/C++、Java等高级语言编写应用程序时,可能需要操作数据库中的数据,这时SQL作为一种 嵌入式语言,嵌入到高级语言程序中,通过数据库接口如ODBC、JDBC访问数据库中的数据。 SQL包括一系列命令,可以满足对数据的各种访问。按照通用的分类标准,SQL命令分为 以下几种类型: ¥ 查询命令 包括SELECT命令 ¥ DML命令 包括INSERT、DELETE、UPDATE命令 ¥ DDL命令 包括CREATE、DROP、ALTER、RENAME、TRUNCATE命令 ¥ 事务控制命令 包括COMMIT、ROLLBACK、SAVEPOINT命令 ¥ DCL命令 包括GRANT、REVOKE命令 命令和相关的参数一起构成了SQL语句。下面将对这些命令分别进行详细的介绍。 第一部分 Oracle使用基础1.1 查询语句 查询语句是使用最为频繁的数据库访问语句,对应的SQL命令是SELECT。虽然只有一条 命令,但是由于它有灵活多样的形式,以及功能强大的子句,可以组成各种复杂的查询语句, 能够完成各种复杂的查询。 SELECT语句可以根据用户的要求查询数据库中的数据,并且可以对它们进行简单的计算 和统计。最简单的SELECT语句只有一个FROM子句,格式如下: SELECT表达式 FROM表名 其中SELECT之后引导一个或多个列名,或者表达式,用来指定需要查询的列,或者对数 据所进行的计算。在FROM子句指定一个或多个表名,用来指定本次查询所涉及的表。查询的 结果是返回一行或多行数据,每行由一个或多个列的列值组成。 完整的SELECT语句包括WHERE、ORDER、GROUP等子句。格式如下: SELECT 表达式 FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 条件 ORDER BY 表达式 SELECT语句最灵活的用法体现在WHERE子句中的查询条件,这个条件用来指定查询什么 样的数据。在以下各节中,我们将分别介绍SELECT语句的各个组成部分。 1.1.1 查询语句的基本用法 如果要查询某个表中一个或多个列的数据,需要在SELECT命令之后指定列名,并在 FROM子句中指定查询所涉及的表。格式如下: SELECT 列1,列2... FROM 表名 查询的结果是从指定的表中将指定列的数据显示出来。例如,要查询dept表中的deptno和 loc列,对应的SELECT语句为: SELECT deptno,loc FROM dept; 这样的语句可以在Oracle提供的实用工具SQL*Plus中执行,也可以在其他实用工具或应用 程序中执行。SQL*Plus的提示符是“SQL>”,在本书中介绍的SQL语句基本上是在SQL*Plus 中执行的。在操作系统的终端窗口中输入sqlplus命令,并指定用户名和口令,即可登录数据库 服务器。例如(其中$为UNIX/Linux系统的SHELL提示符): $ sqlplus scott/tiger SQL语句中除字符串外,各个部分是大小写不敏感的。如果在SQL*Plus中执行SQL语句, 还要在语句末尾加上一个分号。分号并不是SQL语句的一部分,只是语句结束的标志。一条句 可以在一行中书写,也可以分行书写。关于SQL*Plus的详细用法,请参阅第2章。这条命令的 2 第一部分 Oracle使用基础执行结果为: DEPTNO LOC 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON 如果要查询表中的所有的列,可以用“*”符号代替所有的列名。例如: SQL> SELECT * FROM dept; 如果不了解表的结构,可以在SQL*Plus中执行命令DESCRIBE(简写为DESC),查看表的 结构。这个命令的参数是表名,或者其他对象名。注意,这条命令不是SQL命令,而是 SQL*Plus中的命令。 为了演示SQL语句的用法,在本章中大部分SQL语句都以Oracle提供的模板模式中的表emp 和dept为操作对象。只要以用户名scott和口令TIGER登录数据库服务器,就能直接访问这两个 表。其中emp表是员工的信息表,dept是部门信息表,这两个表的结构如下所示(其中各列的 意义是作者标注的): SQL> DESC emp 名称 是否为空 类型 意义 EMPNO NOT NULL NUMBER(4) 员工编号 ENAME NULL VARCHAR2(10) 姓名 JOB NULL VARCHAR2(9) 职务 MGR NULL NUMBER(4) 经理编号 HIREDATE NULL DATE 受聘时间 SAL NULL NUMBER(7,2) 工资 COMM NULL NUMBER(7,2) 奖金 DEPTNO NULL NUMBER(2) 所在的部门号 SQL> DESC dept 名称 是否为空 类型 意义 DEPTNO NOT NULL NUMBER(2) 部门编号 DNAME NULL VARCHAR2(14) 部门名称 LOC NULL VARCHAR2(13) 部门所在的城市 在默认情况下,在显示数据时,各列的标题就是列的名称。在SELECT语句中可以定义列 的别名,这样在显示数据时,列的标题就是这个别名,在整个SQL语句中都可以使用这个别名。 使用别名的SELECT语句格式为: SQL> SELECT 列1 AS 别名1,列2 AS 别名2... 或者在列名后直接指定别名,省略AS关键字。例如: SQL> SELECT deptno AS 部门编号,loc 地址 FROM dept; 这条命令的执行结果为: 部门编号 地址 10 NEW YORK 20 DALLAS 第1章 SQL语言基础  330 CHICAGO 40 BOSTON 在查询结果中如果有重复行,可以使用DISTINCT关键字去掉重复行的显示。重复行是指 在SELECT语句中涉及的所有列的列值完全相同的行。例如,要查询员工所分布的部门,可以 用DISTINCT关键字去掉其中的重复行: SQL> SELECT DISTINCT deptno AS 部门编号 FROM emp; 实际上多名员工在同一部门上班的情况是存在的,但是这条命令执行的结果去掉了重复的 部门编号的显示。命令执行的结果为: 部门编号 10 20 30 SELECT语句不仅可以进行简单的查询,还可以 对查询的列进行简单的计算,也可以在两个列之间进 行计算,或者将某个列与其他表达式,或者两个表达 式进行计算。表1.1中列出了在SELECT语句可以使用 的运算符。 使用||运算符可以将两个数据连接起来。无论是 数字型还是日期型数据,在进行这种运算时,都可以看做是字符型数据。通过||运算符,用户 可以设计自己喜欢的数据显示方式,如将两个列的值连接起来,也可以将列的值与其他文字连 接起来。连接以后所得的数据可以当做一个列来显示。例如,可以将dept表中的deptno和loc列 以及其他文字连接起来,相应的SELECT语句为: SQL> SELECT '部门'||deptno||'的地址为:'||loc AS 部门地址 FROM dept; 这条命令的执行结果为: 部门地址 ------------------------------------- 部门10的地址为:NEW YORK ...... 如果在SQL语句中使用了字符串,必须用一对单引号将字符串限定,并且字符串中的字符 是大小写敏感的。 加减乘除四则运算在SELECT语句中比较简单,需要注意的是空值的计算。空值与其他数 据进行四则运算时,结果将得到空值,而不管它与什么样的数据运算。例如,要在emp表中查 询员工的工资与奖金之和,由于部分员工的奖金为空,致使查询的结果与我们希望的结果不符。 查询语句为: SQL> SELECT sal+comm AS 总收入 FROM emp; 这条语句的执行结果为: 总收入 ---------- 4 第一部分 Oracle使用基础 表1.1 查询语句中可以使用的运算符 运算符 意义 - 取相反数 * / 乘法和除法 + - 加法、减法 || 字符串连接1900 1750 ...... 已选择12行。 在emp表中共有12名员工,每名员工都有工资。如果奖金为空,对应的计算结果就为空。 空值与0或者空格是不同的。空值就是没有数据,而0或者空格是实实在在的数据,就像考试没 有成绩和得了0分是不一样的。为了解决空值的计算问题,SQL提供了一个函数,这个函数是 NVL,它的功能是把空值转换为其他可以参加运算的数据。这个函数的调用格式是: NVL(表达式, 替代值) 当表达式的结果为空时,这个函数就把表达式的值用指定的值代替。有了这个函数,我们 就可以在奖金为空时把它用0或者其他数据代替。改进后的查询工资和奖金之和的语句为: SQL> SELECT sal+nvl(comm,0) AS总收入 FROM emp; 总收入 ---------- 800 1900 1750 2975 ...... 已选择12行。 SELECT命令还可以用来计算一个普通表达式的值,这个表达式可能与表没有任何关系, 如3*5这样的表达式。例如: SQL> SELECT 3*5, 3+5 FROM dept; 不过这样的查询语句所得的结果却不是我们希望的。这条SELECT语句执行的结果为: 3*5 3+5 ---------- ---------- 15 8 15 8 15 8 15 8 原来,查询的结果是把表达式的值重复了若干次。因为SELECT语句必须通过FROM子句 指定一个或多个表,而表达式与这些表是无关的,所以,SELECT语句简单地根据可以查询到 的行数,将表达式的值重复若干次。为了解决这个问题,Oracle提供了一个特殊的表dual,这 个表的结构为: SQL> DESC dual 名称 是否为空? 类型 ----------------------- -------- ------------------------- DUMMY VARCHAR2(1) 通过查询这个表,发现表中只有一行数据: SQL> SELECT * FROM dual; 第1章 SQL语言基础  5D - X 可见,dual表只有一个列,而且中有一行数据。所以,在进行与具体的表无关的运算时, 可以在FROM子句中指定dual表,这样可以保证计算的结果只显示一次。例如: SELECT 3*5, 3+5 FROM dual; 1.1.2 查询语句中的条件 在前面所列举的查询中,由于没有限制条件,所以查询的结果是将表中的所有行都显示出 来。如果希望只查询一部分行,那么可以通过WHERE子句指定条件。WHERE子句的作用是通 过指定条件,使SELECT语句仅仅查询符合条件的行,如部门10的员工数据,或者工资大于 2000元的员工数据等。在更多情况下,都需要根据指定的条件对数据进行查询。 WHERE子句指定的条件是一个关系表达式,如果关系表达式的结果为真,则条件成立, 否则条件不成立。关系表达式用于比较两个表达式的大小,或者进行模糊匹配,或者将一个表 达式的值与一个集合中的元素进行匹配。表1.2列出了常用的关系运算符。 表1.2 常用的关系运算符 关系运算符 用 法 说 明 = != > >= < <= 比较两个表达式的大小及是否相等 LIKE LIKE ‘字符串’ 字符串的模糊匹配 IN IN (元素1, 元素2...) 与集合中的元素进行匹配 BETWEEN BETWEEN a AND b 检查表达式的值是否在a和b之间 AND OR 条件1 AND 条件2 两个条件的连接 条件1 OR 条件2 NOT NOT 条件 条件取反 IS NULL 判断表达式的值是否为空 例如,要在表dept中查询部门10的员工姓名和工资信息,对应的SELECT语句为: SQL> SELECT ename,sal FROM emp WHERE deptno=10; 下面的SELECT语句用于查询员工KING的基本情况: SQL> SELECT empno,ename,sal,comm FROM emp WHERE ename='KING'; LIKE运算符通常用来进行字符串的模糊匹配,而“=”运算符只能对字符串进行精确比较。 在LIKE指定的关系表达式中可以使用两个通配符:%和_,其中%可以代替多个字符,_可以代 替一个字符。例如,要查询包含字符串“AR”的员工姓名,构造的SELECT语句为: SQL> SELECT ename FROM emp WHERE ename LIKE '%AR%'; 又如要查询这样的员工,姓名中第一个字符是任意字符,第二个是“A”,然后是若干任意 字符,这时构造的SELECT语句为: SQL> SELECT ename FROM emp WHERE ename LIKE '_A%'; 注意,%用来代替多个连续的字符,包括空字符串,而_只能用来代替一个字符,不包括空 6 第一部分 Oracle使用基础字符。 IN运算符用来与一个集合中的元素进行比较。SELECT语句将指定的表达式与集合中的元 素一一比较,只要与其中一个相等,则条件成立。如果没有任何一个元素与表达式的值相等, 则条件不成立。例如,下面的SELECT语句用于查询其姓名在指定集合之中的员工: SQL> SELECT ename FROM emp WHERE ename IN ('SMITH','FORD','HELLO'); BETWEEN运算符用于将表达式的值与两个指定数据进行比较,如果表达式的值在这两个 数据之间,则条件成立。这两个数据和表达式必须能够比较大小,而且后一个数据必须大于前 一个数据。例如,下面的SELECT语句用于查询工资在1000到2000之间的员工: SQL> SELECT ename FROM emp WHERE sal BETWEEN 1000 AND 2000; 如果用包含“>”等运算符的表达式改写上述SQL语句,则对应的SELECT语句为: SQL> SELECT ename FROM emp WHERE sal>=1000 AND sal<=2000; 在复杂的查询语句中,可能需要多个条件,这些 条件通过AND或OR运算符连接。多个条件表达式连 接起来以后,就构成一个逻辑表达式。逻辑表达式的 结果要么为真,要么为假,它是与两个关系表达式的 值和所使用的连接运算有关的。假设X和Y是两个关 系表达式,表1.3列出了两个关系表达式的运算规则。 例如,要查询在部门10工作,且工资在1000和 2000之间的员工姓名,相应的SELECT语句为: SQL> SELECT ename FROM emp WHERE deptno=10 AND sal BETWEEN 1000 AND 2000; NOT运算符的作用是对关系表达式的值取反。它的用法是在关系表达式之前加上NOT运算 符。例如,要查询工资不大于1000的员工姓名,相应的SELECT语句为: SQL> SELECT ename FROM emp WHERE NOT sal<1000; 这条语句等价于: SQL> SELECT ename FROM emp WHERE sal>=1000; 在默认情况下,NOT运算符只对最近的一个关系表达式取反,如果要对已经通过AND或 OR连接的多个关系表达式同时取反,则要用一对圆括号将多个关系表达式限定。例如,要对 下列SELECT语句中的两个条件同时取反: SELECT ename FROM emp WHERE sal>1000 AND sal<2000; 对两个条件同时取反以后的SELECT语句为: SELECT ename FROM emp WHERE NOT (sal>1000 AND sal<2000); 这条语句等价于: SELECT ename FROM emp WHERE sal<=1000 OR sal>=2000; 第1章 SQL语言基础  7 表1.3 关系表达式的运算规则 X Y X AND Y X OR Y 真真 真 真 真假 假 真 假真 假 真 假假 假 假在WHERE子句中构造条件时,还要注意空值的运算。空值与任何数据进行赋值运算、四 则运算以及关系运算时,结果都为空值。例如,下列的SELECT语句本意是要查询姓名不为空 的所有员工的工资,查询的结果应该是所有员工的工资,但是结果却为空。 SQL> SELECT sal FROM emp WHERE ename!=NULL; 原因是ename列与空值进行了关系运算,结果为空,于是整个条件表达式的结果为假。判 断某个表达式是否为空值的运算符是“IS NULL”,判断是否不为空值的运算符是“IS NOT NULL”。例如,用运算符“IS NOT NULL”重新构造上述SELECT语句,将得到我们希望的结 果。这个SELECT语句为: SQL> SELECT sal FROM emp WHERE ename IS NOT NULL; 1.1.3 查询语句中的单行函数 在SELECT语句中不仅可以对数据进行前面讲述的各种运算,还可以把数据作为函数的参 数,进行其他的计算。所谓单行函数,就是分别作用于查询结果中的每一行,对于每一行,分 别得到一个计算结果。这些函数都是Oracle提供的系统函数,用户可以在SELECT语句或其他 SQL语句中直接使用它们。Oracle提供的函数很多,这里仅仅把最常用的函数进行分类介绍。 在函数中处理的数据可以是某个列的列值,也可以是某个表达式,在举例时不再单独说明。 1. 字符串处理函数 顾名思义,这类函数以字符串为处理对象,处理的结果是另一字符串或者一个数字。 (1)CONCAT函数 这个函数的参数是两个字符串,计算的结果是将两个字符串连接在一起,生成一个新的字 符串。例如,concat(‘Hello,’,‘World’)的结果为Hello,World。 (2)CHR与ASCII函数 CHR函数的参数是一个正整数,它将这个正整数作为ASCII码,返回对应的字母。例如chr (65)的结果为A。ASCII函数的作用正好相反,它以一个字符为参数,返回这个字符对应的 ASCII码。例如ASCII(‘A’)的结果为65。 (3)INSTR函数 这个函数在一个字符串中查找另一个字符串,如果找到,则返回出现的位置,否则返回0, 位置的编号从1开始。这个函数的语法格式为: instr(字符串,子字符串,start, occurrence) 其中前两个参数是必需的,这个函数在第一个参数中查找第二个参数,得到的结果是第二 个参数在第一个参数中出现的位置,如果没有找到,则返回0。后两个参数是可选的,参数start 指定从第一个参数的什么位置开始查找,默认从1开始,即第一个字符。参数occurrence指定查 找子字符串的第几次出现。例如,要在部门名称中查找字符串‘TI’,相应的SELECT语句为: SQL> SELECT dname,instr(dname,'TI') AS location FROM dept; DNAME LOCATION ---------- -- ACCOUNTING 7 8 第一部分 Oracle使用基础RESEARCH 0 SALES 0 OPERATIONS 6 (4)LENGTH函数 这个函数的作用是求得一个字符串的长度。例如length(‘Hello’)的结果为5。 (5)LOWER和UPPER函数。 这两个函数的作用是进行字符串的大小写转换,它们的参数都是一个字符串。其中 LOWER函数将字符串中的字母转换为对应的小写字母,UPPER函数将字符串中的字母转换为 对应的大写字母。例如,函数lower(‘Hello’)的结果为hello,而函数upper(‘Hello’)的结果 为HELLO。 (6)LPAD与RPAD函数 这两个函数的作用是在字符串中填充指定的字符,使字符串达到指定的长度。LPAD函数 从左边填充,RPAD函数从右边填充,处理的结果是得到一个新的字符串。这两个函数的语法 格式为: LPAD(字符串,长度,填充字符) RPAD(字符串,长度,填充字符) 这两个函数在字符串中填充指定的字符,使其达到指定的长度,默认是填充空格。如果指 定的长度比字符串本来的长度小,则将字符串截断,只保留新的长度。例如,对部门名称分 别进行左填充和右填充,使其长度为10,填充的字符分别是“*”和“#”,对应的SELECT语 句为: SQL> SELECT lpad(dname,12,'*') AS 左填充,rpad(dname,12,'#') AS 右填充 FROM dept; 这条语句执行的结果为: 左填充 右填充 ------------ ------------ **ACCOUNTING ACCOUNTING## ****RESEARCH RESEARCH#### *******SALES SALES####### **OPERATIONS OPERATIONS## (7)LTRIM、RTRIM函数和TRIM函数 这三个函数的作用是去掉字符串左边或右边连续的空格,并得到一个新的字符串。例如, 函数ltrim(‘Hello’)的结果为Hello,rtrim(‘Hello’)的结果为Hello。 TRIM函数的作用是同时去掉字符串左边和右边的连续空格,它相当于对字符串先执行 LTRIM函数,再执行RTRIM函数,或者先执行RTRIM函数,再执行LTRIM函数。 (8)REPLACE函数 这个函数的作用是在一个字符串中查找另一个字符串,并将找到的字符串用第三个字符串 代替。这个函数的语法格式为: replace(字符串,子字符串,替换字符串) 如果在字符串没有找到子字符串,则不做任何处理,如果找到,则用替换字符串代替。如 第1章 SQL语言基础  9果没有指定替换字符串,则将找到的子字符串从原字符串中删除。例如,假设要在部门名称中 查找字符串TI,并将其替换为Hello,为了进行对比,我们将替换前后的字符串都显示出来。对 应的SELECT语句为: SQL> SELECT dname,replace(dname,'TI','Hello') AS new_str FROM dept; 字符串替换的结果为: DNAME NEW_STR ---------- -------------- ACCOUNTING ACCOUNHelloNG RESEARCH RESEARCH SALES SALES OPERATIONS OPERAHelloONS (9)SUBSTR函数 这个函数的作用是根据指定的开始位置和长度,返回一个字符串的子字符串。它的语法格 式为: substr(字符串,开始位置,长度) 位置编号从1开始。开始位置可以是正整数或负整数。如果是负整数,则从字符串的右边 开始数。长度是可选的,如果缺省,则返回从开始位置到字符串末尾的所有字符。例如: substr('Hello',2,3)的结果为ell substr('Hello',-3,2)的结果为ll substr('Hello',-3)的结果为ello 2. 数学函数 数学函数的处理对象是数字型数据,处理的结果一般也是数字型数据。 (1) ABS函数 这个函数的作用是求得一个数字的绝对值。 (2)CEIL和FLOOR函数 这两个函数都以一个数字为参数,CEIL函数返回大于或等于这个数字的最小整数。 FLOOR函数返回小于或等于这个数字的最大整数。例如: ceil(-15.6)的结果是-15,floor(-15.6)的结果是-16。 ceil(15.6)的结果是16,floor(15.6)的结果是15。 (3)MOD函数 这个函数有两个参数,结果是两个数相除所得的余数。 (4)ROUND和TRUNC函数 ROUND函数的作用是对数据进行四舍五入计算。这个函数的语法结构为: round(数据,舍入位置) 其中舍入位置可以是正整数,也可以是负整数。如果是正整数,则从小数点开始向右数, 一直到舍入位置,从这一位开始四舍五入。如果是负整数,则从小数点开始向左数,然后进行 四舍五入。例如: 10 第一部分 Oracle使用基础round(49.456,2)的结果为49.46 round(89.456,-1)的结果为90 round(89.456,-2)的结果为100 TRUNC函数的用法与ROUND函数类似,只不过它的功能是对数据进行截取运算,只舍不 入,也就是把一个数据的指定位之后的数字全部舍去。例如: trunc(49.456,2)的结果为49.45 trunc(49.456,-1)的结果为40 3. 日期型函数 日期型函数的处理对象是日期型数据,处理的结果一般也是日期型数据。 (1) ADD_MONTHS函数 这个函数在某个日期的基础上,加上一个指定的月数,返回一个新的日期。它的格式为: ADD_month(日期,月数) 这个函数是在指定的日期上加上若干个整月数,所以日期中的日应该保持不变。但是指定 的日期如果是当月的最后一天,函数会做相应的调整,以保证返回的日期也是当月的最后一天。 例如: ADD_months(to_date('12-05-10','dd-mm-yy'),6)的结果为"12-11月-10"; ADD_months(to_date('31-01-10','dd-mm-yy'),1)的结果为"28-2月 -10" ADD_months(to_date('28-02-10','dd-mm-yy'),1)的结果为"31-3月 -10" 在ADD_MONTHS函数中,不能直接使用类似“12-05-10”这样的日期型数据,因为 Oracle把这样的数据是当做字符串来处理的,所以首先要调用to_date函数将它转换为真正的日 期型数据。上述第一个例子容易理解。在第二个例子中,因为31日是2010年1月的最后一天, 所以加上一个月后,得到2月的最后一天,即28日。同理,在2010年2月28日上加上一个月后, 得到3月的最后一天,即3月31日。函数返回的结果可能因系统的不同而显示不同的格式,因为 不同系统采用的日期格式可能不同。 (2)LAST_DAY函数 这个函数返回指定日期所在月份的最后一天。例如: last_day(to_date('01-02-10','dd-mm-yy'))的结果为"28-2月-10"。 (3)MONTHS_BETWEEN函数 这个函数有两个参数,都是日期型数据,返回的结果是两个日期之间相差的月数。这个函 数的语法格式为:months_between(日期1,日期2)。例如: months_between (to_date('01-05-10','dd-mm-yy'), to_date ('01-05-09',' dd-mm-yy ')) 结果为12 months_between(to_date('01-05-10','dd-mm-yy'),TO_DATE('12-05-09','dd-mm-yy'))结 果为11.6451613 如果两个日期中的日相同,或者都是当月的最后一天,则返回结果是一个整数,否则将返 回一个小数。第一个参数如果比第二个参数小,则返回的结果为负数。 (4)NEXT_DAY函数 这个函数有两个参数,一个是日期,一个是与星期几对应的整数,返回的结果是这个日期 第1章 SQL语言基础  11之后最近的星期几所对应的日期。函数的调用格式为: next_day(日期,整数) 例如,假设2010年4月28日是星期三,则最近的星期五应该是2010年4月30日,最近的星期 二应该是2010年5月27日。注意星期的编号方法是:星期天为1,星期一为2,依此类推。例如: next_day(to_date('28-04-10','dd-mm-yy'),6)的结果为"30-4月-10" next_day(to_date('28-04-10','dd-mm-yy'),3)的结果为"04-5月-10" (5)ROUND函数 这个函数对指定的日期进行四舍五入处理。它有两个参数,一个是日期,一个是表示日期 某个组成部分的格式字符串。函数的语法格式为: round(日期,格式字符串) 其中格式字符串用于指定从日期的哪一部分开始四舍五入。Oracle支持的格式字符串包括 yy(或yyyy)、mm、dd、hh,分别表示从年、月、日、时位进行四舍五入。例如: round(to_date('28-08-10','dd-mm-yy'),'mm')的结果为"01-9月-10" round(to_date('12-08-10 13:50','dd-mm-yy hh24:mi'),'dd')结果为"13-8月-10" 在第一个例子中,指定从月开始四舍五入,由于28日已经过了该月的一半,故该位向前进 位。在第二个例子中,指定从日开始四舍五入,由于13时已经超过当天中午12时,故向前进位, 使12日变为13日。 (6)SYSDATE函数 这是个很常用的函数,它用来获得系统当前时间。在有些日志操作中,常常需要记录当前 时间,使用这个函数是很方便的。这个函数没有任何参数。这个函数返回的时间可以精确到秒, 但在显示时可能只显示到日,根据系统的不同显示格式有所变化。如果希望得到时、分、秒, 则需要通过to_char函数进行数据类型转换。例如: SQL> SELECT sysdate FROM dual; SYSDATE ---------- 26-4月-10 (7)TRUNC函数 这个函数的功能类似于ROUND函数,但是它不进行四舍五入,而是从指定位开始,截断 其后面的部分,它的语法格式与ROUND函数相同。例如: trunc(to_date('28-08-10 23:50','dd-mm-yy hh24:mi'),'dd')结果为"28-8月-10" trunc(to_date('28-08-10','dd-mm-yy'),'mm')结果为"01-8月-10" 4. 类型转换函数 在进行数据处理时,常常需要对数据进行类型转换。数据类型转换主要涉及字符型、数字 型和日期型数据之间的相互转换,涉及的函数有to_char、to_date和to_number。 (1)to_char to_char函数的作用是将一个日期型或者数字型数据转换为字符串。如果操作对象是日期型 数据,这个函数的语法格式为: 12 第一部分 Oracle使用基础TO_CHAR(日期,格式字符串) 其中格式字符串是由日期格式元素和隔离符号组成的字符串,用来规定转换的格式。例如, 下列语句将员工表中员工的受聘日期按照指定的格式转换为字符串: SELECT ename,to_char(hiredate,'yyyy-mm-dd day') FROM emp WHERE empno=7902; ENAME TO_CHAR(HI ---------- ---------- FORD 1981-12-03 星期四 又如,下列SELECT语句获取当前系统时间,然后按照指定的格式进行转换: SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM dual; TO_CHAR(SYSDATE,'YY ------------------- 2010-05-26 12:17:25 常用的日期格式元素如表1.4所示。 如果没有指定格式字符串,则按照当前系统默认的时间格式转换为字符串。 表1.4 常用的日期格式元素 格式元素 说 明 - / | , 日期中不同部分的分隔符。实际上除数字、字母以外的任何可显示字符都可以作为分隔符 yyyy yy 年的表示。其中yyyy表示4位数的年,yy表示两位数的年 month mon mm 月的表示。month表示月份的全称。mon表示月份名称的缩写。mm表示两位数字的月份 dd ddd 日的表示。dd表示两位数字的日,ddd表示在一年中的编号 d dy day 星期的表示。d表示数字编号,dy表示星期的缩写,day星期的全称 hh hh24 小时的表示,分别表示12小时制和24小时制 am pm 分别表示12小时制中的上午和下午 mi ss 分别表示两位数的分、秒 如果操作对象是数字型数据,to_char函数的语法格式为: to_char(数字,格式字符串) 其中格式字符串是由数字格式元素和小数点、分隔符组成的字符串,用来控制转换的格式。 这种转换主要用在财务报表中。例如: to_char(5432,'$9,999.99')的结果为'$5,432.00' 常用的数字格式元素如表1.5所示。 表1.5 常用的数字格式元素 格式元素 说 明 举 例 , 格式化数字中逗号的位置 1,999 . 小数点的位置 1000.00 9 代表一位十进制数字 1000 0 代表一位十进制数字,当对应位没有数字时,以0填充 01000 $ 在数字前面加上$符号 $1000 L 在数字前面加上本地货币符号 ¥1000 第1章 SQL语言基础  13(2)to_date函数 这个函数的作用是把一个字符串转换为一个日期型数据,它有两个参数,处理的结果是一 个日期型数据。这个函数的语法格式为: to_date(字符串,格式字符串) 其中格式字符串由表1.4中的格式元素组成,它的作用是把字符串中用分隔符分开的不同部 分解释成一个日期的不同部分。例如: to_date('12-09-2010','dd-mm-yyyy')结果为"12-9月-10" 通过格式字符串,把字符串中的12解释为日,09解释为月,2010解释为年,然后将这个字 符串转换为一个日期型数据。格式字符串中的每部分对字符串中的每部分是一一对应地进行解 释的,所以日期字符串中的每一部分对于格式字符串中的对应部分来说,必须是合法的数据。 例如: to_date('12-02-2010','dd-mon-yyyy') 将得到一个错误的结果,原因是格式字符串中的mon试图将字符串中的02解释为某个月份 的缩写,而02并不是某个月份缩写。还要注意的是,转换后得到的日期格式与函数指定的格式 并不一定相同,因为格式字符串是用来解释字符串中的不同部分的,而日期的显示格式依赖于 当前系统的日期格式。 (3)to_number 这个函数的作用是把一个字符串转换为数字,它有两个参数,处理的结果是一个数字型数 据。这个函数的语法格式为: to_number(字符串,格式字符串) 其中格式字符串包含表1.5中所介绍的格式元素,它把字符串中的$以及用逗号、小数点分 开的不同部分分别进行解释,它的分隔方式与字符串不同部分的分隔方式应该是一致的。例如: to_number('$1,000.50','$999,999.99')的结果为1000.5 to_number('$1000.50','$999,999.99')的结果将出错 第二个例子出错的原因是字符串中的数字字符并没有每三位逗号隔开,而格式字符串却要 按这种方式解释它。另外,格式字符串中的9或0的位数不应少于字符串中数字字符的位数。 1.1.4 分组函数与分组统计 1. 分组函数 分组函数又称为聚集函数,是一种多行函数。之所以称为多行函数,是与单行函数对应的, 因为这种函数对多行数据一起进行计算,只返回一个结果,而不是每行都返回一个结果。 聚集函数主要用来进行数据的统计,常用的聚集函数有以下几个: ¥ AVG 求平均值 ¥ MIN 求最小值 ¥ MAX 求最大值 ¥ SUM 求和 14 第一部分 Oracle使用基础¥ COUNT 计数 (1)AVG函数 AVG函数用来求指定列上的平均值,它将自动忽略列上的空值。如果要去掉重复值的计算, 可在列名前加上DISTINCE选项。例如,要求部门30的员工的平均工资,构造的SELECT语句 如下: SQL> SELECT avg(sal),avg(distinct sal) FROM emp WHERE deptno=30; 为了观察重复值对这个函数的影响,在SELECT语句中进行了两种形式的函数调用,其中 第二次调用去掉了重复值,对重复值只计算一次。这条语句的执行结果为: AVG(SAL) AVG(DISTINCTSAL) ---------- ---------------- 1566.66667 1630 (2)MIN与MAX函数 MIN函数的作用是求指定列的最小值,MAX函数的作用是求指定列的最大值。这两个函数 都自动忽略空行。例如,要求部门30的员工的最低工资和最高工资,构造的SELECT语句如下: SQL> SELECT min(distinct sal),max(sal) FROM emp WHERE deptno=30; 这条SELECT语句的执行结果为: MIN(DISTINCTSAL) MAX(SAL) ---------------- ----------- 950 2850 (3)COUNT函数 COUNT函数用来计算数据的行数。在默认情况下,这个函数不计算空行。如果要计算空行, 可以用“*”代替列名。如果要去掉重复值的计算,可在列名前加上DISTINCE选项,这样如果 遇到重复值,只计算一次。例如,要计算公司中领取工资的人数,构造的SELECT语句如下: SQL> SELECT count(sal),count(distinct sal) FROM emp; 为了观察DISTINCE选项的作用,在SELECT语句中进行了两次函数调用。如果有两个员工 的工资相同,只按一个人计算。这条语句的执行结果为: COUNT(SAL) COUNT(DISTINCTSAL) ---------- ------------------ 12 11 (4)SUM函数 SUM函数的作用是对指定列求和,它将自动忽略空值。如果要去掉重复值的计算,可在列 名前加上DISTINCE选项。例如,要求部门30的员工工资总和,构造的SELECT语句如下所示: SQL> SELECT sum(sal) FROM emp WHERE deptno=30; SUM(SAL) ---------- 9400 现在,为了说明这些函数的用法,把它们综合起来,构造一个SELECT语句,求部门30的 第1章 SQL语言基础  15员工的平均工资、最高工资、最低工资、工资总和以及总人数。这条SELECT语句如下所示: SQL> SELECT avg(sal) AS 平均工资, min(sal) AS 最低工资, max(sal) AS 最高工资,sum(sal) AS 工资总和 FROM emp WHERE deptno=30; 这条SELECT语句的执行结果为: 平均工资 最低工资 最高工资 工资总和 ---------- ---------- ---------- ---------- 1566.66667 950 2850 9400 2. 分组统计 在上面最后一个例子中。我们对部门30的员工的工资进行了统计,用这种方法也可以统计 其他部门的数据。但是每进行一次统计,都需要单独构造一条SELECT语句,如果表中的部门 很多,或者部门数很难确定,用这种方法很难满足用户的查询要求。解决这个问题的一个办法 是使用GROUP子句。 分组函数最常见的用法是与GROUP子句一起使用,用来对表中的数据进行分组统计。为 了统计表中各个部门员工的工资,只要一条语句就可以完成。GROUP子句的语法格式为: GROUP BY 列1,列2... GROUP子句根据指定的列对数据进行分组统计。首先根据第一个列进行分组统计,第一 个列相同时再进一步根据第二个列进行分组统计。例如,要对公司各部门的员工工资进行统计, 包括各部门的平均工资、最高工资、最低工资、工资总和和总人数,构造的SELECT语句如下 所示: SQL> SELECT deptno AS 部门号, avg(sal) AS 平均工资, min(sal) AS 最低工资, max(sal) AS 最高工资,sum(sal) AS 工资总和 FROM emp GROUP BY deptno; 这样我们就可以用一条语句完成所有部门的统计。这条SELECT语句的执行结果为: 部门号 平均工资 最低工资 最高工资 工资总和 ------ ---------- -------- ---------- ---------- 10 2916.66667 1300 5000 8750 20 2258.33333 800 3000 6775 30 1566.66667 950 2850 9400 与GROUP子句一起使用的还有一个子句,即HAVING子句。这个子句是可选的,它不能单 独使用,只能配合GROUP子句使用,作用是对GROUP子句设置条件,对统计后的结果进行限 制。例如,对于上述统计,我们只希望显示最低工资在900元以上,并且工资总和在7000元以 上的部门的统计信息,相应的SELECT语句为: SQL> SELECT deptno AS 部门号, avg(sal) AS 平均工资, min(sal) AS 最低工资, max(sal) AS 最高工资,sum(sal) AS 工资总和 FROM emp GROUP BY deptno 16 第一部分 Oracle使用基础HAVING min(sal)>900 AND sum(sal)>7000 这样,部门20因为统计信息不满足设置的条件,就不被显示。这条SELECT语句的执行结 果为: 部门号 平均工资 最低工资 最高工资 工资总和 ---------- ---------- ---------- ---------- ---------- 10 2916.66667 1300 5000 8750 30 1566.66667 950 2850 9400 HAVING子句中的关系表达式必须使用分组函数,可以是在SELECT语句中已经出现的分 组函数,也可以是没有出现的函数。虽然HAVING子句和WHERE子句都是用来设置条件的, 但是WHERE子句设置的条件是在查询时起作用的,它决定查询什么样的数据,如果要进行统 计,这样的条件是在统计之前就已经起作用了。而HAVING子句设置的条件只有在进行统计后 才起作用,它决定了对于统计产生的数据,哪些需要显示给用户。 1.1.5 数据的排序 SELECT语句可以使用的最后一个子句是ORDER子句。以前在查询数据时,数据显示的顺 序是不可预知的。如果要对数据进行某种方式的排序,就要借助于ORDER子句。ORDER子句 的语法格式为: ORDER BY 列1 排序方式,列2排序方式... ORDER子句对查询到的数据按照指定列的大小排序。如果指定了多个排序列,则首先按 照第一个排序列排序,如果这个列的值相同,则再按照第二个排序列继续排序。排序方式包括 ASC和DESC,分别表示升序排序和降序排序,二者可选其一,默认的排序方式是升序排序。 如果指定了多个排序列,可以为每个排序列单独指定排序方式。 例如,要对公司各部门的工资统计情况进行排序,要求是按照工资总和从大到小排序,如 果工资总和相同,再按照部门号从小到大排序。相应的SELECT语句为: SQL> SELECT deptno AS 部门号, avg(sal) AS 平均工资, min(sal) AS 最低工资, max(sal) AS 最高工资,sum(sal) AS 工资总和 FROM emp GROUP BY deptno ORDER BY sum(sal) desc, deptno asc 这条SELECT语句的执行结果为: 部门号 平均工资 最低工资 最高工资 工资总和 ---------- ---------- ---------- ---------- ---------- 30 1566.66667 950 2850 9400 10 2916.66667 1300 5000 8750 20 2258.33333 800 3000 6775 ORDER子句中的排序列可以是列名,可以是列的别名,也可以是其他的表达式,还可以 是它在SELECT语句中的排列序号。例如上述SELECT语句中的第一个排序列就是一个函数, 这个函数可以用前面定义的别名“工资总和”来代替,也可以用它的排列序号5来代替。上面 第1章 SQL语言基础  17的ORDER子句可以改为等价的形式: ORDER BY 5 desc, 部门号 asc 如果在SELECT语句中用到了所有的子句,那么将构成一条复杂的SQL语句。这些子句的 使用顺序是:WHERE子句、GROUP子句、HAVING子句、ORDER子句。现在我们再来看一个 综合的例子,在这个例子中用到了SELECT语句的所有子句。假设要求按照部门号对员工的工 资进行统计,参加统计的员工工资必须大于1000元,将统计结果中凡满足最低工资在900元以 上,并且工资总和在7000元以上的部门统计信息显示出来,显示时按照工资总和从大到小排序, 如果工资总和相同,再按照部门号从小到大排序。相应的SELECT语句为: SQL> SELECT deptno AS 部门号, avg(sal) AS 平均工资, min(sal) AS 最低工资, max(sal) AS 最高工资,sum(sal) AS 工资总和 FROM emp WHERE sal>1000 GROUP BY deptno HAVING min(sal)>900 AND sum(sal)>7000 ORDER BY 5 desc, 部门号 asc 统计的结果如下所示。与以前的统计结果相比,这次的结果不同,原因是这次统计时设 置了WHERE子句中的条件,如果不满足这个条件,就不会被查询到,当然就没有机会参加统 计了。 部门号 平均工资 最低工资 最高工资 工资总和 ---------- ---------- ---------- ---------- ---------- 10 2916.66667 1300 5000 8750 30 1690 1250 2850 8450 1.1.6 多表查询 以前讲述的查询语句都只涉及一个表的数据。在很多情况下,需要查询的数据往往涉及多 个表,这时需要对多个表进行连接查询。例如,如果既要查询员工的信息,又要查询员工所在 部门的信息,这就涉及emp和dept两个表。 表间的连接关系有相等连接、非相等连接、外连接和子连接等多种形式,其中最常用的连 接形式是相等连接。相等连接体现在WHERE子句中指定的条件上,在条件中要指定两个表通 过哪些列进行连接。一般情况下进行连接查询的两个表是通过主键和外键进行关联的,所以最 简单的条件是一个表的外键与另一个表的主键相等。例如,下面的SELECT语句从EMP表中查 询员工的姓名和工资,同时在dept表中查询员工所在部门的名称: SQL> SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno=dept.deptno; 其中DEPTNO是DEPT表中的主键,同时它又是emp表中的外键,在这个查询语句中连接 的条件是它们相等,条件emp.deptno=dept.deptno的意思是在emp表中查询每个员工所在的部门 号,然后根据部门号在dept表中查询对应的部门名称,凡是不满足这个条件的部门名称都将被 过滤掉。 如果一个列在两个表中同时存在,那么在SELECT语句中要用表名进行限定(表名.列名或 18 第一部分 Oracle使用基础者表的别名.列名),否则系统将无法确定是哪个表中的列。上面的SELECT语句可以改为下面 的等价的语句: SQL> SELECT ename,sal,dname FROM emp a,dept b WHERE a.deptno=b.deptno; 在这个SELECT语句中,为了书写方便,为表emp和dept分别定义了别名a和b,这样在其他 子句中就可以使用这样的别名了。在构造查询语句时,首先要仔细分析这个查询要涉及哪些表, 以及这些表通过哪些列进行连接,然后在SELECT语句中指定所有涉及的表,在WHERE子句中 指定连接条件。下面我们再考察一个涉及三个表的查询。除了emp和dept两个表以外,第三个 表salgrade也是Oracle提供的模板表,这个表记录了工资级别的规定,它的结构为: SQL> desc salgrade 名称 是否为空? 类型 -------------------------------- -------- ---------- GRADE NUMBER LOSAL NUMBER HISAL NUMBER 这三个列的意义分别是级别编号、工资下限和工资上限。 现在要查询部门10和20中每个员工的姓名、工资、工资级别以及所在部门的名称,相应的 SELECT语句为: SQL> SELECT ename AS 姓名,sal AS 工资,grade AS 工资级别,dname AS 部门名称 FROM emp a,dept b,salgrade c WHERE a.deptno=b.deptno AND (a.deptno=10 or a.deptno=20) AND (sal>=c.losal and sal<=c.hisal) 这条查询语句的执行结果为: 姓名 工资 工资级别 部门名称 SMITH 800 1 RESEARCH MILLER 1300 2 ACCOUNTING JONES 2975 4 RESEARCH CLARK 2450 4 ACCOUNTING KING 5000 5 ACCOUNTING 外连接是一种特殊的连接方式。假设有两个表A和B,用相等连接查询可以返回表A中的所 有行,而表B中的部分行因为不满足相等条件,所以是不会被查询到的,但是利用外连接可以 返回表B中的所有行。对于表A和B来说,外连接的条件表达式的格式为: WHERE A.列名(+)=B.列名 如果要显示表B中所有行,包括使用相等连接无法显示的行,则在表A的列名之后指定外 连接的标志“(+)”。例如,对于表emp和dept来说,利用相等连接可以查询所有员工的信息以 及员工所在部门的信息。如果某个部门没有员工,那么该部门的信息是查询不到的,因为这样 的部门不满足相等条件。但是如果使用外连接,可以保证它们同样被查询出来。完成这个查询 功能的SELECT语句为: SQL> SELECT ename,dname FROM emp a,dept b WHERE a.deptno(+)=b.deptno; 第1章 SQL语言基础  19这条SELECT语句的执行结果为: ENAME DNAME ...... MARTIN SALES WARD SALES OPERATIONS 其中最后一个部门OPERATIONS为空,在表emp中没有与它的编号相等的员工,在相等连 接查询中它是不会被显示出来的,但是利用外连接,可以保证这样的数据也被查询出来。 自连接是一种特殊的相等连接。相等连接一般涉及多个不同的表,自连接也涉及多个表, 但是它们是同一个表。例如,在表emp中,每个员工都有一个顶头上司的编号,而这个上司同 时也是该公司的员工。如果要查询每个员工的上司姓名,首先要确定上司的编号,然后根据这 个编号再查询emp表,利用相等连接确定上司的姓名,这就相当于两个表的连接。能够完成这 个查询的SELECT语句为: SQL> SELECT a.ename,b.ename AS manager FROM emp a,emp b WHERE a.mgr=b.empno; 这条语句的执行结果为: ENAME MANAGER SMITH FORD ALLEN BLAKE ... ... 由于要把同一个表看成两个不同的表进行连接,所以在FROM子句中要为emp表定义两个 不同的别名,而SELECT之后的两个ENAME列就分别是这两个表中的列,因此要用别名进行限 定。在emp表中,员工KING是最高领导,他没有上司,所以在上述查询结果中并没有显示。为 了在查询中将所有员工姓名都列出来,可以在上述查询的基础上再使用外连接。用于完成这个 查询的SELECT语句为: SQL> SELECT a.ename,b.ename AS manager FROM emp a,emp b WHERE a.mgr=b.empno(+); 1.1.7 子查询 子查询就是嵌套在另一个SELECT语句中的查询。在SELECT语句中,WHERE子句或者 HAVING子句中的条件往往不能用一个确定的表达式来确定,而要依赖于另一个查询,这个被 嵌套使用的查询就是子查询,它在形式上是被一对圆括号限定的SELECT语句。在子查询中还 可以再嵌套子查询。 例如,要查询所有在部门RESEARCH工作的员工姓名。如果使用常规的查询方法,要进行 两次查询,首先查询dept表,确定该部门的部门号,然后根据这个部门号在emp表中查询属于 这个部门的员工。也就是说,需要两条SELECT语句: 20 第一部分 Oracle使用基础SQL> SELECT deptno FROM dept WHERE dname='RESEARCH'; SQL> SELECT ename FROM emp WHERE deptno=20; (部门RESEARCH的部门号) 连接这两条SELECT语句的纽带是中间结果— 部门号(deptno)。要完成这样的查询,不 得不需要人工干预,在两条SELECT语句中传递参数。如果利用子查询,这个问题就迎刃而解。 能够完成这个查询功能的一条SELECT语句为: SQL> SELECT ename FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='RESEARCH'); 这种复杂的SELECT语句的执行过程为:首先执行子查询,将执行的结果返回给主查询, 然后再根据条件执行主查询。 子查询一般出现在SELECT语句的WHERE子句或HAVING子句中,作为条件表达式的一部 分。子查询的结果是返回一行或多行数据,可以被看做一个集合。条件表达式就是要将某个表 达式与这个集合中的元素进行某种比较运算,根据运算的结果是真或是假来决定是否执行上一 层查询。常用的运算符如表1.6所示。 表1.6 子查询中的运算符 运算符 用法 说 明 备注 EXISTS EXISTS S 如果集合S不为空,条件表达式的值为真,否则为假 IN 表达式IN S 如果表达式的值在集合S中,则条件表达式的值为真,否则为假 = 表达式=S 如果表达式的值与集合S中唯一一个元素相等,则条件表达式的 值为真。集合S必须确保最多只有一个元素 > < >= <= 与“=”相同 进行相应的关系运算 ANY 用在集合名之前 指定要与集合中的任一个元素进行比较 ALL 用在集合名之前 指定要与集合中的所有元素进行比较 其中EXISTS运算符测试子查询的返回结果,只要结果不为空,条件就为真,而主查询和 子查询之间可能没有直接关系。例如,在下面的查询语句中,因为子查询返回的结果为空,条 件为假,所以上主查询也返回空。 SQL> SELECT ename FROM emp WHERE exists(SELECT deptno FROM dept WHERE deptno=0 ); IN运算符将某个列的值与子查询的返回结果进行比较,只要与其中的一个结果相等,条件 即为真。例如,要查询所有出现在emp表中的部门名称,即至少有一名员工的部门,构造的 SELECT语句为: SQL> SELECT dname FROM dept WHERE deptno IN(SELECT distinct deptno FROM emp); “=”运算符号比较特殊,它将某个列的值与集合中的元素进行精确匹配。如果子查询只返回 单行结果,那么将这个列与这一行进行比较。如果子查询返回多行结果,那么必须用ANY或 ALL进行限定,否则将出错。ANY运算符的作用是,只要列值与返回结果中的任何一个相等, 条件即为真。ALL运算符的作用是,列值要与返回结果中的所有行都要进行比较。例如,要查询 所有在emp表中出现的部门名称,即至少有一名员工的部门,也可以使用下面的SELECT语句: 第1章 SQL语言基础  21 集合S为子 查询的返回 结果SQL> SELECT dname FROM dept WHERE deptno = any(SELECT distinct deptno FROM emp); 运算符>、<、>=和<=与=的用法相似,子查询可以返回单行结果,也可以返回多行结果。 如果是多行结果,必须用ANY或ALL进行限定。下面我们再考察几个例子,比较ANY和ALL之 间的区别。 如果要查询这样的员工姓名,他的工资高于部门30中的每个员工,相应的SELECT语句为: SQL> SELECT ename FROM emp WHERE sal>all(SELECT sal FROM emp WHERE deptno=30); 如果要查询这样的员工姓名,他的工资不低于部门30中的最低工资。也就是说,工资高于 部门30中其中任何一个员工即可。相应的SELECT语句为: SQL> SELECT ename FROM emp WHERE sal>any(SELECT sal FROM emp WHERE deptno=30); 在子查询中还可以使用分组函数。例如,要查询所有比公司全部员工平均工资高的员工姓 名,构造的SELECT语句为: SQL> SELECT ename FROM emp WHERE sal>(SELECT avg(sal) FROM emp); 如果要查询这样的部门名称,它的平均工资高于其他部门的平均工资,这样的查询需要使 用两次子查询。首先查询其他部门的平均工资,然后根据查询的结果查询其平均工资高于这个 结果的部门号,最后根据这个部门号查询它的部门名称。相应的SELECT语句如下所示: SELECT dname FROM dept WHERE deptno=(SELECT deptno FROM emp a GROUP BY deptno HAVING avg(sal)>all(SELECT avg(sal) FROM emp WHERE deptno!=a.deptno GROUP BY deptno)) 在这条SELECT语句中,最后一个子查询最先执行,用来求得其他部门的平均工资。然后 执行上一个子查询,返回平均工资高于其他部门平均工资的部门号。最后执行最上层的查询, 返回这个部门的名称。 1.2 DML语句 DML是“数据操纵语言”(Data Manipulation Language)的简写。如果说SELECT语句对 数据进行的是读操作,那么DML语句对数据进行的是写操作。DML语句的操作对象是表中的 行,这样的语句一次可以影响一行或多行数据。DML包括三种操作:插入(INSERT)、删除 (DELETE)、修改(UPDATE)。 1.2.1 INSERT语句 INSERT语句的作用是往表中插入一行,它的语法格式为: INSERT INTO 表(列1,列2...) VALUES(表达式1,表达式2...); 22 第一部分 Oracle使用基础在向表中插入一行时,INSERT语句将表达式的值作为对应列的值,列的排列顺序、数据 类型和数量应该与表达式一致,否则可能会出错。如果没有指定某个列,那么在插入数据时这 个列的值将为空。在表达式中,字符串类型数据的大小写是敏感的,日期型数据的格式在不同 系统中是有区别的。例如,要往dept表中插入一行,部门号为50,部门名称为NETWORK,部 门地址为BEIJING,相应的INSERT语句为: SQL> INSERT INTO dept(deptno,dname,loc) VALUES(50, 'NETWORK', 'BEIJING'); 在INSERT语句中如果指定了列名,那么它们的顺序可以随意,只要与VALUES子句中的表 达式一一对应即可。如果要为所有的列都提供数据,则可以省略列名,但是VALUES子句中表 达式的顺序、数据类型和数量必须与表中列的定义一致。例如,上面的INSERT语句为所有的 三个列都提供了数据,所以可以简写为: SQL> INSERT INTO dept VALUES(50, 'NETWORK', 'BEIJING'); 在INSERT语句中为各列指定数据时,可以指定一个常量,或者指定一个表达式,如函数、 算术运算表达式等。例如,当公司新来一名员工时,可以将当前时间作为它的受聘日期,作为 表emp中列hiredate的值: SQL> INSERT INTO emp(empno,ename,deptno,sal,hiredate) VALUES(9999,'Hello',30,1000,sysdate); 利用INSERT语句中还可以从另一个表中复制数据,这时要在INSERT语句中使用子查询, 对应的语法格式为: INSERT INTO 表1(列1,列2...) SELECT 列1,列2... FROM 表2 WHERE 条件表达式 这里的SELECT子句实际上是一个子查询。执行这样的语句时,首先执行SELECT子句, 将返回的查询结果作为指定列的值,插入到表1中。用这种方法可以一次向表中插入多行,但 是需要注意的是,表1的指定各列要与SELECT子句中的各列在排列顺序、数据类型和数量上保 持一致。例如,假设有一个表emp1,它的结构与emp相同,现在希望从表emp中将部门10和部 门20的员工数据复制到表emp1中,相应的SELECT语句为: SQL> INSERT INTO emp1(empno,ename,deptno,sal,hiredate) SELECT empno,ename,deptno,sal,hiredate FROM emp WHERE deptno=10 or deptno=20; 1.2.2 DELETE语句 DELETE语句用来从表中删除指定的行,它一次可以删除一行,也可以删除多行。 DELETE语句的语法格式为: DELETE FROM 表名 WHERE 条件 在默认情况下,DELETE语句可以不使用WHERE子句,这时将删除表中的所有行。例如, 下面的DELETE语句将删除表emp中的所有行: 第1章 SQL语言基础  23SQL> DELETE FROM emp; 如果希望只删除表中的一部分数据,需要通过WHERE指定条件。例如,要从表emp删除中 部门30的工资低于1000元的员工数据,相应的DELETE语句为: SQL> DELETE FROM emp WHERE deptno=30 AND sal<1000; 在DELETE语句的WHERE子句也可以使用子查询,子查询与SELECT语句中的子查询用法 相同。例如,要从表DEPT中删除这样的部门数据,它在表emp中没有所属的员工,即空部门, 相应的DELETE语句为: DELETE FROM dept WHERE deptno NOT in(SELECT distinct deptno FROM emp); 1.2.3 UPDATE语句 UPDATE语句的作用是对表中已经存在的数据进行修改。它可以一次修改一行,也可以修 改多行。这条语句的语法格式为: UPDATE 表名 SET 列1=表达式1,列2=表达式2... WHERE 条件 UPDATE语句通过SET子句为指定列指定新值,将列值修改为指定的表达式。在SET子句 中指定所有需要修改的列。在默认情况下,UPDATE语句不需要WHERE子句,这时UPDATE 语句将修改表中的所有行。例如,下面的UPDATE语句将把emp表中所有员工的工资增加10%, 奖金增加100元: SQL> UPDATE emp SET sal=sal*1.1,comm=nvl(comm,0)+100; 如果通过WHERE子句指定了条件,那么UPDATE语句只修改满足条件的行。例如,如果 要为部门10和20中工资高于2000元的员工增加工资和奖金,增加幅度与上一条UPDATE语句相 同。相应的UPDATE语句为: SQL> UPDATE emp SET sal=sal*1.1,comm=nvl(comm,0)+100; WHERE deptno=10 OR deptno=20 AND sal>2000; 在UPDATE语句的WHERE子句中,也可以使用子查询。这时的条件并不是一个确定的条 件,而是依赖于对另一个表的查询。例如,要对与员工BLAKE同在一个部门的员工增加工资 和奖金,增加幅度与上一条UPDATE语句相同。相应的UPDATE语句为: SQL> UPDATE emp SET sal=sal*1.1,comm=nvl(comm,0)+100 WHERE deptno=(SELECT deptno FROM emp WHERE ename=' BLAKE'); 1.3 事务控制语句 事务是对数据库操作的逻辑单位,在一个事务中可以包含一条或多条DML(数据操纵语言)、 24 第一部分 Oracle使用基础DDL(数据定义语言)和DCL(数据控制语言)语句,这些语句组成一个逻辑整体。事务的执 行只有两种结果:要么全部执行,把数据库带入一个新的状态;要么全部不执行,对数据库不 做任何修改。对事务的操作有两个:提交(COMMIT)和回滚(ROLLBACK)。提交事务时, 对数据库所做的修改便永久写入数据库。回滚事务时,对数据库所做的修改全部撤销,数据 库恢复到操作前的状态。事务可用于操作数据库的任何场合,包括应用程序、存储过程、触 发器等。 我们拿现实生活中的一个例子来说明事务的概念。在两个银行账号之间转账时,首先从第 一个账号中减去转账金额,然后在第二个账号中加上相等数量的金额,这两个操作必须作为一 个整体来完成,不允许只进行第一个操作而不进行第二个操作,也不允许只进行第二个操作而 不进行第一个操作。如果这两个操作都顺利完成,那么这个事务可以提交,这次转账成功。否 则如果有一个操作失败,那么这个事务必须回滚,这次转账失败,两个账号的状态都恢复到操 作以前的状态。在数据库系统中,类似这样可以看做一个整体的多个操作就是一个事务。 事务具有四个属性,这四个属性的英文单词首字母合在一起就是ACID。这四个属性是: ¥ 原子性(Atomicity):事务要么全部执行,要么全部不执行,不允许部分执行。 ¥ 一致性(Consistency):事务把数据库从一个一致状态带入另一个一致状态。 ¥ 独立性(Isolation):一个事务的执行不受其他事务的影响。 ¥ 持续性(Durability):一旦事务提交,就永久有效,不受关机等情况的影响。 一个事务中可以包含多条DML语句,或者包含一条DDL语句,或者包含一条DCL语句。事 务开始于第一条SQL语句,在下列之一情况下结束: ¥ 遇到COMMIT或ROLLBACK命令。 ¥ 遇到一条DDL或者DCL命令。 ¥ 系统发生错误、退出或者崩溃。 总之,事务是一系列可以把系统带入一个新的状态的操作,如果事务被提交,则数据库进 入一个新的状态,否则数据库恢复到事务以前的状态。在数据库中使用事务的好处是首先可以 确保数据的一致性,其次在对数据做永久修改之前可以预览以前的数据改变,还可以将逻辑上 相关的操作进行分组。 控制事务的方式有两种,一种是隐式控制,数据库管理系统根据实际情况决定提交事务还 是回滚事务;另一种方式是显式控制,在事务的最后放置一条COMMIT或ROLLBACK命令, 将事务提交或回滚。 如果是隐式控制,那么事务在遇到一条DDL命令,如CREATE,或者遇到一条DCL命令, 如GRANT,或者从SQL*Plus正常退出,即使没有发出COMMIT或ROLLBACK命令,这个事务 将被自动提交。如果从SQL*Plus非正常退出或发生系统崩溃,那么系统将自动回滚事务。 如果是显式控制,那么在事务的最后就要通过COMMIT命令提交事务,或者通过一条 ROLLBACK命令回滚事务。 如果事务被提交,那么对数据库所作的修改将写入数据库。如果回滚事务,一般情况下将 回滚到事务的开始,即对数据库不做任何修改。在Oracle中,允许部分回滚事务,即可以将事 务有选择地回滚到中间的某个点。部分回滚是通过设置保存点(SAVEPOINT)来实现的。在 第1章 SQL语言基础  25事务中可以通过SAVEPOINT命令设置若干个保存点,这样可以将事务有选择地回滚到某一个 保存点。图1.1表示对事务的提交、回滚和保存点操作。 图1.1 事务控制 在图1.1所示的事务中,有一条INSERT语句,一条UPDATE语句和一条DELETE语句,并 设置了两个保存点。如果在事务的最后执行了COMMIT命令,则这三条语句都将对数据库产生 影响。如果在事务的最后执行了ROLLBACK命令,则事务回退到事务的开始,这三条语句都 数据库不产生任何影响。如果执行了命令ROLLBACK TO B,则事务回退到保存点B,这时仅 有INSERT和UPDATE语句对数据库产生影响,DELETE语句的执行结果被撤销。如果继续执行 命令ROLLBACK TO A,则事务回退到保存点A,这时UPDATE和DELETE语句的执行结果被 撤销。如果继续执行COMMIT命令,则只有INSERT语句的执行结果被写入数据库。 用户访问数据库时,数据库中的数据是放在缓冲区中的,当前用户可以通过查询操作,浏 览对数据操作的结果。如果没有提交事务,其他用户是看不到事务的修改结果的。当一个用户 修改表中的数据时,将对被修改的数据加锁,其他用户无法在此期间对该行数据进行修改,直 到这个用户提交或回滚这个事务。 如果在事务的最后执行了COMMIT命令,则对数据的修改将被写入数据库,以前的数据将 永久丢失,无法恢复,其他用户都可以浏览修改后的结果,在数据上加的锁被释放,其他用户 可以对数据执行新的修改,在事务中设置的所有保存点将被删除。 下面的语句序列演示了在SQL*Plus中执行一条DML语句,然后执行COMMIT命令的情况, 并显示了执行的结果。 SQL> INSERT INTO dept VALUES(60,'HHHHH','HHHHHH'); 已创建1行。 SQL> COMMIT; 提交完成。 如果在事务的最后执行了ROLLBACK命令,那么所有未提交的修改将被丢弃,对数据所 做的修改将被取消,数据恢复到修改以前的状态,在行上加的锁被释放,其他用户可以对这样 的数据进行新的修改。 26 第一部分 Oracle使用基础 INSERT SAVEPOINT A UPDATE SAVEPOINT B DELETE COMMIT ROLLBACK ROLLBACK TO A ROLLBACK TO B下面的语句序列演示了在SQL*Plus中执行一条DML语句,然后执行ROLLBACK命令的情 况,并显示了执行的结果。 SQL> DELETE FROM dept WHERE deptno=60; 已删除1行。 SQL>ROLLBACK; 回退已完成。 如果在事务中设置了保存点,并且在事务的最后执行ROLLBACK命令回滚到某个保存点, 那么在此保存点之后的DML语句所做的修改将被丢弃,但是在此保存点之前的DML语句所做 的修改仍然没有写入数据库,还可以进行提交或回滚。 下面的语句序列是在SQL*Plus中执行的两条DML语句,以及在两条DML语句之间设置的 保存点,然后是回滚到这个保存点,最后提交这个事务的情况。 SQL> DELETE FROM dept WHERE deptno=60; 已删除1行。 SQL> SAVEPOINT a; 保存点已创建。 SQL> DELETE FROM dept WHERE deptno=40; 已删除1行。 SQL>ROLLBACK TO a; 回退已完成。 SQL> COMMIT; 提交完成。 1.4 DDL语句 DDL是“数据定义语言”(Data Defination Language)的缩写,它包含CREATE、ALTER、 DROP、RENAME、TRUNCATE等命令,用来对数据库对象进行创建、修改、删除、重命名等 操作。其中CREATE、ALTER和DROP命令的功能十分强大,几乎可以对所有的数据库对象进 行管理,例如表、视图、索引、存储程序等。在本节中仅介绍与表的操作有关的DDL命令,与 其他数据库对象有关的DDL命令在以后的章节中将陆续介绍。 1.4.1 表的创建 CREATE命令可用来创建表。创建表时要确定表的结构,即确定表中各列的名字和类型。 在关系数据库中,表被看做是一个关系,表中的每个列是关系中的属性,是一个不可再分割的 基本单位。表中的行对应关系中的一个元组。表的结构确定下来以后,就可以通过INSERT语 句向表中插入数据了。用来创建表的CREATE命令格式为: CREATE TABLE 表名( 第1章 SQL语言基础  27列1 数据类型, 列2 数据类型, ... ); 如果已经存在同名的表,则应该先删除原来的表,然后再创建。表名是代表这个数据库对 象的名称,对表名的要求是必须以字母开头,长度为1~30个字符,而且只能包含A~Z、a~z、 0~9、_、$和#等字符,不能使用Oracle的保留字,在同一个用户模式中不能拥有两个同名 的表。 表中的每个列都有一个名字,在同一个表中不能有同名的列。列的数据类型可以是系统预 定义的类型,也可以是用户自定义类型。表1.7列出了系统预定义的数据类型。 在创建表时,可以通过DEFAULT关键字为列指定一个默认值,这样当用INSERT语句插入 一行时,如果没有为该列指定值,就以默认值填充,而不是插入空值。 表1.7 列的数据类型 数据类型 描 述 VARCHAR2(n) 可变长度的字符串,最大长度可达4000KB CHAR(n) 固定长度的字符串,最大长度可达2000KB NUMBER 浮点类型的数据 NUMBER(m,n) 可表示整数和小数,m和n分别为精度和小数位数 DATE 日期型数据 LONG 可变长度的字符串,最大长度可达2GKB RAW或LONG RAW 存储二进制数据的可变长度字符串 LOB 大对象类型,存储大型的无结构的数据,如图形图像、文本等数据,最大可达4GKB 例如,创建一个学生表,表中包括学号、姓名、性别、出生日期、所在学校等列,可以用 下列的CREATE语句: SQL> CREATE TABLE student( sno number(8), sname char(8), birthday date, school varchar(40)); 如果要验证表的结构是否与期望的结果一致,可以在表创建之后通过DESC命令查看表的 结构。这个命令只能列出表中各列的列名、数据类型以及是否为空等属性。 在创建表时,还可以以另一个表为模板确定当前表的结构。一般情况下,可以从一个表复 制它的结构,从而快速创建一个表。复制表的结构是通过子查询来实现的,即在CREATE语句 中可以嵌套SELECT语句。这时的CREATE语句格式为: CREATE TABLE 表名 AS SELECT语句 CREATE语句将根据SELECT子句中指定的列,确定当前表的结构,然后将子查询返回的 数据插入到当前表中,这样在创建表的同时向表中插入了若干行。例如,现在要根据表emp的 结构创建表emp_1,仅复制表emp中的empno、deptno和sal三个列,同时复制部门30的数据。相 28 第一部分 Oracle使用基础应的CREATE语句为: SQL> CREATE TABLE emp_1 AS SELECT empno,deptno,sal FROM emp WHERE deptno=30; 一般情况下,在通过这种方式创建的表中,列名和列的定义与原来的表一致。如果希望在 创建一个新表时指定与原来的表不同的列名,可以在CREATE语句中的表名之后指定新的列名。 如果只希望拷贝表的结构,而不复制表中的数据,可以将SELECT子句中的条件指定为一个永 远为假的条件。例如,现在希望根据表emp创建表emp_2,为复制的三个列指定新的列名,并 且不复制表emp中的数据,相应的CREATE语句为: SQL> CREATE TABLE emp_2(empno_2,deptno_2,sal_2) AS SELECT empno,deptno,sal FROM emp WHERE 1<0; 实际上,创建表的语句是非常复杂的,在表上可以定义约束,可以指定存储参数等属性。 在这里,顺便向读者介绍两个概念,一个是模式(Schema),一个是数据字典(Data dictionary)。模式指的是一个用户所拥有的所有数据库对象的逻辑集合。在创建一个新用户时, 同时创建了一个同名的模式,这个用户创建的所有数据库对象都位于这个模式中。用户在自己 的模式中创建表,需要具有CREATE TABLE系统权限,如果需要在别人的模式中创建表,则需 要具有CREATE ANY TABLE权限。在访问其他用户的数据库对象时,要指定对方的模式名称, 例如,通过SCOTT.EMP引用SCOTT用户的emp表。 数据字典是一些视图,从这些视图中可以查看一些重要的系统系统数据,如数据库中的表、 索引、权限、表空间等信息。这些视图是在创建数据库时自动创建的,它们的内容也是由数据 库服务器自动维护的。用户可以查看以下几种形式的数据字典视图: ¥ 以USER_开始的视图; ¥ 以ALL_开始的视图; ¥ 以DBA_开始的视图。 从USER_视图中可以查看当前用户所创建的某类数据库对象,如在USER_TABLES中可以 查看当前用户所创建的任何一个表的信息。从ALL_视图中可以查看当前用户有权限访问的某 类数据库对象,如在ALL_TABLES中可以查看当前用户有权限的任何一个表的信息。在DBA_ 视图中可以查看当前数据库中所有的某类对象的信息,如在DBA_TABLES中可以查看当前数 据库中所有的表。DBA_视图只能由数据库管理员(一般是SYS用户)查看。 1.4.2 如何修改表的结构 表在创建以后,如果在使用的过程中发现表的结构不合理,可以通过执行ALTER命令修改 它的结构。修改表结构的操作包括增加列、修改某个列的定义、删除列、增加约束、修改约束、 表的重命名等。 ALTER命令可以使用若干个子句,通过这些子句可以完成修改表结构的操作。可以使用的 第1章 SQL语言基础  29子句包括ADD、DROP、MODIFY和RENAME等。 如果要在表中增加一个列,通过ADD子句指定一个列的定义,至少要包括列名和列的数据 类型。增加列的语法结构为: ALTER TABLE 表名 ADD(列1,数据类型, 列2,数据类型, ...); 例如,要在student表中增加两个列,一个是性别,另一个是家庭地址,对应的ALTER语 句为: SQL> ALTER TABLE student ADD(gender char(2) DEFAULT '男', address varchar2(50)); 需要注意的是,如果一个表中已经有数据,这时增加一个列时,不能将该列约束为“非空 (NOT NULL)”,因为我们不能一方面要求该列必须有数据,而另一方面又无法在增加列的同 时向该列插入数据。例如,要向dept表中增加一个非空的列,语句的执行将出错: ALTER TABLE dept ADD (alias char(20) NOT NULL ) 这条语句执行的结果为: ALTER TABLE dept * ERROR 位于第 1 行: ORA-01758: 要添加法定 (NOT NULL) 列,则表必须为空 也就是说,要往一个表中增加一个非空列时,这个表必须是空的。如果表中已经有数据, 单独使用“NOT NULL”约束是不行的。一种好的解决办法是为该列指定默认值,这样在增加 一个非空列的同时,为这个列填充了指定的默认值。 例如,上面的ALTER语句如果修改为下面的形式,就可以保证这个列能够顺利加入到表中: ALTER TABLE dept ADD (alias char(20) default 'NOT KNOWN' NOT NULL ); 利用ALTER语句还可以从表中删除一个列。用来完成这个操作的子句是DROP。用于删除 列的ALTER语句格式为: ALTER TABLE 表名 DROP COLUMN 列名 例如,要删除表student中的gender列,对应的ALTER语句为: ALTER TABLE student DROP COLUMN gender; 删除一个列时,这个列将从表的结构中消失,这个列的所有数据也将从表中被删除。原则 上可以删除任何列,但是一个列如果作为表的主键,而且另一个表已经通过外键在两个表之间 建立了关联关系,这样的列是不能被删除的。例如,如果要删除表dept中的列deptno,系统将 会发生错误: 30 第一部分 Oracle使用基础ALTER TABLE dept DROP COLUMN deptno * ERROR 位于第 1 行: ORA-12992: 无法删除父项关键字列 如果要修改表中一个列的定义,可以使用ALTER语句的MODIFY子句。通过MODIFY子句 可以修改列的长度、非空等属性。使用了MODIFY子句的ALTER语句格式为: ALTER TABLE 表名 MODIFY (列1,新数据类型 非空属性, 列2,新数据类型 非空属性, ...); 例如,要将表student中的gender列由原来的char(2)改为char(4),非空,将address列由 原来的varchar2(50)改为char(40),相应的ALTER语句为: SQL> ALTER TABLE student MODIFY (gender char(4) NOT NULL, address char(40)); 需要注意的是,如果表中目前没有数据,那么可以将一个列的长度增加或减小,也可以将 一个列指定为非空。如果表中已经有数据,那么只能增加列的长度,如果该列有空值,不能将 该列指定为非空。 通过ALTER语句,还可以为表进行重命名,也就是将表的名字改为另一个名字。为表重命 名的ALTER语句格式为: ALTER TABLE 表名 RENAME TO 新表名 例如,要将表student重命名为stu,相应的ALTER语句为: SQL> ALTER TABLE student RENAME TO stu; 1.4.3 其他DDL命令 除了CREATE和ALTER两条主要的命令外,DDL还包括DROP、RENAME和TRUNCATE等 几条命令。其中DROP命令的功能是删除一个对象,通过这条命令几乎可以删除任何类型的数 据库对象。用来删除表的DROP命令的格式为: DROP TABLE 表名 例如,要删除表stu,相应的DROP语句为: SQL> DROP TABLE stu; 数据库对象删除后,它的有关信息就从相关的数据字典中删除。 RENAME命令的作用是对数据库对象重新命名。对表进行重新命名的命令格式为: RENAME 表名 TO 新表名; 例如,要将表STU重新命名为STU_1,相应的RENAME语句为: SQL> RENAME stu TO stu_1; 表被重新命名以后,它的信息就会在相关的数据字典中更新。 第1章 SQL语言基础  31TRUNCATE命令的作用是删除表中的数据。与DELETE语句不同的是,TRUNCATE命令 将删除表中的所有数据,不需要指定任何条件,而且数据被删除后无法再恢复。这条命令的语 法格式为: TRUNCATE TABLE 表名; 例如,要删除表student中的所有数据,可以执行下面的语句: SQL> TRUNCATE TABLE student; TRUNCATE命令作用的结果是删除所有的数据,而且不可恢复,所以这条命令要慎用。从 执行结果来看,一条TRUNCATE语句相当于下列两条语句的组合: DELETE FROM 表名; COMMIT; 1.5 约束 约束是加在表上的一种强制性的规则,是保证数据完整性的一种重要手段。当向表中插入 数据,或修改表中的数据时,必须满足约束所规定的条件。例如,员工的性别必须是“男”或 “女”,部门号只能是已经存在的部门的编号等。在设计表的结构时,应该充分考虑在表上需要 施加的约束。 保证数据完整性的方法大致有三种,即应用程序代码、触发器和约束。其中约束是一种更 为灵活的方式,它不仅维护更加方便,而且性能也比较高,是保证数据完整性的最佳选择。 约束可以在创建表时指定,也可以在表创建之后再指定。如果在表创建之后再指定约束, 可能会因为表中已经存在一些数据不满足这个条件而使得约束无法施加。 1.5.1 约束的类型 在表上可以施加以下五种约束: ¥ NOT NULL 非空约束 ¥ UNIQUE 唯一性约束 ¥ PRIMARY KEY 主键约束 ¥ FOREIFN KEY 外键约束 ¥ CHECK 检查约束 NOT NULL约束规定一个列上的值不能为空。当使用INSERT语句向表中插入一行数据, 或者使用UPDATE语句修改一行数据时,必须为该列指定值,不能使其为空。例如,在表dept 中,在列deptno上施加了NOT NULL约束,其余各列都没有,所以在向这个表中插入数据时, 必须为这个列指定数据。向这个表中插入数据的最简INSERT语句为: SQL> INSERT INTO dept(deptno) VALUES(70); UNIQUE约束规定一个列上的数据必须唯一,不能有重复值,但是允许为空值。例如在表 dept中,可以在部门名称dname列上施加UNIQUE约束,这样可以保证部门的名称不会重复。 当在某个列上指定了UNIQUE约束时,在该列上将自动生成一个唯一性索引。 32 第一部分 Oracle使用基础PRIMARY KEY约束是主键约束。主键用来唯一地标识表中的一行数据,它规定在主键列 上的数据不能重复,并且不能为空。每个设计合理的表都应该有一个主键。主键可以是一个列, 也可以是多个列的组合。如果在某个列上指定了主键约束,那么就不需要在该列上再指定NOT NULL约束和UNIQUE约束了。 在一个表上只能创建一个主键。当创建主键时,在主键列上将自动建立一个唯一性索引, 索引的名字与约束的名字相同。 FOREIFN KEY为外键约束。外键用来与另一个表建立关联关系。两个表之间的关联关系 是通过主键和外键来维持的。外键规定该列中的数据必须是另一个与之关联的表中的主键列中 的数据。外键可以是一个列,也可以是多个列的组合。在一个表中只能有一个主键,但是可以 有多个外键。例如,在表dept中deptno列是主键列,在表emp中,empno列是主键列,deptno列 是外键列,这个外键规定deptno列的数据必须是表dept中deptno列中的数据。假设要往表emp中 插入一行数据,其中deptno列的值为100,而在表dept的deptno列中根本就不存在这样的数据, 所以这行数据就违反了外键约束。 检查约束是一个关系表达式,它规定了一个列必须满足的条件。例如员工的性别只能是 “男”或“女”,员工的工资必须在1000~8000等。当向表中插入一行,或者修改某一行时,都 要检查指定列的值是否满足这个条件,如果满足,这个操作才能成功执行。 1.5.2 如何在创建表时指定约束 约束可以在创建表的同时指定,也可以在表创建之后再指定。如果与表同时创建,那么在 创建表的CREATE语句中通过CONSTRAINT关键字指定约束的名称和约束类型。同时创建表和 约束的CREATE语句格式为: CREATE TABLE 表名( 列1 数据类型 CONSTRAINT 约束名1 约束类型, 列2 数据类型 CONSTRAINT 约束名2 约束类型, ... ); 其中约束名是为约束指定的唯一的名称。约束名可以由用户自己指定,也可以自动产生。 如果省略关键字CONSTRAINT和约束名称,那么约束名称将自动产生。如果约束名称是自动 产生的,那么根据这样的名称无法判断约束所在的表以及约束类型。如果用户自己指定约束名 称,则可以在名称中包含表名、约束类型等有用信息。 如果在一个列的定义之后指定该列上的约束时,这种定义约束的方法称为列级约束。例如, 下面的CREATE语句用来创建一个表,名为student,在各个列上都指定了约束。 SQL> CREATE TABLE student( sno number(8) PRIMARY KEY, sname char(8) NOT NULL, gender char(2) CHECK(gender in ('男','女')), birthday date, school varchar(40)); 第1章 SQL语言基础  33在创建表student时指定了三个约束,第一个约束指定学号sno列为主键,第二个约束指定 姓名sname列不为空,第三个约束指定性别gender列的值只能是“男”或“女”,这些约束都是 列级约束。在创建表时没有为这几个约束指定名字,系统将自动为它们指定各自的名字。下面 的CREATE语句在创建表时指定约束,并为每个约束指定了名字。 SQL> CREATE TABLE student( sno number(8) CONSTRAINT pk_sno PRIMARY KEY, sname char(8) CONSTRAINT nn_sname NOT NULL, gender char(2) CONSTRAINT gen_check CHECK(gender in ('男','女')), birthday date, school varchar(40)); 约束可以在每个列的定义之后分别指定,也可以在所有列的定义之后一起指定。如果一个 约束在表定义的最后才指定,这样的约束定义方法称为表级约束。如果一个约束涉及多个列的 组合,那么就不能在每个列之后指定约束,而只能定义为表级约束。例如,若表student的主键 列不是sno列,而是sno列和sname列的组合,这样的约束就不能在sno列或sname列之后指定, 而只能在所有列的定义之后再指定。定义表级约束的CREATE语句格式为: CREATE TABLE 表名( 列1 数据类型, 列2 数据类型, ... CONSTRAINT 约束名1 约束类型(列名), CONSTRAINT 约束名2 约束类型(列名), ...); 在五种约束中,NOT NULL约束只能以列级约束的形式定义,其余四种既可以以列级约束 的形式定义,也可以以表级约束的形式定义。因为表级约束是在所有列之后定义的,而不是在 某个具体的列之后定义,所以在表级约束中要指定相关的列名。 例如,上面的创建表的CREATE语句也可以改为下面的形式: CREATE TABLE student( sno number(8) , sname char(8) CONSTRAINT nn_sname NOT NULL, gender char(2), birthday date, school varchar(40), CONSTRAINT pk_sno PRIMARY KEY(sno), CONSTRAINT gen_check CHECK(gender in ('男','女')) ); 外键约束的定义形式比较复杂,因为外键要与另一个表的主键进行关联,所以不仅要指定 约束的类型和有关的列,还要指定与哪个表的哪个列进行关联。 如果在列级定义外键约束,定义的格式为: CONSTRAINT 约束名 REFERENCES 表名(列名) 其中约束名是为这个外键约束起的名字。FOREIGN KEY为约束类型,即外键约束。 34 第一部分 Oracle使用基础REFERENCES关键字指定与哪个表的哪个列进行关联。例如,在表emp中,外键列为deptno, 它与表dept的deptno列进行关联。这个外键的定义语句为: CONSTRAINT fk_deptno REFERENCES dept(deptno) 这条代码放置在emp表的deptno列定义之后。如果要在表级定义外键约束,那么外键的定 义代码放置在所有列的定义之后,它的格式为: CONSTRAINT 约束名 FOREIGN KEY(外键列) REFERENCES 表名(列名) 例如,在表emp中的deptno列上施加的外键约束也可以通过下面的形式定义: CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno) 约束作为一种附加在表上的数据库对象,它的信息也被记录在数据字典中。与约束有关的 数据字典有两个,一个是user_constraints;另一个是user_cons_columns。其中在数据字典 user_constraints中记录当前用户所拥有的约束的信息,如约束名、约束类型、约束所在的表、 约束的状态等。如果是外键,还记录了与之关联的主键名称。例如,下面的SELECT语句用来 查询表student上的约束信息: SQL> SELECT constraint_name AS 名称, constraint_type AS 约束类型,status AS 状态 FROM user_constraints WHERE table_name='STUDENT'; 这条SELECT语句的执行结果为: 约束名 约束类型 状态 NN_SNAME C ENABLED GEN_CHECK C ENABLED PK_SNO P ENABLED 如果要进一步查询约束施加在哪个列上,就需要查询另一个数据字典了,它就是 USER_CONS_COLUMNS。例如,下面的SELECT语句查询表student中的各个约束是在哪个列 上定义的: SQL> SELECT constraint_name,table_name,column_name FROM user_cons_columns WHERE table_name='STUDENT'; 这条SELECT语句的执行结果为: CONSTRAINT_NAME TABLE_NAME COLUMN_NAME NN_SNAME STUDENT SNAME PK_SNO STUDENT SNO GEN_CHECK STUDENT GENDER 1.5.3 如何在创建表之后指定约束 约束既可以在创建表的同时定义,也可以在表创建之后再添加。不过在表创建之后再添加 约束可能会带来这样的问题,如果表中已经有数据,而这样的数据不满足将要添加的约束,那 么约束是无法添加的。例如要为表的某个列指定NOT NULL约束,但是这个表中这个列本来就 有很多空值,这种情况导致这个约束无法添加。因此最好的做法是在创建表之前充分考虑需要 第1章 SQL语言基础  35什么样的约束,在创建表的同时定义约束。 添加约束实际上也是对表结构的修改,因此添加约束也是通过执行ALTER语句完成的。因 为表的结构已经确定,所以我们无法采用列级约束的形式,在某个列名之后指定约束,而只能 采用表级约束的形式。添加约束的ALTER语句格式为: ALTER TABLE 表名 ADD(CONSTRAINT 约束名 约束类型(列名)) 其中CONSTRAINT关键字和约束名是可省的,如果没有为约束指定名称,那么名称将自 动产生。如果要添加多个约束,在ADD子句的括号中指定多个用逗号分隔的约束就可以了。现 在假设表student上没有任何约束,我们为这个表添加几个约束,具体的语句为: SQL> ALTER TABLE student ADD(CONSTRAINT pk_sno PRIMARY KEY(sno), CONSTRAINT gen_check CHECK(gender in('男','女'))); 除NOT NULL约束以外,其余四种约束都可以通过ADD子句添加。NOT NULL约束约束比 较特殊,只能通过ALTER命令的MODIFY子句来添加。添加NOT NULL约束的语法格式为: ALTER TABLE 表名 MODIFY (列名 CONSTRAINT 约束名 NOT NULL); 同样,如果要为多个列施加NOT NULL约束,那么在MODIFY子句之后的括号中指定多个 约束项,相互之间用逗号分隔开即可。假设表student中的sname列上没有NOT NULL约束,可 以通过下面的ALTER语句在该列上增加NOT NULL约束: SQL> ALTER TABLE student MODIFY (sname CONSTRAINT nn_sname NOT NULL); 1.5.4 约束的维护 如果希望去掉表上的某个约束,可以将其删除,也可以使其无效。约束是不能被修改的, 如果在表上已经建立了一个约束,现在希望把它改为另一类型的约束,或者希望把它施加在另 一个列上,只能先将这个约束删除,然后重新创建。 删除约束是通过执行ALTER命令的DROP子句来完成的。删除约束的ALTER命令的语法格 式为: ALTER TABLE 表名 DROP CONSTRAINT 约束名; 例如,要删除表student上的约束gen_check,可以执行下面的ALTER命令: ALTER TABLE student DROP CONSTRAINT gen_check; 如果要删除一个主键约束,首先要考虑这个主键列是否已经被另一个表的外键列关联,如 果没有关联,那么这个主键约束可以被直接删除,否则不能直接删除。例如,在表dept中,在 列deptno上定义了主键,在表emp中的deptno上定义外键约束,两个表之间通过主键和外间建 立了关联,那么主键约束是不能被直接删除的。要删除主键约束,必须使用CASCADE关键字, 连同与之关联的外键约束一起删除。删除主键的ALTER命令语法格式为: ALTER TABLE 表名 DROP CONSTRAINT 主键约束名 CASCADE; 例如,要删除表dept上的主键约束pk_dept,可以执行下面的ALTER语句: 36 第一部分 Oracle使用基础SQL> ALTER TABLE dept DROP CONSTRAINT pk_dept CASCADE; 约束被删除后,加在表上的限制条件被取消,在此之后写入的数据或修改的数据再也不用 考虑这些限制条件。例如,将表student上的约束gen_check删除后,员工的性别可以是‘男’ 和‘女’以外的其他数据。 在表中建立主键约束或UNIQUE约束时,在相关的列上将自动建立唯一性索引。当从表中 删除主键约束或UNIQUE约束时,与它们相关的索引也被一起删除。 如果一个表被删除了,那么依附于它的约束也就没有意义了,这个表上的约束也将被一起 删除。 如果希望一个约束暂时不起作用,可以使其无效。使约束无效的操作是通过ALTER命令的 DISABLE子句实现的。使约束无效的ALTER命令格式为: ALTER TABLE 表名 DISABLE CONSTRAINT 约束名; 例如,要使表student上的约束nn_sname无效,相应的语句为: SQL> ALTER TABLE student DISABLE CONSTRAINT nn_sname; 一个约束无效后,它的状态就变为DISABLED,这时通过查询数据字典,可以了解约束的 当前状态。例如,下面的SELECT语句查询表STUDENT上的约束及其状态: SQL> SELECT constraint_name AS 约束名, constraint_type AS 约束类型,status AS 状态 FROM user_constraints WHERE table_name='STUDENT'; 这条SELECT语句的执行结果为: 约束名 约束类型 状态 NN_SNAME C DISABLED GEN_CHECK C ENABLED PK_SNO P ENABLED 当一个约束无效后,这个约束并没有从数据库中被删除,只是暂时不起作用。这时要向表 中插入数据或修改已有数据时,就不必满足这个约束条件了。如果希望一个约束重新有效,可 以执行带ENABLE子句的ALTER命令。这时ALTER命令的格式为: ALTER TABLE 表名 ENABLE CONSTRAINT 约束名; 例如,要使刚才已经无效的约束nn_sname重新有效,可以执行下面的ALTER语句: ALTER TABLE student ENABLE CONSTRAINT nn_sname; 约束重新有效后,它在数据字典中的状态就变为ENABLED。需要注意的是,在一个约束 无效到重新有效的这段时间内,用户可能向表中插入了一些数据,或者修改了一些数据,这些 数据可能恰好不满足这个约束条件,这样在执行上面的ALTER语句时就会出错,约束将无法重 新有效,出错信息类似于这样:“无法验证 (SCOTT.NN_SNAME) - 违反检查约束条件”。 1.6 视图 视图是一种非常重要的数据库对象,它的形式类似于普通表,我们可以从视图中查询数据。 第1章 SQL语言基础  37实际上它是建立在表上的一种虚表,在视图中并不存储真正的数据,而是仅仅保存一条 SELECT语句,对视图的访问将被转化为对表的访问。视图所基于的表称为基表,而视图可以 认为是对基表的一种查询操作。 使用视图的主要目的是为了方便用户访问基表,以及保证用户对基表的安全访问。 对用户而言,往往要对一个表进行大量的查询操作,如果查询操作比较复杂,并且需要频 繁地进行,那么可以为这个查询定义一个视图。假设用户需要经常执行下面的查询: SELECT dname FROM dept WHERE deptno=(SELECT deptno FROM emp a GROUP BY deptno HAVING avg(sal)>all(SELECT avg(sal) FROM emp WHERE deptno!=a.deptno GROUP BY deptno)) 如果为这个查询定义一个视图,那么用户只要执行一条简单的SELECT语句,对这个视图 进行查询,那么实际的操作就是对基表dept执行了上面的查询。 需要注意的是,在视图中并不保存对基表的查询结果,而仅仅保存一条SELECT语句。只 有当访问视图时,数据库服务器才去执行视图中的SELECT语句,从基表中查询数据。虽然我 们对视图没有做过任何修改,但是对视图的多次访问可能得到不同的结果,因为基表中的数据 可能随时被修改。所以视图中并不存储静态的数据,而是从基表中动态查询的。 从另外一个角度来看,视图可以保证对基表的安全访问。在设计表时,我们一般是从整体 的角度来考虑表的结构的,而不是从每个用户的角度来确定表结构以及定义允许的操作。对于 同一个表,不同的用户可以进行不同的操作,可以访问不同的数据。这样我们就可以为不同的 用户定义不同的视图,从而保证用户只能进行允许的操作,访问特定的数据。 例如,对于员工表emp,公司经理可以浏览所有的数据,但是不能修改数据;人事部门可 以查看和修改员工的职务、部门等信息,也可以增加一个新员工;财务部门可以查看、修改员 工的工资和奖金;而对于普通员工,只能查看其他员工的部门和职务等信息。如果为每一类用 户分别定义一个视图,就可以保证他们对同样的数据进行不同的访问。 1.6.1 视图的创建、修改和删除 用户可以在自己的模式中创建视图,只要具有CREATE VIEW这个系统权限即可。如果希 望在其他用户的模式中创建视图,则需要具有CREATE ANY VIEW系统权限。如果一个视图的 基表是其他用户模式中的对象,那么当前用户需要具有对这个基表的SELECT权限。 创建视图的命令是CREATE VIEW,这条命令的格式为: CREATE VIEW 视图名 AS SELECT语句 WITH READ ONLY WITH CHECK OPTION; 其中最后两个选项是可选的,其中“WITH READ ONLY”限定对视图只能进行查询操作, 不能进行DML操作。“WITH CHECK OPTION”限定DML操作必须满足一定的条件。 例如,下面的语句创建视图view_1,它所代表的操作是查询员工表中部门30的员工姓名、 38 第一部分 Oracle使用基础工资和奖金。 SQL> CREATE VIEW view_1 AS SELECT ename,sal,comm FROM emp WHERE deptno=30; 视图view_2所代表的操作是查询部门20和30中工资大于2000元的员工姓名、工资和奖金。 创建这个视图的CREATE语句为: SQL> CREATE VIEW view_2 AS SELECT ename,sal,comm FROM emp WHERE (deptno=30 or deptno=20) and sal>2000 视图被创建之后,可以通过DESC命令查看视图的结构。查看视图结构的方法与查看表的 方法相同,查看的结果是列出视图中各列的定义。 视图的结构是在执行CREATE VIEW语句创建视图时确定的,在默认情况下,列的名称与 SELECT之后基表的列名相同,数据类型和是否为空也继承了基表中的相应列。如果希望视图 中的各列使用不同的名字,那么在创建视图时,在视图的名称之后应该指定各列的名称。例如, 下面的语句重新创建视图view_1,并为这个视图指定了不同的名称。 CREATE VIEW view_1(name,salary,comm1) AS SELECT ename,sal,comm FROM emp WHERE deptno=30; 如果执行DESC命令查看视图view_1的结构,我们将发现视图中各列的名称就是在 CREATE VIEW语句中指定的名称,而数据类型和是否为空继承了基表中的对应列。下面是执 行DESC命令查看视图view_1结构的结果: SQL> DESC view_1; 名称 是否为空? 类型 NAME NULL VARCHAR2(10) SALARY NULL NUMBER(7,2) COMM1 NULL NUMBER(7,2) 视图作为一种数据库对象,它的相关信息被存储在数据字典中。与当前用户的视图有关的 数据字典是USER_VIEWS,查询这个数据字典,可以获得当前用户的视图的相关信息。例如, 需要查询视图VIEW_2中的相关信息,可以执行下面的SELECT语句: SELECT text FROM user_views WHERE view_name='VIEW_2'; 在列TEXT中存储的是创建视图时使用的SELECT语句。另外,在数据字典ALL_VIEWS存 储的是当前用户可以访问的所有视图的信息,在数据字典DBA_VIEWS存储的是系统中的所有 视图的信息,这个数据字典只有DBA可以访问。 如果发现视图的定义不合适,可以对其进行修改。实际上视图中的SELECT语句是不能直 接修改的,所以修改视图的一种方法是先删除视图,再重新创建,另一种方法是在创建视图的 CREATE语句中使用OR REPLACE选项。带OR REPLACE选项的CREATE语句格式为: CREATE OR REPLACE VIEW 视图名 AS SELECT语句 第1章 SQL语言基础  39WITH READ ONLY WITH CHECK OPTION; 这样在创建视图时,如果视图不存在,则创建它。如果已经存在一个同名的视图,那么先 删除这个视图,然后再根据SELECT语句创建新视图,用这个新视图代替原来的视图。 视图在不需要时,可以将其从数据库中删除。删除视图的命令是DROP VIEW。用户可以 直接删除自己创建的视图,如果希望删除其他用户创建的视图,则需要具有DROP ANY VIEW 这个系统权限。DROP VIEW命令的格式为: DROP VIEW视图名; 例如,要删除视图view_1,可以执行下面的语句: DROP VIEW view_1; 视图被删除后,相关的信息也被从数据字典中删除。 1.6.2 如何对视图进行访问 对视图的访问包括查询和受限制的DML操作。访问视图的方法与访问表的方法基本相同。 例如,要查询视图view_1,可以执行下面的SELECT语句: SELECT * FROM view_1; 如果要向视图view_1中插入一行,可以执行下面的INSERT语句: INSERT INTO view_1 VALUES('MARY',1000,200) 在访问视图时,这种访问被转化为对基表的访问,所以在视图上执行DML操作时,也要遵 守基表上的约束。上述INSERT语句在执行时系统将会出错,错误信息是:ORA-01400: 无法将 NULL 插入(“SCOTT”.“EMP”.“EMPNO”),发生错误的原因是这行数据违反了基表的主键约 束。在这一行数据中只提供了姓名、工资和奖金三列的值,而主键列deptno没有对应的数据, 所以就违反了基表上的主键约束。 现在我们来考察对视图的DML操作进行限制的情况的情况。假设我们以下面的语句创建了 视图view_2: CREATE OR REPLACE VIEW view_2 AS SELECT ename,sal,comm FROM emp WHERE (deptno=30 or deptno=20) AND sal>2000; 对这个视图进行查询操作时,将得到一些结果: SQL> SELECT * FROM VIEW_2; ENAME SAL COMM JONES 2975 BLAKE 2850 AAAA 3000 如果再对视图view_2进行一次UPDATE操作,那么再次查询的结果将有所不同: SQL> UPDATE view_2 SET sal=sal-900; 已更新3行。 40 第一部分 Oracle使用基础SQL> SELECT * FROM VIEW_2; ENAME SAL COMM JONES 2075 AAAA 2100 查询的结果表明,执行UPDATE语句之后,有一行数据因为sal列的数据不满足创建视图时 WHERE子句中指定的条件,从而在视图中消失了。为了防止这种情况,我们可以在创建视图 时使用WITH CHECK OPTION,例如,下面的语句重新创建视图view_2: CREATE OR REPLACE VIEW view_2 AS SELECT ename,sal,comm FROM emp WHERE (deptno=30 or deptno=20) AND sal>2000 WITH CHECK OPTION; 那么现在再执行以前的UPDATE语句时,系统将会出错,错误信息为: ORA-01402: 视图 WITH CHECK OPTION 违反 WHERE 子句 这就相当于为视图增加了一个约束,当对视图进行DML操作时,数据必须满足WHERE子 句中指定的条件。 1.6.3 复杂视图 以前在创建视图时,在CREATE VIEW语句的SELECT子句中只涉及一个表的操作,并且 只是对基表中的列进行简单的查询,并没有出现多个基表,或者对基表中的列进行表达式运算 或者函数运算的情况,这种视图被称为简单视图。对简单视图不仅可以进行查询操作,还可以 进行DML操作。 复杂视图是这样的视图,视图中的列是从基表中的列经过表达式或函数运算而来,或者是 对基表进行了DISTINCT查询,或者涉及多个表的操作。总而言之,如果在用CREATE VIEW 语句创建视图时,在SELECT之后的列名中使用了表达式、函数,或者使用了DISTINCT关键 字,或者对多个表进行了连接查询,这样的视图都是复杂视图。 创建复杂视图仍然是通过执行CREATE VIEW命令完成的,只不过因为在SELECT子句中 使用了表达式或者函数,这样的运算式不能作为视图中的列名,所以在创建复杂视图时必须为 每个列指定列名。例如,下面的语句创建视图view_3: SQL> CREATE OR REPLACE VIEW view_3(deptno,max_sal,min_sal,sum_sal) AS SELECT deptno,max(sal),min(sal),sum(sal) FROM emp GROUP BY deptno; 视图view_3中的max_sal、min_sal和sum_sal三个列是通过对基表中的列SAL分别经过三个 函数的运算而来的,所以必须为这三个列明确地指定列名。 下面的语句用来创建视图view_4,这个视图涉及两个表的操作,并且对其中的一个列进行 了表达式运算。 SQL> CREATE OR REPLACE VIEW view_4(empno,ename,dname,sal,comm) AS SELECT empno,ename,dname,sal*1.1,comm 第1章 SQL语言基础  41FROM emp,dept WHERE emp.deptno=dept.deptno; 对复杂视图,允许的操作只有查询,大部分视图不允许DML操作。例如,如果通过下面的 INSERT语句向视图view_4中插入一行,系统将出错。 SQL> INSERT INTO view_4 VALUES(9999,'Kate',10,2000,200); 相应的错误信息为: ORA-01776: 无法通过连接视图修改多个基表 现在我们总结一下在什么样的视图上可以执行DML操作,在什么样的视图不允许执行 DML操作。 对于简单视图,如果基表中的所有列都被包含在视图中,或者至少主键列和所有不允许为 空的列都被包含在视图中,并且在创建视图的CREATE VIEW语句的SELECT语句中最多只使 用了WHERE子句,对这样的视图是可以进行插入、删除、修改操作的。如果在创建这样的简 单视图时使用了WITH CHECK OPTION选项,那么执行DML操作时要遵守一定的约束条件。 对于涉及多个基表的复杂视图,如果其中至少一个表的所有列都被包含在视图中,或者至 少一个表的主键列和所有不允许为空的列都被包含在视图中,并且在创建视图的CREATE VIEW语句的SELECT语句中最多只使用了WHERE子句,这样的视图是允许进行插入、删除、 修改操作的。对这样的视图进行DML操作时,只有其中一个表可以被修改,并且被修改的列只 能映射到一个表中。 对于只涉及一个基表的复杂视图,如果视图中的列是对基表的列经过某种运算而来,包括 表达式、AVG等函数,或者在创建视图的CREATE VIEW语句的SELECT语句中使用了 DISTINCT关键字、GROUP子句,对这样的视图是不能进行DML操作的。 对于涉及多个表的视图,如果视图中的列没有包含其中一个表中的所有主键列和所有不为 空的列,或者视图中的部分列是经过对基表中的列经过某种运算而来的,或者在创建视图时在 SELECT子句中使用了DISTINCT关键字、GROUP子句,这样的视图也是不能进行DML操作的。 1.7 索引 查询是在表上进行的最频繁的访问。在查询数据时,很少有用户愿意查询表中的所有数据, 除非要对整个表进行处理。一般情况下用户总是查询表中的一部分数据。在SELECT语句中, 通常需要通过WHERE子句指定查询条件,以获得满足该条件的所有数据。如果能够在很小的 范围内查询需要的数据,而不是在全表范围内查询,那么将减少很多不必要的磁盘I/O,查询 的速度无疑会大大加快。提供这种快速查询的方法就是索引。 1.7.1 索引的基本概念 索引是一种建立在表上的数据库对象,它主要用于加快对表的查询操作。合理使用索引可 以大大减少磁盘访问的次数,从而大大提高数据库的性能。 使用索引的主要目的是加快查询速度,另外,索引也可以作为唯一性约束。如果在表的一 42 第一部分 Oracle使用基础个列上建立了唯一性索引,那么系统将自动在这个列上建立唯一性约束,这样可以保证插入这 个列的数据是唯一的。 索引究竟是怎样加快查询速度的呢?原来,索引是建立在表中的某个列或几个列上的,这 样的列称为索引列。在创建索引时,数据库服务器将对索引列的数据进行排序,并将排序的结 果存储在索引所占用的存储空间中。在查询数据时,数据库服务器首先在索引中查询,然后再 到表中查询。因为索引中的数据事先进行了排序,所以只需要很少的查找次数就可以找到需要 的数据。 例如,假设要执行下面的查询语句: SQL> SELECT empno,ename,sal FROM emp WHERE empno=7902; 假设在表emp中有1000行数据,如果没有创建索引,那么系统将不得不在全表范围内查询, 查询的次数为1000。但是如果使用了索引进行查询,那么只需要很少的几次查询就可以找到编 号为7902的员工的数据。 图1.2为索引和表之间的关系示意。 图1.2 索引与表的关系 从上图可以看到,在索引中,不仅存储了索引列上的数据,而且还存储了一个ROWID的 值。ROWID是表中的一个伪列,是数据库服务器自动添加的,表中的每一行数据都有一个 ROWID值,它代表这一行的标识,即一行数据在存储空间的物理位置。在访问表中的数据时, 都要根据这个伪列的值找到数据的实际存储位置,然后再进行访问。由于索引列上的数据已经 进行了排序,在索引中很快就能找到这行数据,然后根据ROWID就能直接到表中找到这行数 第1章 SQL语言基础  43 索引 表 EMPNO ROWID 7369 AAAG25AABAAAKTSAAA 7499 AAAG25AABAAAKTSAAB 7521 AAAG25AABAAAKTSAAC 7566 AAAG25AABAAAKTSAAD 7654 AAAG25AABAAAKTSAAE 7698 AAAG25AABAAAKTSAAF 7777 AAAG25AABAAAKTSAAN 7782 AAAG25AABAAAKTSAAG 7839 AAAG25AABAAAKTAAH 7844 AAAG25AABAAAKTSAAI 7900 AAAG25AABAAAKTSAAJ 7902 AAAG25AABAAAKTSAAK 7934 AAAG25AABAAAKTSAAL EMPNO ENAME SAL ---- ---------- -------- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7839 KING 5000 7844 TURNER 1500 7900 JAMES 950 7902 AAAA 3000 7934 MILLER 1300 7777 KATE 2000据了。 需要注意的是,表是独立于索引的,无论对在表上建立了多少索引,无论索引对表中的数 据进行什么样的排序,表中的数据都不会有任何变化。 在查询一行数据时,首先在索引中查询该行的行标识,然后根据这个行标识找到表中的数 据。因为索引中的数据是经过排序的,所以采用了折半查找法查找数据,以达到快速查找的目 的。图1.3表示利用索引查找数据的过程。 图1.3 索引的工作原理 利用折半查找法在索引中查找数据的过程类似于遍历一棵二叉树,首先与根节点比较,如 果与查找的数据相同,则一次访问就完成查询。如果要查找的数据小于根节点,则在根节点的 左子树中查找,否则在右子树中查找,这样查找的范围将缩小一半。按照这种方法,每次将查 找范围缩小一半,然后在剩下的节点中继续查找,直到找到所需的数据。如果利用上述索引在 表emp中查找员工7777,访问一次磁盘就可以得到结果,要查找员工7902,第三次就可以得到 结果。 按照索引列的值是否允许重复,索引可以分为唯一性索引和非唯一性索引,其中唯一性索 引可以保证索引列的值是唯一的。按照索引列中列的数目,索引可以分为单列索引和复合索引。 按照索引列的数据的组织方式,索引可以分为B*树索引、位图索引、反向索引和基于函数的索 引,这里仅介绍B*树索引的用法。 合理地使用索引固然可以大大提高数据库的查询性能,但是不合理的索引反而会降低数据 库的性能,尤其是在进行DML操作时。在创建索引时,表中的数据将被排序,如果对表进行了 DML操作,表中的数据发生了变化,这时索引中的数据也将被重新排序,如果在表上建立了多 个索引,那么每个索引中的数据都要被重新进行排序。这种排序的开销是很大的,尤其是表非 常大时。 索引是关系型数据库系统用来提高性能的有效方法之一,索引的使用可以减少磁盘访问的 次数,从而大大提高了系统的性能。但是在设计索引时必须全面考虑在表上所进行的操作,如 果在表上进行的主要操作是查询操作,那么可以考虑在表上建立索引,如果在表上要进行频繁 的DML操作,那么索引反而会引起更多的系统开销。一般来说,创建索引要遵循以下原则: ¥ 如果每次查询仅选择表中的少量行,应该建立索引。 44 第一部分 Oracle使用基础 empno<7844 empno>7844empno>7566empno<7566 empno>7777empno<7777 7777 7566 7654 7844 7839 7782 7902 7900 7934 7698 7499 7521 7369 ¥ 如果在表上需要进行频繁的DML操作,不要建立索引。 ¥ 尽量不要在有很多重复值的列上建立索引。 ¥ 不要在太小的表上建立索引。在一个小表中查询数据时,速度可能已经足够快,如果建 立索引,对查询速度不仅没有多大帮助,反而需要一定的系统开销。 1.7.2 索引的创建、修改和删除 索引可以自动创建,也可以手工创建。如果在表的一个列或几个列上建立了主键约束或者 唯一性约束,那么数据库服务器将自动在这些列上建立唯一性索引,这时索引的名字与约束的 名字相同。 手工创建索引需要执行SQL命令,创建索引的命令是CREATE INDEX。一个用户可以在自 己的模式中创建索引,只要这个用户具有CREATE INDEX这个系统权限。如果希望在其他用户 的模式中创建索引,那么需要具有CREATE ANY INDEX这个系统权限。 CREATE INDEX 命令的语法格式为: CREATE INDEX 索引名 ON 表名(列1,列2...); 例如,如果要在表emp的empno列上建立索引,可以执行以下SQL语句: SQL> CREATE INDEX idx_1 ON emp(empno); 在这个索引中,索引列只有一个,这样的索引称为单列索引。如果要建立复合索引,则要 指定多个列。例如,下面的语句在表emp上创建了一个复合索引: SQL> CREATE INDEX idx_2 ON emp(empno,deptno); 在默认情况下,创建的索引是非唯一的,也就是说,在表中的索引列上允许存在重复值。 如果要创建唯一性索引,那么需要使用关键字UNIQUE。例如: SQL> CREATE UNIQUE INDEX idx_3 ON emp(ename,deptno); 这时在表emp的ename和deptno列上创建了唯一性索引inx_3,这样可以保证表emp的ename 和deptno列组合没有重复值,也就是说,没有两个员工名字相同,并且在同一个部门。 在创建一个索引时,需要指定一个或多个列,那么到底指定哪些列呢?一个重要原则是选 择经常用在WHERE子句中使用的列。例如,如果要经常根据列empno的值查询员工的数据, 那么可以考虑将列empno作为索引的索引列。 如果在查询时要在WHERE子句中指定多个查询条件,那么可以在涉及的多个列上分别创 建索引。例如,对于条件WHERE A=7788 AND B>2000,可以在列A和列B上创建两个索引, 但是在查询时也要涉及两个索引的查询。如果能够将两个索引合并为一个,那么查询的次数也 会减少。如果在一个索引建立在两个或多个列上,这样的索引就是复合索引。复合索引主要用 于多个条件的查询语句中。 一般情况下,在指定索引中的列时,要遵循以下原则: 第1章 SQL语言基础  45¥ 在WHERE子句中经常使用的列上创建索引。 ¥ 尽量不要在具有大量重复值的列上创建索引。 ¥ 具有唯一值的列是建立索引的最佳选择,但是究竟是否在这个列上建立索引,还要看是 否对这个列经常进行查询。 ¥ 如果WHERE子句中的条件涉及多个列,可以考虑在这些列上创建一个复合索引。 正如前面所说,合理设计的索引将提高系统的性能,而不合理的索引反而会降低系统性能。 所以,在数据库的运行过程中,要经常利用SQL Trace检查索引是否被使用,检查索引是否像 期望的那样提高了数据库的性能。如果一个索引并没有被频繁地使用,或者一个索引对数据库 性能的提高只有微小的帮助甚至没有帮助,这时可以考虑删除这个索引。 1.7.3 索引信息的查询 与索引有关的数据字典有两个:user_indexes和user_ind_columns。 例如,要查询索引的类型、所基于的表、是否唯一性索引,以及状态等信息,可以执行以 下查询语句: SQL>SELECT index_type,table_name,uniqueness,status FROM user_indexes WHERE index_name='IDX_1'; 下面的查询语句用来获得索引所基于的表和表上的列: SQL>SELECT table_name,column_name FROM user_ind_columns WHERE index_name='IDX_1'; 1.8 序列 序列是一种数据库对象,用来自动产生一组唯一的序号。序列是一种共享式的对象,多个 用户可以共同使用序列中的序号。一般将序列应用于表的主键列,这样当向表中插入数据时, 主键列就使用了序列中的序号,从而保证主键列的值不会重复。用这种方法可以代替在应用程 序中产生主键值的方法,可以获得更可靠的主键值。 1.8.1 序列的创建、修改和删除 默认情况下,用户可以在自己的模式中创建序列。如果希望在其他用户的模式中创建序列, 则必须具有CREATE ANY SEQUENCE这个系统权限。创建序列的命令为CREATE SEQUENCE, 它的完整语法格式为: CREATE SEQUENCE 序列名INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NOCACHE 在这个命令的语法格式中,除序列名以外,其余各选项都是可选的。各选项中的n是一个 46 第一部分 Oracle使用基础整数。其中START WITH选项指定序列中的序号从哪个数字开始,默认情况下从它的最小值开 始。INCREMENT选项指定了序列中序号递增的幅度,也就是后一个序号比前一个序号大多 少。序号可以递增,也可以递减,所以INCREMENT选项中的数字n可以是正整数,也可以是 负整数。 MAXVALUE用来指定序列中序号的最大值。如果没有最大值,可用NOMAXVALUE选项 代替这个选项。同样,MINVALUE用来指定序列中序号的最小值,序列中的最小值必须小于或 等于它的开始值。 如果为序列指定了最大值,那么当序列中的序号被消耗完时,用户将无法从这个序列中取 得序号。选项CYCLE使得序列中的序号可以循环使用。当用户正在使用序列中的最大值时, 下一个可以使用的序号就是它的开始值。 用户每使用序列一次,都要对序列进行一次查询。如果把序列中的序号放在内存中进行缓 冲,那么用户获得序号的速度将大大加快。选项CACHE的作用就是将序列中接下来的n个序号 在内存中进行缓冲。如果不希望进行缓冲,可以用NOCACHE选项代替它。 例如,下面的语句使用默认值创建了一个序列seq1: SQL> CREATE SEQUENCE seq1; 下面的语句创建了一个序列seq2,它的开始值是100,增幅是2,最大值为10000,序列中 的序号不在内存中进行缓冲。 SQL> CREATE SEQUENCE seq2 START WITH 10 INCREMENT BY 2 MAXVALUE 10000 NOCACHE; 序列的信息可以从数据字典user_sequences中获得。例如,下面的SELECT语句用于查询序 列seq2的最小值、最大值、增幅、下一个可用序号、是否循环等信息: SQL> SELECT min_value,max_value,increment_by,last_number,cycle_flag FROM user_sequences WHERE sequence_name='SEQ2' MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER C 1 10000 2 10 N 序列在创建之后,在使用的过程中,可以对其进行修改。比如修改它的最大值、最小值、 增幅等,但是不能修改开始值。需要注意的是,如果已经有部分序号被使用,那么对序列的修 改只影响以后的序号,对以前已经使用的序号不起作用。 修改序列的命令是ALTER SEQUENCE。用户可以修改自己的序列,如果希望修改其他用 户的序列,则需要具有ALTER ANY SEQUENCE这个系统权限。ALTER SEQUENCE命令的用 法与CREATE SEQUENCE命令的用法基本相同。例如,下面的语句修改序列seq2的最小值、最 大值、增幅,并使其中的序号可循环使用: SQL> ALTER SEQUENCE seq2 MINVALUE 5 第1章 SQL语言基础  47MAXVALUE 50000 INCREMENT BY 3 CYCLE; 现在重新执行上面的SELECT,查询这个序列的信息,查询结果为: MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER C 5 50000 3 11 Y 删除序列的命令是DROP SEQUENCE。用户可以删除自己创建的序列,如果要删除其他用 户的序列,则要具有DROP ANY SEQUENCE系统权限。序列被删除后,它的相关信息就被从 数据字典中删除。例如,可用下面的语句删除序列seq2: SQL> DROP SEQUENCE seq2; 1.8.2 序列的使用 对用户而言,序列中的可用资源是其中包含的序号。用户可以通过SELECT命令获得可用 的序号,也可以将序号应用于DML语句和表达式中。如果要使用其他用户的序列,则必须具有 对该序列的SELECT权限。 序列提供了两个伪列,即NEXTVAL和CURRVAL, 用来访问序列中的序号。其中NEXTVAL代表下一个可用 的序号,CURRVAL代表当前的序号。序列可以认为是包 含了一系列序号的一个指针。序列刚被创建时,这个指针 位于第一个序号之前,以后每获得一个序号,指针就向后 移动一个位置,这时就可以用CURRVAL访问序列中的当前序号,用NEXTVAL访问下一个序号。 在第一次使用序列中的序号时,必须首先访问NEXTVAL伪列,使指针指向第一个序号。图1.4 为序列中的序号和指针的示意。 通过SELECT语句可以从序列中获得一个可用的序号。例如,对于已经创建的序列SEQ1, 可以执行下面的SELECT语句: SQL> SELECT seq1.nextval FROM dual; 这条语句的执行结果为: NEXTVAL ---------- 1 其中seq1. nextval表示序列seq1的NEXTVAL伪列。这时如果再利用SELECT语句访问这个 序列的CURRVAL伪列,应该返回它的当前序号,即1。在SELECT语句中使用表dual是必要的, 因为SELECT语句将根据表中数据的行数返回若干个序号,并且每访问一次NEXTVAL伪列, 指针就向后移动一个序号。假设把SELECT语句中的表名用dept代替,那么执行结果为: NEXTVAL ---------- 2 48 第一部分 Oracle使用基础 1 2 3 4 5 6 7 ⋯ 指针 图1.4 序列中的序号3 4 5 因为表dept中有四行,所以SELECT语句返回接下来的连续四个序号。 CURRVAL伪列代表序列中的当前序号,访问这个伪列时指针并不向后移动。CURRVAL伪 列的引用方法与NEXTVAL伪列相同,引用格式为:序列名.currval。 序列还可应用于SELECT语句的其他形式。例如,在下面的SELECT语句中,序列seq1为每 行数据提供了一个编号: SELECT seq1.nextval,deptno,dname,loc FROM dept; 这条语句的执行结果类似于以下形式(这里假定第一次使用序列seq1): NEXTVAL DEPTNO DNAME LOC 1 10 ACCOUNTING NEW YORK 2 20 RESEARCH DALLAS 3 30 SALES CHICAGO 在更多情况下序列的作用为表中的主键列或其他列提供一个唯一的序号。例如,要往表 emp中插入一行时,可以利用序列为每个员工指定唯一的员工号。下面的INSERT语句向表emp 中插入一行,其中empno的值为序列seq1中的下一个序号: SQL> INSERT INTO emp(empno,ename,mgr,hiredate,deptno) VALUES(seq1.nextval,'GOOD',7902,sysdate,20); 序列是一种共享式的数据库对象,用户可以直接使用自己创建的序列,其他用户也可以访 问当前用户的序列,只要具有对这个序列的SELECT权限即可。如果一个序号被某个用户获得, 那么其他用户就不能再获得这个序号了。也就是说,序列是可以共享的,但序列中的序号却是 不能共享的。 对序列中序号的访问操作是作为一个单独的事务实现的,这个事务的执行与其他事务的执 行成功与否无关。如果包含一条DML语句的事务被回滚了,那么对序列的操作是无法回滚的。 假设序列seq1的当前序号是10,考虑下面的语句: SQL> INSERT INTO dept VALUES(seq1.nextval,'NETWORK','BEIJING'); SQL> ROLLBACK; SQL> SELECT seq1.nextval FROM dual; 如果上面的语句都执行成功,最后一条语句的执行结果是12。这是为什么呢?因为 INSERT语句获得了序列seq1的下一个序号11,虽然这个事务被回滚了,但是序列中的指针还 是向后移动了,序号11再也无法使用了,下一个可以使用的序号是12。 在访问序列中的序号时,可能会发生序号不连续的情况,不连续的原因可能是事务发生了 回滚,或者多个用户共同访问同一个序列。 一个用户要访问其他用户的序列时,不仅要具有对这个序列的SELECT权限,在访问时还 要在序列的名称前以用户名进行限定。例如,下面的SELECT语句是当前用户访问用户scott的 序列seq1的情况: SELECT scott.seq1.nextval FROM dual; 第1章 SQL语言基础  49如果要将一个序列的SELECT权限授予其他用户,相应的GRANT命令格式为: GRANT SELECT ON 序列名 TO 用户名; 1.8.3 序列信息的查询 序列作为一种数据库对象,它的相关信息也存储在数据字典中。与序列相关的数据字典有 三个:USER_SEQUENCES、DBA_SEQUENCES和ALL_SEQUENCES。 其中数据字典USER_SEQUENCES的各列及意义如下所示: 名称 是否为空? 类型 意义 SEQUENCE_NAME NOT NULL VARCHAR2(30) 序列名称 MIN_VALUE NULL NUMBER 最小值 MAX_VALUE NULL NUMBER 最大值 INCREMENT_BY NOT NULL NUMBER 增加幅度 CYCLE_FLAG NULL VARCHAR2(1) 是否循环使用 ORDER_FLAG NULL VARCHAR2(1) 是否按顺序 CACHE_SIZE NOT NULL NUMBER 是否缓冲 LAST_NUMBER NOT NULL NUMBER 下一个可用序号 例如,要了解序列SEQ2的相关信息,可以执行下面的SELECT语句: SQL> SELECT sequence_name,min_value,max_value,increment_by,last_number FROM user_sequences WHERE sequence_name='SEQ2'; 这条语句的执行结果如下: sequence_name min_value max_value Increment_by last_numbe -------------- ----------- --------- ------------- ------------- SEQ2 1 10000 2 10 1.9 同义词 同义词是一种数据库对象,它是为一个数据库对象定义的别名,使用同义词的主要目的是 为了简化SQL语句的书写。 1.9.1 同义词的概念和类型 利用同义词可以为用户的一个对象,或者其他用户的一个对象定义别名,从而简化命令或 程序的书写,在命令或程序中可以直接使用同义词代替原来的对象。 可以为表、视图、存储程序、序列等对象建立同义词,也可以为一个同义词再建立同义词, 甚至可以为一个不存在的对象建立同义词,系统仅仅在使用同义词时才验证它所代表的对象是 否存在。 同义词本身并不包含原对象中的数据或代码,它的作用仅仅相当于一个指针。在使用同义 词时,系统根据同义词的定义查找它所指向的对象,将对同义词的访问转化为对原对象的访问。 同义词的作用仅仅是为了方便用户操作数据库对象。 50 第一部分 Oracle使用基础Oracle支持两种类型的同义词,即私有同义词和公共同义词。私有同义词由普通用户创建, 在默认情况下只能由用户本人使用。公有同义词一般由DBA创建,可以由所有用户使用。 公共同义词的意义在于它代表了一个大家都可以访问的对象。例如,程序包 DBMS_OUTPUT是属于特权用户SYS的,但是任何一个用户都可以直接使用它,而不用指定它 所在的模式。普通用户能够使用的名称DBMS_OUTPUT显然是一个公共同义词。 用户可以随意为其他用户的一个对象创建一个同义词,但这并不意味着这个这个用户就具 有了访问其他用户的对象的权限。如果要通过同义词访问其他用户的对象,还需要具有相应的 权限。只有在使用同义词时,系统才验证用户是否有相应的访问权限。 1.9.2 同义词的创建与删除 用户可以在自己的模式中创建同义词,这需要具有CREATE SYNONYM这个系统权限。如 果希望在其他用户的模式中创建同义词,则需要具有CREATE ANY SYNONYM这个系统权限。 普通用户创建的同义词一般都是私有同义词,公有同义词一般由DBA创建,普通用户如果希望 创建同义词,则需要CREATE PUBLIC SYNONYM这个系统权限。 创建私有同义词的命令是CREATE SYNONYM,它的语法规则为: CREATE SYNONYM 同义词 FOR 用户名.对象名; 例如,假设当前用户为了方便地访问scott用户的表dept,可以执行下面的CREATE语句创 建同义词: CREATE SYNONYM sy_dept FOR scott.dept; 这样在具有相应权限的情况下,当前用户就可以通过这个同义词代替原来的表,在SQL语 句中通过访问同义词来对原来的表进行操作。例如: SELECT deptno,dname,loc FROM sy_dept; 创建公有同义词的命令也是CREATE SYNONYM,只是要使用PUBLIC关键字进行限定。 创建公有同义词的命令格式为: CREATE PUBLIC SYNONYM 同义词 FOR 用户名.对象名; 例如,为了让大家都能方便地访问scott用户的表dept,DBA可以通过执行下面的语句创建 一个公共同义词: SQL> CREATE PUBLIC SYNONYM pub_dept FOR scott.dept; 在具有相应权限的情况,任何用户都可以通过这个同义词访问SCOTT用户的表dept,例如, 可以向表中写入一行数据: SQL> INSERT INTO pub_dept(deptno,dname,loc) VALUES(100,'NETWORK','BEIJING'); 用户如果不使用同义词时,可以将其删除。删除同义词的命令是DROP SYNONYM。这条 命令的语法格式为: DROP SYNONYM 同义词; 第1章 SQL语言基础  51一个用户可以删除自己创建的同义词,如果要删除其他用户创建的同义词,则要具有 DROP ANY SYNONYM系统权限。DBA可以删除所有的公共同义词,普通用户需要具有 DROP PUBLIC SYNONYM系统权限,才能删除公共同义词。同义词被删除以后,它的相关信 息也将从数据字典中删除。 1.9.3 同义词信息的查询 同义词作为一种数据库对象,它的相关信息被存储在数据字典中。与同义词有关的数据字 典有三个:user_sysnonyms、all_synonyms、dba_synonyms。 其中在数据字典user_synonyme中记录了当前用户所拥有的同义词。这个表的个列定义及 其意义如下所示: 名称 是否为空? 类型 意义 SYNONYM_NAME NOT NULL VARCHAR2(30) 同义词的名称 TABLE_OWNER VARCHAR2(30) 所指向的对象属主 TABLE_NAME NOT NULL VARCHAR2(30) 所指向的对象名称 DB_LINK VARCHAR2(128) 数据库链接 如果要查询当前用户创建了哪些同义词,它们各代表哪个用户的哪个对象,可以执行下面 的SELECT语句进行查询: SQL> SELECT synonym_name,table_name,table_owner FROM user_synonyms; 在数据字典all_synonyms中记录了当前用户所能使用的所有同义词,包括私有同义词和公 共同义词。 在数据字典dba_synonyms中记录了数据库中所有的同义词,包括每个用户创建的私有同义 词和DBA创建的公共同义词。这个视图只有DBA能够访问,它的结构除了包含数据字典 user_synonyms的所有列外,还有一个列owner代表同义词的创建者。 如果要在整个数据库范围内查询某个同义词的信息,可以对数据字典dba_synonyms进行查 询。例如,要查询用户scott所创建的所有同义词,可以执行下面的SELECT语句: SQL> SELECT synonym_name, table_name, table_owner FROM dba_synonyms WHERE owner='SCOTT'; 如果要查询用户scott的表dept具有哪些同义词,可以执行下面的SELECT语句: SQL> SELECT synonym_name,owner FROM dba_synonyms WHERE table_owner='SCOTT' AND table_name='DEPT'; 如果要查询系统中所有的公共同义词,可以执行下面的SELECT语句: SQL> SELECT synonym_name, table_name, table_owner FROM dba_synonyms WHERE owner='PUBLIC'; 52 第一部分 Oracle使用基础第2章 SQL*Plus用法指南 在第1章中我们介绍了SQL的基本用法。SQL是一种命令式的语言,它是用户操作数据库的 最重要的一种方式。SQL语句在执行时,需要有一个执行环境。开发人员在开发软件时,最终 的结果是形成一个应用程序。在应用程序中可以嵌入SQL语句,利用这些SQL语句可以访问数 据库。这时应用程序作为数据库服务器的客户,与数据库服务器以客户/服务器模式工作。把 SQL语句嵌入到应用程序中的好处是使最终用户无需了解SQL的工作原理,也不需要知道SQL 命令的使用方法,只要能够正确操作应用程序,就能够与数据库服务器进行交互。 然而对于数据库管理员,应用程序远远不能达到他们的目的,访问数据库最直接的方式是 利用数据库软件提供的实用程序。Oracle提供了一个功能强大的实用工具— SQL*Plus。 SQL*Plus是Oracle提供的一个客户端工具,也是远程客户访问数据库的一种重要途径。在 SQL*Plus中可以执行任一条SQL语句,可以执行一个PL/SQL块,也可以执行SQL*Plus本身的 命令,而且几乎所有的管理任务都可以在SQL*Plus中完成。 2.1 SQL*Plus的基本用法 如果要使用SQL*Plus与数据库服务器进行交互,首先要登录到数据库服务器上,这时在 SQL*Plus进程和数据库服务器之间将建立一条连接,它们以客户/服务器模式工作。 2.1.1 登录与退出 启动SQL*Plus的命令是sqlplus,在操作系统提示符下输入sqlplus,系统将显示版本、日期、 版权等信息,然后提示用户输入用户名和口令。如果输入正确,则在SQL*Plus和数据库服务器 之间将建立一条连接,并显示服务器的信息,最后出现SQL*Plus的提示符“SQL>”,在这个提 示符下就可以输入需要执行的命令了。例如: $ sqlplus SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 11 16:21:57 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. 请输入用户名: scott 请输入口令: ***** 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> 如果以sys用户身份登录,在用户名之后还应该指定身份“as sysdba”或“as sysoper”。 例如: $ sqlplusSQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 11 16:21:57 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved.请 输入用户名: sys as sysdba 请输入口令: ***** 连接到: 启动SQL*Plus时,还可以在sqlplus命令之后直接指定用户名和口令,这时系统将不再提示 输入用户名和口令,而是直接登录到数据库服务器。格式是:用户名/口令。例如: $ sqlplus scott/tiger $ sqlplus sys/1234 as sysdba 如果在一台计算机上安装了多个数据库服务器,为了登录到正确的数据库,在进入 SQL*Plus之前,先要设置环境变量ORACLE_SID,将其值设置为要登录的数据库。例如: c:\>SET ORACLE_SID=ORCL 如果数据库服务器运行在UNIX下,通过以下方式设置环境变量ORACLE_SID: $ORACLE_SID=ORCL $export ORACLE_SID 在默认情况下,SQL*Plus和Oracle服务器位于同一台计算机上,启动SQL*Plus时,它将自 动登录到本机的数据库服务器上。如果SQL*Plus和Oracle服务器不在同一台计算机上,在登录 时还要指定所使用的网络服务名。例如: C:\>sqlplus scott/tiger@netsvr 其中netsvr是已经定义的网络服务名。这时SQL*Plus和Oracle服务器将通过Oracle Net进行 通信。 无论采用哪种登录方式,登录成功后将出现SQL*Plus的提示符“SQL>”。SQL*Plus是一 个基于字符界面的工具,所有的命令都需要手工输入。在提示符之后输入的命令以分号结束。 如果命令太长,可以输入回车,在换行之后继续输入,这时在每行之前将自动出现当前的行号。 在命令的最后输入分号,然后回车,这条命令将提交给数据库服务器执行。需要注意的是,分 号并不是SQL命令的一部分,而是一条SQL命令结束的标志。例如: SQL> SELECT empno,sal 2 FROM emp 3 WHERE deptno=10; 退出SQL*Plus时,在提示符之后输入命令QUIT或EXIT即可。如果要在不退出SQL*Plus的 情况下断开与数据库服务器的连接,则输入DISCONNECT命令。如果要重新连接,或者在已 经连接的情况下以另一个用户的身份连接,则使用CONNECT命令。这条命令的格式为: CONNECT 用户名/口令 as sysdba 如果是SYS用户,则使用“as sysdba”或者“as sysoper”参数。如果是远程登录,还要在 用户名和口令之后输入网络服务名。 在SQL*Plus中还可以执行操作系统命令。host命令使得用户可以在不退出SQL*Plus的情况 下执行操作系统命令。在SQL*Plus提示符下执行host命令后,将进入操作系统提示符,在这里 54 第一部分 Oracle使用基础可以执行操作系统命令。在操作系统提示符下输入exit命令,将返回SQL*Plus。 SQL*Plus的UNIX版本还提供了一个“!”命令,它使得用户可以在SQL*Plus提示符下直 接执行UNIX命令。UNIX命令执行结束后重新显示SQL*Plus提示符。例如: SQL>!vi /etc/host.conf 2.1.2 如何获取帮助信息 如果在使用SQL*Plus命令时有困难,可以使用HELP或?命令获得帮助信息。首先,可以获 得帮助索引,命令的格式为: SQL>HELP INDEX 命令的执行结果为: @ COPY PAUSE SHUTDOWN @@ DEFINE PRINT SPOOL / DEL PROMPT SQLPLUS ACCEPT DESCRIBE QUIT START APPEND DISCONNECT RECOVER STARTUP ...... 上述命令都属于SQL*Plus,也就是说,这些命令只能在SQL*Plus中执行。读者在学习 Oracle的过程中,一定要搞清楚哪些是SQL*Plus命令,哪些是SQL命令。通过HELP命令可以 进一步获得每条命令的详细帮助信息。获取帮助的命令格式为: HELP 命令 例如: SQL>HELP CONNECT CONNECT ------- Connects a given username to Oracle. CONN[ECT] [{logon|/} [AS {SYSOPER|SYSDBA}]] WHERE logon has the following syntax: username[/password][@CONNECT_identifier] In iSQL*Plus you must always include your username and password. 2.1.3 如何修改SQL*Plus的设置信息 在SQL*Plus中有两类相关的设置信息,一类是SQL*Plus本身的设置信息,这类信息主要 控制SQL*Plus的输出格式;另一类是数据库服务器的设置信息,这类信息主要来自实例的参数 文件。 显示SQL*Plus设置信息的命令是show,例如,显示当前登录用户的命令为: SQL> show user USER 为"SCOTT" 如果要显示SQL*Plus的所有设置信息,执行“show all”命令,命令执行的结果类似于以 下形式: 第2章 SQL*Plus用法指南  55SQL> show all appinfo为OFF并且已设置为"SQL*Plus" arraysize 15 autocommit OFF autoprint OFF autorecovery OFF ... 如果要显示某个具体的设置信息,可以在show命令之后跟上相关的关键字,例如: SQL> show timing timing OFF 表2.1列出了SQL*Plus主要的设置信息及其意义。 表2.1 SQL*Plus中的设置信息 设置信息 可选值 默认值 意义 autocommit on|off|immediate off 控制DML语句执行后是否自动提交 autorecovery on|off off 开启或关闭自动恢复数据库的功能 define 用户自定义 & 在用户自定义变量前面的前缀字符 editfile 用户自定义 afiedt.buf 指定执行edit命令时打开的临时文件 linesize 用户自定义 80 指定一行的宽度,单位为字符 long 用户自定义 80 为long型数据指定显示宽度 null 用户自定义 “ ” 显示空数据时,代替的字符 sqlnumber on|off on 控制在多行SQL语句中,第2行以后继续使用SQL*Plus提示符还是行号 sqlprompt 用户自定义 sql> 指定SQL*Plus的提示符 sqlterminator 用户自定义 ; 指定SQL语句的结束标志符 time on|off off 指定是否将当前时间作为提示符的一部分 timimg on|off off 指定是否为每一条已执行的SQL语句显示已用时间信息。 如果要显示数据库服务器的参数设置信息,可以使用“show parameter”命令,并在命令 之后指定要显示的参数名称。由于这些信息是从参数文件中读取的,因此只有特权用户可以查 看这样的信息。例如,要查看当前数据库的名称,执行如下命令: SQL> show parameter db_name NAME TYPE VALUE db_name string ORCL 在命令执行的结果中包含参数的名称、类型和参数值。 由于参数名都是字符串,在显示参数时,可以只指定参数名称的一部分,这样,系统将显 示所有包含这个字符串的参数。例如,要显示所有包含字符串“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 8192 56 第一部分 Oracle使用基础修改SQL*Plus设置信息的命令是SET,这样可以灵活控制SQL*Plus的显示格式。例如, SQL*Plus的默认提示符是“SQL>”,如果要将提示符改为“SQL*Plus>>”,可以执行以下命令: SQL>set sqlprompt "SQL*Plus>>" 需要注意的是,改变后的设置信息只对SQL*Plus的当前启动起作用。如果要经常修改这些 设置信息,通常的做法是编写一个脚本文件,在脚本文件中指定这些设置信息,然后在 SQL*Plus中执行脚本文件,具体的例子请参阅2.4节。 数据库服务器的参数设置信息来自参数文件,有关参数文件的信息,请参阅第4章。 2.1.4 实例的启动与关闭 Oracle的实例是一组内存结构和后台进程的集合,通过实例,应用程序才可以访问数据库 中的数据。Oracle服务器是由实例和数据库组成的,一个实例只能打开一个数据库。 启动Oracle服务器的命令为startup,这条命令只有SYS用户才可以执行。执行startup命令时, 显示的信息为: SQL> startup ORACLE 例程已经启动。 Total System Global Area 409194496 bytes Fixed Size 2176248 bytes Variable Size 301992712 bytes Database Buffers 96468992 bytes Redo Buffers 8556544 bytes 数据库装载完毕。 数据库已经打开。 在Windows 系统中,数据库服务器表现为一个系统服务。所以,只要启动对应的系统服务, 即可启动数据库服务器。系统服务的命名规则为:OracleService+实例名称。例如,在Windows 系统的DOS窗口中,可以用命令行来启动Oracle服务器,例如: C:>net start OracleServiceORCL OracleServiceORCL 服务正在启动 ...... OracleServiceORCL 服务已经启动成功。 更简单的方法是在控制面板的管理工具中打开“服务”组件,查找Oracle服务器所对应的 系统服务,使其启动,如图2.1所示。 关闭数据库服务器的过程与启动过程正好相反,首先关闭数据库,然后卸载数据库,最后 关闭实例。关闭Oracle服务器对应的命令为shutdown,这条命令也是只能由SYS用户执行。 Oracle服务器关闭时显示的信息为: SQL> shutdown 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 在Windows 系统中,Oracle服务器同样可以作为一个系统服务来关闭。例如: C:>net stop OracleServiceORCL 第2章 SQL*Plus用法指南  57同样,Oracle服务器也可以通过图2.1所示的“服务”组件进行关闭。 图2.1 Windows中的系统服务 2.2 SQL*Plus中的缓冲区 SQL*Plus提供了一个命令缓冲区,用来保存最近执行的一条SQL语句,或者一个PL/SQL 块。用户可以反复执行缓冲区中的内容,也可以对缓冲区中的内容进行编辑。 2.2.1 执行缓冲区中的内容 执行缓冲区中内容的命令有两个:“/”和run。 “/”命令的作用是执行缓冲区中刚刚输入的或者已经执行内容。如果是一条SQL语句,它 的结束标志是“;”,遇到分号,这条SQL语句就会执行。如果在SQL语句执行后输入/命令, 这条SQL语句将再执行一次。如果是PL/SQL块,结束标志仍然是“;”,只是在输入结束后还 必须输入/命令,这个块才能执行。如果再次输入“/”命令,这个块将再次执行。例如: SQL> CREATE TABLE t2(id number); 表已创建。 SQL> / create table t2(id number) * ERROR 位于第 1 行: ORA-00955: 名称已由现有对象使用 首先在SQL*Plus中执行CREATE命令创建表T2,然后输入“/”命令再次执行这条SQL语 句。由于这个表已经创建,所以出现了错误信息。 run命令与“/”命令一样,也是再次执行缓冲区中的内容,只是在执行之前首先显示缓冲 区中的内容。例如,在刚才执行了CREATE语句后,再执行run命令,结果如下所示: SQL> run 1* create table t2(id number) create table t2(id number) * 58 第一部分 Oracle使用基础 启动按钮ERROR 位于第 1 行: ORA-00955: 名称已由现有对象使用 2.2.2 编辑缓冲区的内容 缓冲区真正的意义在于用户可以编辑其中的内容,这样如果语句执行出错,用户可以很方 便地进行修改,特别是长的、复杂的SQL语句或者PL/SQL块。 使用最频繁的编辑命令是edit(或ed)。这条命令的作用是打开默认的编辑器(在Windows 环境中为记事本),并将缓冲区中的内容放在编辑器中。用户可以在编辑器中修改缓冲区中的 内容,修改完后保存并退出编辑器,然后在SQL*Plus中输入/命令,修改后的内容将在 SQL*Plus中执行。图2.2显示的是当一条SQL语句执行出错时,用edit命令打开编辑器的情况。 图2.2 缓冲区内容的编辑 执行edit命令时,SQL*Plus在操作系统当前目录中建立了一个临时文件,用来保存当前缓 冲区的内容。这个文件的默认文件名为“afiedt.buf”。需要注意的是,在这个临时文件中并不 保存所有已经执行的SQL语句或者PL/SQL块,仅当执行edit命令时,才将当前缓冲区中的内容 写入这个文件,文件中以前的内容将被覆盖。 如果要显示缓冲区中的内容,可以执行list(或者l)命令。list命令以分行的形式显示缓冲 区的内容,并在每一行前面显示行号。如果要显示某一行的内容,可以在list命令之后指定行号, 这样只显示指定的一行,并使这一行成为当前行,而不是显示所有内容。例如,假设在缓冲区 中已经有一条SQL语句,我们可以以不同的形式执行list命令: SQL> list 1 SELECT ename 2 FROM emp 3* WHERE deptno=10 SQL> l 2 2* FROM emp SQL> l3 3* WHERE deptno=10 还有一种简单的方法用来显示某一行的内容。在SQL*Plus提示符下直接输入一行的行号, 结果与将行号作为参数的list命令是等价的。 append命令(或者a)的作用是在缓冲区中当前行的末尾追加文本。在默认情况下,最后 一行是当前行。如果以某一行的行号作为参数执行了list命令,那么指定的行将成为当前行。 append命令的格式为: 第2章 SQL*Plus用法指南  59append 文本 append将把指定的文本追加到当前行的末尾。注意追加的文本不需要用引号限定,否则引 号将作为文本的一部分一起被追加。例如,对于前面的SELECT语句,如果希望在刚才的基础 上再检索sal列的值,那么可以在第一行的末尾追加文本“,sal”。追加的步骤为: SQL> l1 1* SELECT ename SQL> append ,sal 1* SELECT ename,sal 这样,缓冲区中第一行的内容由原来的“SELECT ename”变为“SELECT ename,sal”, SELECT语句执行的结果将检索ename和sal两个列的值。 append命令的作用是在当前行的末尾追加文本。如果要在缓冲区中增加一行,就要使用 input命令。input命令(或者i)的作用是在当前行之后追加一行或者多行。在默认情况下, input命令在最后一行之后追加文本。如果要在某一行之后追加,应该先执行list命令使该行成 为当前行,然后再追加。 使用input命令追加文本时,可以只追加一行,这时input命令的格式为: input 文本 如果要追加多行,则输入不带参数的input命令并回车,这时行号将变成ni的形式,其中n 是从当前行号的下一个数字开始的整数,表示该行内容是追加到缓冲区中的。追加结束后以一 个空行和回车符结束。例如,假设当前缓冲区中有一条不完整的SQL语句,现在希望把它补充 完整。当前缓冲区显示的结果为: SQL> list 1 SELECT ename 2* AND sal>1000 在第一行之后增加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提示符,追加操作便告结束。 60 第一部分 Oracle使用基础如果发现缓冲区中内容有错误,可以用edit命令打开编辑器,在编辑器中进行修改。还有 一种修改方法,就是change命令。change(或者c)命令的作用是在缓冲区中当前行上用新的字 符串代替旧的字符串。这条命令的格式为: change /新字符串/旧字符串 例如,要把上面修改后的SELECT语句中的最后一个条件“sal>1000”改为“comm is not null”,操作的步骤为: SQL> list 4 4* and sal>1000 SQL> change /sal>1000/comm is not null/ 4* and comm is not null 重新显示的结果表明这一行的内容已经被修改。 如果要清空缓冲区中的内容,可以执行del命令。当缓冲区被清空后,就不能再执行edit命 令进行编辑,也不能再执行list命令进行显示了。如果只删除缓冲区中的一部分内容,则通过 edit和list命令可以显示剩下的内容。 在默认情况下,del命令删除缓冲区中当前行的全部内容。但是通过指定参数,del命令可 以删除指定的一行或者多行。del命令的格式有以下形式: ¥ del 开始行号 结束行号 删除开始行号和结束行号之间的行。 ¥ del开始行号 * 删除开始行号和当前行之间的行。 ¥ del * 结束行号 删除当前行和结束行号之间的行。 ¥ del last 删除最后一行。 其中开始行号和结束行号是指定的行号,开始行号必须小于结束行号。符号“*”用来代 表当前行,标识符last代表最后一行。 2.2.3 如何对操作系统文件进行读写 在SQL*Plus中可以对操作系统中的文本文件进行简单的读写访问。例如,事先将SQL语句 或者PL/SQL块的代码存放在文本文件中,再把文本文件调入缓冲区中,使之执行。或者把当 前缓冲区中的内容保存到一个文件中,或者把SQL语句、PL/SQL块的执行结果保存到文件中。 读文件涉及的命令包括@、get、start等命令。 @命令的作用是将指定的文本文件的内容读到缓冲区中,并执行它。文本文件可以是本地 文件,也可以是远程服务器上的文件。如果是本地文件,@命令的命令的执行格式为: @文件名 这里的文件名要指定完整的路径,默认的扩展名是.sql,如果脚本文件使用了默认的扩展 名,则在@命令中可以省略扩展名。如果是远程文件,必须将它存放到一个web服务器上,并 以HTTP或FTP方式访问。这时@命令的命令的执行格式为(以HTTP为例): @http://web服务器/文件名 使用@命令读取文件时,文件中可以包含多条SQL语句,每条语句以分号结束;或者可以 第2章 SQL*Plus用法指南  61包含一个PL/SQL块。文件被读入缓冲区中以后,SQL*Plus将按顺序执行文件中的代码,并将 执行结果输出到显示器上。例如,假设在/home/oracle目录下有一个文件,名为a.sql,文件的 内容为: SELECT ename FROM emp WHERE empno=7902; SELECT dname FROM dept WHERE deptno=10; 现在希望通过@命令将这个文件读到缓冲区中,命令执行的执行格式如下: SQL> @/home/oracle /a @命令还有一个用法,就是在启动SQL*Plus的同时,将指定的文件读入缓冲区并执行它。 这时@命令和文件名一起作为SQL*Plus的命令行参数,格式如下: sqlplus 用户名/口令 @文件名 注意,这种格式与以前提到的使用网络服务的格式是很相似的,但是仍然有区别,请注意 观察: sqlplus 用户名/口令@网络服务名 由于文件名和网络服务名都表现为字符串,所以单纯从名字上无法区分到底使用了文件名 还是网络服务名。二者的区别在于第一种格式中在用户名/口令之后有一个空格,这时将把后面 的参数解释为一个文件,并把这个文件加载到缓冲区中。在第二种格式中,用户名/口令之后没 有空格,这时将后面的参数解释为网络服务名。 get命令的作用与@命令相似,但是它只是把文件加载到缓冲区中,并不直接执行。get命 令的的执行格式为: get 文件名 选项 其中文件名的默认扩展名为.sql,在get命令中可以省略。目前get命令只支持本地的操作系 统文件。可以使用的选项有两个:LIST和NOLIST。其中LIST选项指定将文件的内容读到缓冲 区的同时,还要在显示器上输出,这是默认选项。选项NOLIST使得文件的内容不在显示器上 输出。 使用get命令时还要注意,在文本文件中只能包含一条SQL语句,而且不能以分号结束。也 可以只包含一个PL/SQL块,块以分号结束。在使用@和get命令时要注意这些格式上的差别。 例如,假设在/home/oracle目录下有一个文件,名为b.sql,文件的内容为: SELECT ename FROM emp WHERE empno=7902 现在先通过get命令把它读入缓冲区,然后执行/命令使之执行: SQL> get /home/oracle/b 1* SELECT ename FROM emp WHERE empno=7902 SQL> / ENAME ---------- FORD start命令与@命令是等价的,这里不再赘述。 62 第一部分 Oracle使用基础写文件涉及的命令包括save和spool。其中save命令用于将当前缓冲区中的内容写入一个操 作系统文件,而spool命令用于将命令的执行结果输出到一个操作系统文件。 save命令的格式为: SQL>save 文件名 选项 其中选项指定以什么样的方式写文件。可以使用的选项有以下三个: ¥ CREATE 如果文件不存在,则创建。否则,命令执行失败。 ¥ APPEND 如果文件不存在,则创建。否则,在文件末尾追加。 ¥ REPLACE 如果文件不存在,则创建。否则删除原文件,重新创建。 如果不指定完整的路径,则在当前目录下产生这个文件,文件的默认扩展名是.sql。例如, 假设当前缓冲区中有一条SELECT语句,使用save命令可以将这条语句写入文件: SQL> list 1* SELECT * FROM emp SQL> save /home/oracle/aa spool命令利用假脱机技术,用于将SQL*Plus的输出写入到文件中,它有以下几种用法: ¥ 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语句本身。 例如,假设当前目录下有一个文件,名为c.sql,它的内容为: spool cc SELECT ename,sal FROM emp WHERE deptno=10; spool off 现在将这个文件读到缓冲区中,并使之执行,执行结果如下: SQL> @c ENAME SAL CLARK 2450 KING 5000 MILLER 1300 文件中SQL语句的执行结果显示在屏幕上,同时在当前目录下生成了文件cc.LST,文件的 内容与屏幕上显示的结果完全一致。 第2章 SQL*Plus用法指南  632.3 如何在SQL*Plus中使用变量 为了使数据处理更加灵活,在SQL*Plus中可以使用变量。SQL*Plus中的变量在SQL*Plus 中的整个启动期间一直有效,这些变量可以用在SQL语句、PL/SQL块以及文本文件中。在执 行这些代码时,先将变量替换为变量的值,然后再执行。 2.3.1 用户自定义的变量 用户可以根据需要,自己定义变量。有两种类型的自定义变量,第一类变量不需要定义, 可以直接使用,在执行代码时SQL*Plus将提示用户输入变量的值。第二类变量需要事先定义, 并且需要赋初值。 第一类变量不需要事先定义,在SQL语句、PL/SQL块以及脚本文件中可以直接使用。这类 变量的特点是在变量名前面有一个“&”符号。当执行代码时,如果发现有这样的变量, SQL*Plus将提示用户逐个输入变量的值,当用变量值代替变量后,才执行代码。例如,假设用 户构造了一条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列的值为字符型,应该用一对单引号限定,使用了变量以后,仍然要 用一对单引号限定。 上述语句如果需要再次执行,系统将提示用户再次逐个输入变量的值。为了使用户在每次 执行代码时不需要多次输入变量的值,可以在变量名前加上“&&”符号。使用这种形式的变 量,只需要在第一次遇到这个变量时输入变量的值,变量值将保存下来,以后就不需要不断输 入了。例如,假设把上述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' ...... 64 第一部分 Oracle使用基础在SQL*Plus中可以使用的第二类自定义变量需要事先定义,而且需要提供初值。定义变量 的命令是DEFINE。定义变量的格式是: define 变量名=变量值 变量经定义后,就可以直接使用了。实际上,用DEFINE命令定义的变量和使用“&”的 变量在本质上是一样的。用DEFINE命令定义变量以后,由于变量已经有值,所以在使用变量 时不再提示用户输入变量的值。 如果执行不带参数的DEFINE命令,系统将列出所有已经定义的变量,包括系统定义的变 量和用“&”定义的变量,以及即将提到的参数变量。例如: SQL> define DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR) DEFINE _SQLPLUS_RELEASE = " 1001000200" (CHAR) DEFINE _EDITOR = "NOTEPAD" (CHAR) DEFINE _O_VERSION = "Oracle10g Enterprise Edition Release 10.1.0.2.0 - Production With the OLAP and Oracle Data Mining options JServer Release 10.1.0.2.0 - Production" (CHAR) DEFINE _O_RELEASE = " 1001000200" (CHAR) DEFINE _RC = "1" (CHAR) DEFINE TABLE_NAME = "emp" (CHAR) DEFINE NAME = "SMITH" (CHAR) 其中最后两个变量就是刚才我们用“&”定义的变量。 现在,让我们看一个用DEFINE命令定义变量的例子。 SQL> define col_name=ename SQL> define salary=3000 在这里定义了两个变量,然后在SQL语句中就可以直接使用这两个变量了。在使用变量时, 仍然用“&变量名”的形式来引用变量的值。例如: SQL> SELECT &col_name FROM emp WHERE sal>&salary; 在执行这条语句时,用ename代替变量col_name,用3000代替变量salary,生成一条标准的 SQL语句。这条语句的执行结果为: 原值 1: SELECT &col_name FROM emp WHERE sal>&salary 新值 1: SELECT ename FROM emp WHERE sal>3000 ENAME KING 当一个变量不再使用时,可以将其删除。undefine命令用于取消一个变量的定义。删除一 个变量的命令格式为: undefine 变量名 2.3.2 参数变量 在SQL*Plus中,除了用户自定义的变量外,还有一类变量,这就是参数变量。参数变量在 第2章 SQL*Plus用法指南  65使用时不需要事先定义,可以直接使用。 前面我们讲述了get和@命令的用法。这两个命令的作用是将一个文本文件加载到缓冲区中, 使之执行。因为文本文件的内容是固定的,在执行期间不能被修改,所以只能执行固定的代码, 这就为灵活的数据操作带来了一定的困难。例如,要查询某部门中员工的工资情况。部门号事 先不确定,而是根据实际情况临时确定的。这样在文本文件的SELECT语句中就不能将部门号 指定为一个固定值。 解决这个问题的一个办法是使用参数变量。由于部门号是不确定的,所以在执行文本文件 时可以将实际的部门号作为一个参数,在SELECT语句中通过参数变量引用这个参数。参数在 SQL*Plus的命令行中指定的格式为: @文件名 参数1 参数2 参数3... 这样在文本文件中可以用参数变量&1、&2、&3分别引用参数1、参数2、参数3... 例如,要查询某部门中工资大于某个数值的员工姓名,在构造SELECT语句时就不能将部 门号和工资这两个列的值指定为固定值,而是分别用一个参数变量代替。假设我们在目录 /home/oracle中建立了一个文本文件d.sql,文件的内容为: SELECT ename FROM emp WHERE deptno=&1 and sal>&2; 在执行这个文本文件时,需要为参数变量&1和&2指定实际的参数值。参数值是在用get或 者@命令加载文本文件时指定的。例如,要查询部门10中工资大于2000的员工,执行文件d.sql 的命令格式为: SQL> @/home/oracle/d 10 2000 这条命令执行的情况为: 原值 1: SELECT ename FROM emp WHERE deptno=&1 and sal>&2 新值 1: SELECT ename FROM emp WHERE deptno=10 and sal>2000 ...... 从命令的执行结果可以看出,在SQL*Plus中首先用实际参数10代替参数变量&1,用参数 2000代替参数变量&2,生成一条标准的SQL语句,然后才执行这条SQL语句。 2.3.3 与变量有关的交互式命令 SQL*Plus还提供了几条交互式命令,主要包括prompt、accept和pause。这几条命令主要用 在文本文件中,用来完成灵活的输入输出。 prompt命令用来在屏幕上显示指定的字符串。这条命令的格式为: prompt 字符串 注意这里的字符串不需要单引号限定,即使是用空格分开的几个字符串。prompt命令只是 简单地把其后的所有内容在屏幕上显示。例如: SQL> prompt I'm a programmer I'm a programmer accept命令的作用是接收用户的键盘输入,并把用户输入的数据存放到指定的变量中,它 一般与prompt命令配合使用。accept命令的格式为: 66 第一部分 Oracle使用基础accept 变量名 变量类型 prompt 提示信息 选项 其中变量名是指存放数据的变量,这个变量不需要事先定义,可直接使用。变量类型是指 输入的数据的类型,目前SQL*Plus只支持数字型、字符型和日期型数据的输入。prompt用来指 定在输入数据时向用户显示的提示信息。选项指定了一些附加的功能,可以使用的选项包括: hide和default。hide功能使用户的键盘输入不在屏幕上显示,这在输入保密信息时非常有用。 default为变量指定默认值,在输入数据时如果直接回车,则使用该默认值。 例如,希望从键盘输入一个数字型数据到变量xyz,在输入之前显示指定的提示信息,还 为变量指定默认值,这样如果在输入数据时直接回车,那么变量的值就是这个默认值。对应的 accept命令的形式为: SQL> accept xzy number prompt 请输入变量xyz的值: default 0 请输入变量xyz的值:100 pause命令的作用是使当前的执行暂时停止,在用户输入回车键后继续。一般情况下pause 命令用在文本文件的两条命令之间,使第一条命令执行后出现暂停,待用户输入回车键后继续 执行。pause命令的格式为: pause 文本 其中文本是在暂停时向用户显示的提示信息。 现在,我们构造一个文本文件,演示这几条命令的用法。文本文件e.sql的功能是统计某个 部门的员工工资,部门号需要用户从键盘输入。文本文件的内容如下: prompt 工资统计现在开始 accept dno number prompt 请输入部门号: default 0 pause 请输入回车键开始统计... SELECT ename,sal FROM emp WHERE deptno=&dno; 这个脚本文件的执行过程为: SQL> @/home/oracle/e 工资统计现在开始 请输入部门号:10 请输入回车键开始统计... 原值 1: SELECT ename,sal FROM emp WHERE deptno=&dno 新值 1: SELECT ename,sal FROM emp WHERE deptno= 10 ENAME SAL ---------- ---------- CLARK 2450 KING 5000 MILLER 1300 如果希望生成一个报表,那么可以在SELECT前后分别加上spool命令,将统计的结果写到 一个文件中,或者发往打印机。 2.4 SQL*Plus的报表功能 SQL*Plus有一个强大的功能,就是能够根据用户的设计生成美观的报表。实际上,利用本 第2章 SQL*Plus用法指南  67章中前面介绍的知识已经能够生成一个简单的报表了,但是如果要生成规范的、美观的报表, 还要学习SQL*Plus的其他一些功能。 SQL*Plus的报表功能是利用它的命令来实现的。首先,用户要根据自己的意图,设计报表 的显示格式,这包括报表的标题、各列的显示格式等。然后构造查询语句,决定要对哪些数据 进行显示。最后还要决定把报表仅仅显示在屏幕上,还是存放在文本文件中,或者送往打印机。 一般情况下,生成一个报表需要许多条命令,如果每次在生成报表时都输入这么多的命令, 是很麻烦的事情。通常的做法是把这些命令放到一个文本文件中,在需要时只要把这个文本文 件读到缓冲区中,并使其执行即可。 2.4.1 报表的标题设计 报表的标题是利用SQL*Plus的两个命令来设计的,即TTITLE和BTITLE。其中TTITLE命 令用来设计报表的头部标题,而BTITLE用来设计报表的尾部标题。 TTITLE命令设计的头部标题显示在报表每页的顶部。设计头部标题时,要指定显示的信 息和显示的位置,还可以使标题分布在多行之中。TTITLE命令有以下几种执行格式: ¥ ttitle on|off:打开或关闭头部标题的显示,默认为on。 ¥ ttitle 头部标题信息:设计头部标题信息。 ¥ ttitle:获得当前的标题设置信息。 其中ttitle on用来打开头部标题的显示功能,而ttitle off用来关闭这个功能,在默认情况下, 这个功能是可用的。 用ttitle命令设计头部标题的操作是比较复杂的。这条命令的格式为: ttitle format 显示格式 显示位置 显示信息 其中format参数用来规定标题的显示格式,这个参数是可选的。显示位置规定标题在一行 中的位置,可选的位置有三个:CENTER(中间)、LEFT(左边)和RIGHT(右边)。显示信 息指定了标题的内容。一般情况下,标题可以指定为以下内容: ¥ 指定的文本 ¥ SQL.LNO 当前的行号 ¥ SQL.PNO 当前的页号 ¥ SQL.RELEASE 当前Oracle的版本号 ¥ SQL.USER 当前登录的用户名称 例如,设计一个显示在正中的标题,命令格式为: ttitle center 蓝天集团公司员工工资统计表 如果在标题中要分开显示多条信息,例如制表人、当前页号等,可以在title命令中分别设 置不同信息的显示格式、显示位置和显示内容。如果这些信息要在多行中显示,可以在两条信 息之间使用SKIP选项。这个选项使后面的信息跳过指定的行数再显示,它需要一个整型参数, 单位是行数。 例如,在刚才设计的标题的基础上,增加制表人和当前页号,作为副标题。副标题在主标 68 第一部分 Oracle使用基础题之下两行处显示。如果命令太长,一行容纳不下时,可以用“-”符号分行,将命令分为多 行书写。满足上述要求的命令格式为: ttitle center 蓝天集团员工工资统计表 skip 2 left - 制表人: sql.user right 页码: sql.pno 这个标题的显示结果为: 蓝天集团员工工资统计表 制表人:SCOTT 页码:2 BTITLE命令的用法与TTITLE命令是一样的,区别在于BTITLE命令用来设计尾部标题, 显示的位置在报表每页的底部。 2.4.2 报表显示格式的设计 报表的显示格式是指报表中数据的显示格式。有两条命令用来设计报表的显示格式,它们 是COLUMN和BREAK。COLUMN命令用来设计某一列数据的显示格式,而BREAK命令使数 据根据某个标准分组显示。 COLUMN命令的作用是设计某一列的显示格式,包括列标题的文字和对齐方式、列数据的 宽度和显示格式等。这条命令是比较复杂的,主要表现为它有许多可以使用的选项。读者应该 多练习它的使用,注意观察命令执行的结果,从中总结经验。COLUMN命令的格式为: column 列名 选项 COLUMN命令的主要选项有以下几个: ¥ heading:指定列标题的显示文字。 ¥ format:指定列数据的显示格式。 ¥ justify:指定列标题的对齐方式,包括左(left)、居中(center)、右(right)。 ¥ null:当列数据为空时,将显示指定的文本。 ¥ wrapped|truncated:规定当列标题或数据超出规定的宽度时,如何显示。其中wrapped为 默认值表示换一行继续显示。Truncated表示截断余下的数据。 其中heading选项用来规定列的标题。默认情况下,列的标题就是列的名字。用户可以定制 自己喜欢的列标题。如果列标题中有空格,要用双引号限定。还可以把列标题中的文字分成两 行显示,格式是:“第一行文字|第二行文字”。例如,通过下面的命令为ename列定义标题为 “姓名”,为sal列定义标题为“工资”。 column ename heading 姓名 column sal heading 工资 那么,在执行下列SELECT语句时: SELECT ename,sal FROM emp WHERE empno=7902; 显示的结果为: 姓名 工资 ---------- ---------- FORD 3000 第2章 SQL*Plus用法指南  69format选项指定数据的显示格式,主要用来设置字符型、数字型和日期型数据的格式。常 用的格式字符串如表2.2所示。 表2.2 报表中的格式字符串 数据类型 格式字符串 举例 说明 数字型 9999.99 123.40 每个9代表一位数字,如果数据长度超过指定的长度, 则显示“#” 数字型 999 999.00 12,345.00 同上,并且每3位为一组 数字型 $999 999.00 $12,345.00 在数字前加上“$”符号 数字型 0000.00 0123.40 每个0代表一位数字,如果数据长度超过指定的长度, 则显示“#”,如果长度不足,则在前后填充0 字符型 axx 其中xx为正整数,指定数据的宽度为xx个字符,如果超 出这个长度,则换行显示或截断 日期型 yyyy-mm-dd hh24:mi:ss 2010-05-09 15:50:30 yyyy表示四位的年,也可以用yy表示两位的年。mm表 示两位月份,dd表示两位的日。hh24表示24小时制的时, mi表示分钟,ss表示秒。这些格式符可以用其他分隔符隔 开,如“/” 例如,如果通过下列命令为sal列设置了显示格式: column sal heading 工资 format $999,999.00 那么刚才执行的SELECT语句现在的执行结果为: 姓名 工资 ---------- ------------ FORD $3,000.00 Justify选项用来指定列标题的对齐方式,可选的对齐方式有左对齐、居中和右对齐三种方 式。注意这种对齐方式仅对列标题起作用,并不影响列的数据的对齐方式。 Null选项用来指定当列的数据为空时,应该显示什么样的数据。例如,在显示奖金信息时, 如果没有奖金,可以显示为0。 在制作报表时,我们希望属于同一部门的员工数据集中在一起显示,这样我们可以将部门 号作为分组的标准,将数据分组显示。如果部门号变化了,可以跳过几行或一页,继续显示另 一部门的数据。 BREAK命令的作用就是根据指定的列作为分组标准,将数据分组显示。例如将同一部门 的员工集中在一起显示。BREAK命令的格式为: BREAK ON 列名 措施 其中列名就是被指定为分组标准的列,凡是该列数据相同的数据集中在一起显示。可以选 择的措施有以下两个: ¥ skip 行数|PAGE ¥ noduplicates|duplicates 其中skip选项规定当指定列的值发生变化时,怎样显示后面的数据。可以跳过指定的行数, 或者跳过一页,继续显示后面的数据。当把一个正整数作为skip的参数时,跳过这么多行。如 70 第一部分 Oracle使用基础果把PAGE作为skip的参数,跳过一页,二者可选其一。 noduplicates(或nodup)和duplicates(或dup)选项规定了是否显示重复的列值。当所有 的行以指定的列为标准分组显示时,这个列有许多重复的值。如果使用了nodup选项,将不显 示重复值,这是默认的选项,如果使用了dup,将显示重复值。例如,如果以部门号为标准进 行分组,那么所有的部门号为10的行集中在一起显示,在这些行中,可以只在第一行显示部门 号10,其余行均不显示。当部门号为10的行显示完后,跳过若干行后或一页后,继续显示部门 号为20的数据。在所有部门号为20的行中,仍然只在第一行中显示部门号20,其余行均不显示, 依此类推。 现在考察下面的例子。我们希望检索工资大于2000的员工,并且以部门号为标准分组显示。 构造的BREAK命令和SELECT语句如下所示: SQL>break on deptno skip 1 nodup SQL>SELECT deptno,ename,sal FROM emp WHERE sal>2000 ORDER BY deptno 这条SELECT语句执行的结果为: deptno ename sal ---------- ---------- ----------- 10 CLARK 2,450.00 KING 5,000.00 20 JONES 2,975.00 FORD 3,000.00 30 BLAKE 2,850.00 应该注意的是,当使用BREAK命令指定了分组标准后,系统是按照被检索的顺序对行进 行分组显示的,而不是将数据按照分组标准集中在一起后再显示。所以在SELECT语句中附加 ORDER BY子句对行进行排序是必要的。 现在,让我们把COLUMN和BREAK命令综合起来,制作一个比较复杂的报表。制作报表 的代码如下: ttitle center 蓝天集团员工工资统计表 skip 2 left - 制表人: sql.user right 页码: sql.pno btitle skip 1 center "内部资料 概不对外" column dname heading 部门名称 column ename heading 姓名 column sal format $999,999.00 heading 工资 justify center column comm heading 奖金 column loc format a10 heading 部门地址 break on dname skip 1 nodup SELECT dname,ename,sal,comm,loc FROM emp,dept WHERE emp.deptno=dept.deptno 第2章 SQL*Plus用法指南  71ORDER BY emp.deptno; 现在把这段代码存放在一个文本文件中,假设为/home/oracle目录下的report.sql,然后把这 个文件加载到缓冲区中,并使之执行,将得到我们希望的报表。生成的报表如下所示: SQL> @report 蓝天集团员工工资统计表 制表人:SCOTT 页码:1 部门名称 姓名 工资 奖金 部门地址 -------------- ---------- ----------- ---------- ---------- ACCOUNTING CLARK $2,450.00 NEW YORK KING $5,000.00 NEW YORK MILLER $1,300.00 NEW YORK RESEARCH SMITH $800.00 DALLAS FORD $3,000.00 DALLAS JONES $2,975.00 DALLAS 内部资料 概不对外 蓝天集团员工工资统计表 制表人:SCOTT 页码:2 部门名称 姓名 工资 奖金 部门地址 -------------- ---------- ---------------- ---------- ---------- SALES ALLEN $1,600.00 300 CHICAGO JAMES $950.00 CHICAGO TURNER $1,500.00 0 CHICAGO BLAKE $2,850.00 CHICAGO MARTIN $1,250.00 1400 CHICAGO WARD $1,250.00 500 CHICAGO 内部资料 概不对外 已选择12行。 以上生成的报表将在显示器上输出。如果希望把报表保存到文件中,或者直接送到打印机 上进行打印,可在SELECT语句之前加上spool命令,以一个文本文件名作为它的参数。在 SELECT语句之后再加上一个spool命令,并且以off或out作为它的参数。 2.4.3 如何对特定列进行统计 以前介绍的报表仅仅从数据库中检索数据,然后按照指定的格式显示出来。如果要对报表 中的数据进行统计,就要借助于SQL*Plus的另外一条命令:COMPUTE。 一般情况下,对报表的统计不外乎两种形式,即水平统计和垂直统计。水平统计是把一行 72 第一部分 Oracle使用基础中的几个列的值进行计算,例如,求公司中每个员工的工资和奖金之和。这种统计比较简单, 通过SELECT语句就可以实现。例如,下列SELECT语句将计算每个员工的工资和奖金之和: SELECT sal+nvl(comm,0) AS 收入 FROM emp; 垂直统计是对报表中某个特定列的值进行某种计算。例如公司所有员工的工资总和,或者 某个部门中所有员工的奖金之和。这种统计是比较复杂的,通过SQL*Plus的COMPUTE命令可 以实现这种统计。 COMPUTE命令的格式为: compute 函数 label 标签文字 of 列名 on 列名 其中函数指定对数据进行什么样的统计。COMPUTE命令可以实现的统计有SUM(求和)、 AVG(求平均值)、MIN(求最小值)、MAX(求最大值)和COUNT(计数)。 Label选项指定了一个字符串,用来在计算所得的数据之前显示。例如,对某列进行SUM 统计的数据之前可以显示“总计”,在AV G统计的数据之前可以显示“平均”等。默认情况下 显示的信息是所使用的函数名称,如sum、avg。 Of选项指定了一个列名,COMPUTE命令对这个列的数据进行计算,计算的结果显示在这 个列的正下方。需要注意的是,这个列的数据类型必须能够进行指定的计算。 On选项之后也指定了一个列名。COMPUTE命令根据这个列对数据进行分组统计。 COMPUTE命令通常是和BREAK命令配合使用的,BREAK命令对数据进行分组显示,而 COMPUTE命令对分组后的数据分别进行计算。例如,我们可以将员工的数据按照部门号进行 分别显示,然后按照分组的结果对各部门分别进行统计。例如,要对各部门员工的工资分别求 和,相应的COMPUTE命令为: compute sum label 总计 of sal on depnto 计算的结果显示在每个部门员工数据之后,被计算列的正下方。 现在,我们把与报表有关的几个重要命令综合起来,制作一个完整的报表。假设制作报表 的所有命令存放在文本文件report.sql中,这个文件的内容为: ttitle center 蓝天集团员工工资统计表 skip 2 left - 制表人: sql.user format 99 right 页码: sql.pno btitle skip 1 center "内部资料 概不对外" column dname heading 部门名称 column ename heading 姓名 column sal format $999,999.00 heading 工资 justify center column comm format $9999.00 heading 奖金 column 收入 format $999,999.00 break on dname skip 1 nodup compute sum label 总计 of sal on dname compute sum of comm on dname compute sum of 收入 on dname spool wwww SELECT dname,ename,sal,comm,sal+nvl(comm,0) as 收入 FROM emp,dept 第2章 SQL*Plus用法指南  73WHERE emp.deptno=dept.deptno order by emp.deptno; spool off 这样在将这个文件读到缓冲区中并执行时,将生成希望的报表,并将报表存放到当前目录 下的文件www.lst中。报表的内容为: 蓝天集团员工工资统计表 制表人:SCOTT 页码:1 部门名称 姓名 工资 奖金 收入 -------------- ---------- ----------- ---------- ---------- ACCOUNTING CLARK $2,450.00 $2,450.00 KING $5,000.00 $5,000.00 MILLER $1,300.00 $1,300.00 RESEARCH SMITH $800.00 $800.00 FORD $3,000.00 $3,000.00 JONES $2,975.00 $2,975.00 ************* --------- ------------ ------------ 总计 $15,525.00 $15,525.00 内部资料 概不对外 蓝天集团员工工资统计表 制表人:SCOTT 页码:2 部门名称 姓名 工资 奖金 收入 -------------- ---------- -------------- ---------- ---------- SALES ALLEN $1,600.00 $300.00 $1,900.00 JAMES $950.00 $950.00 TURNER $1,500.00 $.00 $1,500.00 BLAKE $2,850.00 $2,850.00 MARTIN $1,250.00 $1400.00 $2,650.00 WARD $1,250.00 $500.00 $1,750.00 ************* -------------- ------------ ------------ 总计 $9,400.00 $2,200.00 $11,600.00 内部资料 概不对外 74 第一部分 Oracle使用基础
还剩84页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 2 人已下载

下载pdf

pdf贡献者

nexuewen

贡献于2012-07-26

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf