PL/SQL学习笔记

Java相关课程系列笔记之三

笔记内容说明

PL/SQL（薛海璐老师主讲，占笔记内容100%）；

目 录

一、 PL/SQL简介 ......................................................................................................................... 1

1.1什么是PL/SQL .................................................................................................................. 1 1.2 PL/SQL程序结构 .............................................................................................................. 1 1.3 PL/SQL运行过程 .............................................................................................................. 1 1.4注释 ..................................................................................................................................... 2 二、 变量与数据类型 ................................................................................................................. 3

2.1数据类型 ............................................................................................................................. 3 2.2标量类型 ............................................................................................................................. 3 2.3变量声明 ............................................................................................................................. 3 三、 流程控制语句 ..................................................................................................................... 5

3.1条件语句 ............................................................................................................................. 5 3.2循环语句 ............................................................................................................................. 6 四、 PL/SQL中的SQL .............................................................................................................. 9

4.1 PL/SQL中的SQL分类 ..................................................................................................... 9 4.2 DML（insert，update，delete）和TCL（commit，rollback） ..................................... 9 4.3 DDL .................................................................................................................................... 9 五、 PL/SQL中的select .......................................................................................................... 11

5.1 select语句的实现 ............................................................................................................. 11 5.2 record类型 ....................................................................................................................... 11 5.3 %rowtype .......................................................................................................................... 12 5.4 record变量的引用 ........................................................................................................... 12 5.5 cursor的概念 .................................................................................................................... 13 5.6 cursor的分类 .................................................................................................................... 13 5.7显式cursor的处理 ........................................................................................................... 13 5.8显式cursor的属性 ........................................................................................................... 14 5.9隐式cursor的属性 ........................................................................................................... 14 5.10 cursor的声明 .................................................................................................................. 14 5.11 open cursor ...................................................................................................................... 14 5.12 fetch cursor ...................................................................................................................... 15 5.13结果集提取的控制 ......................................................................................................... 15 5.14 close cursor ..................................................................................................................... 15 六、 集合 ..................................................................................................................................... 19

6.1什么是collection .............................................................................................................. 19 6.2什么是关联数组 ............................................................................................................... 19 6.3 Associative arrays的定义................................................................................................. 19 6.4声明Associative arrays类型和变量 ............................................................................... 19 6.5关联数组的操作 ............................................................................................................... 20 6.6 Associative arrays的方法................................................................................................. 20 6.7关联数组的遍历 ............................................................................................................... 21 6.8 Associative arrays的异常................................................................................................. 22 6.9批量绑定 ........................................................................................................................... 22 七、 异常 ..................................................................................................................................... 24

1

7.1 Oralce错误 ....................................................................................................................... 24 7.2 Oralce错误处理机制 ....................................................................................................... 24 7.3异常的类型 ....................................................................................................................... 24 7.4 PL/SQL中的异常 ............................................................................................................ 24 7.5异常捕获 ........................................................................................................................... 24 7.6异常的捕获规则 ............................................................................................................... 25 7.7 Oralce预定义错误的捕获 ............................................................................................... 25 7.8非Oracle预定义异常 ...................................................................................................... 26 7.9用户自定义异常 ............................................................................................................... 26 7.10异常处理总结 ................................................................................................................. 27 7.11 sqlcode和sqlerrm .......................................................................................................... 27 7.12异常的传播 ..................................................................................................................... 28 八、 子程序 ................................................................................................................................. 29

8.1子程序 ............................................................................................................................... 29 8.2有名子程序 ....................................................................................................................... 29 8.3有名子程序的分类 ........................................................................................................... 29 8.4有名子程序的优点 ........................................................................................................... 29 九、 过程procedure..................................................................................................................... 30

9.1语法 ................................................................................................................................... 30 9.2创建存储过程 ................................................................................................................... 30 9.3形参和实参 ....................................................................................................................... 30 9.4形参的种类 ....................................................................................................................... 31 9.5调用存储过程 ................................................................................................................... 31 9.6存储过程中的参数 ........................................................................................................... 31 9.7对实际参数的要求 ........................................................................................................... 32 9.8形式参数的限制 ............................................................................................................... 32 9.9带参数的过程调用 ........................................................................................................... 32 9.10使用缺省参数 ................................................................................................................. 32 9.11存储过程中的DDL语句 ............................................................................................... 33 9.12变量 ................................................................................................................................. 33 9.13 PL/SQL中的SQL分类 ................................................................................................. 34 9.14 再一次来看SQL语句的处理过程 .............................................................................. 36 9.15软分析和硬分析 ............................................................................................................. 36 9.16对过程procedure的基本操作 ....................................................................................... 36 9.17案例 ................................................................................................................................. 36 十、 函数function ....................................................................................................................... 38

10.1语法 ................................................................................................................................. 38 10.2创建函数 ......................................................................................................................... 38 10.3调用函数 ......................................................................................................................... 38 10.4对函数function的基本操作 ......................................................................................... 39 10.5过程和函数的比较 ......................................................................................................... 39 10.6匿名块中的过程和函数声明 ......................................................................................... 39 10.7案例 ................................................................................................................................. 39 十一、 包package ....................................................................................................................... 41

2

11.1什么是package ............................................................................................................... 41 11.2包的组成 ......................................................................................................................... 41 11.3包的优点 ......................................................................................................................... 41 11.4 package声明的语法 ....................................................................................................... 41 11.5 package body声明的语法 .............................................................................................. 42 11.6编译包和包体 ................................................................................................................. 42 11.7案例 ................................................................................................................................. 42 十二、 触发器trigger ................................................................................................................. 44

12.1面临问题 ......................................................................................................................... 44 12.2 DML触发器的组成 ....................................................................................................... 44 12.3 DML触发器的类型 ....................................................................................................... 44 12.4 DML触发器的触发顺序 ............................................................................................... 44 12.5 DML行级触发器 ........................................................................................................... 44 12.6 :OLD和:NEW ................................................................................................................ 44 12.7触发器的重新编译 ......................................................................................................... 46 12.8触发器的状态 ................................................................................................................. 46 十三、 其他注意事项 ................................................................................................................. 47

13.1 PL/SQL的特点 .............................................................................................................. 47 13.2写PL/SQL的好处 ......................................................................................................... 47 13.3命名建议 ......................................................................................................................... 47 13.4搞清楚如下内容 ............................................................................................................. 47 13.5保证所有对象的状态都是valid .................................................................................... 47 13.6 declare中都可声明什么 ................................................................................................ 47 13.7数据库对象 ..................................................................................................................... 47

3