PL/SQL 程序设计
目 录
第一章
PL/SQL 程序设计简介 ............................................................................................................................ 3
§1.2 SQL 与 PL/SQL ............................................................................................................................................... 3
§1.2.1 什么是 PL/SQL? ..................................................................................................................................... 3
§1.2.1 PL/SQL 的好处 ....................................................................................................................................... 3
§1.2.2 PL/SQL 可用的 SQL 语句 ........................................................................................................................ 4
§1.3 运行 PL/SQL 程序 .......................................................................................................................................... 4
第二章 PL/SQL 块结构和组成元素 ....................................................................................................................... 5
§2.1 PL/SQL 块 ...................................................................................................................................................... 5
§2.2 PL/SQL 结构 .................................................................................................................................................. 5
§2.3 标识符........................................................................................................................................................... 5
§2.4 PL/SQL 变量类型 .......................................................................................................................................... 6
§2.4.1 变量类型 ................................................................................................................................................. 6
§2.4.2 复合类型 ............................................................................................................................................... 7
§2.4.3 使用%ROWTYPE ........................................................................................................................................ 9
§2.4.4 PL/SQL 表(嵌套表) .................................................................................................................................... 9
§2.5 运算符和表达式(数据定义) .........................................................................................................................10
§2.5.1 关系运算符 ............................................................................................................................................10
§2.5.2 一般运算符 ............................................................................................................................................10
§2.5.3 逻辑运算符 ............................................................................................................................................11
§2.6 变量赋值 ......................................................................................................................................................11
§2.6.1 字符及数字运算特点 .............................................................................................................................11
§2.6.2 BOOLEAN 赋值 ......................................................................................................................................11
§2.6.3 数据库赋值 ............................................................................................................................................11
§2.6.4 可转换的类型赋值 ................................................................................................................................12
§2.7 变量作用范围及可见性 ...............................................................................................................................12
§2.8 注释 .............................................................................................................................................................12
§2.9 简单例子 ......................................................................................................................................................13
§2.9.1 简单数据插入例子...............................................................................................................................13
§2.9.2 简单数据删除例子...............................................................................................................................13
第三章 PL/SQL 流程控制语句 ............................................................................................................................... 14
§3.1 条件语句 ......................................................................................................................................................14
§3.2 CASE 表达式 ................................................................................................................................................15
§3.3 循环 .............................................................................................................................................................15
§3.3 标号和 GOTO .................................................................................................................................................17
§3.4 NULL 语句 .....................................................................................................................................................18
第四章 游标的使用 ............................................................................................................................................... 19
§4.1 游标概念 .........................................................................................................................................................19
§4.1.1 处理显式游标 ..........................................................................................................................................19
§4.1.2 处理隐式游标 ..........................................................................................................................................23
- 1 -
§4.1.3 关于 NO_DATA_FOUND 和 %NOTFOUND 的区别 ..................................................................................24
§4.1.4 游标修改和删除操作 .............................................................................................................................24
第五章 异常错误处理 ....................................................................................................................................... 26
§5.1 异常处理概念 .................................................................................................................................................26
§5.1.1 预定义的异常处理 ..................................................................................................................................26
§5.1.2 非预定义的异常处理............................................................................................................................... 27
§5.1.3 用户自定义的异常处理 ...........................................................................................................................28
§5.2 在 PL/SQL 中使用 SQLCODE, SQLERRM.........................................................................................................29
第六章 存储函数和过程 ....................................................................................................................................... 31
§6.1 引言 ...............................................................................................................................................................31
§6.2 创建函数 .......................................................................................................................................................31
§6.3 存储过程 .......................................................................................................................................................35
§6.3.1 创建过程 ................................................................................................................................................35
§6.3.2 调用存储过程 ........................................................................................................................................36
§6.3.3 AUTHID ................................................................................................................................................... 37
§6.3.4 开发存储过程步骤 ................................................................................................................................38
§6.3.5 删除过程和函数 ....................................................................................................................................38
第七章 包的创建和应用 ....................................................................................................................................... 40
§7.1 引言 ...............................................................................................................................................................40
§7.2 包的定义 .......................................................................................................................................................40
§7.3 包的开发步骤 ................................................................................................................................................41
§7.4 包定义的说明 ................................................................................................................................................41
§7.5 子程序重载 ...................................................................................................................................................49
§7.6 删除包 ...........................................................................................................................................................51
§7.7 包的管理 .......................................................................................................................................................51
第八章 触发器 ....................................................................................................................................................... 52
§8.1 触发器类型 ...................................................................................................................................................52
§8.1.1 DML 触发器 ..............................................................................................................................................52
§8.1.2 替代触发器 ..............................................................................................................................................52
§8.1.3 系统触发器 ..............................................................................................................................................52
§8.2 创建触发器 ...................................................................................................................................................53
§8.2.1 触发器触发次序 ......................................................................................................................................54
§8.2.2 创建 DML 触发器 .....................................................................................................................................54
§8.2.3 创建替代(INSTEAD OF)触发器 .................................................................................................................54
§8.2.3 创建系统事件触发器...............................................................................................................................56
§8.2.4 系统触发器事件属性...............................................................................................................................56
§8.2.5 使用触发器谓词 ...................................................................................................................................... 57
§8.2.6 重新编译触发器 ...................................................................................................................................... 57
§8.3 删除和使能触发器 ........................................................................................................................................ 57
- 2 -
第一章 PL/SQL 程序设计简介
PL/SQL是一种高级数据库程序设计语言,该语言专门用于在各种环境下对ORACLE数据库进行访问。由
于该语言集成于数据库服务器中,所以PL/SQL代码可以对数据进行快速高效的处理。除此之外,可以在
ORACLE数据库的某些客户端工具中,使用PL/SQL语言也是该语言的一个特点。本章的主要内容是讨论引入
PL/SQL语言的必要性和该语言的主要特点,以及了解PL/SQL语言的重要性和数据库版本问题。还要介绍一些
贯穿全书的更详细的高级概念,并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。
本章主要重点:
PL/SQL 概述
PL/SQL 块结构
PL/SQL 流程
运算符和表达式
游标
异常处理
数据库存储过程和函数
包
触发器
§1.2 SQL 与 PL/SQL
§1.2.1 什么是 PL/SQL?
PL/SQL 是 Procedure Language & Structured Query Language 的 缩写。 ORACLE 的 SQL 是 支持
ANSI(American national Standards Institute)和 ISO92 (International Standards Organization)标准的产品。PL/SQL
是对 SQL 语言存储过程语言的扩展。从 ORACLE6 以后,ORACLE 的 RDBMS 附带了 PL/SQL。它现在已经成为
一种过程处理语言,简称 PL/SQL。目前的 PL/SQL 包括两部分,一部分是数据库引擎部分;另一部分是可嵌
入到许多产品(如 C 语言,JAVA 语言等)工具中的独立引擎。可以将这两部分称为:数据库 PL/SQL 和工具
PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具 PL/SQL 另外还增加了用于支持工
具(如 ORACLE Forms)的句法,如:在窗体上设置按钮等。本章主要介绍数据库 PL/SQL 内容。
§1.2.1 PL/SQL 的好处
§1.2.1.1 有利于客户/服务器环境应用的运行
对于客户/服务器环境来说,真正的瓶颈是网络上。无论网络多快,只要客户端与服务器进行大量的数
据交换。应用运行的效率自然就回受到影响。如果使用 PL/SQL 进行编程,将这种具有大量数据处理的应用
放在服务器端来执行。自然就省去了数据在网上的传输时间。
§1.2.1.2 适合于客户环境
PL/SQL 由于分为数据库 PL/SQL 部分和工具 PL/SQL。对于客户端来说,PL/SQL 可以嵌套到相应的工具中,
- 3 -
客户端程序可以执行本地包含 PL/SQL 部分,也可以向服务发 SQL 命令或激活服务器端的 PL/SQL 程序运行。
§1.2.2 PL/SQL 可用的 SQL 语句
PL/SQL 是 ORACLE 系统的核心语言,现在 ORACLE 的许多部件都是由 PL/SQL 写成。在 PL/SQL 中可以使
用的 SQL 语句有:
INSERT,UPDATE,DELETE,SELECT … INTO,COMMIT,ROLLBACK,SAVEPOINT。
提示:在 PL/SQL 中只能用 SQL 语句中的 DML 部分,不能用 DDL 部分,如果要在PL/SQL 中使用DDL(如
CREATE table 等)的话,只能以动态的方式来使用。
ORACLE 的 PL/SQL 组件在对 PL/SQL 程序进行解释时,同时对在其所使用的表名、列名及数据类
型进行检查。
PL/SQL 可以在 SQL*PLUS 中使用。
PL/SQL 可以在高级语言中使用。
PL/SQL 可以 在 ORACLE 的 开发工具中使用。
其它开发工具也可以调用 PL/SQL 编写的过程和函数,如 Power Builder 等都可以调用服务器端的
PL/SQL 过程。
§1.3 运行 PL/SQL 程序
PL/SQL 程序的运行是通过 ORACLE 中的一个引擎来进行的。这个引擎可能在 ORACLE 的服务器端,也可
能在 ORACLE 应用开发的客户端。引擎执行 PL/SQL 中的过程性语句,然后将 SQL 语句发送给数据库服务器
来执行。再将结果返回给执行端。
- 4 -
第二章 PL/SQL 块结构和组成元素
§2.1 PL/SQL 块
PL/SQL 程序由三个块组成,即声明部分、执行部分、异常处理部分
PL/SQL 块的结构如下:
DECLARE
/* 声明部分: 在此声明PL/SQL 用到的变量,类型及游标,以及局部的存储过程和函数 */
BEGIN
/* 执行部分: 过程及SQL 语句 , 即程序的主要部分 */
EXCEPTION
/* 执行异常部分: 错误处理 */
END;
其中 执行部分是必须的。
PL/SQL 块可以分为三类:
1. 无名块:动态构造,只能执行一次。
2. 子程序:存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它
们。
3. 触发器:当数据库发生操作时,会触发一些事件,从而自动执行相应的程序。
§2.2 PL/SQL 结构
PL/SQL 块中可以包含子块;
子块可以位于 PL/SQL 中的任何部分;
子块也即 PL/SQL 中的一条命令;
§2.3 标识符
PL/SQL 程序设计中的标识符定义与 SQL 的标识符定义的要求相同。要求和限制有:
标识符名不能超过 30 字符;
第一个字符必须为字母;
不分大小写;
不能用’-‘(减号);
不能是 SQL 保留字。
提示: 一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.
- 5 -
例如:下面的例子将会删除所有的纪录,而不是 KING 的记录;
DECLARE
Ename varchar2(20) := ’KING’;
BEGIN
END;
变量命名在 PL/SQL 中有特别的讲究,建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求,
使得整个系统的文档在规范上达到要求。下面是建议的命名方法:
DELETE FROM emp WHERE ename=ename;
标识符
命名规则
例子
V_name
C_Name
Name_cursor
E_name
V_name
C_company_name
Emp_cursor
E_too_many
Name_table_type
Emp_record_type
Name_table
Name_record
P_name
G_name
Emp
Emp_record
P_sal
G_year_sal
程序变量
程序常量
游标变量
异常标识
表类型
表
记录类型
SQL*Plus 替代变量
绑定变量
§2.4 PL/SQL 变量类型
在前面的介绍中,有系统的数据类型,也可以自定义数据类型。下表是 ORACLE 类型和 PL/SQL 中的变
量类型的合法使用列表:
§2.4.1 变量类型
在 ORACLE8i 中可以使用的变量类型有:
说 明
类型
子类
CHAR
Character
定长字符串
String
Rowid
Nchar
民族语言字符集
范 围
032767
可选,确省=1
ORACLE 限制
2000
VARCHAR2
Varchar, String
可变字符串
NVARCHAR2
民族语言字符集
BINARY_INTEG
带符号整数,为整数计算优
ER
化性能
NUMBER(p,s)
Dec
小数, NUMBER 的子类型
032767
4000
4000
Double
高精度实数
整数, NUMBER 的子类型
- 6 -
整数, NUMBER 的子类型
与 NUMBER 等价
与 NUMBER 等价
整数, 比 integer 小
变长字符串
日期型
0->2147483647
32,767 字节
公元前 4712 年 1
月 1 日至公元后
4712 年 12 月 31
日
布尔型
存放数据库行号
通用行标识符,字符类型
TRUE, FALSE,NULL 不使用
precision
Integer
Int
Numeric
Real
Small int
LONG
DATE
BOOLEAN
ROWID
UROWID
§2.4.2 复合类型
ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表.
§2.4.2.1 记录类型
记录类型是把逻辑相关的数据作为一个单元存储起来,称作 PL/SQL RECORD 的域(FIELD),其作用是存
放互不相同但逻辑相关的信息。
定义记录类型语法如下:
TYPE record_type IS RECORD(
Field1 type1 [NOT NULL] [:= exp1 ],
Field2 type2 [NOT NULL] [:= exp2 ],
. . . . . .
Fieldn typen [NOT NULL] [:= expn ] ) ;
例 1 :
- 7 -