Oracle PL/SQL从入门到精通 内容简介

《Oracle PL/SQL从入门到精通》以面向应用为原则，深入浅出的介绍了oracle平台上使用pl/sql语言进行数据库开发的技术。通过大量的示例，详细介绍了pl/sql的语言特性、使用技巧，同时配以2个在实际工作中的示例来深入的剖析了pl/sql进行oracle开发的方方面面。

《Oracle PL/SQL从入门到精通》共分为20章，分为5大篇。涵盖的内容主要有pl/sql语言基础、开发环境、变量与类型、控制语句、数据表的管理和查询、数据表的操纵、使用pl/sql的记录与集合、各种内置函数、游标、事务处理、异常处理、子程序、包、面向对象的开发等等技术点。通过示例性的代码，由浅入深，详细介绍了每一个技术要点在实际工作中的应用，对于各种技术要点的应用场合进行了细致的分析。

《Oracle PL/SQL从入门到精通》适合于使用pl/sql进行应用程序开发的人员、对软件开发有兴趣的学生及爱好者；对数据库管理员、企业it运维人员也具有很强的指导作用。

Oracle PL/SQL从入门到精通 目录

第1篇 pl/sql开发入门

第1章 oracle 11g数据库系统

1.1 关系型数据库系统介绍

1.1.1 什么是关系型数据模型

1.1.2 数据库系统范式

1.1.3 关系型数据库管理系统

1.1.4 使用sql语句与数据库管理系统通信

1.2 初识oracle 11g

1.2.1 oracle 11g简介

1.2.2 oracle 11g体系结构

1.2.3 如何创建数据库

1.2.4 比较oracle数据库与sql server数据库

1.3 什么是pl/sql

1.3.1 pl/sql是一种语言

1.3.2 pl/sql的执行环境

1.4 搭建pl/sql开发环境

1.4.1 使用sql*plus

1.4.2 使用oracle sql developer

1.4.3 pl/sql developer开发pl/sql

1.4.4 quest toad开发pl/sql

1.4.5 使用oracle文档库

1.5 小结

第2章 pl/sql基本概念

2.1 功能特点

2.1.1 结构化程序设计

2.1.2 与sql语言整合

2.1.3 面向对象开发

2.1.4 模块化应用程序开发

2.1.5 提高应用程序性能

2.2 语言特性

2.2.1 pl/sql块结构

2.2.2 变量和类型

2.2.3 程序控制语句

2.2.4 过程、函数与包

2.2.5 触发器

2.2.6 结构化异常处理

2.2.7 集合与记录

2.2.8 游标

2.2.9 动态sql

2.3 编码风格

2.3.1 pl/sql词法单位

2.3.2 缩进

2.3.3 标识符命名规则

2.3.4 大小写风格

2.3.5 使用工具格式化代码

2.4 小结

第3章 变量和类型

3.1 变量

3.1.1 变量的声明

3.1.2 变量的赋值

3.1.3 使用%type

3.1.4 使用%rowtype

3.1.5 变量的作用域和可见性

3.1.6 常量的定义

3.2 数据类型

3.2.1 字符类型

3.2.2 数字类型

3.2.3 日期和时间类型

3.2.4 布尔类型

3.2.5 lob对象类型

3.2.6 引用类型

3.2.7 复合类型

3.2.8 用户自定义子类型

3.2.9 数据类型转换

3.3 运算符和表达式

3.3.1 运算符类型

3.3.2 运算符的优先级

3.3.3 表达式类型

3.4 小结

第4章 pl/sql控制语句

4.1 分支控制语句

4.1.1 if-then-else语句

4.1.2 if-then-elsif语句

4.1.3 case语句

4.1.4 搜索case语句

4.2 循环控制语句

4.2.1 loop循环

4.2.2 使用exit退出循环

4.2.3 使用exit-when退出循环

4.2.4 使用continue继续执行循环

4.2.5 while-loop循环

4.2.6 for-loop循环

4.2.7 循环语句使用建议

4.3 顺序控制语句

4.3.1 goto语句和标签

4.3.2 null语句

4.4 小结

第2篇 pl/sql开发基础

第5章 管理数据表

5.1 创建表

5.1.1 数据定义语言ddl

5.1.2 create table语句

5.1.3 在设计器中创建表

5.1.4 创建表副本

5.2 创建约束

5.2.1 创建主键约束

5.2.2 创建外键约束

5.2.3 创建检查约束

5.2.4 查看表约束

5.3 修改表

5.3.1 修改表列

5.3.2 修改约束

5.3.3 移除数据表

5.3.4 在设计器中修改表

5.4 索引

5.4.1 索引简介

5.4.2 索引原理

5.4.3 创建索引

5.4.4 修改索引

5.4.5 删除索引

5.5 使用视图

5.5.1 视图简介

5.5.2 创建视图

5.5.3 修改视图

5.5.4 删除视图

5.6 小结

第6章 查询数据表

6.1 简单查询

6.1.1 查询表数据

6.1.2 指定查询条件

6.1.3 排序

6.1.4 使用函数

6.1.5 统计函数

6.1.6 分组统计

6.1.7 having子句

6.1.8 使用dual表

6.1.9 rownum伪列

6.1.10 rowid伪列

6.2 复杂查询

6.2.1 多表连接查询

6.2.2 使用子查询

6.2.3 表集合操作

6.2.4 层次化查询

6.3 小结

第7章 操纵数据表

7.1 插入记录

7.1.1 数据操纵语言dml

7.1.2 插入单行记录

7.1.3 插入默认值和null值

7.1.4 使用子查询插入多行数据

7.1.5 使用insert插入多表数据

7.2 更新记录

7.2.1 更新单行记录

7.2.2 使用子查询更新记录

7.2.3 使用merge合并表行

7.3 删除记录

7.3.1 删除单行记录

7.3.2 使用子查询删除记录

7.3.3 使用truncate清除表数据

7.4 提交和回滚记录

7.4.1 提交更改

7.4.2 回滚更改

7.5 使用序列

7.5.1 序列简介

7.5.2 创建数据序列

7.5.3 nextval和currval伪列

7.5.4 使用数据序列

7.5.5 修改序列

7.5.6 删除序列

7.6 同义词

7.6.1 同义词简介

7.6.2 创建和使用同义词

7.7 小结

第8章 记录与集合

8.1 记录类型

8.1.1 记录类型简介

8.1.2 定义记录类型

8.1.3 记录类型赋值

8.1.4 操纵记录类型

8.1.5 使用嵌套记录

8.2 理解集合类型

8.2.1 集合简介

8.2.2 定义索引表

8.2.3 操纵索引表

8.2.4 定义嵌套表

8.2.5 操纵嵌套表

8.2.6 数据库中的嵌套表

8.2.7 定义变长数组

8.2.8 操纵变长数组

8.2.9 数据库中的变长数组

8.2.10 选择集合类型

8.3 使用集合方法

8.3.1 使用exists方法

8.3.2 使用count方法

8.3.3 使用limit方法

8.3.4 first和last方法

8.3.5 prior和next方法

8.3.6 extend方法

8.3.7 trim方法

8.3.8 delete方法

8.3.9 集合的异常处理

8.3.10 使用批量绑定

8.3.11 使用bulk collect

8.4 小结

第9章 sql内置函数

9.1 基本函数

9.1.1 字符型函数

9.1.2 数字型函数

9.1.3 日期时间函数

9.1.4 类型转换函数

9.1.5 分组函数

9.1.6 其他函数

9.2 oracle分析函数

9.2.1 什么是分析函数

9.2.2 基本语法

9.2.3 分析函数结构

9.2.4 分析函数列表

9.3 分析函数使用示例

9.3.1 记录排名

9.3.2 首尾记录查询

9.3.3 前后排名查询

9.3.4 层次查询

9.3.5 范围统计查询

9.3.6 相邻记录比较

9.3.7 抑制重复

9.3.8 行列转换查询

9.3.9 在pl/sql中使用分析函数

9.4 小结

第10章 使用游标

10.1 游标基本结构

10.1.1 游标简介

10.1.2 游标分类

10.1.3 定义游标类型

10.1.4 打开游标

10.1.5 使用游标属性

10.1.6 提取游标数据

10.1.7 批量提取游标数据

10.1.7 关闭游标

10.2 操纵游标数据

10.2.1 loop循环

10.2.2 while循环

10.2.3 游标for循环

10.2.4 修改游标数据

10.3 游标变量

10.3.1 游标变量简介

10.3.2 声明游标变量类型

10.3.3 定义游标变量

10.3.4 打开游标变量

10.3.5 控制游标变量

10.3.7 处理游标变量异常

10.3.8 在包中使用游标变量

10.3.9 游标变量的限制

10.4 小结

第11章 事务处理和锁定

11.1 事务处理简介

11.1.1 什么是事务处理

11.1.2 使用commit提交事务

11.1.3 使用rollback回滚事务

11.1.4 使用savepoint保存点

11.1.5 使用set transaction设置事务属性

11.2 使用锁定

11.2.1 理解锁定

11.2.2 记录锁定

11.2.3 表锁定

11.2.4 使用lock table

11.3 小结

第12章 异常处理机制

12.1 理解异常处理

12.1.1 异常处理简介

12.1.2 异常处理语法

12.1.3 预定义异常

12.2 自定义异常

12.2.1 声明异常

12.2.2 作用域范围

12.2.3 使用exception_init

12.2.4 使用raise_application_error

12.2.5 抛出异常

12.2.6 处理异常

12.2.7 使用sqlcode和sqlerrm

12.3 异常的传递

12.3.1 执行时异常传递

12.3.2 声明时异常传递

12.3.3 异常处理器中的异常

12.3.4 重新抛出异常

12.3.5 异常处理准则

12.4 小结

第3篇 pl/sql进阶编程

第13章 pl/sql子程序

13.1 子程序结构

13.1.1 子程序简介

13.1.2 子程序的优点

13.1.3 创建过程

13.1.4 创建函数

13.1.5 return语句

13.1.6 查看和删除子程序

13.2 子程序参数

13.2.1 形参与实参

13.2.2 参数模式

13.2.3 形式参数的约束

13.2.4 参数传递方式

13.2.5 参数默认值

13.2.6 使用nocopy编译提示

13.3 子程序进阶技术

13.3.1 在sql中调用子程序

13.3.2 嵌套子程序

13.3.3 子程序的前向声明

13.3.4 重载子程序

13.3.5 子程序自治事务

13.3.6 递归调用子程序

13.3.7 理解子程序依赖性

13.3.8 子程序权限管理

13.4 小结

第14章 包

14.1 理解pl/sql包

14.1.1 什么是包

14.1.2 包的优点

14.1.3 定义包规范

14.1.4 定义包体

14.1.5 调用包组件

14.1.6 编译和调试包

14.1.7 查看包的源代码

14.2 包的进阶技术

14.2.1 包重载

14.2.2 包初始化

14.2.3 包的纯度级别

14.2.4 包权限设置

14.2.5 在包中使用游标

14.3 管理数据库中的包

14.3.1 查看和删除包

14.3.2 检查包的依赖性

14.4 使用系统包

14.4.1 使用dbms_output包

14.4.2 使用dbms_pipe包

14.4.3 使用dbms_alter包

14.4.4 使用dbms_job包

14.5 小结

第15章 触发器

15.1 理解触发器

15.1.1 触发器简介

15.1.2 定义触发器

15.1.3 触发器的分类

15.2 dml触发器

15.2.1 触发器的执行顺序

15.2.2 定义dml触发器

15.2.3 调试触发器

15.2.4 使用语句触发器

15.2.5 使用old和new谓词

15.2.6 使用referencing子句

15.2.7 使用when子句

15.2.8 使用条件谓词

15.2.9 控制触发顺序

15.2.10 触发器限制

15.2.11 使用自治事务

15.3 替代触发器

15.3.1 替代触发器的作用

15.3.2 定义替代触发器

15.3.3 update与delete替代触发器

15.3.4 嵌套表替代触发器

15.4 系统事件触发器

15.4.1 定义系统触发器

15.4.2 触发器事件列表

15.4.3 触发器属性列表

15.4.4 属性函数使用示例

15.4.5 定义servererror触发器

15.4.6 触发器的事务与约束

15.5 触发器的管理

15.5.1 查看触发器源代码

15.5.2 删除和禁用触发器

15.5.3 名称与权限的管理

15.6 小结

第16章 动态sql语句

16.1 理解动态sql语句

16.1.1 动态sql基础

16.1.2 动态sql使用时机

16.1.3 本地动态sql

16.2 使用execute immediate

16.2.1 execute immediate语法

16.2.2 执行sql语句和pl/sql语句块

16.2.3 使用绑定变量

16.2.4 使用returnning into子句

16.2.5 执行单行查询

16.2.6 指定参数模式

16.3 多行查询语句

16.3.1 使用open-for语句

16.3.2 使用fetch语句

16.3.3 关闭游标变量

16.4 使用批量绑定

16.4.1 批量execute immediate语法

16.4.2 使用批量fetch语句

16.4.3 使用批量forall语句

16.5 动态sql的使用建议

16.5.1 用绑定变量改善性能

16.5.2 使用重复占位符

16.5.3 使用调用者权限

16.5.4 传递null参数

16.5.5 动态sql异常处理

16.6 小结

第4篇 pl/sql高级编程

第17章 面向对象编程

17.1 对象基础

17.1.1 面向对象简介

17.1.2 什么是对象类型

17.1.3 pl/sql中对象的组成结构

17.2 定义对象类型

17.2.1 定义对象类型

17.2.2 定义对象体

17.2.3 定义属性

17.2.4 定义方法

17.2.5 使用self关键字

17.2.6 定义构造函数

17.2.7 定义map和order方法

17.2.8 使用对象类型

17.2.9 使用嵌套对象类型

17.2.10 对象继承

17.2.11 方法重载

17.3 管理对象表

17.3.1 定义对象表

17.3.2 插入对象表

17.3.3 检索对象表

17.3.4 更新对象表

17.3.5 删除对象表

17.3.6 创建对象列

17.3.7 使用对象视图

17.4 管理对象类型

17.4.1 查看对象类型

17.4.2 修改对象类型

17.5 小结

第18章 pl/sql性能优化建议

18.1 了解pl/sql程序性能

18.1.1 影响性能常见原因

18.1.2 使用dbms_profiler包

18.1.3 使用dbms_trace包

18.2 pl/sql性能优化技巧

18.2.1 理解查询执行计划

18.2.2 联接查询的表顺序

18.2.3 指定where条件顺序

18.2.4 避免使用*符号

18.2.5 使用decode函数

18.2.6 使用where而非having

18.2.7 使用union 而非or

18.2.8 使用exists而非in

18.2.9 避免低效的pl/sql流程控制语句

18.2.10 避免隐式类型的转换

18.3 小结

第5篇 pl/sql案例实战

第19章 企业ic芯片欠料计算程序

19.1 系统设计

19.1.1 程序需求简介

19.1.2 数据表er关系图

19.1.3 系统总体流程

19.1.4 示例环境的搭建

19.2 系统编码实现

19.2.1 创建包规范

19.2.2 初始化数据

19.2.3 获取ic需求量

19.2.4 ic物料检查函数

19.2.5 获取已走货ic数量

19.2.6 获取销售订单数量

19.2.7 计算企业ic需求量

19.2.8 预备下次计算数据

19.2.9 定义调用主程序

19.3 调试和部署应用程序

19.3.1 编译应用程序

19.3.2 调试应用程序

19.3.3 查看程序结果

19.3.4 部署到生产服务器

19.4 小结

第20章 pl/sql邮件发送程序

20.1 系统设计

20.1.1 程序需求简介

20.1.2 使用utl_ smtp发送电子邮件

20.1.3 系统总体流程

20.1.4 示例环境的搭建

20.2 系统编码实现

20.2.1 认识mime类型

20.2.2 实现mime类型邮件发送

20.2.3 定义包规范

20.2.4 邮件初始化函数xm_init

20.2.5 发送并关闭连接xm_close

20.2.6 发送html邮件

20.2.7 发送邮件附件

20.2.8 发送excel附件内容

20.2.9 写入工作薄wb_header

20.2.10 写入工作表xm_worksheet

20.2.11 写入表格行xm_ws_row

20.2.12 写入工作表尾信息

20.2.13 执行sql语句写入工作表

20.3 编译和部署应用程序

20.3.1 编译与调试应用程序

20.3.2 验证测试结果

20.3.3 部署到生产服务器

20.4 小结

Oracle PL/SQL从入门到精通 精彩文摘

第1章Oracle11g数据库系统

Oracle数据库系统是世界领先的数据库管理系统，Oracle数据库以其功能强大和配置灵活而著称，同时也因有一定的操作难度，让很多初学者望而却步。本书将由浅入深，以简单易懂的示例带领读者拨开Oracle的神秘面纱。

目前在Oracle世界主要有两类人员：一类是Oracle数据库管理人员，简称DBA；一类是Oracle开发人员。OracleDBA主要的工作是负责日常的数据库维护和性能优化管理。由于Oracle系统较庞大、复杂，要成为一名合格的DBA，需要掌握的知识较多，比如要掌握Oracle体系结构和性能优化等方面的知识，相对其他数据库而言入门门槛较高，但是薪酬一般也较丰厚。Oracle开发人员的主要工作是使用Oracle提供的SQL语言和PL/SQL结构化程序设计语言操作数据库，主要职责是操纵Oracle数据库对象，不需要对Oracle系统结构有深入了解，入门较容易。当Oracle开发人员积累了一定的开发知识后，可以通过学习OracleDBA方面的知识向数据库管理员转型。

1.1关系型数据库系统介绍

1970年6月，IBM公司的研究员E.F.Codd博士（中文名：埃德加·弗兰克·科德），发表了名为“大型共享数据库的关系模型”的论文，受到了学术界和产业界的高度重视和广泛响应，使得关系型数据库系统很快成为数据库市场的主流。E.F.Codd博士被誉为“关系数据库之父”，其照片如图1.1所示。

图1.1关系数据库之父埃德加·弗兰克·科德

1.1.1什么是关系型数据模型

关系型数据库简而言之就是使用关系或二维表存储信息。以公司的人员信息管理为例，为了存储公司内部的员工信息，人事部门一般会建一份列表，在公司未引入信息化管理系统时，大多数人事职员会使用Excel来保存员工信息，例如图1.2是一份简单的人员信息列表的Excel文件。

这种Excel存储数据的方式，将人员的所有信息都包含在一张表中，随着Excel中的栏位和记录数越来越多，这份人员信息表会变得越来越繁杂，这种存储数据的方式称为平面文件数据模型。

为了简化修改与维护的复杂性，关系型数据库设计人员通过使用实体关系模型进行数据库建模，例如人员信息表可以分为员工表和部门表，通过部门编号进行关联，ER模型如图1.3所示。

图1.2Excel人员信息列表图1.3人员信息表ER关系模型

由图1.3中可以看到，通过将员工和部门分别存储在不同的二维表格中，使用主键（PK）和外键（FK）进行关联，使得获取和维护数据变得更容易，这就是关系型数据模型。上述ER图的3个关键组件分别如下所示。

*实体：需要了解的信息，比如部门和员工信息。

*属性：一般也称为列或字段，描述实体必须或可选的信息，比如员工表中的工号和姓名等。

*关系：实体之间指定的关联，比如员工的部门编号关联到了部门表的编号属性。

关系型数据模型还涉及一些较复杂的组成元素，涉及较多的数学知识，有兴趣的读者可以参考一些理论性的读物。

1.1.2数据库系统范式

为了规范化关系型数据模型，关系型数据库系统在设计时必须遵循一定的规则，这种规则称为关系型数据库系统范式。了解范式是每个数据库设计或开发人员必须具备的基本功，范式的主要目的是降低数据冗余，设计结构合理的数据库。目前较常用的范式有如下3种。

1．第一范式（1NF）：字段必须具有单一属性特性，不可再拆分

如果字段中的值已经是无法再分割的值，则符合第一范式，即1NF。例如，在员工表中，姓名字段一般仅包含员工的正式姓名，这是符合第一范式的，但是如果要在姓名字段中包含中文名、英文名、昵称、别名等信息，就意味着姓名字段是可再拆分的。因此员工名的设计可以如图1.4所示。

图1.4修改后的员工表以匹配1NF范式

2．第二范式（2NF）：表要具有唯一性的主键列

第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分，为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。第二范式是在第一范式的基础上的进一步增强，在数据库设计时一般使用唯一性主键来唯一地标识行。比如在员工表中定义了以工号作为主键，因为公司员工的工号通常用来识别某个员工个体，不能进行重复；在部门表中通过部门编号作为主键，来唯一地区分一个部门。

3．第三范式（3NF）：表中的字段不能包含在其他表中已出现的非主键字段

第三范式（3NF）是在前两个范式的基础上的进一步增强，主要用来降低数据的冗余。比如，员工表中包含了部门编号，它引用到部门表中的部门编号这个主键，符合第三范式。如果在员工表中又包含一个部门名称，那么表中的字段就包含了其他表中已出现的非主键字段，造成了数据的冗余，不符合第三范式。

范式主要用来规范数据库的设计，使得设计出来的数据库结构清晰，简洁易懂，避免了数据冗余和操作的异常。在设计数据库模型时，灵活地应用范式是创建一个优秀的数据库系统的基石。

1.1.3关系型数据库管理系统

关系型数据库管理系统，简称DBMS，是基于关系型数据库理论而开发的软件系统。目前比较热门的关系型数据管理系统有：Oracle、MicrosoftSQLServer、Access、MySQL及PostgreSQL等。数据库管理系统是用于建立、使用和维护数据库，对数据库进行统一的管理和控制，保证数据库的安全性和完整性的一套大型的电脑程序。数据库管理系统功能结构示意图如图1.5所示。

图1.5数据库管理系统功能结构示意图

如图1.5所示，一个数据库管理系统通常要提供如下所示的几项功能。

*定义数据库结构：DBMS提供数据定义语言来定义（DDL）数据库结构，用来搭建数据库框架，并被保存在数据字典中。

*存取数据库内容：DBMS提供数据操纵语言（DML），实现对数据库数据的基本存取操作，即检索、插入、修改和删除等。

*数据库的运行管理：DBMS提供数据控制功能，即数据的安全性、完整性和并发控制等，对数据库运行进行有效的控制和管理，以确保数据正确有效。

*数据库的建立和维护：包括数据库初始数据的装入，数据库的转储、恢复、重组织，系统性能监视、分析等功能。

*数据库的传输：DBMS提供处理数据的传输，实现用户程序与DBMS之间的通信，通常与操作系统协调完成。

有了关系型数据库管理系统，开发人员就可以在数据库中创建数据库、创建表、存取数据库内容、对数据库进行备份和管理，只需要理解常用的系统相关的操作，而不用去研究关系型数据库系统内部深奥难懂的数据方面的理论知识。

1.1.4使用SQL语句与数据库管理系统通信

关系型数据库管理系统提供了SQL语言，允许用户操纵数据库。SQL语言的全称是结构化查询语言（StructuredQueryLanguage），它是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解其具体的数据存放方式，其操作示意图如图1.6所示。

尽管SQL语言已经被ISO组织定义了具有国际标准的SQL规范，但是各种数据库系统厂商在其数据库管理系统中都对SQL规范做了某些编改和扩充。所以，实际上不同数据库系统之间的SQL不能完全相互通用，目前比较流行的两大类SQL语言分别是微软的T-SQL和Oracle的PL/SQL，这两类SQL既有相似之处又有不同之处。

图1.6使用SQL操作数据库管理系统

SQL语言主要又分为如下两大类。

*DML数据操纵语言，主要是完成数据的增、删、改和查询的操作。

*DDL数据定义语言，主要用来创建或修改表、视图、存储过程及用户等。

除此之外，还包含称为DCL的数据控制语言。数据库管理员，即DBA，通常使用DDL来管理数据库的对象，而数据操纵语言DML则主要由数据库开发人员使用来操纵数据。

举个例子，如果想要在数据库中创建如图1.4所示的关系模型，可以使用代码1.1的DDL语句来实现。

代码1.1使用DDL语句创建列和键

--创建员工表

CREATETABLE员工表

(--定义员工表列

工号INTNOTNULL,

中文姓名NVARCHAR2(20)NOTNULL,

英文姓名VARCHAR2(20)NULL,

别名VARCHAR2(20)NULL,

年龄INTDEFAULT18,

入职日期DATENULL,

部门编号INTNULL,

--定义员工表主键

CONSTRAINTPK_员工表PRIMARYKEY(工号）