PL/SQL程序设计

PL/SQL程序设计
1
目 录
第一章 PL/SQL 程序设计简介....................................................................................4
§1.2 SQL与PL/SQL......................................................................................................................4
§1.2.1 什么是PL/SQL?...............................................................................................................4
§1.2.1 PL/SQL的好处................................................................................................................4
§1.2.2 PL/SQL 可用的SQL语句.................................................................................................5
§1.3 运行PL/SQL程序..................................................................................................................5
第二章 PL/SQL块结构和组成元素.................................................................................6
§2.1 PL/SQL块..............................................................................................................................6
§2.2 PL/SQL结构...........................................................................................................................6
§2.3 标识符...................................................................................................................................6
§2.4 PL/SQL 变量类型................................................................................................................7
§2.4.1 变量类型............................................................................................................................7
§2.4.2 复合类型..........................................................................................................................9
§2.4.3 使用%ROWTYPE.............................................................................................................11
§2.4.4 LOB类型*........................................................................................................................11
§2.4.5 Bind 变量........................................................................................................................11
§2.4.6 INDEX BY TABLES.......................................................................................................12
§2.4.7 数据类型的转换*............................................................................................................13
§2.5 运算符和表达式(数据定义)................................................................................................13
§2.5.1 关系运算符......................................................................................................................13
§2.5.2 一般运算符......................................................................................................................13
§2.5.3 逻辑运算符......................................................................................................................14
§2.6 变量赋值..............................................................................................................................14
§2.6.1 字符及数字运算特点......................................................................................................14
§2.6.2 BOOLEAN 赋值.............................................................................................................14
§2.6.3 数据库赋值......................................................................................................................14
§2.6.4 可转换的类型赋值..........................................................................................................15
§2.7 变量作用范围及可见性.......................................................................................................15
§2.8 注释.....................................................................................................................................16
§2.9 简单例子..............................................................................................................................17
§2.9.1 简单数据插入例子........................................................................................................17
§2.9.2 简单数据删除例子........................................................................................................17
第三章 PL/SQL流程控制语句........................................................................................18
§3.1 条件语句..............................................................................................................................18
§3.2 CASE 表达式.....................................................................................................................19
§3.3 循环.....................................................................................................................................19
§3.3 标号和GOTO........................................................................................................................21
§3.4 NULL 语句............................................................................................................................21
第四章 游标的使用........................................................................................................23 - 1 -
2
§4.1 游标概念.................................................................................................................................23
§4.1.1 处理显式游标....................................................................................................................23
§4.1.2 处理隐式游标....................................................................................................................26
§4.1.3 游标修改和删除操作......................................................................................................27
第五章 异常错误处理.................................................................................................29
§5.1 异常处理概念..........................................................................................................................29
§5.1.1 预定义的异常处理............................................................................................................29
§5.1.2 非预定义的异常处理........................................................................................................30
§5.1.3 用户自定义的异常处理....................................................................................................31
§5.1.4 用户定义的异常处理......................................................................................................32
§5.2 异常错误传播..........................................................................................................................33
§5.2.1 在执行部分引发异常错误................................................................................................33
§5.2.2 在声明部分引发异常错误................................................................................................34
§5.3 异常错误处理编程..................................................................................................................34
§5.4 在 PL/SQL 中使用 SQLCODE, SQLERRM....................................................................35
第六章 存储函数和过程.................................................................................................36
§6.1 引言.......................................................................................................................................36
§6.2 创建函数................................................................................................................................36
§6.3 存储过程................................................................................................................................39
§6.3.1 创建过程..........................................................................................................................39
§6.3.2 调用存储过程..................................................................................................................40
§6.3.3 开发存储过程步骤..........................................................................................................42
§6.3.4 与过程相关数据字典......................................................................................................43
第七章 包的创建和应用.................................................................................................44
§7.1 引言.......................................................................................................................................44
§7.2 包的定义................................................................................................................................44
§7.3 包的开发步骤........................................................................................................................45
§7.4 包定义的说明........................................................................................................................45
§7.5 子程序重载............................................................................................................................48
§7.6 删除过程、函数和包............................................................................................................50
§7.7 包的管理................................................................................................................................50
第八章 触发器...............................................................................................................51
§8.1 触发器类型............................................................................................................................51
§8.1.1 DML触发器........................................................................................................................51
§8.1.2 替代触发器........................................................................................................................51
§8.1.3 系统触发器........................................................................................................................51
§8.2 创建触发器............................................................................................................................51
§8.2.1 触发器触发次序................................................................................................................53
§8.2.2 创建DML触发器...............................................................................................................53
§8.2.3 创建替代(Instead_of)触发器............................................................................................54
§8.2.3 创建系统事件触发器........................................................................................................54 - 2 -
3
§8.2.4 系统触发器事件属性........................................................................................................55
§8.2.5 使用触发器谓词................................................................................................................56
§8.2.6 重新编译触发器................................................................................................................56
§8.3 删除和使能触发器................................................................................................................56
§8.4 触发器和数据字典................................................................................................................57
§8.5 数据库触发器的应用举例..................................................................................................57
- 3 -
4
第一章 PL/SQL 程序设计简介
PL /SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。本章的主要内容是讨论引入PL/SQL语言的必要性和该语言的主要特点，以及了解PL/SQL语言的重要性和数据库版本问题。还要介绍一些贯穿全书的更详细的高级概念，并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。
本章主要重点：