第7章 PL SQL编程.docx

第7章 PL SQL编程.docx

《第7章 PL SQL编程.docx》由会员分享，可在线阅读，更多相关《第7章 PL SQL编程.docx（60页珍藏版）》请在冰豆网上搜索。

第7章PLSQL编程

第7章PL/SQL编程

学习目标：

●了解PL/SQL语言的特点和应用。

●掌握PL/SQL的语句结构。

●掌握游标的定义和应用方法。

●了解事务在PL/SQL程序中的应用。

●熟悉PL/SQLDeveloper工具的使用方法。

SQL语言是对数据库的一种访问语言，其特点是非过程化，不能实现结构控制，提供的编程能力较弱。

Oracle作为一个强大的数据库管理系统，迫切需要一种兼容SQL的编程语言，PL/SQL的出现正是为了解决这一问题。

PL/SQL（ProceduralLanguage/SQL）是一种过程化语言，它不仅允许嵌入SQL语句，而且它与C、C++、Java等语言一样关注于处理细节，允许定义常量和变量，允许使用条件语句和循环语句，允许使用异常来处理各种错误，因此可以用来实现比较复杂的业务逻辑。

本章主要讲述PL/SQL的编程基础，介绍PL/SQL语言特点、语句结构、游标定义和应用方法、事务在程序设计中的应用，以及PL/SQLDeveloper的开发环境等。

.1PL/SQL语言基础

PL/SQL是Oracle对标准数据库语言的扩展，它不是一个独立的产品，而是一个整合到Oracle服务器和Oracle工具中的技术，从版本6开始PL/SQL就被可靠地整合到Oracle中了，近几年中更多的开发人员和DBA开始使用PL/SQL。

可以把PL/SQL看作Oracle服务器内的一个引擎，SQL语句执行者处理单个的SQL语句，PL/SQL引擎处理PL/SQL程序块。

当PL/SQL程序块在PL/SQL引擎处理时，Oracle服务器中的SQL语句执行器处理PL/SQL程序块中的SQL语句。

.1.1PL/SQL语言优点

PL/SQL是一种高效的事务处理语言，它具备以下优点：

●PL/SQL是一种高性能的基于事务处理的语言，能运行在任何Oracle环境中，支持所有数据处理命令。

通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。

●PL/SQL支持所有SQL数据类型和所有SQL函数，同时支持所有Oracle对象类型。

●PL/SQL块可以被命名并存储在Oracle服务器中，同时也能被其他PL/SQL程序或SQL命令调用，任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性。

●可以使用Oracle数据工具管理PL/SQL程序的安全性。

可以授权或撤销数据库其他用户访问PL/SQL程序权限

●PL/SQL代码可以使用任何ASCII文本编辑器编写，所以对任何能够运行Oracle的操作系统都是非常便利的。

●对于SQL，Oracle必须在同一时间处理每一条SQL语句，在网络环境下这就意味着每一个独立的调用都必须被Oracle服务器处理，从而占用大量的服务器时间，导致网络拥挤。

而PL/SQL以整个语句块形式发给服务器，可以减少网络拥挤。

.1.2PL/SQL的基本结构

PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL程序包含了一个或多个逻辑块，每个块都可以划分为三个部分：

声明部分（用DECLARE开头）、执行部分（以BEGIN开头）和异常处理部分（以EXCEPTION开头）。

其中执行部分是必须的，其他两个部分可选。

无论PL/SQL程序段的代码量有多大，基本结构都是由这三部分组成。

PL/SQL块的基本语法结构如下：

DECLARE

声明部分

BEGIN

执行部分

EXCEPTION

异常处理部分

END;

其中：

●声明部分：

此部分用来定义变量和常量的数据类型和初始值，以及程序中要使用的函数、游标、异常处理名称等。

如果没有需要声明的对象，可以省略这一部分。

●执行部分：

此部分是PL/SQL块中指令部分，所有可执行语句都放在这一部分，包括对数据库的操作语句和各种流程控制语句，也可以嵌套其他的PL/SQL块。

注意，执行部分至少包含一条可执行语句。

●异常处理部分：

包含在执行部分中。

当程序检测到错误而产生异常时，就转到由EXCEPTION标识的部分执行异常处理程序。

这部分是可选的，利用异常处理可以提高PL/SQL程序的健壮性。

PL/SQL块可以是不包含声明部分或异常处理部分的特殊结构，也可以是带有命名的PL/SQL程序块，语法结构如下：

<>/*命名的PL/SQL程序块*/

DECLARE

声明部分

BEGIN

执行部分

EXCEPTION

异常处理部分

END<>;

PL/SQL程序块中还可以包含PL/SQL程序，即主块中包含子块，其语法结构如下：

DECLARE

主块声明部分

BEGIN

主块执行部分

DECLARE

子块声明部分

BEGIN

子块执行部分

EXCEPTION

子块异常处理部分

END；

EXCEPTION

主块异常处理部分

END;

.1.3PL/SQL字符集

与其他程序设计语言一样，PL/SQL也有其字符集，包括允许使用的合法字符，操作符以及一些常用的字符。

1.合法字符

在使用PL/SQL进行程序设计时，可以使用的有效字符包括以下四类：

●大写字母A~Z和小写字母a~z

●数字0~9

●符号（）、+、-、*、/、<、>、=、！

、~、^、;、:

、.、’、@、”、#、$、_、{}、[]、?

●制表符、空格符、回车符等非显示的间空符号

可以使用合法字符作为标识符，标识符由一个字母开始，后面选择性的跟随任意多的字母、数字、货币符号（$）、下划线（_）、#等符号组成。

不允许使用空格、斜线（/）、短横线（-）、&、%。

最大长度为30个字符。

表7.1列出了部分合法和非法标识符。

表7.1合法和非法的标识符

合法的

非法的

注释

X

You&me

不允许有&

LastName

on/off

不允许有/

Phone#

Stu-name

不允许有-

t_2

5_m

必须由字母开头

Oracle$char

_num

必须由字母开头

2.操作符

PL/SQL提供的操作符有：

算术操作符、关系操作符、逻辑操作符。

（1）算术操作符用来执行算术运算，表7.2列出了常用的算术操作符。

表7.2算术操作符

操作符

操作说明

+

加

-

减

/

除

*

乘

**

乘方

（2）关系操作符（又称比较操作符）主要用于测试两个表达式之间的满足关系，其运算结果为逻辑值TRUE或FALSE。

表7.3列出了PL/SQL中常用的关系操作符。

表7.3关系操作符

操作符

操作说明

<

小于操作符

<=

小于或等于操作符

>

大于操作符

>=

大于或等于操作符

=

等于操作符

!

=

不等于操作符

<>

不等于操作符

:

=

赋值操作符

ISNULL

检索空数据。

如果操作数为NULL返回TRUE

LIKE

检索匹配字符样式的数据

BETWEEN…AND…

检索两值之间的内容

IN

检索匹配列表中的值

（3）逻辑操作符用于对某个条件进行测试，运算结果为TRUE或FALSE，表7.4列出了PL/SQL中常用的的逻辑操作符。

表7.4逻辑操作符

操作符

操作说明

AND

两个表达式为真则结果为真

OR

只要有一个为真则结果为真

NOT

取相反的逻辑值

3.其他字符

PL/SQL为支持编程，还使用了一些其他符号，表7.5列出了PL/SQL中常用符号。

表7.5常用符号

操作符

操作说明

（）

列表分隔

;

语句结束

:

=

赋值

‘‘

字符串定界符

||

并置

--

注释符

/**/

注释定界符

.1.4PL/SQL基本语法

4.常量与变量

PL/SQL编程中使用的常量与变量都要在PL/SQL块的声明部分进行声明。

（1）声明

声明常量和变量的基本语法如下：

<常（变）量名>[CONSTANT]<数据类型>[NOTNULL][（宽度）:

=<初始值>]

注意问题：

●常（变）量名必须以字母开头，其后跟可选的一个或多个字母、数字或特殊字符#、$、_，中间不允许有空格，长度不能超过30个字符。

●如果有CONSTANT，则表明声明的是常量。

●如果有NOTNULL，则表明声明的常（变）量不能为空，即在声明时就必须赋值。

如果没有赋值，则表示初始化为NULL。

●赋初始值可以是表达式、文本、或者其他变量、函数。

例如：

①声明一个长度为10B的变量count，初值为1，类型为VARCHAR２。

countVARCHAR2（10）:

=‘1’;

②声明一个NUMBER数据类型的常量c_limit，并赋予初值5000.00。

c_limitCONSTANTNUMBER（8.2）:

=5000.00;

③声明一个NUMBER数据类型的常量c_area，并用表达式给它赋初值。

c_areaCONSTANTNUMBER（8.5）:

=3.14159*3**2;

（2）作用域

变量的作用域指变量的有效使用范围，它从变量声明开始，直到当前程序块结束，只有在其作用域范围内，程序才能使用该变量，否则将导致编译错误。

在同一程序块中，不允许声明两个同名变量，但在不同程序块内可以声明两个同名变量。

在程序块内声明的变量称为局部变量，在程序块外声明的变量称为全局变量。

引用程序块内的局部变量不需要加限定词，而引用块外的全局变量需要加限定词（即父块的名称）。

子块中可以引用父块的变量，但是父块中不能引用子块的变量。

如果父块与子块具有相同的变量名，在子块中引用这个变量时若是不加限定词则引用的是子块内的局部变量。

若两个块不是父子关系，则他们之间的变量不能相互引用。

（3）变量的属性

变量有名字和数据类型两个属性。

变量名用于标识该变量，变量的数据类型确定了该变量存放值的格式及允许的运算。

在PL/SQL中，使用“%”表示属性提示符，后面紧跟属性名。

%TYPE和%ROWTYPE是PL/SQL中两个特殊的属性，常用来定义变量，使被定义变量的数据类型与一个已定义变量（或参照表中的记录）的数据类型相一致。

●%TYPE

使用%TYPE属性定义变量时，被定义变量的数据类型与一个已经定义了的变量的数据类型相一致，当被参照的变量数据类型改变后，新定义变量的数据类型也随之改变。

当无法准确知道被参照变量的数据类型时，只能采用这种定义方法。

定义格式为：

<新变量名><被参照表.被参照列>%TYPE

【例7.1】声明三个变量“v_班级名称”，“v_班级人数”，“v_班主任”，其数据类型分别对应“班级表”中相应字段的数据类型。

根据输入的班级代码，输出对应的班级名称、人数和班主任信息。

代码如下：

Setserverouton

DECLARE

v_班级名称班级表.班级名称%TYPE;

v_班级人数班级表.班级人数%TYPE;

v_班主任班级表.班主任%TYPE;

BEGIN

SELECT班级名称,班级人数,班主任intov_班级名称,v_班级人数,v_班主任

FROM班级表

WHERE班级代码='&班级代码';

DBMS_OUTPUT.PUT_LINE（'班级名称：

'||v_班级名称）;

DBMS_OUTPUT.PUT_LINE（'班级人数：

'||v_班级人数）;

DBMS_OUTPUT.PUT_LINE（'班主任：

'||v_班主任）;

END;

●%ROWTYPE

使用%ROWTYPE属性能够定义记录变量，使得被定义的记录成员个数、名称、数据类型与已定义的表或视图中列的个数、名称和数据类型完全相同。

这种定义方式，可以定义一个表，当被参照表中列及数据类型改变时，新定义表中的列及数据类型自动改变。

当一个表有较多列时，使用%ROWTYPE定义记录，比使用%TYPE要方便，并且不容易出错。

定义格式为：

<新表名><被参照表>%ROWTYPE

【例7.2】声明一个变量“v_班级”，与班级表具有相同的数据类型。

根据输入的班级代码，输出对应的班级名称、人数和班主任信息。

代码如下：

DECLARE

v_班级班级表%ROWTYPE;

BEGIN

SELECT*intov_班级FROM班级表

WHERE班级代码='&班级代码';

DBMS_OUTPUT.PUT_LINE（'班级名称：

'||v_班级.班级名称）;

DBMS_OUTPUT.PUT_LINE（'班级人数：

'||v_班级.班级人数）;

DBMS_OUTPUT.PUT_LINE（'班主任：

'||v_班级.班主任）;

END;

5.数据类型

PL/SQL使用Oracle数据库中提供的数据类型，这些数据类型可以分为四类，分别是标量类型，复合类型，引用类型和LOB类型，以及用于数据类型定义的运算符（%TYPE，%ROWTYPE）。

标量类型没有内部组件；而复合类型包含了能够被单独操作的内部组件；引用类型类似于3G语言中的指针，能够引用一个值；LOB类型的值就是一个LOB定位器，能够指示出大对象（如图像）的存储位置。

下面介绍几种PL/SQL中常用的预定义数据类型。

（1）标量类型

标量类型分为四类：

数字、字符、布尔和日期/时间。

数字类型：

可以存储整数、实数和浮点数，可以表示数值的大小，参与计算。

表7.6列出了常用的数字类型的数值范围、子类型和使用描述。

表7.6常用的PL/SQL数据类型

数据类型

取值

子类型

描述

BINARY_INTEGER

-2**31至2**31

NATURAL

NATURALN

NPOSITIVE

POSITIVEN

SIGNTYPE

用于存储单字节整数。

所需的存储空间小于NUMBER

用于限制范围的子类型（SUBTYPE）：

NATURAL：

用于非负数POSITIVE：

只用于正数

NATURALN：

只用于非负数和非NULL值

POSITIVEN：

只用于正数，不能用于NULL值

SIGNTYPE：

只有值:

-1、0或1.

NUMBER

1E-130至10E125

DEC

DECIMAL

DOUBLE

PRECISION

FLOAT

INTEGERIC

INT

NUMERIC

存储数字值，包括整数和浮点数。

可以选择精度和刻度方式，语法：

NUMBER（p，s）

p和s时可选的，它们必须是整数，p表示精度，指定数字的总长度，取值范围为1~38；s表示刻度，指定小数点后面的位数，取值范围为-84~127

PLS_INTEGER

-2**31至2**31

REAL

SMALLINT

与BINARY_INTEGER基本相同，运算的速度要高于NUMBER和BINARY_INTEGER。

●字符型：

字符类型可以存放字符和数字混合的数据，表现词和文章，操作字符串。

表7.7列出了常用的字符类型的数值范围、子类型和使用描述。

表7.7常用的字符类型

数据类型

取值

子类型

描述

CHAR

最大长度32767字节

CHARACTER

存储定长字符串，如果长度没有确定，缺省是1。

但CHAR类型在数据库的字段中最大存储长度为2000个字节，所以，不能往数据库CHAR类型字段中插入超过2000字节的字符。

LONG

最大长度2147483647字节

存储可变长度字符串

RAW

最大长度32767字节

用于存储二进制数据和字节字符串，当在两个数据库之间进行传递时，RAW数据不在字符集之间进行转换。

LONGRAW

最大长度2147483647字节

与LONG数据类型相似，同样他也不能在字符集之间进行转换。

ROWID

18个字节

与数据库ROWID伪列类型相同，能够存储一个行标示符，可以将行标示符看作数据库中每一行的唯一键值。

VARCHAR2

最大长度32767字节

STRING

VARCHAR

与VARCHAR数据类型相似，存储可变长度的字符串。

声明方法与VARCHAR相同

●布尔型：

布尔类型能存储逻辑值TRUE、FALSE和NULL（NULL代表缺失、未知或不可用的值）。

只有逻辑操作符才允许应用在布尔变量上。

数据库SQL类型并不支持布尔类型，只有PL/SQL才支持。

所以就不能往数据库中插入或从数据库中检索出布尔类型的值。

●日期/时间型：

用于定义日期和时间。

分为DATE和TIMESTAMP两种类型。

表7-8列出了常用日期/时间数据类型。

表7.8常用的日期/时间类型

数据类型

取值

描述

DATE

公元前4721年1月1日

到公元9999年12月31日

存储固定长的日期和时间值，默认的DATE格式是由初始化参数NLS_DATE_FORMATE来设置的，如：

默认格式为：

DD-MON-YY。

可以对日期进行加减运算

TIMESTAMP

与DATE类型相同，精确到时间秒

包含了年、月、日、时、分、秒，还包括了上午、下午。

【例7.3】声明一个TIMESTAMP类型的变量，并为它赋值。

代码如下：

SETSERVEROUTON

DECLARE

checkoutTIMESTAMP

（2）;

BEGIN

checkout:

='10-6月-0707:

48:

53.275';

DBMS_OUTPUT.PUT_LINE（checkout）;

END;

程序输出结果为：

10-6月-0707.48.53.275上午

（2）LOB型

LOB（LargeObject）数据类型提供了BFILE、BLOB、CLOB和NCLOB具体类型，可以最大存储4G的无结构数据（例如：

文本、图形、视频剪辑和音频等）块。

并且，它们允许高效地随机地分段访问数据。

PL/SQL通过定位器来操作LOB的。

例如，当我们查询出一个BLOB值，只有定位器被返回。

如果在事务中得到定位器，LOB定位器就会包含事务的ID号，这样我们就不能在另外一个事务中更新LOB内容了。

同样，我们也不能在一个会话中操作另外一个会话中的定位器。

.2PL/SQL程序结构

PL/SQL提供了大量的程序控制语句来辅助用户的开发工作。

这些程序结构包括选择结构、循环结构和顺序结构等。

每种结构都有多种语法格式。

.2.1选择结构

选择结构又称条件结构，通过判断条件的真假来选择执行不同的语句段。

选择结构允许嵌套执行。

6.IF…THEN…ENDIF结构

语法格式如下：

IF条件表达式THEN

语句段

ENDIF；

此结构中当条件表达式为“真”时，执行语句段，否则结束条件判断。

7.IF…THEN…ELSE…ENDIF结构

语法格式如下：

IF条件表达式THEN

语句段1

ELSE

语句段2

ENDIF;

当条件表达式为“真”时，执行语句段1，当条件表达式为“假”时，执行语句段2。

【例7.4】查找“ASP.NET程序设计”课程，若是“备注”信息为“C#”，将其改为“J#”，否则将“备注”信息置空。

代码如下：

SETSERVEROUTON

DECLARE

v_课程课程表%ROWTYPE;

BEGIN

SELECT*INTOv_课程FROM课程表

WHERE课程名='ASP.NET程序设计';

IFv_课程.备注='c#'THEN

UPDATE课程表

SET备注='J#'

WHERE课程名=v_课程.课程名;

ELSE

UPDATE课程表

SET备注=''

WHERE课程名=v_课程.课程名;

ENDIF;

DBMS_OUTPUT.PUT_LINE（'更新完成!

'）;

END;

8.IF…THEN…ELSIF…ENDIF结构

语法格式如下：

IF条件表达式1THEN

语句段1

ELSIF条件表达式2THEN

语句段2

ELSIF条件表达式3THEN

语句段3

……

ELSE

语句段n

ENDIF;

当条件表达式1为“真”时，执行语句段1，否则判断ELSIF后面的条件表达式2，若为“真”则执行语句段2，否则继续判断ELSIF后的条件表达式，直到所有的条件表达式都为“假”，则执行ELSE后面的语句段n。

通常情况下建议尽量使用ELSIF代替ELSE子句的嵌套条件语句，以提高程序的可读性。

注意：

保留字是ELSIF而不是ELSEIF。

【例7.5】学生成绩按照分数段分为“优秀”、“良好”、“合格”、“不及格”4种等级，根据等级的不同，可以输出对应的分数段，代码如下：

SETSERVEROUTON

DECLARE

v_resultVARCHAR2（20）:

='良好';

BEGIN

IFv_result='优秀'THEN

DBMS_OUTPUT.PUT_LINE（'90~100'）;

ELSIFv_result='良好'THEN

DBMS_OUTPUT.PUT_LINE（'80~89'）;

ELSIFv_result='合格'THEN

DBMS_OUTPUT.PUT_LINE（'60~79'）;

ELSIFv_result='不及格'THEN

DBMS_OUTPUT.PUT_LINE（'<60'）;

ELSE

DBMS_OUTPUT.PUT_LINE（'不存在该等级'）;

ENDIF;

END;

9.CASE结构

语法格式1如下：

CASE

WHEN条将表达式1THEN

语句段1

WHEN条将表达式2THEN

语句段2

……

ELSE

语句段n

ENDCASE;

使用CASE语句可以对多种条件表达式进行比较判断，执行相应的语句段。

CASE结构还有另一种语法结构。

语法格式2如下：

CASE条件表达式

WHEN条件表达式结果1THEN

语句段1

WHEN条件表达式结果2THEN

语句段2

……

ELSE

语句段n

ENDCASE;

【例7.6】从“成绩表”中根据学生的学号和课程号查找学生某门课程的成绩，并输出成绩的等级。

采用第一种语法格式，代码如下：

SETSERVEROUTON

DECLARE

v_scoreNUMBER（3）;

BEGIN

SELECT成绩intov_scoreFROM成绩表

WHERE学号=&学号and课程号=&课程号;

CASE

WHEN（v_score>=90andv_score<=100）THEN

DBMS_OUTPUT.PUT_LINE（'成绩：

'||v_score||'优秀'）;

WHEN（v_score>=80andv_score<90）THEN

DBMS_OUTPUT.PUT_LINE（'成绩：

'||v_score||'良好'）;

WHEN（v_score>=60andv_score<80）THEN

DBMS_OUTPUT.PUT_LINE（'成绩：

'||v_score||'合格'）;

WHEN（v_score>=0andv_score<60）THEN

DBMS_OUTPUT.PUT_LINE（'成绩：

'||v_score||'不及格'）;

ELSE

DBMS_OUTPUT.PUT_LINE（'成绩：

'||v_score||'成绩异常'）;

ENDCASE;

END;

【例7.7】使用CASE语