PL/SQL

这是对Oracle-SQL知识点详细介绍的文章系列，其他文章如下：

• Oracle-SQL系列知识点(一)
• Oracle-SQL系列知识点(二)
• Oracle-SQL系列知识点(三)

PL/SQL基础知识

• 什么是PL/SQL？

PL/SQL是由Oracle开发，专用于Oracle的一种程序设计语言；

PL代表 Procedural Language；

SQL代表Structured Query Language；

PL/SQL是Oracle数据库对SQL语句的扩展，增加了编程语言的特点；

PL/SQL 包含过程化语句和SQL语句数据操作和查询语句被包含在PL/SQL代码的程序单元中（PL/SQL块）,经过逻辑判断、循环等操作完成复杂的功能或者计算.

• PL/SQL的优点

改善了性能

PL/SQL以整个语句块发送给服务器，这个过程在单次调用中完成，降低了网络拥挤。而如果不使PL/SQL，每条SQL语句都有单独的传输交互，在网络环境下占用大量的服务器时间，同时导致网络拥挤。

可重用性

PL/SQL能运行在任何ORACLE环境中（不论它的操作系统和平台），在其他ORACLE能够运行的操作系统上无需修改代码。

模块化

每个PL/SQL单元可以包含一个或多个程序块，程序中的每一块都实现一个逻辑操作，从而把不同的任务进行分割，由不同的块来实现，块之间可以是独立的或是嵌套的。

1477182319341.png

• PL/SQL块 的类型

匿名块：一般在要运行的应用中说明，运行时传递给PL/SQL引擎处理，只能执行一次，不能被存储在数据库中。

过程，函数和包（Procedure，Function & Package）：是命名的PL/SQL块，被存储在数据库中，能够被多次执行，可以用外部程序来显示执行。

触发器（Trigger）：是命名的PL/SQL块，被存储在数据库中，能够被多次执行，当相应的触发事件发生时自动被执行；

• PL、SQL块的组成

PL/SQL语言以块为单位，块中可以嵌套子块。

一个基本的PL/SQL块由3部分组成：

定义部分（DECLARE)

PL/SQL中使用的变量、常量、游标和异常处理的名字都必须先定义后使用。并且必须定义在以DECLARE关键字开头的定义部分。

可执行部分（BEGIN）

PL/SQL块的主体，包含该块的可执行语句。该部分定义了块的功能，是必须的。 由关键字BEGIN开始，以END结束。

异常处理部分（EXCEPTION）

该部分包含块的异常处理程序（错误处理程序）。当该块程序体中的某个语句出现异常（检测到一个错误）时，oracle将程序控制转到异常部分的相应的异常处理程序中进行进一步的处理。该部分由关键字EXCEPTION开始，END关键字结束

1477182602911.png

• PL/SQL语句的处理

1477182651274.png

• PL/SQL中变量

PL/SQL中可使用标识符来声明变量、常量、游标、用户定义的异常等，并在SQL语句或过程化的语句中使用。

标识符的命名和Oracle对数据库对象的命名原则相同。

至多有30个字符；

不能是保留字；

必须以字母开头；

包含字母、$、_、# 、数字符号；

对标识符的命名最好遵循相关命名规范,不建议与数据库中；

表的列名相同；

1477182743997.png

• 变量的类型

1、PL/SQL变量

标量型(只能存储单值、内部没有分量）

标量数据类型:

容纳单个值,内部没有分量
CHAR [(maximum_length)]，
VARCHAR2 (maximum_length)（在程序中的大小32767），
DATE，
NUMBER [(precision, scale)]，
BINARY_INTEGER，
PLS_INTEGER，
BOOLEAN，
BINARY_FLOAT，
BINARY_DOUBLE

复合型；
引用型；
LOB型 (大型的对象)；

2、外部变量(非PL/SQL变量)

表单应用程序中的屏幕域；
SQL*Plus 主机变量；

• 声明变量

1477182932062.png

1、变量的命名规则与SQL的规则基本相同，即每个标识符必须以字母开头，而且不分大小写。

2、使用NOT NULL约束条件定义变量时，必须为变量赋予一个值

3、在每行上声明一个变量，使代码更易于阅读和维护
在常量声明中，关键字CONSTANT必须位于类型指定符之前，必须被初始化

4、使用赋值运算符(:=)或DEFAULT保留字将变量初始化

5、在PL/SQL中使用的变量、常量、游标和异常处理的名字都必须先声明后使用。

6、声明部分是包括在关键字DECLARE和BEGIN之间的部分，每条语句之后用‘；’结束

7、数据库中列名与变量名不建议相同

• 变量赋值

方式一：

1477183058963.png

方式二：

另外一种为变量赋值的方式是从数据库中选取值赋给变量。语法：

SELECT  column  INTO  variable
FROM  table  
WHERE  condition;

• %TYPE 的属性

通过%TYPE属性声明一个变量，实际上就是参照变量或者表中字段的类型作为变量的类型，并且保持同步。变量将遵循下面的类型声明：

• 已经声明过的变量类型
• 数据库中表的字段类型

可以作为%TYPE前缀的可以是

• 表名.列名
• 前面声明的变量名称

PL/SQL在运行程序时确定变量的数据类型和大小

• PL/SQL 块的代码注释

/* 和*/之间的多行注释；

单行注释，以 －－ 开始；

• 事务控制语句

事务开始于COMMIT或ROLLBACK后的第一个DML语句；

使用 COMMIT 和 ROLLBACK 语句来终止一个事务；

在事务处理过程中使用SAVEPOINT 来标记中间点；

编写控制结构

• IF语句

1477183403324.png

任何包含空值的算术表达式结果均为空值

• case语句

CASE语句可以根据条件从多个执行分支中选择相应的执行动作，并能返回一个值；

CASE selector
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2...
    WHEN expressionN THEN resultN
    [ELSE resultN+1]
END;

• 循环控制: LOOP 语句

LOOP 语句多次执行语句序列

LOOP 语句有三种形式:

Basic loop 无条件的循环

1477183570534.png

FOR loop 有计数的循环

1477183591633.png

WHILE loop 有条件的循环

1477183613350.png

EXIT 是强制结束循环的语句

游标

• 游标的定义

游标（cursor）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果

• 游标的分类

隐式游标：PL/SQL隐式建立并自动管理这一游标

显式游标:由程序员显式说明及控制，用于从表中取出多行数据，并将多行数据一行一行单独处理

• 隐式游标

由Oracle在内部声明
由Oracle自行管理游标
可以使用游标属性从最近执行的SQL语句中获取信息
用于处理DML语句以及返回单行的查询

属性：

1477183763601.png

• 显式游标

由用户显式声明，查询返回多行记录

1、使用游标时，select语句查询的结果可以是单条记录，多条记录，也可以是零条记录。
2、游标工作区中，存在着一个指针（POINTER）,在初始状态它指向查询结果的首记录。
3、要访问查询结果的所有记录，可以通过FETCH语句，进行指针的移动来实现。
4、使用游标进行操作，包括定义游标、打开游标、提取数据以及关闭游标几步。

1477183897298.png

1477183919880.png

属性：

1477183949276.png

• 游标的声明

1477183986474.png

在游标声明中， SELECT子查询不能使用INTO子句。
如果需要按指定的次序处理行，可在查询中使用ORDER子句。

• 打开游标

1477184040480.png

使用游标之前应首先打开游标；

打开游标，实际上是执行游标定义时对应的SELECT语句，将查询结果检索到工作区中。

如果没有要返回的行，不会出现异常；

当执行 OPEN 语句时，并不将活动集中的行赋
给变量，而是在执行 FETCH 语句时才从活动集中提取一行；

• 从游标中提取数据

1477184109474.png

1、在使用FETCH语句之前必须先打开游标，这样才能保证工作区中有数据。

2、对游标第一次使用FETCH语句时，游标指针指向第一条记录，因此操作的对象是第一条记录，使用后，游标指针指向下一条记录。

3、游标指针只能向下移动，不能回退。如果想查完第二条记录后又回到第一条记录，则必须关闭游标，然后重新打开游标。

4、INTO子句中的变量个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应。

• 关闭游标

1477184157817.png

游标一旦关闭，不可再从游标中提取数据
当关闭了游标后，所有和该游标相关的资源都会被释放；

• 游标和记录

将提取的行值存入一个PL/SQL RECORD 中能方便地处理活动集中的行。

1477184193739.png

• 游标处理中的FOR循环

1477184226658.png

游标式的 FOR 循环可以更方便地处理显式游标。
隐式地打开、提取和关闭游标。
隐式声明记录类型变量。

• 不需声明的游标

1477184268376.png

• 带有参数的游标

1477184294444.png

• FOR UPDATE 子句

1477184391684.png

在事务执行期间可以显式锁定以拒绝访问。
在更新或删除行时要锁定该行。

• WHERE CURRENT OF 子句

1477184413309.png

异常处理

什么是异常?

Oracle中出现错误的情形通常分为编译时错误（compile-time error）和运行时错误(run-time error)，在PL/SQL运行过程中出现的警告或错误，当发生异常时，块就会停止执行，但是可以转到指定异常处理机部分继续执行。

异常是如何触发的?

隐式触发：发生了一个 Oracle 错误时，oracle自动触发一个异常。
显示触发：程序员可以使用RAISE语句显式触发异常。

如何处理异常?

捕捉异常：用处理机截获
传递异常：在调用环境中传播异常

• 异常的类型

1477184579001.png

• 捕获异常

语法：

1477184597613.png

1、在异常部分WHEN 子句没有数量限制
2、WHEN OTHERS 是最后一个子句
3、异常处理部分从关键字EXCEPTION开始
4、当异常抛出后，控制无条件转到异常处理部分
5、在离开块之前只能执行一种异常处理

• 预定义异常

1477184675282.png

• 非预定义异常

1477184729795.png

例子：

1477184740699.png

• 捕获异常的函数

1477184791399.png

• 用户定义异常

1477184838710.png

例子：

1477184855280.png

Raise_Application_Error过程

1、用于创建用户定义的错误消息的过程
2、向用户返回错误，并且其返回格式和其它Oracle错误的格式相同
3、既可以在可执行部分中使用，也可以在异常部分中使用

Raise_Application_Error(error_number,message);

4、错误编号必须介于 –20000 和 –20999 之间
5、错误消息的长度可长达 2048 个字节

例子：

1477184947648.png

存储过程

命名的PL/SQL块
能够接受参数
能够被重复调用
用于执行某项操作
存储在数据库中

• 创建过程

1477185032850.png

1、REPLACE选项指示如果过程存在，它将被删除并且用语句创建的新版本代替
2、在IS之后，声明本地变量，不需要使用DECLARE开始声明
3、PL/SQL 块，既可以用BEGIN开始也可以用局部变量的声明开始，既可以用END结束也可以用END procedure_name 结束；

• 过程的参数

1477185109292.png

1477185132766.png

• IN参数示例

1477185158041.png

• OUT参数示例

1477185174128.png

• IN OUT参数示例

1477185187503.png

• 传递参数的方法

位置：实际参数与形式参数排列的顺序相同
指定：实际参数联合其相应的形式参数以任意顺序排列
组合：实际参数的排列一些用位置，一些用指定

• 处理异常

1477185291915.png

• 删除过程

1477185322554.png

• Oracle-SQL系列知识点(一)
• Oracle-SQL系列知识点(二)
• Oracle-SQL系列知识点(三)