第4章 PLSQL语言基础理论01修改后.docx
《第4章 PLSQL语言基础理论01修改后.docx》由会员分享，可在线阅读，更多相关《第4章 PLSQL语言基础理论01修改后.docx（39页珍藏版）》请在冰豆网上搜索。
第4章PLSQL语言基础理论01修改后
第4章
PL/SQL语言基础
本章学习内容
1.PL/SQL语言简介
2.程序结构
3.流程控制
4.异常处理
5.开发动态SQL
本章学习目标
1.掌握PL/SQL程序结构
2.掌握PL/SQL流程控制
3.掌握PL/SQL异常处理
4.掌握PL/SQL中开发动态SQL
本章简介
在第3章中，我们已经学习了同义词、序列、视图、索引等知识。
并通过同义词以一种非常简便的方式去访问其它方案中的对象；通过视图将一些复杂的SQL语句封装起来方便使用；通过序列作为表的自动编号并做为主键实现了数据库的实体完整性；通过使用各种索引在数据量非常宠大的表中提高查询效率。
但是要实现复杂的商用业务逻辑仅凭这些对象是远远不够的，我们必须使用程序流程控制和异常处理机制等。
本章将继续学习程序结构、流程控制、异常处理、开发动态SQL等知识。
流程控制是PL/SQL对SQL最重要的扩展。
流程控制结构包括条件控制、循环控制和顺序控制，流程控制为我们提供了实现复杂业务逻辑的一种途径。
为处理程序中的异常，PL/SQL提供了一种异常错误处理机制，可以帮助实现对错误的捕获和处理，当异常发生时，PL/SQL会自动地捕获异常并自动地将程序控制流程转移到异常处理部分的程序。
在PL/SQL程序设计中，可以直接使用DML和事务控制的语句，但DDL语句及系统控制语句却不能在PL/SQL中直接使用。
如果需要在PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现，如需要动态建表或某个不确定的操作需要动态执行。
4.1PL/SQL语言简介
4.1.1PL/SQL概述
PL/SQL是Oracle在标准SQL语言上的过程性扩展，它不仅允许嵌入SQL语句，而且允许定义变量和常量，允许过程语言结构（条件分支语句和循环语句），允许使用异常来处理Oracle错误等。
在任何运行Oracle的平台上，应用开发人员都可以使用PL/SQL。
通过使用PL/SQL，可以在一个PL/SQL块中包含多条SQL语句和PL/SQL语句。
PL/SQL可以用于创建存储过程、触发器和程序包等，也可以用于处理业务规则、数据库事件或给SQL命令的执行添加程序逻辑。
4.1.2PL/SQL优点
PL/SQL是一种可移植的高性能事务处理程序，它支持SQL和面向对象编程，提供了良好的性能和高效的处理能力，具有以下6个方面的优点。
1.提高应用程序的运行性能
在编写Oracle数据库应用程序时，开发人员可以直接将PL/SQL块内嵌到应用程序中，其最大的优点是可以降低网络开销、提高应用程序性能。
对于其他异构数据库（例如SQLServer、Sybase、DB2等），当应用程序访问RDBMS时，每次只能发送单条SQL语句。
而对于Oracle数据库而言，通过使用PL/SQL块，可以将多条SQL语句组织到同一个PL/SQL块中，从而降低了网络开销，提高了应用程序的性能。
2.提供模块化的程序设计功能
当开发数据库应用程序时，为了简化客户端应用程序的开发和维护工作，可以首先将企业规则或商业逻辑集成到PL/SQL子程序（过程、函数和包）中，然后在应用程序中调用子程序实现相应的程序功能。
3.允许定义标识符
当使用PL/SQL开发应用模块时，为了使应用模块与应用环境实现数据交互，需要定义变量、常量、游标等各种标识符。
例如，函数get_sal中的no为输入参数，用于接收雇员编号的输入值，而salary变量则用于临时存储雇员工资。
4.具有过程语言控制结构
PL/SQL是Oracle在标准SQL上的过程性扩展，它不仅允许在PL/SQL块内嵌入SQL语句，而且允许在PL/SQL块中使用各种类型的条件分支语句和循环语句。
5.具有良好的兼容性
PL/SQL是Oracle所提供的用于实现应用模块的语言，在允许运行Oracle的任何平台上都可以使用PL/SQL。
例如，不仅可以在Oracle数据库中使用PL/SQL开发数据库端的过程、函数和触发器，也可以在Oracle提供的应用开发工具Developer中使用PL/SQL开发客户端的过程、函数和触发器。
6.处理运行错误
当设计并开发应用程序时，为了提高应用程序的健壮性，可以使用PL/SQL所提供的异常处理（EXCEPTION）集中处理各种Oracle错误和PL/SQL错误，从而简化错误处理。
4.1.3PL/SQL块
所有的PL/SQL程序都以块作为基本单位组成。
块中包含过程化语句和SQL的DML语句。
这些块可以按顺序出现，也可以相互嵌套（一个块在另一个块的内部）。
按是否带有名称以及在数据库中的存储方式，块可以分为以下5类。
1.匿名块
匿名块是出现在应用程序中的没有名字且不存储到数据库中的块。
匿名块可以出现在SQL语句可以出现的地方，它们可以调用其他程序，却不能被其他程序调用。
2.命名块
命名块是一种带有标签的匿名块，标签为块指定了一个名称。
3.子程序
子程序是存储在数据库中的过程（procedure）、函数（function），生成之后可以被多次执行。
4.程序包
程序包是存储在数据库中的一组子程序、变量定义。
程序包中的子程序可以被其他程序包或者子程序调用。
如果声明为局部子程序，则只能在定义该局部子程序的块中调用该局部子程序。
5.触发器
触发器是一种存储在数据库中的命名块，生成之后可以被多次执行。
在相应的触发器事件发生之前或之后就会被执行一次或多次（每行记录一次）。
触发器事件一般是指对特定的数据库表、视图进行的操作，如INSERT、UPDATE和DELETE等（称为DML触发器）；或者对数据库级的操作，如关闭、启动、登录、退出数据库，创建对象、修改对象、删除对象等（称为系统触发器）。
4.2程序结构
4.2.1组成部分
PL/SQL程序块由3个部分组成：
定义部分、执行部分和异常处理部分。
其中，定义部分用于定义常量、变量、游标、异常和复杂数据类型等；执行部分用于实现应用模块功能，该部分包含需要执行的PL/SQL语句和SQL语句；异常处理部分用于处理执行部分可能出现的运行错误。
PL/SQL块的基本结构如下：
DECLARE
定义部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;
其中，定义部分以DECLARE开始，该部分是可选的；执行部分以BEGIN开始，该部分是必须的；异常处理部分以EXCEPTION开始，该部分是可选的；END则是PL/SQL程序块的结束标记。
创建一个匿名程序块，该程序块用于接收用户输入的部门名称，计算并输出该部门总工资，还用于处理用户输入的部门名称在库中不存在的异常，如图1.4.1所示。
图1.4.1PL/SQL程序块示例演示
注意
4.2.2PL/SQL注释
在PL/SQL程序中加入注释，可以帮助理解程序，PL/SQL编译器在编译时会忽略注释。
1.单行注释
单行注释可以在一行的任何地方由两个短横线（--）开始，并且直到该行的最后。
2.多行注释
多行注释由“/*”开始、“*/”结束，可以跨越多行，但不允许嵌套。
在调试PL/SQL程序时，完全可以用单行或多行注释来将暂时不需要或不正确的语句进行注释或禁用。
图1.4.2将演示PL/SQL中的注释的使用。
图1.4.2PL/SQL中的注释示例演示
4.2.3常量与变量
在声明部分中可以声明需要使用的常量、变量、函数、游标、异常处理名称等。
1.声明
声明常量、变量的语法如下：
identifier_name[CONSTANT]data_type[NOTNULL]
[:
=value_expression]|[DEFAULTvalue_expression]
其中，identifier_name指定需要声明的常量、变量的名称；data_type指定数据类型；“:
=”是赋值运算符（或使用DEFAULT）；value_expression是赋值表达式。
如果有CONSTANT，则表明声明的是一个常量；如果有NOTNULL，则表明声明的变量不能为空（即在声明时必须赋值）。
常量应该立即赋值，如果没有赋值则表示初始化为NULL。
注意
图1.4.3中演示了声明和使用变量、常量的过程。
图1.4.3声明和使用变量、常量
2.使用SELECTINTO语句给变量赋值
除了可以使用常量来给变量赋值之外，还可以从数据库表中查询获得值来赋予变量。
图1.4.4中演示了通过SELECTINTO语句将从数据库表中查询的结果赋予变量的过程，根据雇员编号，查询获得雇员工资、补助和总工资。
图1.4.4使用SELECTINTO语句给变量赋值
4.2.4数据类型
编写PL/SQL程序时，若临时存储数值，则必须定义变量和常量；若在应用环境和子程序之间传递数据，则必须为子程序指定参数。
而在PL/SQL程序中定义变量、常量和参数时，必须为它们指定PL/SQL数据类型。
1.CHAR
CHAR表示固定长度字符串，长度不够的使用空格来补充，最多可以存储2000字节。
2.VARCHAR2
VARCHAR2表示可变长度字符串，最多可以存储4000字节。
3.NUMBER
NUMBER类型可以存储正数、负数、零、定点数和精度为38位的浮点数。
其格式为：
NUMBER（M,N）。
其中，M表示精度，代表数字的总位数；N表示小数点右边，数字的位数。
4.DATE类型
DATE数据类型用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日。
DATE类型的长度是7，7个字节分别表示世纪、年、月、日、时、分和秒。
5.TIMESTAMP类型
TIMESTAMP数据类型用于存储日期的年、月、日、小时、分和秒值。
其中，秒值精确到小数点6位，该数据类型同时包含时区信息。
6.CLOB大字符串对象类型
CLOB数据类型用于存储可变长度的字符数据，最多可存储4GB。
该数据类型用于存储VARCHAR2类型不能存储的长文本信息。
7.BLOB大二进制类型
BLOB数据类型用于存储较大的二进制对象，如图形、视频剪辑和声音剪辑等，该类型最多可以存储4GB数据。
8.%TYPE
当定义PL/SQL变量存放值时，必须确保变量使用合适的数据类型和长度，否则可能会在运行过程中出现PL/SQL运行错误。
为了避免这种不必要的错误，可以使用%TYPE属性来定义变量。
当使用%TYPE属性定义变量时，Oracle会自动地按照数据库列或其他变量来确定新变量的类型和长度。
图1.4.5将演示使用%TYPE定义变量的过程。
图1.4.5使用%TYPE定义变量
由图1.4.5可知，变量v_ename、v_sal与EMP表的ename列、sal列的数据类型和长度完全一致，而变量v_tax_sal与变量v_sal的数据类型和长度完全一致。
因此，当ename列和sal列的类型和长度发生改变时，该PL/SQL块将不需要进行任何修改。
9.%ROWTYPE
如果一张表中包含较多的列，则可以使用%ROWTYPE来定义一个表示表中一行记录的变量，这种方式比分别使用%TYPE来定义表示表中各个列的变量要简洁得多。
为了使一个变量的数据类型与一张表中记录的各个列的数据类型对应一致，Oracle提供了%ROWTYPE定义方式。
图1.4.6中演示了使用%ROWTYPE来定义变量的过程。
图1.4.6使用%ROWTYPE定义变量
思考
10.RECORD
PL/SQL记录类似于高级语言中的结构，每个PL/SQL记录一般都包含多个成员。
当使用PL/SQL记录时，首先需要在定义部分定义记录类型和记录变量，然后在执行部分引用该记录变量。
当引用记录成员时，必须将记录变量作为前缀，格式为：
记录变量.记录成员。
语法：
TYPErecord_nameISRECORD
（
v1data_type1[NOTNULL][:
=default_value],
v2data_type2[NOTNULL][:
=default_value],
vndata_typen[NOTNULL][:
=default_value]
）;
图1.4.7将演示在PL/SQL块中定义并使用RECORD数据类型的过程。
图1.4.7定义并使用RECORD数据类型
11.TABLE
索引表相当于一个键值集合，键是唯一的，用于查找对应的值。
键可以是整数或字符串。
语法：
TYPEtable_nameISTABLEOFelement_type[NOTNULL]
INDEXBY[BINARY_INTEGER|PLS_INTEGER|VARCHAR2];
关键字INDEXBY表示创建一个主键索引，以便引用记录表变量中的特定行。
图1.4.8将演示索引表数据类型的定义及使用过程。
图1.4.8定义并使用TABLE数据类型
图1.4.8中的v_emp_empnos和v_emp_enames变量都按类似一维数组的方式来使用。
在定义TABLE数据类型时，如果指定element_type参数值为%ROWTYPE类型，则TABLE数据类型就可以按类似二维数组的方式来使用，类似于数据库中表的结构。
图1.4.9将演示TABLE数据类型作为二维数组使用的过程。
图1.4.9按二维数组使用TABLE数据类型
12.VARRAY
数组是具有相同数据类型的一组成员的集合。
每个成员都有一个唯一的下标，它取决于成员在数组中的位置。
在PL/SQL中，数组数据类型是VARRAY。
语法：
TYPEvarray_nameISVARRAY（size）OFelement_type[NOTNULL];
其中，varray_name是VARRAY数据类型的名称，size指定可以容纳的成员的最大数量，每个成员的数据类型是element_type。
图1.4.10中演示了在PL/SQL块中定义并使用VARRAY数据类型的过程。
图1.4.10定义并使用VARRAY数据类型
注意
4.3流程控制
流程控制是PL/SQL对SQL最重要的扩展，流程控制结构包括条件控制、循环控制和顺序控制。
4.3.1条件控制
在许多情况中，需要按照某种条件来选择执行某些语句段。
条件控制先测试一个条件，然后根据测试的结果选择、运行不同的语句段。
条件结构中允许嵌套。
1.IF...THEN...ENDIF结构
IF...THEN...ENDIF结构是最简单的条件控制结构。
语法：
IF条件表达式THEN
语句段
ENDIF;
2.IF...THEN...ELSE...ENDIF结构
IF...THEN...ELSE...ENDIF结构是二重条件分支结构。
当使用二重条件分支时，如果满足条件，则执行一组操作；如果不满足条件，则执行另外一组操作。
语法：
IF条件表达式THEN
语句段1
ELSE
语句段2
ENDIF;
3.IF...THEN...ELSIF...ENDIF结构
IF...THEN...ELSIF...ENDIF结构是多重条件分支结构。
当使用多重条件分支时，如果满足第一个条件，则执行第一种操作；如果不满足第一个条件，则检查是否满足第二个条件，如果满足则执行第二种操作；如果不满足第二个条件，则检查是否满足第三个条件……依此类推。
语法：
IF条件表达式1THEN
语句段1
ELSIF条件表达式2THEN
语句段2
ELSIF条件表达式3THEN
语句段3
......
ELSIF条件表达式n
语句段n
ENDIF;
图1.4.11将演示IF条件控制语句的使用。
图1.4.11使用IF条件控制结构
4.CASE结构
CASE结构是在Oracle9i中引入的另一种逻辑判断结构，它提供了另一种逻辑判断的实现方法。
语法：
CASE表达式
WHEN条件表达式结果1THEN
语句段1;
WHEN条件表达式结果2THEN
语句段2;
......
ELSE
语句段n;
ENDCASE;
或者：
CASE
WHEN条件表达式结果1THEN
语句段1;
WHEN条件表达式结果2THEN
语句段2;
......
ELSE
语句段n;
ENDCASE;
其中，前一种形式的CASE结构非常适合于从几个值中选择一个的情况，以决定需要执行的语句段。
在应用中，需要尽可能地将很长的IF...THEN...ELSIF...ENDIF语句改写成CASE结构，因为CASE结构的可读性及有效性更高。
图1.4.12中演示了CASE结构的使用过程。
后面一种形式的语法格式被称为CASE搜索结构，图1.4.13中演示了CASE搜索结构的使用过程。
图1.4.12使用CASE结构
图1.4.13使用CASE搜索结构
4.3.2循环控制
为了在编写的PL/SQL块中重复执行一条语句或者一组语句，可以使用循环控制结构。
编写循环控制结构时，用户可以使用基本循环、WHILE循环和FOR循环等类型的循环语句。
1.基本循环
在PL/SQL中，格式最简单的循环语句是基本循环语句，这种循环语句以LOOP开始，以ENDLOOP结束。
语法：
LOOP
语句段;
EXIT[WHEN条件表达式]
ENDLOOP;
由语法可知，当使用基本循环时，无论是否满足条件，语句至少会被执行一次。
当“条件表达式”为TRUE时，会退出循环，并执行ENDLOOP后的操作。
注意
图1.4.14中演示了LOOP循环的使用过程，根据VARRAY中的数据产生一些随机项，并插入到表中。
图1.4.14LOOP循环的使用
2.WHILE循环
基本循环至少需要执行一次循环体内的语句，而对于WHILE循环而言，只有当条件为TRUE时，才会执行循环体内的语句。
语法：
WHILE条件表达式LOOP
语句段;
ENDLOOP;
为了演示WHILE循环的具体用法，创建一张关于商品库存表，命名为tb_stock，如图1.4.15所示。
图1.4.15商品库存表
图1.4.16的代码用于让用户输入商品编号，并计算该商品的采购期限（当库存量小于或等于最小库存量时就必须采购）。
图1.4.16计算商品采购期限
3.FOR循环
当使用基本循环或WHILE循环时，需要定义循环控制变量。
循环控制变量不仅可以使用NUMBER类型，还可以使用其他数据类型。
当使用FOR循环时，Oracle会隐含定义循环变量。
语法：
FOR循环变量in[REVERSE]初值表达式..终值表达式LOOP
语句段;
ENDLOOP;
默认情况下，当使用FOR循环时，每次循环时控制变量会自动增1；如果指定了REVERSE选项，则每次循环时循环控制变量会自动减1。
在循环体内部，可以引用循环变量，但不能对其赋值。
图1.4.17中演示了FOR循环的使用过程。
图1.4.17FOR循环的使用
4.3.3顺序控制
PL/SQL不仅提供了条件分支语句和循环控制语句，而且还提供了顺序控制语句GOTO和NULL。
1.GOTO语句
GOTO语句用于跳转到特定标号处去执行语句。
使用GOTO语句会增加程序的复杂性，并使应用程序可读性变差，所以在开发应用时建议不要使用GOTO语句。
2.NULL语句
NULL语句不会执行任何操作，并直接将控制传递到下一个条语句。
使用NULL语句的优点在于可以提高PL/SQL程序的可读性。
图1.4.18将演示NULL语句的使用过程。
图1.4.18NULL语句的使用
4.4异常处理
PL/SQL异常是指通过了编译的PL/SQL程序在运行时产生的错误。
导致异常的原因有很多，如内存用尽、硬件故障、违反表的完整性约束、设计上的缺陷等。
PL/SQL提供了一种异常错误处理机制，可以帮助实现对错误的捕获和处理。
当异常发生时，PL/SQL会自动地捕获异常并自动地将程序控制流程转移到异常处理部分的程序。
语法：
EXCEPTION
WHEN异常错误名称1[OR异常错误名称2......]THEN
语句段1;
WHEN异常错误名称3[OR异常错误名称4......]THEN
语句段2;
......
WHENOTHERSTHEN
语句段3;
由语法可知，异常处理部分以保留字EXCEPTION开始，可以使用WHEN子句按“异常错误名称”捕获各种异常错误，如果还有其他没有预计到的异常错误，可以使用WHENOTHERS子句进行捕获。
4.4.1预定义异常
针对一些常见的错误，PL/SQL预定义了一些异常错误。
当PL/SQL程序违反了Oracle的规定或超出了系统规定的限制时，就会隐含地引发一个预定义的错误。
PL/SQL在程序包STANDARD中包含了这些预定义异常，常用的预定义异常错误见表1.4.1。
表1.4.1PL/SQL预定义异常
异常
ORACLE错误
描述
ACCESS_INTO_NULL
ORA-6530
试图访问一个未初始化的对象时出现
CASE_NOT_FOUND
ORA-6592
CASE语句中的选项与用户输入的数据不匹配时出现
CURSOR_ALREADY_OPEN
ORA-6511
试图打开一个已打开的光标
DUP_VAL_ON_INDEX
ORA-0001
试图破坏一个唯一性限制
INVALID_CURSOR
ORA-1001
试图使用一个无效的光标
INVALID_NUMBER
ORA-1722
试图对非数字值进行数字操作
LOGIN_DENIED
ORA-1017
无效的用户名或者口令
NO_DATA_FOUND
ORA-1403
查询未找到数据
NOT_LOGGED_ON
ORA-1012
还未连接就试图数据库操作
PROGRAM_ERROR
ORA-6501
内部错误
ROWTYPE_MISMATCH
ORA-6504
主变量和光标的类型不兼容
STORAGE_ERROR
ORA-6500
内部错误
TIMEOUT_ON_RESOURCE
ORA-0051
发生超时
TOO_MANY_ROWS
ORA-1422
SELECTINTO命令返回的多行
TRANSACTION_BACKED_OUT
ORA-006
由于死锁提交被退回
VALUE_ERROR
ORA-6502
转换或者裁剪错误
ZERO_DIVIDE
ORA-1476
试图被零除
在图1.4.19中，向DEPT表中插入一行记录时，如果违反了实体完整性的主键约束，则会触发预定义的DUP_VAL_ON_INDEX异常错误；查询DEPT表时，如果查询的结果集多于一行，则会触发预定义的TOO_MANY_ROWS异常错误。
图1.4.19捕获并处理预定义的异常错误
4.4.2自定义异常
预定义异常都是由Oracle判断的异常错误。
在实际的PL/SQL程序开发过程中，为了具体的业务规则、编程和调试的需要，程序员可以自定义一些异常。
自定义异常必须要声明，并且必须使用RAISE语句显式地引发。
在更新表中的数据时，如果没有符合条件的记录，则不会更新数据。
因为这不是错误，所以不会有任何的错误提示
升级会员 