Oracle 数据库是一种高效率、高可靠性、适应高吞吐量的数据库解决方案；在本书的讲解过程中，系统地描述了Oracle 体系结构，并在此基础上展开阐述了SQL 语言，用户权限和对象管理以及实践编译开发，最后是Oracle 数据库导入导出和闪回技术。本书内容实用，结构合理，实例丰富，可以帮助Oracle 初学者系统地了解Oracle 体系架构以及开发实践，除此之外，书中嵌入大量来自作者的实战经验，可以帮助有着一定资历的Oracle 数据库开发者在面对具体问题时找到打开思路的方法。

Oracle数据库一直是大型应用项目首选的数据库产品，其事务控制能力以及内核机制是其他同类产品无法比拟的。随着Web应用开发的兴起，对于小型或中型项目，MySQL数据库受到开发者的青睐，但对于大型项目或超大项目，MySQL是不能与Oracle抗衡的。

笔者自Oracle 7时开始接触它，如今Oracle已发展到19c（Oracle 12.2或更高版本）。在这二十几年间阅读了大量的Oracle书籍，遇上一些好书，从中获益匪浅。然而，初学时也遇见一些不是很好的书，为此踩了很多不必要的坑。作者的切身体会是：一本好书，尤其是首本，对于初学者至关重要，能让你少走很多弯路。更重要的是，它能帮你建立起正确有效的数据管理和运维开发思路，让你的工作事半功倍。

本书侧重实战，融入大量的示例或实例。在由浅入深、循序渐进的过程中，适时地引入生产系统中的一些东西，尤其是其中的SQL范本，将其提炼出来供读者参考和借鉴。

本书写作宗旨概括如下。

（1）贴近实战。书中所用实例以及数据均来自真实环境中的系统，其目的是让读者切身感受如何在真实环境中使用SQL语句操作数据库，在实战中掌握Oracle的运维技能。

（2）兼顾初学者和进阶者的需要。本书涵盖了从基础知识到Oracle开发运维在内的大部分内容，确保初学者和进阶者各取所需。

（3）用示例或实例来验证所学知识点。书中每个知识点至少使用一个示例或实例进行验证和测试，以确保知识点的正确性和结果的可再现性。

本书组织结构

本书涵盖了Oracle数据库大部分开发技术以及实用运维技术，全书共分为4篇。首篇对Oracle体系结构及SQL语言（查询语句和over()函数）的讲解可帮助读者夯实从事数据库开发的理论基础。接下来的用户权限及对象管理篇是从事Oracle工作的必由之路，它承上启下而且要求透彻理解。拾级而上，读者可以通过Oracle的PL/SQL编程学到数据库开发技能，为从事数据库开发工作铺就上升的阶梯。最后是Oracle数据库导入/导出与闪回篇，它与数据库运维相关，读者可以了解到常用的数据库维护技术，这也是从业者必须掌握的技能。

作者简介

笔者毕业于北京交通大学电气工程及自动化专业，当前供职于中国铁路北京局集团有限公司。

参加工作以来，一直致力于计算机应用系统的研发和建设，先后参与了多个铁路应用项目的研发工作，如《铁道物资管理信息系统平台》《机车（含机动车）汽柴油及油卡信息系统平台》等。也曾与多家IT企业合作开发项目，如《通用商城（含商场）营运系统平台》《华北大区国家电厂营运系统平台》等。在这些项目中，主要承担Oracle数据库架构设计和必要的数据库应用开发等工作。

从2014年起，在天津大学、天津民航大学、天津工业大学等学校从事兼职教学工作，所授课程为PHP和Oracle。在教学工作中，笔者积累了较为丰富的经验，也了解到了用何种方式能将Oracle开发运维技能更有效地表达出来，让读者轻松学会。

孟飞老师目前是天津农学院电子信息专业的在读研究生。他曾参与内蒙古自治区博士自然科学基金“混合微电网综合协调控制与能量分配策略研究”和“风光储混合分布式发电系统协调控制与能量分配策略研究”项目研发。除此之外，孟飞老师曾与多家IT企业合作研发数据库应用项目，积累了丰富的数据库维护与开发经验，尤其是Oracle和MySQL，擅长数据库内嵌程序的开发。

配套资源

为了让读者切实学习好本书，随书提供下列配套资料。

（1）书中源代码。下载包中源代码的代码编号与书中的代码编号是一一对应的；这样就省去了读者敲写的麻烦，通过复制粘贴操作就可在自己的环境下执行了。

（2） Oracle数据库实验环境。该环境是从一个在用的生产系统中通过exp导出的dmp文件，实实在在的真实环境而非模拟虚构（其中的敏感数据已处理），对于从事Oracle 数据库开发的读者具有很好的借鉴和参考价值，是非常难得的资料。

读者需要通过imp命令将该dmp文件导入到自己数据库中，具体的导入操作，请参阅下载包中的使用说明。

（3）一套完整的Oracle数据库学习题库。笔者根据书中的内容，特地为读者整理出一套带有翔实讲解答案的题库，可帮助读者更加扎实地夯实本书所学内容。

适用人群

笔者在写作本书时，力求做到内容实用，实例丰富，希望能帮助Oracle数据库初学者夯实体系架构和SQL语言基础，并拾级而上，稳步提升开发和运维技能。与此同时，笔者将大量实践案例融入其中，除帮助初学者缩短理论到实践的距离外，也期望为具备一定经验的开发者找到解决具体问题的思路和方法。

致谢

特别感谢中国铁道出版社有限公司在本书写作中对笔者的帮助，借此向中国铁道出版社有限公司的所有工作人员表示感谢！

面对当今信息科技的日新月异，笔者也深感追赶不上时代的脚步，书中难免有疏漏和不足的地方，敬请读者朋友批评指正。

第一篇 Oracle体系结构及SQL语言

第1章 Oracle体系结构

1.1 实例的体系结构.. 2

1.1.1 实例的概念... 2

1.1.2 实例的构成... 3

1.2 实例的内存结构.. 4

1.2.1 数据库高速缓存... 5

1.2.2 重做日志缓冲区... 9

1.2.3 共享池... 9

1.2.4 Java池... 10

1.2.5 PGA（程序全局区）... 11

1.3 实例中的后台进程.. 12

1.3.1 DBWR（数据库写进程）... 14

1.3.2 LGWR（日志写进程）... 15

1.3.3 CKPT（检查点进程）... 17

1.3.4 SMON（系统监视进程）... 18

1.3.5 PMON（监控和管理进程）... 19

1.3.6 ARCH（归档日志进程）... 19

1.4 实例的内存结构管理.. 20

1.4.1 自动内存管理... 20

1.4.2 自动共享内存管理... 21

1.4.3 手动共享内存管理... 22

1.5 Oracle数据库的连接模式.. 23

1.5.1 专用数据库连接模式... 24

1.5.2 共享数据库连接模式... 24

1.5.3 如何设置共享连接模式... 26

1.6 数据库的逻辑结构.. 27

1.6.1 表空间（TABLESPACE）... 28

1.6.2 段（Segment）... 30

1.6.3 区（Extent）... 32

1.6.4 数据块（Block）... 32

1.7 Oracle数据库的物理结构.. 32

1.7.1 数据文件... 33

1.7.2 控制文件... 33

1.7.3 重做日志文件... 34

1.7.4 跟踪文件和警告文件... 35

1.8 特权用户与口令文件.. 35

1.9 数据字典视图与动态性能视图.. 37

1.9.1 数据字典视图... 37

1.9.2 动态性能视图... 38

1.10 Oracle数据库初始化参数.. 39

1.10.1 参数文件... 39

1.10.2 初始化参数的查看... 40

1.10.3 初始化参数的修改... 40

1.11 本章小结.. 41

第2章 Oracle数据库SQL语言基础

2.1 Oracle数据类型.. 43

2.1.1 字符型... 44

2.1.2 数值型... 46

2.1.3 日期时间型... 48

【示例2-1】使用SYSDATE函数获取当前服务器的时间................................................ 48

【示例2-2】分别使用SYSDATE函数和SYSTIMESTAMP函数
获取当前服务器的时间.................................................................................................... 48

【示例2-3】timestamp日期类型在默认精度为9的情况下输出日期和时间............................ 50

【示例2-4】timestamp日期类型在设置精度为7且加入时区的情况下
输出日期和时间.................................................................................................................. 51

【示例2-5】本地时区（即数据库时区）的timestamp with local time zone的用法................ 53

2.1.4 关于日期时间型timestamp和data以及字符串数据类型之间的转换与处理... 55

2.1.5 interval 时间间隔数据类型... 58

2.1.6 ROWID伪列数据类型... 65

2.1.7 blob、clob、nclob及bfile数据类型... 66

2.1.8 不同数据类型之间的转换约定... 66

2.2 DML语句.. 67

2.2.1 INSERT语句（插入操作）... 67

2.2.2 DELETE语句（删除操作）... 68

2.2.3 UPDATE语句（修改操作）... 69

2.3 事务控制语句.. 69

2.4 DDL语句.. 73

2.4.1 表的创建：CREATE语句... 73

2.4.2 修改表的结构：ALTER语句... 75

2.4.3 对象的删除和重命名操作... 77

2.5 Oracle数据库的约束.. 78

2.5.1 约束的类型... 78

2.5.2 如何在创建表时指定约束... 80

2.5.3 如何在创建表之后指定约束... 82

2.5.4 约束的维护... 83

2.6 Oracle数据库的视图.. 84

2.6.1 视图的创建、修改和删除... 85

2.6.2 如何对视图进行访问... 87

2.6.3 复杂视图... 88

2.7 Oracle数据库的索引.. 89

2.7.1 索引的基本概念... 89

2.7.2 索引的创建、修改和删除... 91

2.7.3 索引信息的查询... 93

2.7.4 索引使用原则... 93

2.8 Oracle数据库的序列.. 95

2.8.1 序列的创建、修改和删除... 95

2.8.2 序列的使用... 97

2.8.3 序列信息的查询... 98

2.9 Oracle数据库的同义词.. 100

2.9.1 同义词的概念和类型... 100

2.9.2 同义词的创建与删除... 100

2.9.3 同义词信息的查询... 101

2.10 本章小结.. 102

第3章 Oracle查询语句

3.1 查询语句的基本用法.. 104

3.1.1 SELECT语句的简单使用... 104

3.1.2 SQL查询使用的运算符... 106

3.2 查询语句中的条件.. 108

3.2.1 Oracle常用的关系运算符简介... 108

3.2.2 Oracle关系运算符的使用.... 109

【示例3-1】在SQL中通过“ESCAPE”关键字对特殊字符（%、_、双引号
及单引号等）进行转义，即还原要转义字符的原始属性.............................................. 110

3.3 Oracle查询语句中的单行函数.. 113

3.3.1 字符串处理函数... 113

3.3.2 数学函数... 116

3.3.3 日期型函数... 117

3.3.4 类型转换函数... 125

3.3.5 流程函数.... 130

3.4 分组函数与分组统计.. 144

3.4.1 AVG函数... 145

3.4.2 MIN与MAX函数... 145

3.4.3 COUNT 函数... 145

3.4.4 SUM函数... 146

3.4.5 分组统计... 146

3.5 Oracle数据库的排序.. 148

3.6 Oracle数据库的多表查询.. 149

3.7 Oracle数据库的子查询.. 152

3.8 Oracle数据库的连接查询.. 159

3.8.1 Oracle数据库内连接... 163

3.8.2 Oracle数据库外连接... 164

3.8.3 Oracle数据库自然连接... 168

3.8.4 Oracle数据库笛卡儿积和交叉连接... 168

3.8.5 Oracle数据库自连接... 168

3.9 Oracle数据库的集合运算.. 168

3.9.1 Oracle数据库的并集运算... 169

3.9.2 Oracle数据库的交集运算... 169

3.9.3 Oracle数据库的差集运算... 170

3.10 摘自应用项目中的典型示范样本.. 170

3.10.1 搭建数据环境... 171

3.10.2 SQL范本... 184

3.11 本章小结.. 209

第4章 Oracle over()函数的使用

4.1 over()分析函数.. 210

4.1.1 over()分析函数语法... 210

4.1.2 与over()配合使用的函数简要说明... 212

4.1.3 over()函数的非开窗示例... 213

【示例4-1】统计某商店的营业额....................................................................................................... 213

【示例4-2】统计各班成绩第一名的同学信息................................................................................. 214

【示例4-3】分类统计 (按某列分类，对某列求和并显示必要信息)........................................ 215

【示例4-4】分类统计（求个人工资占部门工资的百分比并显示必要信息）....................... 217

【示例4-5】求个人工资占部门工资的百分比................................................................................. 218

4.2 给over开窗.. 219

4.2.1 over()函数开窗说明... 219

4.2.2 over()函数开窗示例... 220




【示例4-6】形如“sum (列) over（order by 列 range between 数值型值
1 preceding and 数值型值2 following)”语法形式的开窗示例........................... 220

【示例4-7】形如“sum(aa) over（order by aa rows between 2 preceding and 4
following）”语法形式的开窗示例............................................................................... 221

4.3 与over()函数配合使用的函数.. 222

4.3.1 over()配合函数row_number()的使用... 223

4.3.2 over()配合函数rank()、dense_rank()及row_number()的使用... 225

4.3.3 over()配合函数count()的使用... 226

4.3.4 over()配合函数max()及min()的使用... 228

4.3.5 over()配合函数sum()的使用... 229

4.3.6 over()配合函数avg()的使用... 230

4.3.7 over()配合函数first_value()及last_value()的使用... 230

4.3.8 over()配合函数lead()及lag()的使用.... 231

4.4 本章小结.. 232

第二篇 Oracle用户权限及数据库对象管理

第5章 用户与权限管理

5.1 用户管理.. 234

5.1.1 数据库中有哪些用户... 234

5.1.2 如何创建用户... 235

5.1.3 如何修改用户的信息... 236

5.1.4 如何删除用户... 237

5.2 用户权限的管理.. 238

5.2.1 系统权限的管理... 238

5.2.2 对象权限的管理... 243

5.2.3 权限信息的查询... 246

5.3 角色的管理.. 248

5.3.1 角色的创建和删除... 248

5.3.2 角色中权限的添加和删除... 250

5.3.3 角色的分配和回收... 250

5.3.4 角色信息的查询... 250

5.4 PROFILE（用户配置文件）的管理.. 251

5.4.1 PROFILE的创建与删除... 252

5.4.2 如何利用PROFILE对用户口令进行控制... 257

5.4.3 如何利用PROFILE对用户使用资源进行控制... 257

5.4.4 默认的PROFILE. 258

5.5 本章小结.. 259

第6章 基本数据库对象管理

6.1 普通表的管理.. 260

6.1.1 普通表的结构... 260

6.1.2 普通表的创建... 262

6.1.3 普通表的修改... 265

6.1.4 普通表的删除... 266

6.2 分区表的管理.. 269

6.2.1 分区的概念... 269

6.2.2 范围分区... 270

【示例6-1】创建一个分区表call，用来记录用户的电话通话信息，包括主叫、
被叫、通话的年、月、日及时长，并且根据月进行分区..................................... 270

6.2.3 列表分区... 271

【示例6-2】创建一个产品销售记录表sales，记录产品的销售情况........................................ 271

6.2.4 散列分区... 272

【示例6-3】创建产品销售散列分区表.............................................................................................. 272

6.2.5 复合分区... 272

【示例6-4】创建产品销售复合分区表.............................................................................................. 273

6.2.6 非分区表转换为分区表的分区... 274

【示例6-5】通过交换分区（Partition exchange）实现把普通
表数据载入分区表........................................................................................................... 276

【示例6-6】通过Oracle的在线重定义包dbms_redefinition实现将非分区
表（普通表）数据载入分区表..................................................................................... 279

6.3 索引的管理.. 283

6.3.1 反向索引... 283

6.3.2 位图索引... 284

6.3.3 基于函数的索引... 285

6.3.4 分区索引... 286

6.3.5 索引的维护... 287

6.4 簇的管理.. 288

6.4.1 簇的创建... 289

6.4.2 簇的修改... 290

6.4.3 簇的删除... 291

6.4.4 簇信息的查询... 291

6.5 索引组织表的管理.. 292

6.5.1 索引组织表的创建... 293

6.5.2 索引组织表的维护... 293

6.6 本章小结.. 294

第三篇 Oracle数据库编程开发

第7章 PL/SQL编程

7.1 PL/SQL中的变量.. 297

7.1.1 变量的定义与使用... 297

【示例7-1】通过匿名块实现对变量id、name及birthday的声明、赋值和输出.................. 298

7.1.2 如何在PL/SQL中自定义数据类型... 300

【示例7-2】在PL/SQL块中自定义数据类型示例......................................................................... 300

【示例7-3】自定义集合类型变量简单使用..................................................................................... 303

【示例7-4】使用INDEX BY定义集合变量下标数据类型.......................................................... 304

【示例7-5】关联数组类型的集合变量与表结合并输出............................................................... 305

【示例7-6】嵌套表类型的集合变量与表结合并输出................................................................... 307

【示例7-7】可变长数组类型的集合变量与表结合并输出........................................................... 308

【示例7-8】可变长数组元素为对象类型的集合变量并与表结合输出..................................... 312

7.2 PL/SQL中的流控制.. 315

7.2.1 IF语句... 315

【示例7-9】要计算1+2+3+…+500的值，当和大于100 000时停止，
求“累计和”大于100 000的最后一个整数............................................................ 316

7.2.2 LOOP语句... 316

【示例7-10】计算1+2+3+…的值，当和大于500时停止，求“累计和”
大于500的最后一个整数............................................................................................ 317

7.2.3 WHILE语句... 317

【示例7-11】求表达式1+2+3+…+ 100的值（请用WHILE语句实现）................................ 317

7.2.4 FOR语句... 318

【示例7-12】求表达式1+2+3+…+100的值（请用FOR语句实现）...................................... 318

7.3 PL/SQL如何访问数据库.. 319

7.3.1 如何对数据进行查询... 319

【示例7-13】通过select into把查询到的数据存放在变量中并进行处理................................ 319

7.3.2 如何使用DML语句... 321

7.4 子程序设计.. 323

7.4.1 如何使用过程... 323

【示例7-14】使用存储过程把外部参数传递到内部使用............................................................. 324

7.4.2 如何使用函数... 327

【示例7-15】在匿名块里调用自定义函数income()及tax()........................................................ 328

【示例7-16】PL/SQL块的变量和函数变量的关系........................................................................ 329

7.4.3 函数与过程的重载... 330

【示例7-17】定义两个重载函数，分别对整数8和浮点数8.8求10次幂............................. 331

【示例7-18】通过重载存储过程increase_salary更新emp表.................................................... 332

7.4.4 函数与过程的递归调用... 333

【示例7-19】通过递归调用，求整数m（m=10）的阶乘，即10*9*8*7*6*5*4*3*2*1
的结果............................................................................................................................... 334

【示例7-20】通过存储过程的递归调用（即反复使用）得到某个员工的经理以及
该经理的更上一级领导的信息................................................................................... 335

7.5 存储过程与存储程序.. 336

7.5.1 存储过程... 337

【示例7-21】输出存储过程返回的zongsr（总收入）及zongsj（总税款）结果.................. 339

【示例7-22】在存储过程中以重载的形式加入子存储过程，经过一系列处理后，
最后返回zongsr（总收入）及zongsj（总税款）结果........................................ 340

7.5.2 存储函数... 342

【示例7-23】在存储函数中以重载的形式加入子存储过程，经过一系列处理后，最后
返回dno（应缴纳所得税款的部门）及total_tax（总税款）结果................... 343

7.5.3 程序包... 345

7.5.4 系统预定义程序包... 348

【示例7-24】dbms_output包中的put()、put_line()及get_line()等方法
使用简单示例.................................................................................................................. 350

【示例7-25】通过 dbms_sql包执行DDL（CREATE、DROP及ALTER等）
命令.................................................................................................................................... 356

7.5.5 与存储程序有关的数据字典... 358

7.6 异常处理.. 361

7.6.1 异常处理程序... 361

7.6.2 预定义异常... 362

【示例7-26】通过SQLCODE和SQLERRM这两个函数提示错误代码及
错误信息........................................................................................................................... 365

【示例7-27】将错误信息写入错误日志表....................................................................................... 366

7.6.3 非预定义异常... 368

7.6.4 用户自定义异常... 369

【示例7-28】编写一个PL/SQL块，求1+2+3+…100的值。在求和的过程中
如果发现结果超出了1 000，则抛出异常，并停止求和..................................... 370

7.6.5 异常的传递... 373

7.7 游标的应用.. 378

7.7.1 隐式游标... 378

7.7.2 显式游标... 379

【示例7-29】通过游标从emp表中检索员工7902的姓名、工资、工作时间....................... 381

【示例7-30】通过使用显式游标属性来处理例外情况................................................................. 383

7.7.3 带参数的游标... 386

【示例7-31】通过使用带参数的游标输出scott账户下emp表满足游标参数（形参）
设定值（实参）的记录................................................................................................ 387

7.7.4 如何通过游标修改表中的数据... 389

7.8 触发器.. 391

7.8.1 触发器的使用... 392

7.8.2 语句级触发器... 394

7.8.3 行触发器... 396

【示例7-32】创建一个比较复杂的触发器，这个触发器对表emp上的
DML操作进行监视。................................................................................................... 397

7.8.4 视图上的触发器... 401

7.8.5 与触发器有关的数据字典... 403

7.9 本章小结.. 404

第四篇 Oracle数据库的卸出与复原

第8章 Oracle数据库的导入/导出及闪回

8.1 导入/导出工具的用法.. 406

8.2 表的导入与导出.. 407

8.3 Oracle数据库导入/导出模式.. 409

8.4 表空间的导入与导出.. 410

8.5 Oracle数据泵的使用.. 412

8.5.1 为何选择数据泵方式... 412

8.5.2 目标库环境准备... 413

8.5.3 Oracle数据泵导入/导出操作... 416

8.5.4 expdp（导出）及impdp（导入）命令参数说明... 419

8.5.5 错误及处理... 425

8.5.6 通过dblink来使用数据泵... 426

8.6 Oracle 11g exp不能导出空表的解决方法.. 427

8.7 闪回（Flashback）技术在数据库恢复中的应用.. 428

8.7.1 回收站的应用... 429

8.7.2 闪回技术在表上的应用... 430

8.7.3 Flashback技术在数据库恢复中的应用... 433

8.8 本章小结.. 434