第4章PLSQL语言基础（理论）01修改后.doc

第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.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可以用于创建存储过程、触发器和程序包等，也可以用于处理业务规则、数据库事件或给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块中，从而降低了网络开销，提高了应用程序的性能。
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.3 PL/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所示。
4. 异常处理部分
3. 执行业务逻辑
1. 设置输出、显示环境变量
2. 定义块变量

图1.4.1 PL/SQL程序块示例演示
DECLARE、BEGIN和EXCEPTION后面没有“；”（分号），而END后则必须要带“；”（分号）。

注意

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 常量与变量
在声明部分中可以声明需要使用的常量、变量、函数、游标、异常处理名称等。
1. 声明
声明常量、变量的语法如下：
identifier_name [CONSTANT] data_type [NOT NULL]
[:= value_expression] | [DEFAULT value_expression]
其中，identifier_name指定需要声明的常量、变量的名称；data_type指定数据类型；“:=”是赋值运算符（或使用DEFAULT）；value_expression是赋值表达式。如果有CONSTANT，则表明声明的是一个常量；如果有NOT NULL，则表明声明的变量不能为空（即在声明时必须赋值）。常量应该立即赋值，如果没有赋值则表示初始化为NULL。
在PL/SQL中，每一行中能声明一个常量或变量。在引用一个常量或变量之前，必须先声明该常量或变量。
注意

图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程序中定义变量、常量和参数时，必须为它们指定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定义变量
在图1.4.6中，将dept中某条记录的所有列都赋给v_emp_record变量，如何实现只将某条记录的部分列赋给v_emp_record变量？
思考

10. RECORD
PL/SQL记录类似于高级语言中的结构，每个PL/SQL记录一般都包含多个成员。当使用PL/SQL记录时，首先需要在定义部分定义记录类型和记录变量，然后在执行部分引用该记录变量。当引用记录成员时，必须将记录变量作为前缀，格式为：记录变量.记录成员。
语法：
TYPE record_name IS RECORD
(
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 NULL]
INDEX BY [BINARY_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数据类型
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数据类型
对于VARRAY数据类型而言，首先的3个步骤是定义、声明、初始化。这3个步骤缺一不可，否则就会在后面的使用过程中出错。在引用VARRAY数组中的成员时，需要在一对括号中使用顺序下标，下标从1开始。
注意

4.3 流程控制
流程控制是PL/SQL对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 条件表达式 THEN
语句段1
ELSE
语句段2
END IF;
3. IF...THEN...ELSIF...END IF结构
IF...THEN...ELSIF...END IF结构是多重条件分支结构。当使用多重条件分支时，如果满足第一个条件，则执行第一种操作；如果不满足第一个条件，则检查是否满足第二个条件，如果满足则执行第二种操作；如果不满足第二个条件，则检查是否满足第三个条件……依此类推。
语法：
IF 条件表达式1 THEN
语句段1
ELSIF 条件表达式2 THEN
语句段2
ELSIF 条件表达式3 THEN
语句段3
......
ELSIF 条件表达式n
语句段n
END IF;
图1.4.11将演示IF条件控制语句的使用。

图1.4.11 使用IF条件控制结构
4. CASE结构
CASE结构是在Oracle 9i中引入的另一种逻辑判断结构，它提供了另一种逻辑判断的实现方法。
语法：
CASE 表达式
WHEN 条件表达式结果1 THEN
语句段1;
WHEN 条件表达式结果2 THEN
语句段2;
......
ELSE
语句段n;
END CASE;
或者：
CASE
WHEN 条件表达式结果1 THEN
语句段1;
WHEN 条件表达式结果2 THEN
语句段2;
......
ELSE
语句段n;
END CASE;
其中，前一种形式的CASE结构非常适合于从几个值中选择一个的情况，以决定需要执行的语句段。在应用中，需要尽可能地将很长的IF...THEN...ELSIF...END IF语句改写成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开始，以END LOOP结束。
语法：
LOOP
语句段;
EXIT [WHEN 条件表达式]
END LOOP;
由语法可知，当使用基本循环时，无论是否满足条件，语句至少会被执行一次。当“条件表达式”为TRUE时，会退出循环，并执行END LOOP后的操作。
注意
EXIT语句必须放在循环体内，且只能退出循环体，不能退出PL/SQL块。当需要退出PL/SQL块时，应该使用RETURN语句。

图1.4.14中演示了LOOP循环的使用过程，根据VARRAY中的数据产生一些随机项，并插入到表中。

图1.4.14 LOOP循环的使用
2. WHILE循环
基本循环至少需要执行一次循环体内的语句，而对于WHILE循环而言，只有当条件为TRUE时，才会执行循环体内的语句。
语法：
WHILE 条件表达式 LOOP
语句段;
END LOOP;
为了演示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
语句段;
END LOOP;
默认情况下，当使用FOR循环时，每次循环时控制变量会自动增1；如果指定了REVERSE选项，则每次循环时循环控制变量会自动减1。在循环体内部，可以引用循环变量，但不能对其赋值。图1.4.17中演示了FOR循环的使用过程。
使用FOR循环输出TALBE数据类型元素的值

图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 NULL语句的使用
4.4 异常处理
PL/SQL异常是指通过了编译的PL/SQL程序在运行时产生的错误。导致异常的原因有很多，如内存用尽、硬件故障、违反表的完整性约束、设计上的缺陷等。PL/SQL提供了一种异常错误处理机制，可以帮助实现对错误的捕获和处理。当异常发生时，PL/SQL会自动地捕获异常并自动地将程序控制流程转移到异常处理部分的程序。
语法：
EXCEPTION
WHEN 异常错误名称1 [OR 异常错误名称2......] THEN
语句段1;
WHEN异常错误名称3 [OR 异常错误名称4......] THEN
语句段2;
......
WHEN OTHERS THEN
语句段3;
由语法可知，异常处理部分以保留字EXCEPTION开始，可以使用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_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
SELECT INTO命令返回的多行
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语句显式地引发。
在更新表中的数据时，如果没有符合条件的记录，则不会更新数据。因为这不是错误，所以不会有任何的错误提示，但可以使用自定义错误异常的方法来提示。图1.4.20中通过判断隐含游标的属性来获知是否更新了记录，并使用自定义异常的方法来提示。
2. 引发异常
1. 定义异常变量
3. 处理异常

图1.4.20 使用自定义异常
4.5 开发动态SQL
4.5.1 动态SQL简介
在PL/SQL程序设计中，可以直接使用DML和事务控制的语句，但DDL语句及系统控制语句却不能在PL/SQL中直接使用。需要在PL/SQL中使用DDL语句及系统控制语句时，可以通过使用动态SQL来实现，例如需要动态建表或某个不确定的操作需要动态执行。
在PL/SQL块中使用的SQL语句可分为两种：静态SQL语句和动态SQL语句。如果在PL/SQL块中使用的SQL语句在编译时是明确的，且执行的是确定对象，则属于静态SQL；如果在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作，则属于动态SQL。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句，对语句进行语法分析并执行该语句。
4.5.2 处理非查询语句
为了动态地处理非查询语句（例如DDL、DCL、和DML语句），可以在PL/SQL块中使用EXECUTE IMMEDIATE语句。注意，除了可以处理动态DDL、DCL和DML语句之外，它还可以用于处理单行查询语句。
语法：
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[,define_variable]...| record}]
[USING [IN |OFF |IN OUT] bind_argument
[,[ IN | OUT |IN OUT] bind_argument] ...]
[{RETURNING | RETURN } INTO bind_argument [, bind_argument] ...];
在语法中，dynamic_string用于指定存放SQL语句或PL/SQL块的字符串变量，define_variable用于指定存放单行查询结果的变量，输入bind_argument(IN)用于指定存放被传递给动态SQL值的变量，输出bind_argument(OUT)用于指定存放动态SQL返回值的变量。
以下通过示例说明如何在PL/SQL块中使用EXECUTE IMMEDIATE语句执行动态SQL操作。
1. 使用EXECUTE IMMEDIATE处理DDL和DCL操作
当在PL/SQL块中处理DDL和DCL操作时，EXECUTE IMMEDIATE后面只需要带有DDL和DCL语句文本即可，无需INTO和USING子句。由图1.4.21可知，该示例首先通过DDL动态创建表，然后通过DCL为其赋予权限给某用户。

图1.4.21 使用EXECUTE IMMEDIATE处理DDL和DCL操作
提问
假设存在tb_shop表，执行以下代码会产生错误，则应如何改进错误？
BEGIN
EXECUTE IMMEDIATE 'drop table tb_shop;';
END;

2. 使用EXECUTE IMMEDIATE处理DML操作
（1）处理无占位符和RETURNING子句的DML语句。
当使用EXECUTE IMMEDIATE处理无占位符和RETURNING子句的DML语句时，不需要带有USING和INTO子句。以下通过给部门30的所有雇员增加10%的工资为例来说明这种处理方法，如图1.4.22所示。

图1.4.22 处理无占位符和RETURNING子句的DML语句
（2）处理包含占位符的DML。
当使用EXECUTE IMMEDIATE处理包含占位符的DML语句时，需要使用USING子句为占位符提供输入数据。以下通过给指定部门增加工资为例来说明这种处理方法，如图1.4.23所示。

图1.4.23 处理包含占位符的DML语句
执行上述PL/SQL块后，会为部门30的每个雇员增加20%的工资。其中，USING后的第一个值提供给第一个占位符，第二个值提供给第二个占位符。
占位符可以使用任意的名称。
注意

（3）处理包含RETURNING子句的DML语句。
当使用EXECUTE IMMEDIATE处理包含RETURNING子句的DML语句时，必须使用RETURNING INTO子句接收返回的数据。注意，当直接使用EXECUTE IMMEDIATE处理包含RETURNING子句的DML语句时，只能处理作用在单行上的DML语句。以下通过给特定雇员增加工资并输出新工资为例，来说明处理带有RETURNING子句的动态DML语句的方法，如图1.4.24所示。

图1.4.24 处理带有RETURNING子句的动态DML语句
4.5.3 使用EXECUTE IMMEDIATE处理单行查询
使用EXECUTE IMMEDIATE不仅可以处理DDL、DCL和DML语句，而且还可以用于处理单行查询语句，但在使用EXECUTE IMMEDIATE语句处理单行查询语句时，需要使用INTO子句接收返回的数据。以下通过输出特定雇员的雇员姓名和工资为例，来说明使用EXECUTE IMMEDIATE处理单行查询语句的方法，如图1.4.25所示。
执行SQL语句，返回单行记录

图1.4.25 EXECUTE IMMEDIATE处理单行查询语句
EXECUTE IMMEDIATE只能处理返回单行记录或没有返回记录集的SQL语句，需要处理返回多行的动态SQL语句时，可以使用游标。
注意

4.5.4 使用BULK子句处理多行查询
通过在EXECUTE IMMEDIATE语句中引入BULK子句，不仅可以处理单行查询，而且还可以处理多行查询语句。以下通过动态显示特定部门的所有雇员名为例，来说明在EXECUTE IMMEDIATE语句中使用BULK子句处理多行查询语句的方法，如图1.4.26所示。
注意下标从1开始

图1.4.26 在EXECUTE IMMEDIATE语句中使用BULK子句处理多行查询语句
本章总结
Ø PL/SQL是Oracle在标准SQL语言上的过程性扩展，它不仅允许嵌入SQL语句，而且允许定义变量和常量，允许过程语言结构。
Ø PL/SQL程序块可以分为匿名块、命名块、子程序、程序包和触发器。
Ø PL/SQL程序块由3个部分组成：声明部分、执行部分和异常处理部分。
Ø 在PL/SQL程序块中，可以使用SELECT INTO语句将查询的结果赋给变量。
Ø 当使用%TYPE属性定义变量时，Oracle会自动地按照数据库列或其他变量来确定新变量的类型和长度。
Ø 可以使用%ROWTYPE来定义一个表示表中一行记录的变量。
Ø TABLE索引表相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串。
Ø VARRAY数组是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标，它取决于成员在数组中的位置。
Ø 自定义异常必须要声明，并且必须使用RAISE语句显式地引发。
Ø 需要在PL/SQL中使用DDL语句及系统控制语句时，可以通过使用动态SQL来实现。
本章作业
一、选择题
1. 下列选项中，不能直接嵌入PL/SQL块中的语句有（ ）。
A. SELECT
B. INSERT
C. CREATE TABLE
D. GRANT
E. COMMIT
2. 当SELECT INTO语句没有返回行时，会触发的异常是（ ）。
A. TOO_MANY_ROWS
B. VALUE_ERROR
C. NO_DATA_FOUND
D. 不会触发任何异常
3. 以下PL/SQL块的输出结果是（ ）。
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('*');
i:=i+2;
END LOOP;
END;
A. **
B. ***
C. *
*
*
D. 有语法错误
4. 以下PL/SQL块的输出结果是（ ）。
DECLARE
v_1 NUMBER(2);
v_2 NUMBER(3);
BEGIN
v_2:=CASE WHEN v_1 IS NULL THEN 10 ELSE 20 END;
DBMS_OUTPUT.PUT_LINE('v_2:' || v_2);
END;
A. 10
B. 20
C. 0
D. 有语法错误
二、简答题
1. 什么是PL/SQL？
2. PL/SQL有哪些常见的数据类型？
3. 在PL/SQL块中，如何处理自定义异常？
4. 什么是动态SQL语句？
三、操作题
由用户输入一个雇员编号，要求在emp表中查找是否存在与输入的雇员编号对应的雇员。
（1）如果存在，则输出“查到此雇员”，并判断其部门是否为“SALES”部门。
1）如果是“SALES”部门，则输出“此雇员属于SALES部门”，并删除对应的记录。
2）否则输出“此雇员不属于SALES部门，不能删除！”。
（2）如果不存在与之对应的雇员，则输出“找不到此雇员！”。
第4章 理论 第 32 页 共 32 页