空间数据库实验教程：PL/SQL基础

PL／SQL是由Oracle公司在20世纪80年代末至90年代初开发的一门面向过程的编程语言，是对SQL语言的过程化扩展，是嵌入在Oracle数据库中的三大关键编程语言之一（SQL、Java和PL／SQL）。PL／SQL把数据操作和查询语句组织在PL／SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL／SQL支持静态和动态SQL。静态SQL支持DML操作和事务PL／SQL块控制。动态SQL是SQL允许嵌入PL／SQL块的DDL语句。PL／SQL允许一次发送整块语句到数据库。这降低了网络流量，并提供高性能的应用程序。PL／SQL可以查询、转换并在数据库中更新数据。PL／SQL强劲的异常处理、封装、数据隐藏和面向对象数据类型等特征可以节省设计和调试的时间。编写PL／SQL应用程序是完全可移植的。PL／SQL具有以下特点：

（1）紧密结合集成SQL。

（2）提供广泛的错误检查。

（3）提供大量的数据类型。

（4）提供多种编程结构。

（5）支持通过函数和过程结构化编程。

（6）支持面向对象的编程。

下面主要介绍PL／SQL中的块结构、分支结构、循环结构、字符串与数组、过程和函数以及包等方面的基础知识。

7.3.1.1 PL／SQL块结构

PL／SQL是一种块结构的语言，这意味着PL／SQL程序被划分为编写代码的逻辑块。每块由三个子部分组成：①声明部分，使用关键字DECLARE开头。它是一个可选的部分，并限定在该程序中使用的所有变量、游标、子程序和其他元素。②执行部分关键字BEGIN和END封闭，这是一个强制性的部分。它有程序可执行文件的PL／SQL语句。它应具有至少一个可执行的代码行，这可能仅仅是一个空命令，以指示什么都不执行。③异常处理部分开头使用关键字EXCEPTION，此部分又是可选的，含有异常，在程序处理错误中。代码如下所示：

7.3.1.2 PL／SQL分支结构

PL／SQL分支结构是程序设计中的基本控制结构之一。PL／SQL编程语言提供了两种类型的分支语句，分别是IF语句和CASE语句。IF语句的基本语法结构如下：

其中，ELSIF和ELSE都是可选部分。IF-THEN-ENDIF是必须部分。例如：

CASE语句选择要执行的语句序列，它使用一个选择而不是多个布尔表达式。选择器是一个表达式，其值被用来从各个选项中选择一个。PL／SQL的基本语法结构如下：

其中，CASE、WHEN、THEN、ELSE和ENDSCASE是关键字，selector的值可能为“value1”“value2”“value3”或其他值，例如：

CASE语句除了这种格式外，还有另外一种叫做搜索CASE语句。所搜索的CASE语句没有选择WHEN子句包含给布尔值的搜索条件。其基本语法结构如下：

7.3.1.3 PL／SQL循环结构

循环结构也是程序设计中三种基本结构之一。循环语句可以执行语句多次。PL／SQL提供了以下三种循环类型。

（1）LOOP循环：在这个循环结构中，语句序列封闭在LOOP和END LOOP语句之间。在每次迭代中，语句序列被执行，然后在循环的顶部恢复控制。PL／SQL编程语言的一个基本循环的语法是：

（2）WHILE循环：当给定条件为真时，重复执行循环体内的语句。语法格式如下：

（3）FOR循环：根据给定循环变量初始值、变化步长和判定条件表达式，当条件表达式为真时，执行循环体内语句。FOR循环重复的控制结构，可以有效地编写需要执行的特定次数的循环。语法如下：

在FOR循环结构中，初始步骤首先被执行，并且只有一次。这一步可以声明和初始化任何循环控制变量。接着，对条件initial＿value..final＿value进行计算。如果为“true”，则执行循环体；如果为“false”，在循环体内不执行，只是之后的FOR循环流量控制跳转到下一条语句。循环体执行后，计数器变量的值被增加或减少。条件重新计算，如果为“true”，循环执行的过程重复，反之，则FOR-LOOP终止。需要注意的是，initial＿value、循环变量和final＿value可以是字面值、变量或表达式，但计算结果必须为数字。否则，PL／SQL就会抛出预定义异常value＿error。下面是一个FOR循环的例子：

如果启用关键字REVERSE，则是反转FOR循环语句。缺省情况下，迭代前进从初始值到最终值，大体是由上界到下界约束。可以通过使用REVERSE关键字实现相反顺序。在这种情况下，每次迭代后循环计数器递减。例如：

此外，PL／SQL循环可以被标记。标记用双尖括号括起来（＜＜和＞＞），并出现在LOOP语句的开头。标签名称也可以出现在循环语句结束位置。可以使用标签在EXIT语句从循环退出。下面的程序展示了这个概念：

除了循环结构，还有一些循环控制语句。循环控制语句改变其正常的顺序执行。当执行离开范围时，在该范围内创建的所有对象自动被销毁。PL／SQL支持以下控制语句。

EXIT语句：立刻退出循环。

CONTINUE语句：退出循环的本次执行，进入下一次循环。

GOTO语句：控制权转移给标签对应的语句。

例如，下面的程序使用EXIT退出循环：

除了上述格式EXIT语句，还可以使用EXIT WHEN语句，下面的代码实现了和上面代码一样的执行效果：

另外，PL／SQL允许使用一个循环内嵌套另一个循环。下面代码是基本循环结构的嵌套：

下面代码是FOR循环嵌套：

下面代码是WHILE循环嵌套：

同时，不同种类的循环结构之间也可以进行嵌套。这里不再一一列举。

7.3.1.4 PL／SQL字符串

PL／SQL字符串实际上是一个字符序列。字符可以是数字、字母、空白、特殊字符或全部的组合。PL／SQL提供了三种类型的字符串。

（1）固定长度字符串：按指定的长度生成字符串，如果字符个数小于指定长度，该字符串会向右填充空格以达到指定的长度。

（2）变长字符串：最大长度可达32 767，如果字符个数没有达到指定长度，也不会进行自动填充。

（3）字符大对象（CLOB）：也是可变长度的字符串，适于存储文本型的数据，可以达到4GB。

Oracle数据库提供了大量的字符串数据类型，如CHAR、NCHAR、VARCHAR2、NVARCHAR2、CLOB和NCLOB。前面加上一个“N”的数据类型为Unicode字符数据。如果需要声明一个可变长度的字符串时，必须提供该字符串的最大长度。例如，VARCHAR2数据类型。要声明一个固定长度的字符串，使用CHAR数据类型。下面的例子说明了声明和使用一些字符串变量：

PL／SQL提供了一些字符串函数和操作符。PL／SQL采用连接运算符（｜｜）用于连接两个字符串。表7-12提供了用PL／SQL的字符串功能（函数）。

表7-12　PL/SQL常用字符串函数

续表7-12

下面的例子展示了上面部分函数的使用方法：(www.xing528.com)

7.3.1.5 PL／SQL数组

PL／SQL程序设计语言提供一种叫做VARRAY的数据结构，可存储相同类型元素的一个固定大小的连续集合，也即PL／SQL的数组。创造一个VRRAY类型的基本语法：

create or replace type varray＿type＿name is varray（n）of＜element＿type＞

其中，varray＿type＿name：数组类型名；n：VARRAY元素（最大值）的数目；element＿type：数组元素的数据类型。PL／SQL块创建VRRAY类型的基本语法：

type varray＿type＿name is varray（n）of＜element＿type＞

下面的程序说明了在PL／SQL块中如何使用可变数组：

注意：在Oracle环境中，可变数组的起始索引值始终为1。

VARRAY的元素也可以是％TYPE任何数据库表或％ROWTYPE数据库表的字段。以存储在数据库中的CUSTOMERS表为例，使用游标示例如下：

上面的程序展示了如何访问数组。可以从指定的条目处取值，把条目的数目作为下标。下标可以是返回整数值（该值小于或等于数组条目数）的任意表达式。对VARRAY变量使用count（）方法，可以知道这个数组中正在使用的条目数。当VARRAY类型被声明的时候，其最大的容量也就被定义了；可以用limit（）方法得到该容量；还可以使用多种技术，最简单的是使用FOR循环：

也可以使用first（）和last（）方法。first（）返回数组的第一个条目的下标（总是1），last（）返回数组的最后一个条目的下标（与count方法相同）。代码如下：

还可以使用prior（n）和next（n）方法，这两个方法分别返回给定条目的前一个和后一个条目的下标。例如，下面的代码用来向后遍历整个数组：

prior（n）和n-1是一样的，next（n）和n+1是一样的，但是prior（1）和next（v.count（））则返回null。

如果数组的空间不够，则可以使用EXTEND（k）方法对VARRAY扩展。这个方法可以在VARRAY的最后追加k个新的条目。如果k没有被指定，只增加一个条目。新增的条目没有值（默认为null），但是可以对它们进行初始化。需要注意的是，对VARRAY的扩展不可以超过其最大容量（通过limit（）方法得到），且在对VARRAY扩展前必须要对它进行初始化。

如果需要缩减数组，使用TRIM（k）方法。这个方法是在VARRAY的尾部删除最后k个条目。当k没有被指定时，删除最后一个条目。已被删除的条目数值将丢失。DELETE（）方法用于删除数组中的所有条目，并把其容量设置为0。

7.3.1.6 PL／SQL过程与函数

模块化设计中的子程序是一个程序单元／模块执行特定的任务。子程序可以调用另一个子程序或程序。子程序使用CREATE PROCEDURE或CREATE FUNCTION语句创建。它被存储在数据库中，并且可以使用DROP PROCEDURE或DROP FUNCTION语句删除。在PL／SQL中，子程序还可以在一个包内创建，这样的子程序是封装子程序。它也存储在数据库中，当使用DROP PACKAGE语句删除包的时候，该子程序也可以被删除。

PL／SQL命名子程序，可使用一组参数来调用PL／SQL块。PL／SQL提供两种子程序：①函数，这些子程序主要用于计算并返回一个值；②过程，这些子程序没有直接返回值，主要用于执行操作。每个PL／SQL子程序有一个名称，称为过程名称，并且可以具有一个参数列表，使用CREATE OR REPLACE PROCEDURE语句创建，简化语法如下：

其中，procedure＿name是指定程序的名称；［OR REPLACE］是选项允许修改现有的程序，可选的参数列表中包含名称、模式和类型的参数；IN表示该值将被从外部传递；OUT表示该参数将被用于从过程返回一个值到外面；procedure＿body为可执行部分。下面的示例创建一个简单过程，执行时将“Hello World！”显示在屏幕上：

一个独立的程序可以有两种方式调用：①使用EXECUTE关键字调用；②从PL／SQL块调用过程的名称。上面名为“greetings”的程序可以通过EXECUTE关键字调用为：

EXECUTE greetings；

也可以从另一个PL／SQL块调用：

使用DROP PROCEDURE语句可以删除上面创建的greetings过程，语法如下：

DROP PROCEDURE procedure-name；

所以，可以使用下面的语句删除greetings：

PL／SQL子程序参数模式包括IN、OUT、IN OUT三种模式。①IN模式：一个IN参数的作用就像一个常数，它不能再被分配值。可以通过一个常量、文字、初始化变量或表达式作为一个IN参数。也可以把它初始化为默认值。IN是参数传递的默认模式。参数是通过引用传递。②OUT模式：OUT参数返回一个值到调用程序。在内部的子程序OUT参数就像一个变量，可以改变它的值并引用分配后的值。实际参数必须是变量，它是按值传递。③IN OUT模式：IN OUT参数传递一个初始值到子程序，并返回一个更新值给调用者。它可以被分配一个值，其值可被读取。一个IN OUT形式参数对应的实际参数必须是一个变量，不能是常量或表达式。形式参数必须分配一个值，实际参数就是按值传递。例如，该程序Code＿7＿45查找两个值中的最小值，这里使用IN模式接收两个数字，并使用OUT参数返回它们的最小值。代码如下：

下面这个例子Code＿7＿46计算传递值的平方值。这个例子展示如何使用相同的参数传递一个值，然后返回另一个结果。代码如下：

在方法传递参数中，实际参数可以通过以下三种方式：

（1）位置标记。在位置表示法中，第一实际参数代入所述第一形式参数，第二实际参数代入所述第二形式参数，以此类推。以findMin（a，b，c，d）为例，a取代为x，b取代为y，c取代为z，以及d代替m。

（2）命名符号。名为符号，实际参数与使用箭头符号的形式参数相关（＝＞），所以程序调用如下所示：

findMin（x＝＞a，y＝＞b，z＝＞c，m＝＞d）；

（3）混合符号。在混合符号表示法中，可以混合这两种过程调用写法，但是位置标记应先于指定符号。下面的调用是合法的：

findMin（a，b，c，m＝＞d）；

但是，这样是不合法的：

findMin（x＝＞a，b，c，d）；

PL／SQL函数与过程相同，不同之处在于函数有一个返回值。因此，前面关于过程的所有讨论基本都适用于函数。建立一个独立函数可以使用CREATE FUNCTION语句创建。CREATE OR REPLACE PROCEDURE语句语法如下：

其中，function＿name指定函数的名称；［OR REPLACE］选项允许修改现有的函数；可选的参数列表中包含的名称、模式和类型的参数，如IN表示该值将被从外部传递和OUT表示该参数将被用于过程外面返回一个值，IN OUT表示该值先传入函数，然后再返回一个值；函数必须包含一个RETURN语句，RETURN子句指定要在函数返回的数据类型；function＿body包含可执行部分。

例如，Code＿7＿47定义和调用了一个简单的PL／SQL函数，计算并返回两个值中的最大值。代码如下：

7.3.1.7 PL／SQL包

PL／SQL包是一组逻辑相关的PL／SQL类型，由变量和子程序等构成的对象。程序包将有两个强制性的部分：一个是包规范定义，另一个是包主体或包定义。包规范定义部分，只是声明类型、变量、常量、异常、游标和子程序等。置于规范定义部分的所有对象为公共对象，可从外部引用。任何子程序在包主体中，但没有在包定义中声明的为私有对象。下面的代码显示了程序包规范定义部分的创建，以及一个包中可以定义的全局变量和多个程序或函数。代码如下：

包主体主要是指用于实现已经在包定义和其他私人声明中声明的各种方法。使用CREATE PACKAGE BODY语句创建包体。下面的代码片段显示了包主体声明上面创建的example包。代码如下：

访问包元素（变量、过程或函数）的语法如下：

package＿name.element＿name；

例如，下面的程序Code＿7＿50展示了如何访问example包内的变量和过程。Code＿7＿51给出了example的包完整定义。代码如下：