第4章 PLSQL语言基础理论01修改后.docx

1、第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语句封装起来方便使用；通过序列作为表的自动编号并做为主键实现了数据库的实体完整性；通过使用各种索引在数据量非常宠大的表中提高查询效率。但是要实现复

2、杂的商用业务逻辑仅凭这些对象是远远不够的，我们必须使用程序流程控制和异常处理机制等。本章将继续学习程序结构、流程控制、异常处理、开发动态SQL等知识。流程控制是PL/SQL对SQL最重要的扩展。流程控制结构包括条件控制、循环控制和顺序控制，流程控制为我们提供了实现复杂业务逻辑的一种途径。为处理程序中的异常，PL/SQL提供了一种异常错误处理机制，可以帮助实现对错误的捕获和处理，当异常发生时，PL/SQL会自动地捕获异常并自动地将程序控制流程转移到异常处理部分的程序。在PL/SQL程序设计中，可以直接使用DML和事务控制的语句，但DDL语句及系统控制语句却不能在PL/SQL中直接使用。如果需要在

3、PL/SQL中使用DDL语句及系统控制语句，可以通过使用动态SQL来实现，如需要动态建表或某个不确定的操作需要动态执行。4.1 PL/SQL语言简介4.1.1 PL/SQL概述PL/SQL是Oracle在标准SQL语言上的过程性扩展，它不仅允许嵌入SQL语句，而且允许定义变量和常量，允许过程语言结构（条件分支语句和循环语句），允许使用异常来处理Oracle错误等。在任何运行Oracle的平台上，应用开发人员都可以使用PL/SQL。通过使用PL/SQL，可以在一个PL/SQL块中包含多条SQL语句和PL/SQL语句。PL/SQL可以用于创建存储过程、触发器和程序包等，也可以用于处理业务规则、数据

4、库事件或给SQL命令的执行添加程序逻辑。4.1.2 PL/SQL优点PL/SQL是一种可移植的高性能事务处理程序，它支持SQL和面向对象编程，提供了良好的性能和高效的处理能力，具有以下6个方面的优点。1. 提高应用程序的运行性能在编写Oracle数据库应用程序时，开发人员可以直接将PL/SQL块内嵌到应用程序中，其最大的优点是可以降低网络开销、提高应用程序性能。对于其他异构数据库（例如SQL Server、Sybase、DB2等），当应用程序访问RDBMS时，每次只能发送单条SQL语句。而对于Oracle数据库而言，通过使用PL/SQL块，可以将多条SQL语句组织到同一个PL/SQL块中，从而

5、降低了网络开销，提高了应用程序的性能。2. 提供模块化的程序设计功能当开发数据库应用程序时，为了简化客户端应用程序的开发和维护工作，可以首先将企业规则或商业逻辑集成到PL/SQL子程序（过程、函数和包）中，然后在应用程序中调用子程序实现相应的程序功能。3. 允许定义标识符当使用PL/SQL开发应用模块时，为了使应用模块与应用环境实现数据交互，需要定义变量、常量、游标等各种标识符。例如，函数get_sal中的no为输入参数，用于接收雇员编号的输入值，而salary变量则用于临时存储雇员工资。4. 具有过程语言控制结构PL/SQL是Oracle在标准SQL上的过程性扩展，它不仅允许在PL/SQL块

6、内嵌入SQL语句，而且允许在PL/SQL块中使用各种类型的条件分支语句和循环语句。5. 具有良好的兼容性PL/SQL是Oracle所提供的用于实现应用模块的语言，在允许运行Oracle的任何平台上都可以使用PL/SQL。例如，不仅可以在Oracle数据库中使用PL/SQL开发数据库端的过程、函数和触发器，也可以在Oracle提供的应用开发工具Developer中使用PL/SQL开发客户端的过程、函数和触发器。6. 处理运行错误当设计并开发应用程序时，为了提高应用程序的健壮性，可以使用PL/SQL所提供的异常处理（EXCEPTION）集中处理各种Oracle错误和PL/SQL错误，从而简化错误处

7、理。4.1.3 PL/SQL块所有的PL/SQL程序都以块作为基本单位组成。块中包含过程化语句和SQL的DML语句。这些块可以按顺序出现，也可以相互嵌套（一个块在另一个块的内部）。按是否带有名称以及在数据库中的存储方式，块可以分为以下5类。1. 匿名块匿名块是出现在应用程序中的没有名字且不存储到数据库中的块。匿名块可以出现在SQL语句可以出现的地方，它们可以调用其他程序，却不能被其他程序调用。2. 命名块命名块是一种带有标签的匿名块，标签为块指定了一个名称。3. 子程序子程序是存储在数据库中的过程（procedure）、函数（function），生成之后可以被多次执行。4. 程序包程序包是存储

8、在数据库中的一组子程序、变量定义。程序包中的子程序可以被其他程序包或者子程序调用。如果声明为局部子程序，则只能在定义该局部子程序的块中调用该局部子程序。5. 触发器触发器是一种存储在数据库中的命名块，生成之后可以被多次执行。在相应的触发器事件发生之前或之后就会被执行一次或多次（每行记录一次）。触发器事件一般是指对特定的数据库表、视图进行的操作，如INSERT、UPDATE和DELETE等（称为DML触发器）；或者对数据库级的操作，如关闭、启动、登录、退出数据库，创建对象、修改对象、删除对象等（称为系统触发器）。4.2 程序结构4.2.1 组成部分PL/SQL程序块由3个部分组成：定义部分、执行

9、部分和异常处理部分。其中，定义部分用于定义常量、变量、游标、异常和复杂数据类型等；执行部分用于实现应用模块功能，该部分包含需要执行的PL/SQL语句和SQL语句；异常处理部分用于处理执行部分可能出现的运行错误。PL/SQL块的基本结构如下：DECLARE定义部分BEGIN执行部分EXCEPTION异常处理部分END;其中，定义部分以DECLARE开始，该部分是可选的；执行部分以BEGIN开始，该部分是必须的；异常处理部分以EXCEPTION开始，该部分是可选的；END则是PL/SQL程序块的结束标记。创建一个匿名程序块，该程序块用于接收用户输入的部门名称，计算并输出该部门总工资，还用于处理用户

10、输入的部门名称在库中不存在的异常，如图1.4.1所示。图1.4.1 PL/SQL程序块示例演示注意4.2.2 PL/SQL注释在PL/SQL程序中加入注释，可以帮助理解程序，PL/SQL编译器在编译时会忽略注释。1. 单行注释单行注释可以在一行的任何地方由两个短横线（-）开始，并且直到该行的最后。2. 多行注释多行注释由“/*”开始、“*/”结束，可以跨越多行，但不允许嵌套。在调试PL/SQL程序时，完全可以用单行或多行注释来将暂时不需要或不正确的语句进行注释或禁用。图1.4.2将演示PL/SQL中的注释的使用。图1.4.2 PL/SQL中的注释示例演示4.2.3 常量与变量在声明部分中可以声

11、明需要使用的常量、变量、函数、游标、异常处理名称等。1. 声明声明常量、变量的语法如下：identifier_name CONSTANT data_type NOT NULL:= value_expression | DEFAULT value_expression其中，identifier_name指定需要声明的常量、变量的名称；data_type指定数据类型；“:=”是赋值运算符（或使用DEFAULT）；value_expression是赋值表达式。如果有CONSTANT，则表明声明的是一个常量；如果有NOT NULL，则表明声明的变量不能为空（即在声明时必须赋值）。常量应该立即赋值，如果

12、没有赋值则表示初始化为NULL。注意图1.4.3中演示了声明和使用变量、常量的过程。图1.4.3 声明和使用变量、常量2. 使用SELECT INTO语句给变量赋值除了可以使用常量来给变量赋值之外，还可以从数据库表中查询获得值来赋予变量。图1.4.4中演示了通过SELECT INTO语句将从数据库表中查询的结果赋予变量的过程，根据雇员编号，查询获得雇员工资、补助和总工资。图1.4.4 使用SELECT INTO语句给变量赋值4.2.4 数据类型编写PL/SQL程序时，若临时存储数值，则必须定义变量和常量；若在应用环境和子程序之间传递数据，则必须为子程序指定参数。而在PL/SQL程序中定义变量、

13、常量和参数时，必须为它们指定PL/SQL数据类型。1. CHARCHAR表示固定长度字符串，长度不够的使用空格来补充，最多可以存储2000字节。2. VARCHAR2VARCHAR2表示可变长度字符串，最多可以存储4000字节。3. NUMBERNUMBER类型可以存储正数、负数、零、定点数和精度为38位的浮点数。其格式为：NUMBER(M,N)。其中，M表示精度，代表数字的总位数；N表示小数点右边，数字的位数。4. DATE类型DATE数据类型用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日。DATE类型的长度是7，7个字节分别表示世纪、年、月、日

14、、时、分和秒。5. TIMESTAMP类型TIMESTAMP数据类型用于存储日期的年、月、日、小时、分和秒值。其中，秒值精确到小数点6位，该数据类型同时包含时区信息。6. CLOB大字符串对象类型CLOB数据类型用于存储可变长度的字符数据，最多可存储4GB。该数据类型用于存储VARCHAR2类型不能存储的长文本信息。7. BLOB大二进制类型BLOB数据类型用于存储较大的二进制对象，如图形、视频剪辑和声音剪辑等，该类型最多可以存储4GB数据。8. %TYPE当定义PL/SQL变量存放值时，必须确保变量使用合适的数据类型和长度，否则可能会在运行过程中出现PL/SQL运行错误。为了避免这种不必要的

15、错误，可以使用%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来定义一个表示表中一行记录的变量，

16、这种方式比分别使用%TYPE来定义表示表中各个列的变量要简洁得多。为了使一个变量的数据类型与一张表中记录的各个列的数据类型对应一致，Oracle提供了%ROWTYPE定义方式。图1.4.6中演示了使用%ROWTYPE来定义变量的过程。图1.4.6 使用%ROWTYPE定义变量思考10. RECORDPL/SQL记录类似于高级语言中的结构，每个PL/SQL记录一般都包含多个成员。当使用PL/SQL记录时，首先需要在定义部分定义记录类型和记录变量，然后在执行部分引用该记录变量。当引用记录成员时，必须将记录变量作为前缀，格式为：记录变量.记录成员。语法：TYPE record_name IS REC

17、ORD( v1 data_type1 NOT NULL :=default_value, v2 data_type2 NOT NULL :=default_value, vn data_typen NOT NULL :=default_value);图1.4.7将演示在PL/SQL块中定义并使用RECORD数据类型的过程。图1.4.7 定义并使用RECORD数据类型11. TABLE索引表相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串。语法：TYPE table_name IS TABLE OF element_type NOT NULLINDEX BY BINARY_

18、INTEGER | PLS_INTEGER | VARCHAR2;关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。图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数

19、据类型12. VARRAY数组是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置。在PL/SQL中，数组数据类型是VARRAY。语法：TYPE varray_name IS VARRAY(size) OF element_type NOT NULL;其中，varray_name是VARRAY数据类型的名称，size指定可以容纳的成员的最大数量，每个成员的数据类型是element_type。图1.4.10中演示了在PL/SQL块中定义并使用VARRAY数据类型的过程。图1.4.10 定义并使用VARRAY数据类型注意4.3 流程控制流程控制是PL/SQL对

20、SQL最重要的扩展，流程控制结构包括条件控制、循环控制和顺序控制。4.3.1 条件控制在许多情况中，需要按照某种条件来选择执行某些语句段。条件控制先测试一个条件，然后根据测试的结果选择、运行不同的语句段。条件结构中允许嵌套。1. IF.THEN.END IF结构IF.THEN.END IF结构是最简单的条件控制结构。语法：IF 条件表达式 THEN 语句段END IF;2. IF.THEN.ELSE.END IF结构IF.THEN.ELSE.END IF结构是二重条件分支结构。当使用二重条件分支时，如果满足条件，则执行一组操作；如果不满足条件，则执行另外一组操作。语法：IF 条件表达式 THE

21、N语句段1ELSE语句段2END IF;3. IF.THEN.ELSIF.END IF结构IF.THEN.ELSIF.END IF结构是多重条件分支结构。当使用多重条件分支时，如果满足第一个条件，则执行第一种操作；如果不满足第一个条件，则检查是否满足第二个条件，如果满足则执行第二种操作；如果不满足第二个条件，则检查是否满足第三个条件依此类推。语法：IF 条件表达式1 THEN语句段1ELSIF 条件表达式2 THEN语句段2ELSIF 条件表达式3 THEN语句段3.ELSIF 条件表达式n语句段nEND IF;图1.4.11将演示IF条件控制语句的使用。图1.4.11 使用IF条件控制结构4

22、. CASE结构CASE结构是在Oracle 9i中引入的另一种逻辑判断结构，它提供了另一种逻辑判断的实现方法。语法：CASE 表达式WHEN 条件表达式结果1 THEN语句段1;WHEN 条件表达式结果2 THEN语句段2;.ELSE语句段n;END CASE;或者：CASEWHEN 条件表达式结果1 THEN语句段1;WHEN 条件表达式结果2 THEN语句段2;.ELSE语句段n;END CASE;其中，前一种形式的CASE结构非常适合于从几个值中选择一个的情况，以决定需要执行的语句段。在应用中，需要尽可能地将很长的IF.THEN.ELSIF.END IF语句改写成CASE结构，因为CA

23、SE结构的可读性及有效性更高。图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开始，以END LOOP结束。语法：LOOP语句段;EXIT WHEN 条件表达式E

24、ND LOOP;由语法可知，当使用基本循环时，无论是否满足条件，语句至少会被执行一次。当“条件表达式”为TRUE时，会退出循环，并执行END LOOP后的操作。注意图1.4.14中演示了LOOP循环的使用过程，根据VARRAY中的数据产生一些随机项，并插入到表中。图1.4.14 LOOP循环的使用2. WHILE循环基本循环至少需要执行一次循环体内的语句，而对于WHILE循环而言，只有当条件为TRUE时，才会执行循环体内的语句。语法：WHILE 条件表达式 LOOP语句段;END LOOP;为了演示WHILE循环的具体用法，创建一张关于商品库存表，命名为tb_stock，如图1.4.15所示。

25、图1.4.15 商品库存表图1.4.16的代码用于让用户输入商品编号，并计算该商品的采购期限（当库存量小于或等于最小库存量时就必须采购）。图1.4.16 计算商品采购期限3. FOR循环当使用基本循环或WHILE循环时，需要定义循环控制变量。循环控制变量不仅可以使用NUMBER类型，还可以使用其他数据类型。当使用FOR循环时，Oracle会隐含定义循环变量。语法：FOR 循环变量 in REVERSE 初值表达式.终值表达式 LOOP 语句段;END LOOP;默认情况下，当使用FOR循环时，每次循环时控制变量会自动增1；如果指定了REVERSE选项，则每次循环时循环控制变量会自动减1。在循环

26、体内部，可以引用循环变量，但不能对其赋值。图1.4.17中演示了FOR循环的使用过程。图1.4.17 FOR循环的使用4.3.3 顺序控制PL/SQL不仅提供了条件分支语句和循环控制语句，而且还提供了顺序控制语句GOTO和NULL。1. GOTO语句GOTO语句用于跳转到特定标号处去执行语句。使用GOTO语句会增加程序的复杂性，并使应用程序可读性变差，所以在开发应用时建议不要使用GOTO语句。2. NULL语句NULL语句不会执行任何操作，并直接将控制传递到下一个条语句。使用NULL语句的优点在于可以提高PL/SQL程序的可读性。图1.4.18将演示NULL语句的使用过程。图1.4.18 NU

27、LL语句的使用4.4 异常处理PL/SQL异常是指通过了编译的PL/SQL程序在运行时产生的错误。导致异常的原因有很多，如内存用尽、硬件故障、违反表的完整性约束、设计上的缺陷等。PL/SQL提供了一种异常错误处理机制，可以帮助实现对错误的捕获和处理。当异常发生时，PL/SQL会自动地捕获异常并自动地将程序控制流程转移到异常处理部分的程序。语法：EXCEPTIONWHEN 异常错误名称1 OR 异常错误名称2. THEN语句段1;WHEN异常错误名称3 OR 异常错误名称4. THEN语句段2;.WHEN OTHERS THEN语句段3;由语法可知，异常处理部分以保留字EXCEPTION开始，可

28、以使用WHEN子句按“异常错误名称”捕获各种异常错误，如果还有其他没有预计到的异常错误，可以使用WHEN OTHERS子句进行捕获。4.4.1 预定义异常针对一些常见的错误，PL/SQL预定义了一些异常错误。当PL/SQL程序违反了Oracle的规定或超出了系统规定的限制时，就会隐含地引发一个预定义的错误。PL/SQL在程序包STANDARD中包含了这些预定义异常，常用的预定义异常错误见表1.4.1。表1.4.1 PL/SQL预定义异常异常ORACLE错误描述ACCESS_INTO_NULLORA-6530试图访问一个未初始化的对象时出现CASE_NOT_FOUNDORA-6592CASE语句

29、中的选项与用户输入的数据不匹配时出现CURSOR_ALREADY_OPENORA-6511试图打开一个已打开的光标DUP_VAL_ON_INDEXORA-0001试图破坏一个唯一性限制INVALID_CURSORORA-1001试图使用一个无效的光标INVALID_NUMBERORA-1722试图对非数字值进行数字操作LOGIN_DENIEDORA-1017无效的用户名或者口令NO_DATA_FOUNDORA-1403查询未找到数据NOT_LOGGED_ONORA-1012还未连接就试图数据库操作PROGRAM_ERRORORA-6501内部错误ROWTYPE_MISMATCHORA-6504

30、主变量和光标的类型不兼容STORAGE_ERRORORA-6500内部错误TIMEOUT_ON_RESOURCEORA-0051发生超时TOO_MANY_ROWSORA-1422SELECT INTO命令返回的多行TRANSACTION_BACKED_OUTORA-006由于死锁提交被退回VALUE_ERRORORA-6502转换或者裁剪错误ZERO_DIVIDEORA-1476试图被零除在图1.4.19中，向DEPT表中插入一行记录时，如果违反了实体完整性的主键约束，则会触发预定义的DUP_VAL_ON_INDEX异常错误；查询DEPT表时，如果查询的结果集多于一行，则会触发预定义的TOO_MANY_ROWS异常错误。图1.4.19 捕获并处理预定义的异常错误4.4.2 自定义异常预定义异常都是由Oracle判断的异常错误。在实际的PL/SQL程序开发过程中，为了具体的业务规则、编程和调试的需要，程序员可以自定义一些异常。自定义异常必须要声明，并且必须使用RAISE语句显式地引发。在更新表中的数据时，如果没有符合条件的记录，则不会更新数据。因为这不是错误，所以不会有任何的错误提示