《oracle数据库技术》课件第7章

7.1PL/SQL简介

7.2基本语法要素

7.3PL/SQL的控制结构

7.4PL/SQL复合类型

7.5游标

7.6异常处理

7.7小结

习题与思考题

实践7PL/SQL编程基础第7章PL/SQL编程基础7.1PL/SQL简介

SQL是用来访问关系型数据库的一种通用语言，其执行特点是非过程化的，即不用指明执行的具体方法和途径，而是简单地调用相应语句来直接取得结果。显然，这种不关注任何实现细节的语言对于开发者来说有着极大的便利，然而，在实际应用中，有些复杂的业务流程都是过程性的，同时又要求相应的程序来处理。例如，想确定员工的消费水平，可根据统计出的消费表中的消费金额的高低来判断，消费金额高的为高消费人群，消费金额低的为低消费人群。对于这种问题的解决，仅依靠SQL就无能为力了，为此Oracle公司开发了PL/SQL语言。

PL/SQL(ProceduralLanguage/SQL)简称过程化SQL，是一种过程化语言，是Oralce对SQL的一种扩充，它将SQL的强大功能和灵活性与过程化语言的过程性融为一体，处理了上述SQL的不足，更关注于处理细节，因此可以用来实现比较复杂的业务逻辑。7.1.1PL/SQL的特点

PL/SQL不是一个独立的产品，而是嵌入在Oracle服务器和Oracle开发工具中的，所以在Oracle客户端的各种工具和服务器中都可以编写PL/SQL程序，使用起来非常方便，主要具有以下一些特点：

(1) PL/SQL通过增加过程化语言中的过程化控制结构来对SQL进行扩充，所以PL/SQL既具有对数据库存取功能比较强的特点，可以使用SQL中相关的语句对数据进行各种操作，又具有过程化语言的条件判断、循环以及出错处理等特点。

(2) PL/SQL是一种高性能的事务处理语言，支持DML命令和SQL的事务控制语句，但不支持DDL命令和DCL命令。

(3) PL/SQL程序有良好的性能，特别是在网络环境中，将整个语句块一次性地发送到Oracle服务器，可以显著地降低应用程序和Oracle之间的通信量。另外，PL/SQL块可以被命名并存储在Oracle服务器中，也可以在另一个PL/SQL程序或SQL命令行中被重用，以及在任何连接到Oracle服务器的工具中调用。

(4) PL/SQL在所有的Oracle平台上是100%可移植的，因为它的数据类型是基于数据库服务器上的数据类型，该语言与机器完全无关。一个PL/SQL程序可以不需要任何修改，就可以在所有的Oracle服务器上编译并运行。

(5)存储在服务器上的PL/SQL程序的安全性可以使用授权和回收权限进行管理。7.1.2PL/SQL的结构

1．块的基本结构

每一个PL/SQL块包括3个基本部分：声明部分、执行部分和异常处理部分，其基本结构如下：

[DECLARE

声明部分]

BEGIN

执行部分

[EXCEPTION

异常处理部分]

END；其中：

●声明部分：以关键字DECLARE开始，所有在PL/SQL块中要用到的对象在使用之前都必须在这部分集中定义，例如变量、常量、用户自定义数据类型等。

●执行部分：以关键字BEGIN开始，END结束。定义程序块要完成的处理工作，包括对数据库的数据操纵语句和各种流程控制语句。

●异常处理部分：包含在执行部分中，以关键字EXCEPTION开始，包含对程序执行中产生的异常情况的处理语句。这些异常情况既可以是系统预定义的，也可以是用户自定义的。

在PL/SQL块中，只有执行部分是必需的，其他两部分都是可选的。注意：

(1)分号(;)是PL/SQL语句的结束符，关键字DECLARE、BEGIN、EXCEPTION不必用分号结束，而END和所有其他的PL/SQL语句都要求以分号结束。

(2) PL/SQL程序块有两种注释方式：以“--”开头的为单行注释，采用“/*……*/”形式的为多行注释。

(3)本章涉及的都是PL/SQL无名程序块，只能在SQL*Plus、iSQL*Plus等环境下运行，不能单独进行编译，并且只能被执行一次。在第8章将要讲解的存储过程、存储函数、触发器为命名的PL/SQL程序块，可以单独进行编译，并存储在数据库中，以便以后能够多次调用，重复执行。

2．块的嵌套结构

PL/SQL块中还可以包含另一个PL/SQL子块，这种情况叫做块的嵌套。子块可位于PL/SQL中的任何部分，从父块的角度看，子块相当于一个PL/SQL语句。7.2基本语法要素7.2.1变量

1．变量的声明在程序的运行过程中其值可以改变的量称为变量。变量在使用之前必须在PL/SQL程序块的声明部分进行声明，其目的是为它分配合适的内存空间。声明语法格式如下：变量名[CONSTANT]数据类型[NOTNULL][:=|DEFAULTPL/SQL表达式];其中：

●变量名：变量的名字，是由字母、数字、下划线(_)、美元符号($)和(#)组成，但必须以字母开头，不区分大小写，最大长度不超过30个，命名中不能出现空格，并且不能使用Oracle中的关键字。

● CONSTANT：此项为可选项，表示定义常量的关键字。常量在程序内其值不能改变，并且必须在声明的同时赋予初值。

●数据类型：变量的数据类型可以是Oracle所支持的各种数据类型，包括系统预定义的数据类型和用户自定义的数据类型。

●NOTNULL：此项为可选项，用于限制变量必须要包含初始值，但初始值不能为

NULL值。

● :=|DEFAULTPL/SQL表达式：此项为可选项，用于给变量和常量赋初值，当有NOTNULL修饰时，则必须赋初值。“:=”是赋值运算符，“DEFAULT”与其含义一样，二者选一。“PL/SQL表达式”可以是任何PL/SQL表达式，可以是变量、常量、运算符和函数组成的表达式，其值作为变量或常量的初值。例如：

snameCHAR(20);--声明“sname”为字符型变量，长度为20

snoCHAR(5):=‘00001’;

--声明“sno”为字符型变量,长度为5,初始值为“00001”

tBOOLEANDEFAULTtrue;

--声明“t”为布尔型变量，初始值为true

piCONSTANTNUMBER(5,2):=3.14;

/*声明“pi”为数字型常量，初始值为3.14，这里的初 始值是不可以缺少的*/

date1CONSTANTDATEDEFAULT‘15-7月-78’;

/*声明“date1”为日期型常量，初始值为“1978年7月15 日”，这里的初始值是不可缺少的*/注意：

(1)没有赋初值的变量默认初始化为NULL值。

(2)在PL/SQL声明语句中，变量名或常量名在前，数据类型在后，并且一个语句只能声明一个变量或常量，否则就会导致编译错误，这与其他编程语言有所不同。例如，在Java的声明语句中，数据类型在前，变量名或常量名在后，并且一个语句可同时声明多个变量或常量(如inta,b)，而PL/SQL中正确的声明方法为

aint;

bint;

2．变量的赋值

除了在声明时直接初始化变量外，还可以用以下两种方法为变量赋值：

(1)用“:=”赋值运算符为单个变量赋值。例如：

DECLARE

v1NUMBER(5,2);

v2CHAR(4);

BEGIN

v1:=9.99;

v2:=‘ABCD’;

END;

此语句块在执行部分完成将数值9.99赋值给数值型变量“v1”，将字符串“ABCD”赋值给字符型变量“v2”。

(2)通过SELECTINTO语句或FETCHINTO语句给多个变量同时赋值。例如：

DECLARE

s1CHAR(5);

s2VARCHAR(20);

BEGIN

SELECTsno,snameINTOs1,s2FROMstaffWHEREsno=‘00001’;

END;此语句块完成将“staff”表中“sno”为“00001”的数据行中的“sno”列和“sname”列的值分别赋给“s1”和“s2”变量。

注意：在PL/SQL程序中，SELECT语句总是和INTO配合使用，INTO子句后面就是要被赋值的变量，SELECT后面的字段类型、个数应与INTO后的变量类型、个数一致，而且要保证SELECT的查询结果一定是单条记录，否则会出现编译错误。

3．变量的作用域

变量的作用域是指变量值的有效使用范围。只有在变量的作用域内，程序块使用该变量才是有意义的，否则将导致异常情况。

变量的作用域从变量声明开始到PL/SQL程序块结束。在块的嵌套结构中，主块中声明的变量叫做全局变量，而子块中声明的变量叫做局部变量。全局变量的作用域是从全局变量声明语句开始到主块的结束标识结束。局部变量的作用域是从局部变量声明语句开始到子块的结束标识结束。当局部变量与全局变量同名时，在子块中默认使用局部变量的值。例如：7.2.2数据类型

任何变量和常量都需要一个数据类型，以指定其存储格式、约束和值的有效范围。PL/SQL语言中的数据类型分为标量类型、复合类型、参考类型和LOB类型4种。

1．标量类型

标量类型又称基本数据类型，是系统预定义好的。标量类型又分为4种：数值型、字符型、日期型和布尔型。每种数据类型又包含很多的子类型，下面给出常见的基本数据类型，如表7-1所示。

2．复合类型

复合类型是用户定义的，复合类型的变量内部包含了一个或多个标量类型的变量，这些变量也称为分量。在PL/SQL中，主要的复合类型有记录(RECORD)、表(TABLE)和数组(VARRAY)3种，详细内容将在7.4节中介绍。

3．参考类型

在很多情况下，用户经常会将数据表的某字段值存储在某个变量中，这时必须保证变量的数据类型与该字段的数据类型完全一致。但有时候字段的数据类型可能会有所改动，为了不影响程序的运行，就需要随之修改变量的数据类型，使之保持与字段的数据类型一致，否则就会产生编译错误。为了解决这个问题，可以通过将数据表的某字段的数据类型指定给所声明的变量，来保证变量的数据类型与数据表中某字段的数据类型动态绑定。这样，数据表中某字段的数据类型改变了，变量的数据类型就会随之改变，从而使得PL/SQL程序具有相对的稳定性，下面介绍3种常见的参考类型。

1) %TYPE

%TYPE即一个变量的类型定义参考另一个已经定义的变量的类型，或参考数据表中某个字段的数据类型定义。

例如：

v1CHAR(6);

v2v1%TYPE;

/*--变量“v2”的数据类型与变量“v1”相同*/

snamestaff.sname%TYPE;

/*--变量“sname”的数据类型与“staff”表中的 “sname”字段的数据类型相同*/

2) %ROWTYPE

%ROWTYPE即一个变量的类型参考某个表或视图的结构，此类型变量内的分量的名字、数据类型与表或视图结构中的字段名字、数据类型完全一致。定义后，使用此类型的变量时，用“变量名.表或视图中的字段名”来表示。

例如：

stastaff%ROWTYPE;

/*“sta”的数据类型为“staff”的表结构，由于“staff”表中有“sno”、“sname”、“ssex”、“sbirthday”、“saddress”、“stel”、“cno”、“bno”8个字段，所以“sta”中就有8个分量，分量的名字和数据类型分别与“staff”表中的8个字段对应*/

sta.sname:=‘高斌’;

--表示给“sta”变量中的“sname”分量赋值

使用%ROWTYPE的好处是，当用SELECT语句检索一行记录时很方便，不必定义多个变量来存储表中的字段值，简化了PL/SQL程序。

3)游标

PL/SQL语言中的另一个常用的参考类型为游标，类似于C语言中的指针，详细内容将在7.5节介绍。

4．LOB类型

LOB类型用来存储非结构化的数据，如Video、Sound、Image等，这些大型对象可以是一个二进制数或字符数值，长度可达4GB。在PL/SQL中操纵大型对象可用Oracle提供的包DBMS_LOB进行处理。本书不作详细介绍。

5．标量数据类型之间的相互转换

在一个表达式中，必须保证数据类型是一样的。如果在一个表达式中有不同的数据类型出现，必须要进行数据类型转换，否则将会出现编译错误，并会影响性能。

在PL/SQL中支持两种形式的数据类型转换：隐式转换和显式转换。

1)隐式转换

隐式转换是指PL/SQL可根据需要自动将一个类型的值转换成另一个类型的值。例如：

DECLARE

n1NUMBER(5);

c1CHAR(10);

BEGIN

n1:=1234;

c1:=n1;--数值型自动转换成字符型

END;

注意：隐式转换容易带来转换的不确定性，尽量少用，避免产生编译错误。

2)显式转换

显式转换是指通过合适的转换函数将一种数据类型转换成另一种数据类型。常见的转换函数如表7-2所示。

其中，参数“format”代表用于转换的格式，参数“num”、“d”、“string”分别指转换前的值。例如：

SETSERVEROUTPUTON

DECLARE

n1NUMBER(5);

c1CHAR(10);

BEGIN

c1:=‘1234’;

n1:=TO_NUMBER(c1);

--通过TO_NUMBER函数将字符型的值显示转换成数值型

END;7.2.3运算符

同其他编程语言一样，在PL/SQL中也提供了丰富的运算符。PL/SQL中的运算符有以下5种类型。

1．算术运算符

算术运算符用于对数值型数据进行加、减、乘、除等算术运算，运算的结果仍为数值型数据。算术运算符包括 +、-、*、/ 和**运算符。

2．关系运算符

关系运算符用于数值与表达式或表达式与表达式之间的比较，关系运算符两边的表达式的数据类型必须一致，其运算的结果总是为布尔型值TRUE或FALSE。关系运算符包括=、<>或!=、>、>=、<和<=运算符。

3．比较运算符

比较运算符是对关系运算符的补充，运算的结果仍然为布尔型值TRUE或FALSE，同样要求运算符两边的表达式的数据类型必须要一致。比较运算符包括(NOT)ISNULL、(NOT)LIKE、(NOT)IN和(NOT)BETWEEN…AND运算符。

4．逻辑运算符

逻辑运算符用于组合多个关系运算符及比较运算符。将一个运算结果(TRUE、FALSE、NULL)与另一个运算结果(TRUE、FALSE、NULL)进行AND、OR或NOT运算，运算结果为布尔型值TRUE、FALSE或NULL。逻辑运算符包括AND、OR和NOT运算符。

5．连接运算符

PL/SQL中提供了一个字符串连接运算符“||”，可以将其两边的字符串合并为一个字符串。

例如：

连接运算符 运算结果

'hello,'||'world!' hello,world!

6．说明

(1)将运算符和各种类型的变量组合，便构成了PL/SQL中的各种类型的表达式。例如，由算术运算符连接的表达式叫做算术表达式，由关系运算符连接的表达式叫做关系表达式。

(2)在同一个表达式中如果有多个运算符，Oracle会按照运算符默认的优先级进行计算，优先级高的运算符比优先级低的运算符先执行，同一级别的运算符按从左到右的顺序执行。Oracle中运算符默认的优先级如表7-3所示。说明：优先级按排列顺序从高到低变化，排在同一行的优先级相同。在实际应用中，经常用括号来控制运算符的优先顺序。7.3PL/SQL的控制结构计算机语言专家已经证明，无论多么复杂的程序，都是由3种基本的程序控制结构组成的。这3种基本的程序控制结构分别是：顺序结构、条件结构和循环结构。其中，顺序结构是最常使用的自然形式的结构，程序是按照语句出现的先后次序顺序执行的，这里不再赘述。下面着重分析PL/SQL中的条件结构和循环结构。7.3.1条件结构

条件结构就是根据条件表达式的值决定程序的执行分支。

1．IF语句

IF语句的语法如下：

IF<条件1>THEN

语句序列1；

[ELSIF<条件2>THEN

语句序列2；

[ELSE语句序列n；]

ENDIF；

其中，条件表示的是一个布尔型的变量或表达式，语句序列表示的是PL/SQL语句。

IF语句执行流程如图7-1所示。图7-1IF语句执行流程

【例7.1】将消费表“consume”中某医保卡的消费金额进行统计并输出，消费金额在1000以上的为“高消费”，500～1000之间的为“中消费”，0～500之间的为“低消费”，0以下的为“其他”。

SETSERVEROUTPUTON

DECLARE

sum1NUMBER(7,2);

chCHAR(15):=‘319970452100037’;

BEGIN

SELECTsum(csmoney)INTOsum1FROMconsumeWHEREcno=ch;

DBMS_OUTPUT.PUT_LINE('医保卡'||ch); IF(sum1>1000)THEN

DBMS_OUTPUT.PUT_LINE(‘消费金额为’||sum1||‘高 消费’);

ELSIF(sum1>500ANDsum1<=1000)THEN

DBMS_OUTPUT.PUT_LINE(‘消费金额为’||sum1|| ‘中等消费’);

ELSIF(sum1>=0ANDsum1<=500)THEN

DBMS_OUTPUT.PUT_LINE(‘消费金额为’ ||sum1||‘低消费’);

ELSEDBMS_OUTPUT.PUT_LINE(‘其他’);

ENDIF;

END;执行结果为

本例体现了利用PL/SQL可以很好地将SQL和过程化控制结合起来解决实际问题。由于每个员工只拥有一张医保卡，因此先利用SQL语句求得某医保卡所消费的金额，然后再通过过程化的条件结构加以判断此消费金额在哪个消费范围内，进而就能确定某个员工的消费水平了。

注意：

(1)“PUT_LINE”是Oracle服务器内置包“DBMS_OUTPUT”的一个函数，用于输出一行信息到客户端屏幕上。

(2)在SQL*Plus或iSQL*Plus中，应将环境变量SERVEROUTPUT设为ON，即允许服务器结果输出，具体命令如下：

SETSERVEROUTPUTON

2．CASE语句

CASE语句也称为多分支语句，可以增强程序的可读性，并且使程序更加有效。CASE语句有两种格式：标准CASE语句和搜索CASE语句。

1)标准CASE语句

标准CASE语句的语法如下：

CASE<表达式>

WHEN<常量1>THEN语句序列1；

WHEN<常量2>THEN语句序列2；

WHEN<常量n>THEN语句序列n；

[ELSE语句序列n+1；]

ENDCASE；其中：

①表达式的值必须是整型或字符型，常量1～n也必须是整型或字符型。

②当没有一个常量与表达式的值相同时，若有ELSE的话，则执行ELSE后面的语句序列；若无ELSE的话，CASE将不进行任何处理。

③在同一个CASE语句中，CASE后的常量值必须互不相同。

标准CASE语句执行流程如图7-2所示。图7-2标准CASE语句执行流程

【例7.2】利用标准CASE语句根据grade变量的值A、B、C、D、E分别输出“优秀”、“良好”、“中等”、“及格”和“不及格”。

SETSERVEROUTPUTON

DECLARE

gradeCHAR(1):='B';

BEGIN

CASEgrade

WHEN‘A’THENDBMS_OUTPUT.PUT_LINE(‘优秀’);

WHEN‘B’THENDBMS_OUTPUT.PUT_LINE(‘良好’);

WHEN‘C’THENDBMS_OUTPUT.PUT_LINE(‘中等’);

WHEN‘D’THENDBMS_OUTPUT.PUT_LINE(‘及格’);

WHEN‘E’THENDBMS_OUTPUT.PUT_LINE(‘不及格’);

ELSEDBMS_OUTPUT.PUT_LINE(‘不存在’);

ENDCASE;

END;执行结果为

2)搜索CASE语句

由IF语句做多分支的条件判断很麻烦，容易出现逻辑错误，并且常会导致程序执行结果的错误。这时，可以通过搜索CASE语句来做。

搜索CASE语句的语法如下：

CASE

WHEN<搜索条件1>THEN语句序列1；

WHEN<搜索条件2>THEN语句序列2；

WHEN<搜索条件n>THEN语句序列n；

[ELSE语句序列n+1；]

ENDCASE；其中，搜索条件的值必须为布尔型值(TRUE或FALSE)。此种CASE语句按照搜索条件出现的先后顺序，依次进行判断，进而执行。如果一个搜索条件的值为TRUE，则执行该条件后的语句序列，其后序的搜索条件不再进行计算，直接退出CASE语句。如果所有搜索条件的值都不是TRUE，则ELSE子句被执行。ELSE子句是可选的，当没有ELSE子句时，应保证搜索条件的值至少有一个是TRUE。

【例7.3】用搜索CASE语句重做例7.1。

SETSERVEROUTPUTON

DECLARE

sum1NUMBER(7,2);

chCHAR(15):=‘319970452100037’;

BEGIN

SELECTsum(csmoney)INTOsum1FROMconsumeWHEREcno=ch;

DBMS_OUTPUT.PUT_LINE(‘医保卡’||ch);

CASE WHEN(sum1>1000)THENDBMS_OUTPUT.PUT_LINE(‘消费金额为’||sum1||‘高消费’);

WHEN(sum1>500ANDsum1<=1000)THENDBMS_OUTPUT.PUT_LINE(‘消费金额为’||sum1||‘中等消费’);

WHEN(sum1>=0ANDsum1<=500)THENDBMS_OUTPUT.PUT_LINE(‘消费金额为’||sum1||‘低消费’);

ELSEDBMS_OUTPUT.PUT_LINE(‘其他’);

ENDCASE;

END;

执行结果同例7.1。7.3.2循环结构

为了在PL/SQL程序中重复执行某些语句序列，可以使用循环结构。在PL/SQL中的循环结构有3种类型，即：

(1) LOOP循环：一直运行，直到遇到EXIT语句为止。

(2) WHILE-LOOP循环：一直运行，直到指定的条件不再满足为止。

(3) FOR循环：重复运行指定的次数。

1．LOOP循环

LOOP循环也称简单循环，语法格式如下：

LOOP

语句序列；

<退出循环语句>；

ENDLOOP；

其中，退出循环语句有两种形式：

① EXITWHEN<退出条件>；

② IF<退出条件>THEN

EXIT；

ENDIF；

【例7.4】计算并显示1*2*3*…*10的值。

●采用第一种退出方式：

SETSERVEROUTPUTON

DECLARE

ji10NUMBER:=1;

iNUMBER:=1;

BEGIN

LOOP

ji10:=ji10*i;

i:=i+1;

EXITWHENi>10;

ENDLOOP;

DBMS_OUTPUT.PUT_LINE(‘10以内的正整数之积为’||ji10);

END;执行结果为

2．WHILE-LOOP循环

WHILE-LOOP循环的语法如下：

WHILE<条件>LOOP

语句序列；

ENDLOOP；

当条件满足，也就是条件的值为TRUE时，执行语句序列，否则退出循环。

3．FOR循环

LOOP循环和WHILE-LOOP循环的循环次数是未知的，它取决于循环条件，而FOR循环的循环次数是已知的。FOR循环的语法如下：

FOR<循环变量>IN[REVERSE]初值表达式..终值表达式LOOP

语句序列；

ENDLOOP；

其中，IN表示循环变量的值从小到大变化，每次循环步长自动加1；INREVERSE表示循环变量的值从大到小变化，每次循环步长自动减1；FOR循环隐含声明循环变量为整数型，所以循环语句中的“初值表达式”和“终值表达式”的结果会自动转换为整型数。

FOR循环的执行过程：首先计算“初值表达式”和“终值表达式”的值，并将“初值表达式”的值赋给循环变量，判断循环变量是否超出了“终值表达式”的值。如果没有超出，则执行语句序列，循环变量自动增加步长1，再次进入到下一次循环，直到循环条件超出“终值表达式”的值，退出循环。

【例7.6】利用FOR循环重做例7.4。

SETSERVEROUTPUTON

DECLARE

ji10NUMBER:=1;

BEGIN

FORiIN1..10LOOP

ji10:=ji10*i;

ENDLOOP;

DBMS_OUTPUT.PUT_LINE(‘10以内的正整数之积为’||ji10);

END;

执行结果同例7.4。7.4PL/SQL复合类型复合数据类型需要由用户自定义给出。复合数据类型的变量内部包含了一个或多个标量。PL/SQL中常用的复合类型有记录类型、数组类型和表类型3种。在PL/SQL中复合数据类型必须先定义后使用。7.4.1记录类型(RECORD)

Oracle中的记录类型类似于C语言中的结构体，将一些彼此之间有联系又相互独立的变量逻辑上组成一个整体来表示一类事物。例如，想描述一个员工信息，员工的基本属性有员工编号、员工姓名、员工性别、出生日期、员工住址、联系电话、医保卡号、企业编号，这些属性各自独立，又共同决定了员工的特征，即可使用记录类型。

1．记录类型的定义

定义记录类型的语法结构如下：

TYPE<记录类型名>ISRECORD

( <分量1>数据类型[NOTNULL][:=|DEFAULT表达式1],

<分量2>数据类型[NOTNULL][:=|DEFAULT表达式2],

<分量n>数据类型[NOTNULL][:=|DEFAULT表达式n]

);

其中，记录类型名与分量名要符合Oracle变量的命名规则，分量的声明与一般变量的声明语法相同。

例如，定义一个员工记录类型。

TYPEstaff_record_typeISRECORD

(v_snoCHAR(5),

v_snameCHAR(20),

v_ssexCHAR(2),

v_sbirthdayDATE,

v_saddressCHAR(20),

v_stelCHAR(15),

v_cnoCHAR(15),

v_bnoCHAR(10)

);以上语句定义了一个员工记录类型“staff_record_type”，与第2章中的“staff”表结构一致。其中，“v_sno”、“v_sname”、“v_ssex”、“v_sbirthday”、“v_saddress”、“v_stel”、“v_cno”和“v_bno”分别与表中的字段相对应。

记录类型的定义还可以使用参考类型(%TYPE)，使记录类型中各分量的数据类型与相应表中各字段的数据类型一致，这样可以使得记录类型中的各分量与相应的表中字段动态绑定，当表中字段类型发生改变的时候，无需对记录类型进行改动，保证了程序的稳定性。例如：

TYPEstaff_record_typeISRECORD

(v_snostaff.sno%type,

v_snamestaff.sname%type,

v_ssexstaff.ssex%type,

v_sbirthdaystaff.sbirthday%type,

v_saddressstaff.saddress%type,

v_stelstaff.stel%type,

v_cnoo%type,

v_bnostaff.bno%type

);

2．记录类型变量的声明

在定义了一个记录数据类型后，就可以用它来声明记录类型的变量了。

声明记录类型变量的语法如下：

<变量名><记录类型名>；

例如：

v1_staffstaff_record_type;

以上语句声明了一个“staff_record_type”记录类型的变量“v1_staff”。记录类型变量的声明还可以使用%ROWTYPE参考某一个表的表结构来声明记录类型变量，也就是说，用这个表的所有字段作为记录类型数据的分量，分量名及分量的数据类型和字段名及字段的数据类型一一对应。具体语法如下：

<变量名><表名>%ROWTYPE；

例如：

v2_staffstaff%ROWTYPE;

以上语句声明了一个记录类型的变量“v2_staff”，它所含的分量和分量的数据类型与“staff”表的所有字段的字段名和字段数据类型一样。

3．记录类型变量的赋值

在声明完记录类型的变量后，就可以对记录类型变量赋值了。对于记录类型变量的赋值规则可以分为整体赋值和单个分量赋值两种。

1)整体赋值

两个相同记录类型的变量之间可以整体赋值，即必须声明为同一个记录类型，两者之间才允许整体赋值。

2)单个分量赋值

对记录类型变量中的分量可以单独赋值，不管是否声明为同一记录类型，只要两个记录类型变量之间分量的数据类型一致，记录类型变量中单个分量的赋值总是合法的。

【例7.7】考查记录类型变量的赋值。

DECLARE

TYPEbusiness1_record_typeISRECORD

(v_bnoCHAR(10),

v_bnameCHAR(50),

v_btypeCHAR(4),

v_baddressCHAR(20),

v_btelCHAR(13));

v1_businessbusiness1_record_type;

v4_businessbusiness1_record_type;

TYPEbusiness2_record_typeISRECORD

(v_bnoCHAR(10),

v_bnameCHAR(50),

v_btypeCHAR(4),

v_baddressCHAR(20),

v_btelCHAR(13));

v2_businessbusiness2_record_type;

TYPEbusiness3_record_typeISRECORD

(v_bnoCHAR(10),

v_bnameCHAR(50),

v_btypeCHAR(4));

v3_businessbusiness3_record_type;

BEGIN

SELECT*INTOv1_businessFROMbusinessWHEREbno=‘B198800101’;

v4_business:=v1_business;

v2_business:=v1_business;

v4_business.v_bno:=v1_business.v_bno;

v2_business.v_bname:=v1_business.v_bname;

v3_business.v_btype:=v1_business.v_btype;

END;执行结果为从本例中可以看出，定义了3个不同的记录类型“business1_type”、“business2_type”和“business3_type”，声明了4个记录类型变量“v1_business”、“v2_business”、“v3_business”和“v4_business”，其中“v1_business”和“v4_business”是完全相同的记录类型变量，两者之间可以相互整体赋值；“v1_business”和“v2_business”两个记录类型变量中的分量名、类型完全一致，但声明为不同的记录类型，彼此之间仍不能进行整体赋值，因此，执行结果中此语句产生错误；对于单个分量赋值来说，不管记录类型中分量数目是否相同、分量名是否相同、记录类型是否相同，只要单个分量的数据类型一致即可。

4．记录类型变量的引用

引用记录类型变量的语法如下：

<变量名>.<分量名>

例如：

v1_staff.v_sno

v1_staff.v_sname

以上语句引用了记录类型变量“v1_staff”中的“v_sno”和“v_sname”分量。对记录类型变量的引用，即为对记录类型变量中的单个分量进行引用。

【例7.8】查看“staff”表中员工编号为“00007”的员工的姓名和性别。

SETSERVEROUTPUTON

DECLARE

TYPEstaff_record_typeISRECORD

(v_snostaff.sno%type,

v_snamestaff.sname%type,

v_ssexstaff.ssex%type,

v_sbirthdaystaff.sbirthday%type,

v_saddressstaff.saddress%type,

v_stelstaff.stel%type,

v_cnoo%type,

v_bnostaff.bno%type

);

v1_staffstaff_record_type;

BEGIN

SELECT*INTOv1_staffFROMstaffWHEREsno=‘00007’;

DBMS_OUTPUT.PUT_LINE(v1_staff.v_sname||v1_staff.v_ssex);

END;执行结果为从本例可以看出，SELECT和INTO配合从数据库中查询出来员工编号为“00007”的一条记录并把它赋给自定义的记录类型“staff_record_type”的变量“v1_staff”，然后再对变量“v1_staff”中的分量“v_sname”和“v_ssex”进行引用，输出员工的姓名和性别。7.4.2数组类型(VARRY)

数组是相同类型的数据按顺序组成的一种复合数据类型。通过数组名加数组的下标索引来使用数组中的数据，下标索引从1开始。

1．数组的定义

定义数组的语法结构如下：

TYPE<数组类型名>ISVARRAY(<数组长度>)OF数据类型；

其中，数组类型名要符合Oracle变量的命名规则，数组长度用来定义数组包含的元素的个数，数组中所有元素的数据类型是一致的。例如：

TYPEv_varray_typeISVARRAY(4)OFCHAR(20);

/*定义了一个数组类型v_varray_type，包 含了4个CHAR型的数组元素*/

2．数组变量的声明

定义了数组类型之后，就可以声明数组变量了。声明数组变量的语法如下：

<变量名><数组类型名>；

例如：

v1_varryv_varray_type；

3．数组变量的初始化

数组变量在引用前必须通过数组构造函数(数组类型同名函数)来给数组元素赋初值，这样才能引用数组元素。没有初始值的元素可以赋NULL。数组初始化的语法如下：

<变量名>:=构造函数(初始值1[,初始值2,…,初始值n])

例如：

v1_varry:=v_varray_type(‘aa’,‘bb’,‘cc’,null,null);

v2_varry:=v_varray_type(null,null,null,null);

注意：数组只能整体赋初值，不能单个元素赋初值。

4．数组变量的引用

数组变量在初始化后，就可以通过下标索引引用自己的元素了，引用数组变量的语法如下：

<变量名>(<下标索引>)

例如，v1_varry(1)、v1_varry(2)、v2_varry(1)等。

【例7.9】数组类型的使用。

SETSERVEROUTPUTON

DECLARE

TYPEv_varray_typeISVARRAY(4)OFCHAR(5);

v1_varryv_varray_type;

BEGIN

v1_varry:=v_varray_type(‘eee’,null,‘ccc’,‘ddd’);

v1_varry(1):=‘aaa’;

v1_varry(2):=‘bbb’;

DBMS_OUTPUT.PUT_LINE(v1_varry(1)||v1_varry(2)||v1_varry(3)||v1_varry(4));

END;执行结果为从本例中可以看出，数组变量“v1_varry”在引用前必须利用数组的构造方法整体赋初值，赋初值之后还可以通过对单个数组元素赋值的方法来改变各个数组元素的值。这里，将数组中第一个元素的初始值“eee”修改为“aaa”，第二个元素的初始空值修改为“bbb”。

【例7.10】

利用数组类型重做例7.8。

SETSERVEROUTPUTON

DECLARE

TYPEv_varray_typeISVARRAY(4)OFCHAR(20);

v1_varryv_varray_type;

BEGIN

v1_varry:=v_varray_type(null,null,null,null);

SELECTsname,ssexINTOv1_varry(1),v1_varry(2)FROMstaffWHEREsno=‘00007’;

DBMS_OUTPUT.PUT_LINE(v1_varry(1)||v1_varry(2));

END;

执行结果同例7.8。

从本例中可以看出，员工姓名和性别都是CHAR类型的，所以可以利用CHAR类型的数组存储它们，但由于数组中元素的类型和长度定义必须是相同的，因此需要将数组元素CHAR类型的长度定义为姓名和性别的最大长度。

5．数组的属性

除了构造函数外，数组还有很多内置函数，增加了数组属性的功能，数组属性如表7-4所示。数组的属性方便了数组中元素的使用，其语法如下：

<数组变量名>.<属性>

【例7.11】数组属性的使用。

SETSERVEROUTPUTON

DECLARE

TYPEv_varray_typeISVARRAY(6)OFVARCHAR2(5);

v1_varryv_varray_type;

BEGIN

v1_varry:=v_varray_type(‘aa’,‘bb’,null,‘dd’,‘ee’,‘ff’);

DBMS_OUTPUT.PUT_LINE(‘数组v1的元素个数为：’||v1_varry.count);

IFv1_varry.exists(3)THEN

DBMS_OUTPUT.PUT_LINE(‘数组v1_varry的第3个下标索引位置上有值，值为：’||v1_varry(3));

ENDIF; DBMS_OUTPUT.PUT_LINE('数组v1_varry的第一个元素的下标索引值为：'||v1_varry.first);

DBMS_OUTPUT.PUT_LINE('数组v1_varry的最后一个元素的下标索引值为：'||v1_varry.last);

DBMS_OUTPUT.PUT_LINE('数组v1_varry的第5个元素的初始值为：'||v1_varry(5));

v1_varry(5):=null;

DBMS_OUTPUT.PUT_LINE('数组v1_varry的第5个元素置空后的值为：'||v1_varry(5)); DBMS_OUTPUT.PUT_LINE(‘数组v1_varry的第5个元素的前一个元素的下标索引值为：’||v1_varry.prior(5)||‘元素值为：’||v1_varry(v1_varry.prior(5)));

DBMS_OUTPUT.PUT_LINE(‘数组v1_varry的第5个元素的后一个元素的下标索引值为：’||v1_varry.next(5)||‘元素值为：’||v1_varry(v1_varry.next(5)));

v1_varry.delete;

DBMS_OUTPUT.PUT_LINE(‘数组v1_varry的元素个数为：’||v1_varry.count);

END;执行结果为从本例中可以看出，数组由构造函数进行初始化，构造函数和数组的名字相同，同时有一组参数，每个参数对应一个元素。如果参数为NULL，那么对应的元素就被初始化为NULL；如果创建了元素，但没有填充数据，那么元素将保持NULL值，可以被引用，但不能保存数据。另外，数组中元素的索引位置是固定连续的，尽管下标索引值为5的元素的值被置为NULL，但其前后元素的下标索引值仍为4和6。

注意：数组中不允许单个元素的删除，如果想清除某个元素的值，只能将此元素置空处理，但是可以通过数组的DELETE属性将数组中的所有元素删除。7.4.3表类型(TABLE)

表类型是另外一种常用的复合数据类型，与数组类似，也是专门用来处理集合数据的，但比数组功能强大。表类型与数组类型的主要差别如下：

(1)元素的个数是否确定：数组中的元素的个数是确定的，由数组长度决定，而表中的元素的个数是不确定的。

(2)元素之间的稀疏性不同：稀疏性描述了集合的下标索引值是否可以离散。数组类型是紧密的，它的下标索引值之间必须是连续的，一定是从1开始顺序递增的，而表总是稀疏的，它的下标索引值没有限定，既可以是离散的，也可以是连续的，可以使用任何整数，没有顺序之分。

(3)元素的创建不同：元素的创建即为元素分配内存空间。数组中的元素被引用前，必须通过构造函数对数组元素整体赋初值的方式创建数组中所有的元素，而表的元素个数是不确定的，只能单个元素进行创建，即用到哪一个元素才通过对该元素赋值的方式来分别创建表中的元素。

注意：这里所说的表类型与数据库中的表是有区别的。数据库中的表是二维表，是一种数据库对象，是用于存储数据信息的，而这里的表类型是一种数据结构，它既可以是一维的，也可以是二维的。

1．定义表类型

定义表类型的语法如下：

TYPE<表类型名>ISTABLEOF<数据类型>INDEXBYBINARY_INTEGER；

其中，表类型名要符合Oracle变量的命名规则，INDEXBYBINARY_INTEGER表示以符号整数为下标索引，数据类型定义了表中元素的数据类型，由数据类型的不同决定了表是一维的，还是二维的。当数据类型为标量或%TYPE时，表为一维表；当数据类型为记录或%ROWTYPE时，表为二维的。例如：

TYPEs1_table_typeISTABLEOFVARCHAR2INDEXBYBINARY_INTEGER；

--定义一维表类型，表元素的数据类型为VARCHAR2

TYPEs2_table_typeISTABLEOFstaff.sname%TYPEINDEXBYBINARY_INTEGER；

--定义一维表类型，表元素的数据类型为“staff”表中“sname”字段的数据类型

TYPEs3_table_typeISTABLEOFstaff%ROWTYPEINDEXBYBINARY_INTEGER；

--定义二维表类型，表元素的数据类型为“staff”的表结构

TYPEs4_table_typeISTABLEOFstaff_record_typeINDEXBYBINARY_INTEGER；

--定义二维表类型，表元素的数据类型为“staff_record_type”记录类型

2．声明表变量

定义了表类型之后，就可以声明表变量了。声明表变量的语法如下：

<变量名><表类型名>；

例如：

s1_tables1_table_type;--s1_table为一维表变量

s3_tables3_table_type;--s3_table为二维表变量

3．引用表变量

与数组不同，对于表变量来说，初始化是自动进行的。在声明完表变量后，就可以对表变量进行引用了，但根据表变量的类型不同(一维还是二维)，引用的语法有所不同，具体如下：

一维表变量的引用语法如下：

<变量名>(<下标索引>)

二维表变量的引用语法如下：

<变量名>(<下标索引>).<分量/字段名>

【例7.12】表类型的使用。

SETSERVEROUTPUTON

DECLARE

TYPEs1_table_typeISTABLEOFVARCHAR2(8)INDEXBYBINARY_INTEGER;

TYPEemployee_typeISRECORD

(v_enoCHAR(5),

v_enameCHAR(20)

);

TYPEs3_table_typeISTABLEOFemployee_typeINDEXBYBINARY_INTEGER;

s1_tables1_table_type;

s3_tables3_table_type;

BEGIN

s1_table(3):=‘hello’;

s1_table(5):=‘world!’;

s3_table(-2).v_eno:=‘10010’;

s3_table(-2).v_ename:=‘张平’;

DBMS_OUTPUT.PUT_LINE(s1_table(3)||s1_table(5));

DBMS_OUTPUT.PUT_LINE(s3_table(-2).v_eno||s3_table(-2).v_ename);

END;执行结果为本例中利用基本标量类型定义了一维表类型“s1_table_type”，用记录类型定义了二维表类型“s3_table_type”。“s1_table”和“s3_table”分别为“s1_table_type”和“s3_table_type”类型的变量。对表类型的使用，需要注意以下几点：

(1)声明了一个表变量时，表的元素数目并未确定，通过对表元素的赋值来真正地创建一个表元素，即为此表元素分配内存空间。其他未赋值的表元素不分配内存空间，例如，由于“s1_table(4)”没有被赋值，所以没有被分配内存空间。

(2)表元素是通过索引值定位的，例如“s1_table(3)”、“s1_table(5)”、“s3_table(-2)”。索引值又称为Key值，它们是BINARY_INTEGER类型，没有确定范围，且没有顺序之分。

【例7.13】利用表类型重做例7.8。

SETSERVEROUTPUTON

DECLARE

TYPEs1_table_typeISTABLEOFCHAR(20)INDEXBYBINARY_INTEGER;

TYPEs3_table_typeISTABLEOFstaff%ROWTYPEINDEXBYBINARY_INTEGER;

s1_tables1_table_type;

s3_tables3_table_type;

BEGIN

SELECTsname,ssexINTOs1_table(-1),s1_table(10)FROMstaffWHEREsno=‘00007’;

DBMS_OUTPUT.PUT_LINE(s1_table(-1)||s1_table(10));

SELECT*INTOs3_table(-5)FROMstaffWHEREsno=‘00007’;

DBMS_OUTPUT.PUT_LINE(s3_table(-5).sname||s3_table(-5).ssex);

END;执行结果为本例中“s1_table_type”为一维表类型，作用类似于例7.10中的数组类型“v1_varry_type”，所不同的是表元素不需要初始化就可以使用，而数组元素必须要进行初始化；“s3_table_type”为二维表类型，作用类似于例7.8中的记录类型“staff_record_type”，所不同的是表变量的引用要通过索引值定位。

4．表的属性

在PL/SQL中，表和数组拥有大致相同的属性，用法差别不大。这里强调说明一下表的DELETE属性。与数组不同，对于表来说，DELETE属性的使用有3种形式，具体形式如下：

(1) DELETE，表示删除表中的所有元素。

(2) DELETE(i)，表示删除表中索引值为i的元素。

(3) DELETE(i,j)，表示删除表中索引值在i和j之间的所有元素。

其中，DELETE属性是数组和表所共有的属性，而DELETE(i)和DELETE(i,j)是表所特有的属性。

【例7.14】表属性的使用，注意与数组的区别。

SETSERVEROUTPUTON

DECLARE

TYPEt1_table_typeIStableOFvarCHAR2(5)indexbybinary_integer;

t1_tablet1_table_type;

BEGIN

t1_table(5):=‘aa’;

t1_table(-2):=‘bb’;

t1_table(-10):=‘cc’;

t1_table(0):=‘dd’;

t1_table(8):=‘ee’;

t1_table(99):=‘ff’;

DBMS_OUTPUT.PUT_LINE('表t1_table的元素个数为：'||t1_table.count);

IFt1_table.exists(5)THEN

DBMS_OUTPUT.PUT_LINE(‘表t1_table的下标索引值为5的位置上有值，值为：’||t1_table(5));

ENDIF;

DBMS_OUTPUT.PUT_LINE(‘表t1_table的第一个元素的下标索引值为：’||t1_table.first);

DBMS_OUTPUT.PUT_LINE(‘表t1_table的最后一个元素的下标索引值为：’||t1_table.last);

DBMS_OUTPUT.PUT_LINE('表t1_table的下标索引值为5的前一个元素的下标索引值为：'||+t1_table.prior(5)||'元素值为：'||t1_table(t1_table.prior(5)));

DBMS_OUTPUT.PUT_LINE(‘表t1_table的下标索引值为5的后一个元素的下标索引值为：’||t1_table.next(5)||‘元素值为：’||t1_table(t1_table.next(5)));

t1_table.delete(8);

DBMS_OUTPUT.PUT_LINE(‘删除下标索引值为8的元素后，表t1_table的下标索引值为5的元素的后一个元素的下标索引值为：’||t1_table.next(5)||‘元素值为：’||t1_table(t1_table.next(5)));

DBMS_OUTPUT.PUT_LINE(‘表t1_table的元素个数为：’||t1_table.count);

t1_table.delete(-2,5);

DBMS_OUTPUT.PUT_LINE(‘删除下标索引值在-2和5之间的元素后，表t1_table的元素个数为：’||t1_table.count);

t1_table.delete;

DBMS_OUTPUT.PUT_LINE(‘删除所有的元素后，表t1_table的元素个数为：’||t1_table.count);

END;执行结果为

从本例中可以看出，表不需要初始化，可以直接对单个元素赋值。表中元素是可以离散分布的，没有赋值的元素不存在，所以下标索引值为5的元素的前一个元素的索引值为0而不是4。使用DELETE属性的3种方式可以灵活地删除表中的元素。7.5游标

SELECT语句的执行结果是一个结果集，只能对该结果集的数据进行浏览。SELECT与INTO一起使用也只能处理单行记录信息，无法做到对结果集中的数据逐行进行处理。如果希望对SELECT结果集中的数据逐行地处理，就需要使用游标技术了。游标(CURSOR)是PL/SQL中的一个参考类型，是将从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用FETCH语句可以移动该指针，从而对游标中的数据逐行进行各种操作，还可以将操作结果写回数据表中。7.5.1游标的基本使用

游标的使用过程是非常规范的，分为以下几个步骤：

(1)声明游标。

(2)打开游标。

(3)提取游标中的数据并处理数据。

(4)关闭游标。

根据定义游标时是否加入参数的定义，将游标分为不带参数游标和带参数游标两种。

1．不带参数的游标

1)声明游标

声明游标的语法如下：

CURSOR<游标名>ISSELECT语句；

说明：

①声明游标要在程序块的声明部分进行。

② CURSOR是声明游标的关键字。

③游标名由用户定义，要符合Oracle变量的命名规则。

④ SELECT语句不能含INTO子句，但可以带WHERE、ORDERBY、GROUPBY等子句，也可以包含子查询。例如：

CURSORstaff1_cursorISSELECT*FROMstaff;

CURSORstaff2_cursorISSELECT*FROMstaffWHEREsbirthday='10-10月-77';

2)打开游标

在声明了游标之后，就可以在程序块的执行部分打开游标。打开游标实际上就是执行游标所对应的SELECT语句，将其查询结果以临时表的形式存放在内存中，游标中的数据指针指向首记录。

打开游标的语法如下：

OPEN<游标名>；

例如：

OPENstaff1_cursor；

OPENstaff2_cursor；

3)提取游标中的数据

通过FETCH语句提取游标中的数据，程序每执行一次FETCH语句只能取一行数据，每次取出数据之后，游标中的数据指针就向下移动一行。

FETCH语句的语法如下：

FETCH<游标名>INTO<变量1>[,<变量2>,…]

或

FETCH<游标名>INTO<记录类型变量>

其中，在FETCH语句中，INTO后面的变量列表必须与声明游标的SELECT语句中的列在个数、类型、顺序上一致。对于记录类型变量，一般比较方便的方法是用%ROWTYPE先定义一个与游标结构一致的记录类型变量，然后再将一行记录放到该变量中。定义一个记录类型变量的常用格式如下：