PL/SQL

PL/SQL简介

详情详见《Oracle从入门到精通(第3版) 明日科技》的5章

https://www.oraclejsq.com/plsql/010200446.html

PL/SQL（Procedural Language/SQL）是一种过程化语言，在 PL/SQL中可以通过IF语句或LOOP语句实现控制程序的执行流程，甚至可以定义变量，以便在语句之间传递数据信息，这样PL/SQL就能够实现操控程序处理的细节过程，不像普通的SQL语句（如DML语句、 DQL语句）那样没有流程控制，也不存在变量，因此使用PL/SQL可以实现比较复杂的业务逻辑。PL/SQL是Oracle的专用语言，它是对标准 SQL语言的扩展，它允许在其内部嵌套普通的SQL语句，这样就将SQL 语句的数据操纵能力、数据查询能力和PL/SQL的过程处理能力结合在一起，达到取长补短的目的。

PL/SQL块结构

PL/SQL程序都是以块（Block）为基本单位，整个PL/SQL块分3 部分：声明部分（用DECLARE开头）、执行部分（以BEGIN开头）和异常处理部分（以EXCEPTION开头）。其中执行部分是必需的，其他两个部分可选。无论PL/SQL块的代码量有多大，其基本结构都是由这3部分组成。标准PL/SQL块的语法格式如下。

[DECLARE]
--声明部分，可选
BEGIN
--执行部分，必需
[EXCEPTION]
--异常处理部分，可选
END

接下来对PL/SQL块的3个组成部分进行详细说明。

声明部分由关键字DECLARE开始，到BEGIN关键字结束。在这部分可以声明PL/SQL程序块中所用到的变量、常量和游标等。需要注意的是：在某个PL/SQL块中声明的内容只能在当前块中使用，而在其他PL/SQL块中是无法引用的。
执行部分以关键字BEGIN开始，它的结束方式通常有两种。如果PL/SQL块中的代码在运行时出现异常，则执行完异常处理部分的代码就结束；如果没有使用异常处理或PL/SQL块未出现异常，则以关键字END结束。执行部分是整个PL/SQL块的主体，主要的逻辑控制和运算都在这部分完成，所以在执行部分可以包含多个PL/SQL语句和 SQL语句。
异常处理部分以关键字EXCEPTION开始，在该关键字所包含的代码执行完毕后，整个PL/SQL块也就结束了。在执行PL/SQL代码（主要是执行部分）的过程中，可能会产生一些意想不到的错误，例如除数为零，空值参与运算等，这些错误都会导致程序中断运行。这样程序设计人员就可以在异常处理部分通过编写一定量的代码来纠正错误或者给用户提供一些错误信息提示，甚至是将各种数据操作回退到异常产生之前的状态，以备重新运行代码块。另外，对于可能出现的多种异常情况，用户可以使用WHEN THEN语句来实现多分支判断，然后在每个分支下通过编写代码来处理相应的异常。

对于PL/SQL块中的语句，需要指出的是：每一条语句都必须以分号结束，每条SQL语句可以写成多行的形式，同样必须使用分号来结束。另外，一行中也可以有多条SQL语句，但是它们之间必须以分号分隔。

代码注释和标识符

注释用于对程序代码进行解释说明，它能够增强程序的可读性，使程序更易于理解。注释编译时会被PL/SQL编译器忽略掉，注释有单行注释和多行注释两种情况。另外，在PL/SQL块中声明的变量、常量、游标和存储过程等标识符的名称都是由一系列字符集所组成的，Oracle 对这些组成标识符的字符集有一定的规范和要求。

单行注释

单行注释由两个连接字符“--”开始，后面紧跟着注释内容。

注意：如果注释超过一行，就必须在每一行的开头使用连接字符 (- -) 。

多行注释

多行注释由/*开头，以*/结尾，这种多行注释的方法在大多数的编程语言中是相同的。

标识符

标识符 ( Identifier) 用于定义PL/SQL块单元和程序项的名称。通过使用标识符，可以定义常量、变量、异常、显式游标、游标变量、参数、子程序及包的名称。当使用标识符定义PL/SQL块或程序单元时，需要满足以下规则。

当定义变量、常量时，每行只能定义一个变量或者常量（行终止符：;）。
当定义变量、常量时，名称必须以英文字符（A_Z、az）开始，并且最大长度为30个字符。如果以其他字符开始，那么必须使用双引号引住。
当定义变量、常量时，名称只能使用符号A_Z、az、0~9、 _、$和#。如果使用其他字符，那么必须用双引号引住。当定义变量、常量时，名称不能使用Oracle关键字。例如不能使用SELECT、UPDATE等作为变量名。如果要使用Oracle关键字定义变量、常量，那么必须使用双引号引住。

所有的PL/SQL程序元素（如关键字、变量名、常量名等）都是由一些字符序列组合而成的，而这些字符序列中的字符都必须取自 PL/SQL所允许使用的字符集，那么这些合法的字符集主要包括以下内容。

大写和小写字母：A_Z或az。
数字：0~9。
非显示的字符：制表符、空格和回车。
数学符号：+，-， * ，/，>，<，=等。
间隔符：包括()，{}，[]，?，!，;，:，@，#，%，$，&等。

分界符

分界符 ( Delimiter) 是对PL/SQL有特殊意义的符号(单字符或者字符序列) 。它们用来将标识符相互分割开。表列出了在PL/SQL中可以使用的分界符。

文本

文本是指实际的数值的文字，包括数字文本、字符文本、布尔文本、日期时间文本、字符串文本等。

数字文本：数字文本是指整数或者浮点数。当编写PL/SQL代码时，可以使用科学计数法和幂操作符 ( **) 。科学计数法和幂操作符只适用于PL/SQL语句，而不适用于SQL语句。
字符文本：字符文本是指用单引号引住的单个字符，这些字符可以是PL/SQL支持的所有可打印字符，包括英文字符(A_Z、az)、数字字符(0~9)及其他字符(<、>等) 。
布尔文本：布尔文本是指BOLLEAN值(TRUE、FALSE和 NULL)，并且布尔文本主要用在条件表达式中。
日期时间文本：日期时间文本是指日期时间值，日期文本必须用单引号引住，并且日期值必须与日期格式和日期语言匹配。
字符串文本：字符串文本是指由两个或两个以上字符组成的多个字符值，字符串文本必须用单引号引住。

注意：如果要使用分隔符[]、 {}、<>为字符串赋值，那么不仅需要在分隔符前后加单引号，而且需要带有前缀q。

数据类型

与其他编程语言一样，PL/SQL也有多种数据类型，这些数据类型能够满足在编写PL/SQL程序过程中定义变量和常量之用。

数据类型	类型解释
VARCHAR2(length)	字符串类型：存储可变的长度的字符串，length:是字符串的最大长度，默认不填的时候是1，最大长度不超过4000。
CHAR(length)	字符串类型：存储固定长度的字符串，length:字符串的固定长度大小，默认是1，最大长度不超过2000。
NUMBER(a,b)	数值类型：存储数值类型，可以存整数，也可以存浮点型。a代表数值的最大位数：包含小数位和小数点，b代表小数的位数。例子：number(6,2)，输入123.12345，实际存入：123.12 。number(4,2)，输入12312.345，实际春如：提示不能存入，超过存储的指定的精度。
DATA	时间类型：存储的是日期和时间，包括年、月、日、时、分、秒。例子：内置函数sysdate获取的就是DATA类型
TIMESTAMP	时间类型：存储的不仅是日期和时间，还包含了时区。例子：内置函数systimestamp获取的就是timestamp类型
CLOB	大字段类型：存储的是大的文本，比如：非结构化的txt文本，字段大于4000长度的字符串。
BLOB	二进制类型：存储的是二进制对象，比如图片、视频、声音等转换过来的二进制对象

其他数据类型详见《Oracle从入门到精通(第3版) 明日科技》的5.2节

变量

定义变量

变量是指其值在程序运行过程中可以改变的数据存储结构，定义变量必需的元素就是变量名和数据类型，另外还有可选择的初始值，其标准语法格式如下。

<变量名> <数据类型> [(长度):=<初始值>];

可见，与很多面向对象的编程语言不同，PL/SQL中的变量定义要求变量名在数据类型的前面，而不是后面；语法中的长度和初始值是可选项，需要根据实际情况而定。

变量初始化

许多语言没有规定未经过初始化的变量中应该存放什么内容。因此在运行时，未初始化的变量就可能包含随机的或者未知的取值。在一种语言中，运行使用未初始化变量并不是一种很好的编程风格。一般而言，如果变量的取值可以被确定，那么最好为其初始化一个数值。

但是，PL/SQL定义了一个未初始化变量应该存放的内容，其被赋值为NULL 。NULL意味着“未定义或未知的取值” 。换句话讲，NULL可以被默认地赋值给任何未经过初始化的变量。这是PL/SQL的一个独到之处，许多其他程序设计语言没有定义未初始化变量的取值。

常量

定义常量

常量是指其值在程序运行过程中不可改变的数据存储结构，定义常量必需的元素包括常量名、数据类型、常量值和CONSTANT关键字，其标准语法格式如下。

<常量名> CONSTANT <数据类型>:=<常量值>;

PL/SQL表达式

表达式不能独立构成语句，表达式的结果是一个值，如果不给这个值安排一个存放的位置，则表达式本身毫无意义。通常，表达式作为赋值语句的一部分出现在赋值运算符的右边，或者作为函数的参数等。

例如，123*23-24+33就是一个表达式，它是由运算符串连起来的一组数，按照运算符的意义运算会得到一个运算结果，这就是表达式的值。

“操作数”是运算符的参数。根据所拥有的参数个数，PL/SQL运算符可分为一元运算符(一个参数) 和二元运算符(两个参数)。表达式按照操作对象的不同，也可以分为字符表达式和布尔表达式两种。

字符表达式

布尔表达式

PL/SQL控制结构都涉及布尔表达式。布尔表达式是一个判断结果为真还是为假的条件，它的值只有TRUE、FALSE或NULL。

布尔表达式有3个布尔运算符：AND、OR和NOT，与高级语言中的逻辑运算符一样，它们的操作对象是布尔变量或者表达式。

流程控制语句

详情详见《Oracle从入门到精通(第3版) 明日科技》的5.3节

流程控制语句是所有过程性程序设计语言的关键，因为只有能够进行流程控制才能灵活地实现各种操作和功能，PL/SQL也不例外。

若要在PL/SQL中实现控制程序的执行流程和实现复杂的业务逻辑计算，就必须使用流程控制语句，因为只有能够进行流程控制才能灵活地实现各种复杂操作和功能，PL/SQL中的流程控制语句主要包括选择语句、循环语句两大类。

PL/SQL游标

游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器中的数据处理请求。游标的作用就相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并可以对该行数据执行特定操作，从而为用户在处理数据的过程中提供了很大方便。

在Oracle中，通过游标操作数据主要使用显式游标和隐式游标。另外，还包括具有引用类型特性的REF游标。两种经常使用的游标(显式游标和隐式游标)。

基本原理

在PL/SQL块中执行SELECT、INSERT、UPDATE和DELETE语句时，Oracle会在内存中为其分配上下文区(Context Area)，即一个缓冲区。游标是指向该区的一个指针，或是命名一个工作区(Work Area)，或是一种结构化数据类型。它为应用程序提供了一种对多行数据查询结果集中的每一行数据进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方法。

游标分为显式游标和隐式游标两种。显式游标是由用户声明和操作的一种游标；隐式游标是Oracle为所有数据操纵语句(包括只返回单行数据的查询语句)自动声明和操作的一种游标。在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的open cursors参数定义。

显式游标

显示游标是由用户声明和操作的一种游标，通常用于操作查询结果集(即由SELECT语句返回的查询结果)，使用它处理数据的步骤包括声明游标、打开游标、读取游标和关闭游标4个步骤。其中读取游标可能是个反复操作的步骤，因为游标每次只能读取一行数据，所以对于多条记录，需要反复读取，直到游标读取不到数据为止，其操作过程如图所示。

游标声明需要在块的声明部分进行，其他的3个步骤都在执行部分或异常处理中进行。

声明游标

声明游标主要包括游标名称和为游标提供结果集的SELECT语句。因此，在声明游标时，必须指定游标名称和游标所使用的SELECT语句，声明游标的语法格式如下。

打开游标

在游标声明完毕之后，必须打开才能使用。

读取游标

当打开一个游标之后，就可以读取游标中的数据了，读取游标就是逐行将结果集中的数据保存到变量中。

关闭游标

当所有的活动集都被检索以后，游标就应该被关闭。PL/SQL程序将被告知对于游标的处理已经结束，与游标相关联的资源可以被释放了。这些资源包括用来存储活动集的存储空间，以及用来存储活动集的临时空间。

隐式游标

在执行一个SQL语句时，Oracle会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。隐式游标主要是处理数据操纵语句(如UPDATE、DELETE语句)的执行结果，当然特殊情况下，也可以处理SELECT语句的查询结果。由于隐式游标也有属性，当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称——SQL。

游标属性

无论是显式游标还是隐式游标，都具有%FOUND、%NOTFOUND、%ROWCOUNT和%ISOPEN 4个属性，通过这4个属性可以获知SQL语句的执行结果以及该游标的状态信息。它们描述与游标操作相关的DML语句的执行情况。游标属性只能用在PL/SQL的流程控制语句内，而不能用在SQL语句内。

%FOUND：布尔型属性，如果SQL语句至少影响到一行数据，则该属性为TRUE，否则为FALSE。
%NOTFOUND：布尔型属性，与%FOUND属性的功能相反。
%ROWCOUNT：数字型属性，返回受SQL语句影响的行数。
%ISOPEN：布尔型属性，当游标已经打开时返回TRUE，游标关闭时则为FALSE。

参数化游标：在定义游标时，可以带上参数，使得在使用游标时，根据参数不同所选中的数据行也不同，达到动态使用的目的。

游标变量

如同常量和变量的区别一样，前面所讲的游标都是与一个SQL语句相关联，并且在编译该块的时候此语句已经是可知的，是静态的，而游标变量可以在运行时与不同的语句关联，是动态的。游标变量被用于处理多行的查询结果集。在同一个PL/SQL块中，游标变量不同于特定的查询绑定，而是在打开游标时才能确定所对应的查询。因此，游标变量可以一次对应多个查询。使用游标变量之前，必须先声明，然后在运行时必须为其分配存储空间，因为游标变量是REF类型的变量，类似于高级语句中的指针。