ORACLE 11g权威指南
上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
上一章目录下一章

第6章 PL/SQL基础编程

本章介绍PL/SQL的基础知识。PL/SQL（Procedural Language/SQL）是Oracle在数据库中引入的一种过程化编程语言。PL/SQL构建于SQL之上，可以用来编写包含SQL语句的程序。PL/SQL是第三/四代语言，其中包含这类语言的标准编程结构。例如：

◎ 块结构；

◎ 变量和类型；

◎ 条件逻辑；

◎ 循环；

◎ 游标，保存查询返回的结果；

◎ 过程；

◎ 函数；

◎ 包，可以用来将过程和函数组合到一个单元中。

通常，PL/SQL用于向数据库中加入业务逻辑。这种集中化的代码可以由任何能够访问数据库的程序调用，其中包括SQL*Plus、C程序和Java程序。

本章的主要内容如下：

◎ PL/SQL概述

◎ PL/SQL编程

◎ 过程和函数

◎ 错误处理

6.1 PL/SQL概述

与其他编程语言类似，PL/SQL也有着自身的特点和功能，本节主要介绍PL/SQL这些特点和功能，以及PL/SQL的开发和编译环境，最后将以一个简单的PL/SQL程序来描述PL/SQL的基本语言。

6.1.1 PL/SQL语言

相对于第三代程序设计语言如C++或Java来说，SQL这类的第四代程序语言使用起来非常简单，语言中语句的种类也比较少。但这类语言将用户与实际的数据结构和算法隔离开来，对数据的具体处理完全由该类语言的运行时系统实现。而在某些情况下，第三代语言使用的过程结构在表达某些程序过程来说是非常有用的。这也就是引入PL/SQL语言的原因，即PL/SQL语言将第四代语言的强大功能和灵活性与第三代语言的过程结构的优势融为一体。

PL/SQL代表面向过程化的语言与SQL语言的结合。从该语言的名称中可以看出，PL/SQL是在SQL语言中扩充了面向过程语言中使用的程序结构，比如：

◎ 变量和类型；

◎ 控制语句；

◎ 过程和函数；

◎ 对象类型和方法。

PL/SQL语言实现了将过程结构与Oracle SQL的无缝集成，从而为用户提供了一种功能强大的结构化程序设计语言。例如，要在数据库中修改一个学生的记录。如果没有该学生的记录，就为该学生创建一个新的记录。用PL/SQL编制的程序代码可以实现此要求，如下所示。

DECLARE
/* 定义变量类型 */
v_NewMajor VARCHAR2(10):= 'Chemistry';
v_FirstName VARCHAR2(10):= 'Scott';
v_LastName VARCHAR2(10):= 'Tiger';
BEGIN
/* 更新学生表 */
UPDATE students
SET major = v_NewMajor
WHERE first_name = v_FirstName
AND last_name = v_LastName;
/* 检查记录是否存在，如果不存在，则插入记录 */
IF SQL%NOTFOUND THEN
INSERT INTO students (ID, first_name, last_name, major)
VALUES(student_sequence.NEXTVAL, v_FirstName, v_LastName,v_NewMajor);
END IF;
END;

从上面的例子可以看出，本例使用了两个不同的SQL语句（UPDATE和INSERT），这两个语句是第四代程序结构，同时该段程序中还使用了第三代语言的结构（变量声明和IF条件语句），可见，PL/SQL是两代语言结合的结晶。

注意

以上代码只是为了演示代码结构，读者要想运行上面的程序例子，先要创建程序中引用的数据库对象（即表students和序列student_sequence）。

Oracle服务器有执行SQL语句的引擎，并且单独有一个执行PL/SQL的引擎。Oracle开发工具中也有一个单独的引擎，用来执行PL/SQL。执行SQL语句时，一次向服务器发送一条语句，因此，每一条SQL语句都单独访问服务器，这可能会导致大量的网络流量。另一方面，一个PL/SQL块中的所有的SQL语句是一次发送到服务器的，这就减少了系统的开销，并提高了系统性能。如图6-1所示。

图6-1 SQL与PL/SQL的比较

PL/SQL扩展了SQL，因而，功能更加强大，使用更加方便。能够使用PL/SQL更加灵活地操作数据，因为它完全支持所有的SQL数据操作语句、事务控制语句、函数和操作符。PL/SQL同样支持动态SQL，能够动态执行SQL数据定义、数据控制和会话控制语句。而且，PL/SQL与当前的ANSI/ISO SQL标准保持一致。

在PL/SQL中能够使用所有的SQL函数，包括：AVG、COUNT、GROUPING、MAX、MIN、STDDEV、SUM、VARIANCE和所有的聚集函数。

6.1.2 PL/SQL主要特性

Oracle 11g对PL/SQL进行了扩展，增强了PL/SQL的功能，主要体现在以下几个方面。

◎ SQL和PL/SQL编译器集成：PL/SQL支持SQL所有范围的语法，如INSERT、UPDATE、DELETE等。

◎ 支持CASE语句和表达式。

◎ 继承和动态方法释放。

◎ 类型进化：属性和方法可以添加到对象类型中，也可以从对象类型中删除，不需要重新构建类型和相应数据，这使得类型体系能够随着应用而改变，不需要一开始就规划好。

◎ 新的日期/时间类型：新的数据类型TIMESTAMP记录包括秒的时间值，新的数据类型TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE可以根据时区不同纠正日期和时间值。

◎ PL/SQL代码的本地编译：通过使用典型的C开发工具编译Oracle提供的和用户编写的存储过程为本地执行语句，提高了性能。

◎ 改善了全球和国家语言支持。

◎ 表函数和游标表达式：可以像表一样返回一个查询结果行集合。结果集合可以从一个函数传给另一个函数，同时，结果集的行可以每隔一定时间返回一部分，减少内存的消耗。

◎ 多层集合：用户可以嵌套集合类型，例如，创建PL/SQL的VARRAY表、VARRAY的VARRAY表或者PL/SQL表的PL/SQL表。可以建造复杂的数据结构。

◎ 对LOB数据类型更好的集成：可以与操作其他类型一样操作LOB类型，也可以在CLOB和NCLOB类型上使用函数，还可以将BLOB类型作为RAW。LOB与其他类型之间的转换也变得更加容易，特别是从LONG转换为LOB类型。

◎ 对批操作的增强：用户可以使用本地动态SQL执行批SQL操作，如批提取。同时也可以执行批插入和更新操作，可能在某些行会遇到错误，但是，批处理可以继续执行，当执行完以后，用户再检查操作所遇到的问题。

◎ MERGE语句：这是一个将插入和更新合并为单个操作的专用语句，主要用于数据仓库，执行特定模式的插入和更新操作。

在PL/SQL中，用户可以使用SQL语句操作Oracle数据和用于处理数据的流控语句，而且，可以声明常量和变量、定义过程和函数、跟踪运行错误，PL/SQL将SQL的数据操纵功能同过程语言的数据处理功能结合起来了。此外PL/SQL还具有以下特性。

1．数据抽象

数据抽象可以从数据中提取必要的属性、忽略不必要的细节。一旦设计了数据结构，就可以忽略其细节，设计操纵数据结构的算法。

在PL/SQL中，面向对象的编程是基于对象类型的，对象类型封装了数据结构、函数和过程。组成数据结构的变量称为属性，函数和过程称为方法。

对象类型减少了将大系统分解为逻辑实体的复杂性，可以用来创建软件组件，易于维护、模块化和重用。

当使用CREATE TYPE语句定义对象类型时，用户就为现实中的对象创建了一个抽象模板。例如，下面是一个银行账号的示例，模板定义了对象在应用环境中所需的属性和行为。

CREATE TYPE Bank_Account AS OBJECT (
acct_number INTEGER(5),
balance REAL,
status VARCHAR2(10),
MEMBER PROCEDURE open (amount IN REAL),
MEMBER PROCEDURE verify_acct (num IN INTEGER),
MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL),
MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL),
MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL),
MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL);

运行时，如果数据结构带有值，就可以创建一个抽象银行账号的实例。

2．信息隐藏

使用信息隐藏，我们就只能看到算法和数据结构设计的给定层次上的信息。信息隐藏使得将高层设计决策与底层设计细节相分离。

通过使用至顶向下的设计方法可以为算法实现信息隐藏，一旦定义了底层过程的目标和接口，就可以忽略实现细节，在高层中，它们是隐藏的。

我们可以通过数据封装实现数据结构的信息隐藏，通过为数据结构开发一个子程序，将它同用户和其他开发人员隔离开。这样，其他开发人员就可以知道如何使用对数据结构进行操作的子程序，但是不知道结构如何描述的。

使用PL/SQL的包，可以将子程序定义成公共的或私有的。使用包，可以将数据进行封装，将子程序的定义放在黑箱中，私有定义是隐藏的，不可访问，如果定义改变了，只有包受到影响，应用程序不会受到影响，从而简化了维护。

总的来说，PL/SQL具有许多优点，主要包括：

◎ 对SQL的支持；

◎ 支持面向对象编程；

◎ 提供更好的性能；

◎ 执行效率更高；

◎ 使用方便；

◎ 同Oracle集成更好；

◎ 更高的安全性。

6.1.3 PL/SQL的开发和运行环境

PL/SQL编译和运行系统是一项技术而不是一个独立的产品，可以将这项技术想象成为一个可以编译和执行PL/SQL块和子程序的引擎，该引擎可以安装在Oracle服务器上，或者在应用开发工具中，如Oracle Form和Oracle Reports。PL/SQL能够驻留在两种环境中：

◎ Oracle数据库服务器；

◎ Oracle开发工具。

这两种环境是相互独立的，PL/SQL与Oracle服务器捆绑在一起的，但是，在有些工具中是不可用的。在这两种环境中，PL/SQL引擎接受任何PL/SQL块和子程序作为输入，图6-2显示了PL/SQL引擎如何处理块的。引擎执行过程语句，将SQL语句发送给Oracle服务器的SQL语句执行器执行。

缺乏本地PL/SQL引擎的应用开发工具必须依靠Oracle处理PL/SQL块和子程序，包含PL/SQL引擎时，Oracle服务器能够处理PL/SQL块和子程序，就像处理简单的SQL语句一样，Oracle将块和子程序发送给本地PL/SQL引擎。

匿名PL/SQL块能够嵌套在Oracle预编译器或者OCI程序中，运行时，如果缺乏本地PL/SQL引擎，程序就会将这些块发送给Oracle服务器，在服务器上进行编译和执行。同时，诸如SQL*Plus和Enterprise Manager等工具，如果没有PL/SQL引擎，就必须将匿名块发送给Oracle服务器。

图6-2 PL/SQL引擎

子程序可以经过编译，存储在Oracle数据库中。若一个子程序可以使用Oracle工具显式创建，那么，这个子程序就称为存储的子程序，经过编译，并存储在数据字典中，成为一个方案对象，能够被连接到数据库的应用引用。

数据库触发器是与数据库表、视图和事件相关联的存储子程序，当INSERT,UPDATE和DELETE语句影响Oracle表时触发执行。数据库触发器的用途之一是审计数据修改情况。例如，下面的table-level触发器在emp表的salaries修改时启动。

CREATE TRIGGER audit_sal
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_audit VALUES ...
END;

当应用开发工具包含PL/SQL引擎时，它就能处理PL/SQL块和子程序，此时，应用开发工具将块传给PL/SQL引擎，引擎执行所有的过程语句，然后将SQL语句发送给Oracle。因此，大量的工作在应用站点就完成了，而在服务器上只进行少量工作，从而减少了服务器的负载。

如果块中没有包含SQL语句，那么，引擎也能在应用站点处理整个块，这对于使用条件和循环控制的应用是非常有益的。

6.1.4 运行PL/SQL程序

接下来的学习中将会接触到许多PL/SQL程序，为了方便读者，我们将采用Oracle的示例数据库SH方案并以Oracle的SQL*PLUS Worksheet工具来调试PL/SQL程序，通过以下方式进入编译的SQL*PLUS Worksheet环境。

1．方式1

（1）使用sys用户登录到SQL*PLUS Worksheet。

（2）对SH表和用户解锁，输入ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK命令，更改SH的用户名和密码。

（3）使用SH用户登录，即可方便地以SH方案来操作了。

2．方式2

这些步骤也可以方便地在OEM中完成，具体操作如下：

（1）使用sys用户登录到OEM中。

（2）打开SH用户的属性页，修改SH用户的密码，并将该用户解锁。

（3）在OEM中打开SQL*PLUS Worksheet工具，并重新使用SH用户登录到SQL*PLUS Worksheet中，此时即可以SH方案来操作。

注意

此处只是为了方便读者调试PL/SQL程序，所以没有采用直接的SQL*PLUS编译环境，其实SQL*PLUS Worksheet只是SQL*PLUS的一个外挂模型，最终的代码编译还是要通过SQL*PLUS来执行。

本例通过一个简单的PL/SQL程序，并通过运行该程序和得到结构来描述PL/SQL程序的执行过程。由于本程序在本章后面的学习中会接触到，这里就不进行详细解释了。

DECLARE
  v_Number sales.prod_id%TYPE;
  v_Comment VARCHAR2(35);
  v_Test VARCHAR2(10):=’See Here’;
BEGIN
  SELECT Min(prod_id)
    INTO v_Number
    FROM sales;
CASE
  WHEN v_Number < 500 THEN
    v_Comment := 'Too small';
    INSERT INTO temp_table (my_com , vcomment)
      VALUES ('This is too cazy' , v_Comment);          
  WHEN v_Number < 1000 THEN
    v_Comment := 'A little bigger';
    INSERT INTO temp_table (my_com , vcomment)
      VALUES ('This is only luck' , v_Comment);
  WHEN v_Test:=’See Here’ THEN
    v_Comment := 'Test Model';
    INSERT INTO temp_table (my_com , vcomment)
      VALUES ('This is Test' , v_Comment);
  ELSE
    v_Comment := 'That enough';
    INSERT INTO temp_table (my_com , vcomment)
      VALUES ('Maybe good' , v_Comment);
  END IF;
END;

注意

本例使用了一个temp_table表，因此，读者需要建立一个temp_table表。由于本例只是用来向读者展示PL/SQL程序的样式，在本章的流程控制中会有该程序的介绍及temp_table表的建立步骤，因此，这里就不重复说明了。

6.2 PL/SQL编程

PL/SQL结合了第三代和第四代编程语言的功能和特性，因此，它也具有这两代语言的共同特征，如语言块、数据类型、变量和常量、语句流程等，本节主要介绍这些共有的语言特性。

6.2.1 基本语言块

PL/SQL程序可划分成为块结构，每一个块都包含PL/SQL和SQL语句。所有的PL/SQL程序都是由语言块构成的，与C等第三代语言一样，它们之间是可以嵌套的。一般说来，一个语言块负责程序中的一部分功能。

在PL/SQL中，用户可以使用SQL语句操作Oracle数据和用于处理数据的流控语句，而且，可以声明常量和变量、定义过程和函数、跟踪运行错误，PL/SQL将SQL的数据操纵功能同过程语言的数据处理功能结合起来了。

典型的PL/SQL代码块包含如下结构：

DECLARE         －Optional
        Variables，cursors，user-defined，exceptions
BEGIN                   －Mandatory
        －SQL statements
        －PL/SQL statements
EXCEPTION               －Optional
        Actions to perform when errors occur
END；                    －Mandatory

具体描述如下：

◎ DECLARE：声明变量、游标、用户自定义类型和异常。

◎ BEGIN：程序开始标志，表示开始执行SQL和PL/SQL程序。

◎ EXCEPTION：异常处理部分，处理SQL和PL/SQL语句处理中的异常部分。

◎ END：程序结束标志，表示程序结束。

其中，变量声明和异常处理部分是可选的，程序执行部分（BEGIN和END之间）是必需的。在执行部分，可以嵌套子块和异常处理部分，也可以在声明部分定义子程序。

PL/SQL是块结构语言，也就是说，构成PL/SQL程序的基本单元（过程、函数和其他块）是逻辑块。块包括任何嵌套的子块，一般来说，每个逻辑块对应于需要解决的问题或者子问题，然而，PL/SQL支持问题解决的划分和竞争方法，称为stepwise refinement。

块可以让用户将相关的声明和语句分成逻辑上的组，也就是说，可以将声明放在它们使用的位置。变量声明对于块是本地的，当块完成以后，就不存在了。

6.2.2 字符集和语法注释

PL/SQL程序中的字符集和语法注释分别介绍如下。

1．字符集

所有的PL/SQL程序都是由语法单元构成的。从实质上来说，一个语法单元就是一个字符序列，字符序列中的字符来自PL/SQL语言的字符集。PL/SQL的字符集包括：

◎ 所有大小写字母；

◎ 数字0～9；

◎ 符号：()+－* /〈〉=！~；：.'@ % "# $ ^ & _ | {}？ [ ]；

此外，一些空白的制表符、空格符和回车符也是PL/SQL字符集中的合法字符。

2．语法注释

通过注释可以提高程序的可读性，通常注释在程序执行过程中会被忽略掉。PL/SQL中的注释分为两种：单行注释和多行注释，这一点和C语言极为相似。

（1）单行注释

单行注释的开始标志是单行注释符“--”，从单行注释符开始到该行行尾都是注释。下面是一个没有使用注释的例子。

REM 没有使用注释
DECLARE
  v_Id CHAR(2):=CH;
  v_Name        VARCHAR(40):=China;
  v_Subregion   VARCHAR(30):=Asia;
  v_Region              VARCHAR(20):=Asia;
  v_Total       VARCHAR(11);
BEGIN
  INSERT INTO countries (country_id, country_name,
country_subregion, country_region, country_total)
  VALUES (v_Id, v_Name, v_Subregion, v_Region, v_Total);
END;

可以看出没有使用注释的代码的可读性不高，而使用单行注释的例子如下：

REM 使用单行注释
DECLARE
  v_Id CHAR(2):=CH; -- 定义国家Id为2个字符的字符变量
  v_Name        VARCHAR(40):=China; -- 定义国家名称
  v_Subregion   VARCHAR(30):=Asia; -- 定义国家子范围
  v_Region       VARCHAR(20):=Asia; -- 定义国家的范围
  v_Total       VARCHAR(11); -- 定义Total
BEGIN
  -- 将Id、姓名、子范围、范围和Total等国家信息
  -- 插入到countries表中
INSERT INTO countries (country_id, country_name,
country_subregion, country_region, country_total)
  VALUES (v_Id, v_Name, v_Subregion, v_Region, v_Total);
END;

注意

如果注释超过一行，就必须在每一行的开头都使用单行注释符。

（2）多行注释

除了使用单行注释外，PL/SQL还可以使用多行注释。多行注释由“/*”开头，由“*/”结束。下面是一个使用多行注释的例子。

REM 使用多行注释
DECLARE
  v_Id CHAR(2):=CH; /* 定义国家Id为2个字符的字符变量 */
  v_Name        VARCHAR(40):=China; /* 定义国家名称为最大四十个
                                                                        字符的字符变量 */
  v_Subregion   VARCHAR(30):=Asia; /* 定义国家子范围
                                                                                最大为30个字符的变量 */
  v_Region              VARCHAR(20):=Asia; /* 定义国家的范围最大为20
                                                                                个字符的字符变量 */
  v_Total       VARCHAR(11); /* 定义Total */
BEGIN
  /* 将Id、姓名、子范围、范围和Total等国家信息
    插入到countries表中 */
INSERT INTO countries (country_id, country_name,
country_subregion, country_region, country_total)
    VALUES (v_Id, v_Name, v_Subregion, v_Region, v_Total);
END;

由此可以看出，当注释较多时，使用多行注释要比使用多个单行注释方便。

注意

多行注释是不允许嵌套的，即在一个“/* */”里使用“/* */”是不合法的。

下面是一个不合法的多行注释例子。

REM 不合法的多行注释
BEGIN
  /* We are now inside a comment. If we were to begin another
     comment such as /* this */ it would be illegal. */
  NULL;
END;

6.2.3 数据类型和类型转换

本节主要介绍Oracle的数据类型及相互间的转换。

1．数据类型

Oracle的数据类型种类较多，所有的数据类型见附录，常用的数据类型有以下几种。

◎ 数字类型系列：Number,PLS_Integer,BINARY_Integer

◎ 字符类型系列：Char,Varchar2,Long,Nchar2,Nvarchar2

◎ 日期类型系列：Date,TimeStamp,Interval

◎ 布尔类型：Boolean

◎ LOB类型：blob,clob,nclob

◎ 其他类型：bfile,long raw,raw(n)

下面详细介绍这几种数据类型。

（1）数字类型

Number类型可以用来表示可变长的数值列，其语法为NUMBER(m, n)，该类型允许0、正值及负值。其中，m是指所有有效数字的位数，n是指小数点以后的位数。m和n的取值范围分别为m=1 to 38，n=-84 to 127。需要注意的是，如果数值超过了位数的限制，那么就会被截取多余的位数。如表6-1所示。

表6-1 Number的用法

注意

当位数值为负值时，表示实际值是按小数点左边进行舍入。

PLS_Integer类型与Number类型不同的是，Number类型可以存储实数，而PLS_Integer类型仅能存储整数。PLS_Integer类型的存储范围为类2147483647～＋2147483647，如果使用PLS_Integer类型时发生溢出，那么，系统就会出现错误。

BINARY_Integer类型与PLS_Integer类型十分相似，也是只能存储整数，范围和PLS_Integer类型一样为类2147483647～＋2147483647。与PLS_Integer不同的是，BINARY_Integer类型在溢出时不会发生错误，系统会自动指定一个Number类型来替代这个BINARY_Integer类型，而且这个Number类型拥有最大的值范围。

下面的代码是使用PLS_Integer类型溢出时的情况。

DECLARE
  v_Number PLS_INTEGER;
BEGIN
  --将v_Number赋值为2147483640
  v_Number:=2147483640;
  --对v_Number加10，显然将导致
  --v_Number溢出，然后再减去10
  --使得v_Number恢复原值
  --此时系统显示溢出错误
  v_Number:=v_Number+10-10;
END;

系统显示结果：

DECLARE
*
ERROR 位于第 1 行:
ORA-01426: 数字溢出
ORA-06512: 在line 11

下面的代码是使用BINARY_Integer类型溢出时的情况。

DECLARE
  v_Number BINARY_INTEGER;
BEGIN
  --将v_Number赋值为2147483640
  v_Number:=2147483640;
  --对v_Number加10，显然将导致
  --v_Number溢出，然后再减去10
  --使得v_Number恢复原值
  --而且系统没有显示错误
  v_Number:=v_Number+10-10;
END;

系统显示结果：

PL/SQL 过程已成功完成。

（2）字符类型

Char类型用来表示定长的字符串，其语法为Char[(L)]。其中，L为指定的最大长度，需要注意的是，L是可选的，如果没有指定L的值，那么默认值将是1。PL/SQL中的Char类型的最大长度为32767。下面是一个比较字符串的例子。

DECLARE
  v_Char1 Char(3):='No';
  v_Char2 Char(6):='No';
  v_Result Char(10);
BEGIN
  IF v_Char1=v_Char2 THEN
    v_Result:='EQUL'; --如果相等，则输出EQUL
  ELSE
    v_Result:='Not EQUL'; --如果不等，则输出Not EQUL
  END IF;
  DBMS_OUTPUT.PUT_LINE(v_Result);
END;

系统显示结果：

EQUL
PL/SQL 过程已成功完成。

VarChar2类型用来存储可变长的字符串，其语法为VarChar2(L)。其中，L为指定的最大长度，需要注意的是，L是必需的。PL/SQL中的VarChar2类型的最大长度也是32767。下面的代码显示了VarChar2类型与Char类型的最大不同。

DECLARE
  v_Char1 VarChar2(3):='No';
  v_Char2 VarChar2(6):=' No';
  v_Result Char(10);
BEGIN
  IF v_Char1=v_Char2 THEN
    v_Result:='EQUL';
  ELSE
    v_Result:='Not EQUL';
  END IF;
  DBMS_OUTPUT.PUT_LINE(v_Result);
END;

系统显示结果：

Not EQUL
PL/SQL 过程已成功完成。

Long类型用来存储可变长的字符串，其使用方法和VarChar2十分相似，所不同的是，其最大长度为32760。

NChar和NVarChar2类型是PL/SQL中用来存储不同语言的字符集中的字符串的。NChar和NVarChar2类型的语法对应于Char和VarChar2类型的语法，但是字符长度可能有所不同。由于这两种类型使用较少，这里就不多叙述了。

（3）布尔类型

Boolean类型主要在PL/SQL的控制语句中使用，Boolean类型的合法赋值为True,False和Null，以下对Boolean类型的赋值是不合法的。

v_Boolean BOOLEAN:=1;

（4）其他类型

其他类型的使用方法如表6-2所示。

表6-2 其他常用的数据类型

2．类型转换

PL/SQL可以处理数据类型之间的转换。常见的处理类型之间的转换函数有以下几种。

◎ To_Char：可以将Number和Date类型转换为Varchar2类型。

◎ To_Date：可以将Char转换为Date类型。

◎ To_Number：可以将Char类型转换为Number类型。

此外，PL/SQL还会自动转换各种类型，如下面的例子。

DECLARE
        v_Customer_Id Varchar2(10);
BEGIN
        SELECT Max(CUST_ID) into v_Customer_Id
        FROM customers where CUST_FIRST_NAME='Anne';
END;

在数据库中，Max(CUST_ID)是一个Number类型的字段，但是，v_Customer_Id却是一个Varchar2(10)的变量，PL/SQL会自动将数值类型转换为字符串类型。此外，PL/SQL还可以在下列类型中自动转换。

◎ 字符和数字；

◎ 字符和日期。

PL/SQL可以在某些类型之间自动转换，但是，使用转换函数对于程序的可读性等都是一个很好的习惯。对于上面的例子，可以使用To_Char函数将Number类型进行转换，具体代码如下：

DECLARE
        v_Customer_Id Varchar2(10);
BEGIN
        SELECT To_Char(Max(CUST_ID)) into v_Customer_Id
        FROM customers where CUST_FIRST_NAME='Anne';
END;

6.2.4 变量和常量

本节主要介绍PL/SQL变量的声明、属性及其作用域。

1．变量和常量声明

信息在数据库与PL/SQL程序之间是通过变量进行传递的。所谓变量，就是可以由程序读取或赋值的存储单元。在6.2.3节的例子中，v_Customer就是一个变量。通常，变量是在PL/SQL块的声明部分定义的。

每个变量都有一个特定的类型与其关联，变量的类型定义了变量可以存放的信息类别。如下所示，PL/SQL变量可以与数据库列具有同样的类。

DECLARE
v_StudentName VARCHAR2(20);
v_CurrentDate DATE;
v_NumberCredits NUMBER(3);

PL/SQL变量也可以是其他类型，如下所示。

DECLARE
v_LoopCounter BINARY_INTEGER;
v_CurrentlyRegistered BOOLEAN;

除此之外，PL/SQL还支持用户自定义的数据类型，如记录类型、表类型等。使用用户自定义的数据类型，可以定制程序中使用的数据类型结构。下面是一个用户自定义的数据类型的例子。

DECLARE
TYPE t_StudentRecord IS RECORD (FirstName VARCHAR2(10),
LastName VARCHAR2(10), CurrentCredits NUMBER(3));
v_Student t_StudentRecord;

声明常量与声明变量一样，只是需要在前面加入CONSTANT，同时，将值赋给常量。例如：

credit_limit CONSTANT REAL := 5000.00;

变量是PL/SQL中用来处理数据项所用的名字。程序员根据下列规则选择变量名称。

◎ 变量必须以字母（A～Z）开头；

◎ 其后跟可选的一个或多个字母、数字（0～9）或特殊字符$、#或_；

◎ 变量长度不超过30个字符；

◎ 变量名中不能有空格。

先来看几个实例。表6-3给出了样例变量名，并评价了其合法性。

表6-3 合法及不合法的变量名

2．变量属性

PL/SQL变量中包含属性，可以不用重复定义就可以引用一个项目的数据类型和结构。数据库列和表包含相似的属性，可以用来简化维护，其中%用来表示属性提示符。

%TYPE属性提供了变量和数据库列的数据类型。当声明一个包含数据库值的变量时非常有用。例如，假设在表title中包含books列，若要声明变量my_title，与title的数据类型相同，则使用点注释和%TYPE属性，具体格式如下：

my_title books.title%TYPE;

使用%TYPE声明my_title有两个优点：第一，不必知道title确切的数据类型；第二，如果改变了title的数据库定义，my_title的数据类型在运行时也会自动修改。

%ROWTYPE

可以使用%ROWTYPE属性声明描述表的行数据的记录，对于用户定义的记录，必须声明自己的域。记录包含唯一的命名域，具有不同的数据类型，示例如下：

DECLARE
TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT);
TYPE MeetingTyp IS RECORD (
date_held DATE,
duration TimeRec,
location VARCHAR2(20),
purpose VARCHAR2(50));

注意

可以嵌套记录，也就是说，记录可以是记录的一个组件。

在PL/SQL中，记录用于将数据分组，一个记录包含几个相关的域，这些域用于存储数据。%ROWTYPE属性提供了表示一个表中一行的记录类型。这个记录可以存储整个从表所选的数据行，或者是从游标中提取的数据行。

行中的列和记录中相对应的域具有相同的名称和数据类型，例如，我们声明一个记录名为dept_rec，与dept表具有相同的名称和数据类型。

DECLARE
dept_rec dept%ROWTYPE;

可以使用点注释引用域，格式如下：

my_deptno := dept_rec.deptno;

如果声明了游标提取last name,salary,hire date和job title，那么，就可以使用%ROWTYPE声明一个记录，存储相同的信息，代码如下：

DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, job FROM emp;
emp_rec c1%ROWTYPE;

当执行下面的语句时：

FETCH c1 INTO emp_rec;

在emp表的ename列的值就赋予emp_rec的ename域，如图6-3所示为可能出现的结果。

图6-3 显示结果

3．变量作用域

变量作用域是指可以访问该变量的程序部分。对于PL/SQL变量来说，其作用域就是从变量的声明到语言块的结束。当变量超出了作用域，PL/SQL解释程序就会自动释放该变量的存储空间。

6.2.5 表达式和运算符

本节介绍表达式和运算符，其中，表达式中运算符的优先级决定了表达式求值的顺序。

1．表达式

表达式是一个变量和文字的序列，用运算符进行分隔。表达式的数值由组成它的变量和文字的取值以及运算符的定义来决定。

表达式中运算符的优先级决定了表达式求值的顺序。表达式如下：

9+2×2

运算符优先级的存在，使得表达式的值是13，而不是通过顺序运算得到的22。当然，可以通过更高级的运算符来改变运算的顺序。比如，可以使用括号，使用下面的表达式，得到的值是22。

(9+2)×2
2．运算符

PL/SQL的运算符可以是一元的（带有一个参数），也可以是二元的（带有两个参数）。运算符通常可以分为以下几类：

◎ 算术运算符；

◎ 关系运算符；

◎ 其他符号。

（1）算术运算符

表6-4列出了PL/SQL中常用的算术运算符及其意义。

表6-4 算术运算符及其意义

算术运算符的优先级为（由高到低）：幂、正负、乘除、加减。

（2）关系运算符

表6-5列出了PL/SQL中常用的关系运算符及其意义。

表6-5 关系运算符及其意义

关系运算符的优先级是相等的。

（3）逻辑运算符

表6-6列出了PL/SQL中常用的逻辑运算符及其意义。

表6-6 逻辑运算符及其意义

逻辑运算符的优先级为（由高到低）：NOT、AND、OR。

（4）其他符号

PL/SQL为支持编程，也使用了其他符号。表6-7列出了部分常用符号，使用PL/SQL的用户必须对此有所了解。

表6-7 部分常用符号

6.2.6 流程控制

控制结构是PL/SQL对SQL最重要的扩展，使得PL/SQL不仅可以操作Oracle数据，而且可以使用提交、迭代和顺序流控处理语句（如IF-THEN-ELSE、CASE、FOR-LOOP、WHILE-LOOP、EXIT-WHEN、GOTO）数据。

1．IF-THEN-ELSE（条件控制）

IF-THEN-ELSE的语法如下：

IF boolean_expression1 THEN
        sequence_of_statements;
[ELSIF Boolean_expression2 THEN
        sequence_of_statements;]
...
[ELSE
        sequence_of_statements;]
END IF;

注意

IF-THEN-ELSE语法中ELSIF的拼写，其中缺少一个字母“E”，而不是“ELSEIF”。

我们经常需要根据不同环境采取相应的操作，使用IF-THEN-ELSE语句可以按照条件执行语句序列。IF子句检查条件，THEN字句定义条件为TRUE时的操作，ELSE子句定义IF条件为FALSE或者为NULL时执行的操作。

下面是一个简单的IF-THEN-ELSE语句。

DECLARE
  v_Number sales.prod_id%TYPE;
  v_Comment VARCHAR2(35);
BEGIN
  /*判断prod_id的大小，并作出相应的判断 */
  SELECT MAX(prod_id)
    INTO v_Number
    FROM sales;
  IF v_Number < 500 THEN
    v_Comment := 'Too small';
  ELSIF v_Number < 1000 THEN
    v_Comment := 'A little bigger';
  ELSE
    v_Comment := 'That enough';
  END IF;
END;

上面的语句结构十分清楚，首先通过Select语句选出sales表中prod_id中的最大值，并保存在变量v_Number中，然后做出一系列的判断。

第一个条件是：

v_Number < 500

第一个语言序列是：

v_Comment := 'Too small';

由于v_Number的值大于500，所以条件一为False，则要执行条件二：

v_Number < 1000

第二个语言序列是：

v_Comment := 'A little bigger';

同样v_Number的值大于1000，所以要执行最后的语言序列：

v_Comment := 'That enough';

可以看出，只有当条件为True时，相应的语言序列才会被执行。

上例中的语言序列仅起到给变量赋值的作用，其实可以在语言序列中加入相应的处理语句。下例是对上例的扩展。

DECLARE
    v_Number sales.prod_id%TYPE;
    v_Comment VARCHAR2(35);
  BEGIN
    /*判断prod_id的大小，并做出相应的判断 */
    SELECT MAX(prod_id)
      INTO v_Number
      FROM sales;
  IF v_Number < 500 THEN
    v_Comment := 'Too small';
    INSERT INTO temp_table (my_com , vcomment)
      VALUES ('This is too cazy' , v_Comment);           /* 将信息插入到temp_table表中*/
  ELSIF v_Number < 1000 THEN
    v_Comment := 'A little bigger';
    INSERT INTO temp_table (my_com , vcomment)
      VALUES ('This is only luck' , v_Comment);
  ELSE
    v_Comment := 'That enough';
    INSERT INTO temp_table (my_com , vcomment)
      VALUES ('Maybe good' , v_Comment);
  END IF;
END;

注意

本例使用了temp_table表，读者需要建立一个temp_table表，创建该表的SQL语句如下所示：

CREATE TABLE "SH"."TEMP_TABLE" (
"MY_COM" VARCHAR2(35),
"VCOMMENT" VARCHAR2(35))

NULL条件：下面两个语言块说明了条件值为False或Null时的差别。

语言块1：

DECLARE
  v_Number1 NUMBER ;
  v_Number2 NUMBER;
  v_Result VARCHAR(10);
BEGIN
... –-给v_Number1和v_Number2赋值
IF v_Number1 > v_Number2 THEN
v_Result :='The first is big';
  ELSE
v_Result :='The second is big';
  END IF;
END;

语言块2：

DECLARE
  v_Number1 NUMBER ;
  v_Number2 NUMBER;
  v_Result VARCHAR(10);
BEGIN
... –-给v_Number1和v_Number2赋值
IF v_Number1 <= v_Number2 THEN
v_Result :='The second is big';
  ELSE
v_Result :='The first is big';
  END IF;
END;

当v_Number1和v_Number2的值都存在时，以上两个语言块的执行结构相同；若有一个值为Null，则两个语言块的执行结果就不相同了。

假设v_Number1的值为8，v_Number2的值为Null，语言块1中条件（8>Null）值为NULL，则v_Result := 'The second is big'；若语言块2中的条件（8<=Null）值也为NULL，则v_Result := 'The first is big'。由此可见，当v_Number1和v_Number2两个变量中有一个值为空时，语言块1和语言块2的执行结构都会不同。

解决方法如下。

语言块1：

DECLARE
  v_Number1 NUMBER ;
  v_Number2 NUMBER;
  v_Result VARCHAR(10);
BEGIN
... –-给v_Number1和v_Number2赋值
IF v_Number1 IS NULL OR
     v_Number2 IS NULL THEN
v_Result :='Unknown'; --确保两个变量不为NULL
ELSIF v_Number1 > v_Number2 THEN
v_Result :='The first is big';
  ELSE
v_Result :='The second is big';
  END IF;
END;

语言块2：

DECLARE
  v_Number1 NUMBER ;
  v_Number2 NUMBER;
  v_Result VARCHAR(10);
BEGIN
... –-给v_Number1和v_Number2赋值
IF v_Number1 IS NULL OR
    v_Number2 IS NULL THEN
v_Result :='Unknown'; --确保两个变量不为NULL
ELSIF v_Number1 <= v_Number2 THEN
v_Result :='The second is big';
  ELSE
v_Result :='The first is big';
  END IF;
END;

当两个变量中有NULL时，比较部分就不会执行了，并将v_Result赋值为Unknown。

2．CASE语句

CASE的语法结构如下：

CASE test_var
WHEN value1 THEN sequence_of_statements1；
WHEN value2 THEN sequence_of_statements2；
．．．
WHEN valuen THEN sequence_of_statementsn；
[ELSE else_sequence;]
END CASE；

CASE用来判断test_var和value是否相等，如果相等，则执行value后的语句，如果不相等，则执行ELSE后的语句。下面是一个典型的CASE语句。

DECLARE
  v_First_Name customer.costomer first_name%TYPE ;
  v_Result VARCHAR(20);
BEGIN
--从数据库中取出要判断的值
SELECT customer_first_name
INTO v_First_Name
FROM customer
WHERE id=100;
--判断v_First_Name的值，并给出结果
CASE v_First_Name
    WHEN 'Abner' THEN
        V_Result:='Abner Everett';
    WHEN 'Ada' THEN
        V_Result:='Ada Nenniger';
    WHEN 'Anne' THEN
        V_Result:='Anne Koch';
    WHEN ''Annie THEN
        V_Result:='Annie Gilmour';
    ELSE
        v_Result:='Nothing';
  END CASE;
END;

此外，CASE语句还有一种变化形式，如下所示。

CASE
WHEN test1 THEN sequence_of_statements1;
WHEN test2 THEN sequence_of_statements2;
……
WHEN testn THEN sequence_of_statementsn;
END CASE;

这种变化形式中，CASE语句是通过每一个WHEN语句中的布尔表达式来判断的，只有当布尔表达式的值为TRUE时，相应的语句才会被执行。下面的代码是前面的条件语句的变化，具体代码如下所示。

DECLARE
  v_Number sales.prod_id%TYPE;
  v_Comment VARCHAR2(35);
  v_Test VARCHAR2(10):=’See Here’;
BEGIN
  /*判断prod_id的大小，并作出相应的判断 */
  SELECT Min(prod_id)
    INTO v_Number
    FROM sales;
CASE
  WHEN v_Number < 500 THEN
    v_Comment := 'Too small';
  INSERT INTO temp_table (my_com , vcomment)
    VALUES ('This is too cazy' , v_Comment);             /* 将信息插入temp_table表中*/
  WHEN v_Number < 1000 THEN
    v_Comment := 'A little bigger';
  INSERT INTO temp_table (my_com , vcomment)
    VALUES ('This is only luck' , v_Comment);
  WHEN v_Test:=’See Here’ THEN
    v_Comment := 'Test Model';
  INSERT INTO temp_table (my_com , vcomment)
    VALUES ('This is Test' , v_Comment);
  ELSE
    v_Comment := 'That enough';
    INSERT INTO temp_table (my_com , vcomment)
      VALUES ('Maybe good' , v_Comment);
  END IF;
END;

注意

CASE语句按顺序执行，只要有值为TRUE，那么，执行完对应的sequence_of_statements后就将结束CASE语句。上例中，如果第一个WHEN子句条件为TRUE，那么，即使第三个WHEN子句的条件也为真，它也不会执行。

3．LOOP循环控制

PL/SQL有多种循环控制语句，LOOP循环语句是其中最简单的一种。LOOP语句的格式如下：

LOOP
-- sequence of statements
END LOOP;

已经看出，这种循环语句是没有终止的，如果不人为控制，其中的sequence of statements将会无限地执行。一般可以通过加入EXIT语句来终结该循环，如下所示。

DECLARE
v_Counter BINARY_INTEGER:=1;
BEGIN
LOOP
    --将v_Counter的值插入number_table表中
INSERT INTO number_table(num)
    VALUES( v_Counter);
v_Counter:=v_Counter+1;
--当v_Counter:=20时退出循环
IF v_Counter=20 THEN
    EXIT;
END IF;
END LOOP;
END;

注意

本例使用了number_table表，读者需要建立一个number_table表，创建该表的SQL语句如下所示。

CREATE TABLE "SH"."NUMBER_TABLE" ("NUM" NUMBER(10))
4．FOR-LOOP循环

FOR-LOOP语句的格式如下：

FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP
sequence of statements
END LOOP;

其中，loop_counter为循环变量，low_bound和high_bound为循环的最小、最大值。FOR-LOOP语句在该范围内的每个整数执行一次，直到循环结束。对上例中的LOOP循环代码的改进如下：

DECLARE
v_Counter BINARY_INTEGER:=1;
BEGIN
FOR v_Counter IN 1..19 LOOP
    --将v_Counter的值插入number_table表中
INSERT INTO number_table(num)
    VALUES(v_Counter);
END LOOP;
END;

此外，PL/SQL中还可以动态定义循环的最大值和最小值，下面是一个动态制定最大、最小值的例子。

DECLARE
v_MinValue:=1;
v_MaxValue:=19;
v_Counter BINARY_INTEGER:=1;
BEGIN
FOR v_Counter IN v_MinValue..v_MaxValue LOOP
    --将v_Counter的值插入number_table表中
INSERT INTO number_table(num)
    VALUES(v_Counter);
END LOOP;
END;
5．WHILE-LOOP循环

WHILE-LOOP的语法如下：

WHILE condition LOOP
sequence_of_statement;
END LOOP

WHILE-LOOP语句的执行与条件相关。在循环前，首先对条件condition进行检查，如果条件为true，执行语句sequence_of_statement，如果条件为false或为null，则跳过循环。

示例：

DECLARE
v_Counter BINARY_INTEGER:=1;
BEGIN
WHILE v_Counter< 20 LOOP
--将v_Counter的值插入number_table表中
INSERT INTO number_table(num)
VALUES (v_Counter);
END LOOP;
END;

需要注意的是，v_Counter一定要赋值，否则若条件v_Counter< 20为空，则不会执行其对应的语句。

6．GOTO顺序控制

GOTO语句的语法如下：

GOTO label;

其中，label是用双箭头括号括起来的标记。

使用GOTO语句可以控制执行顺序，具体使用参考下面的示例。

DECLARE
v_Counter BINARY_INTEGER:=1;
BEGIN
LOOP
    --将v_Counter的值插入number_table表中
INSERT INTO number_table(num)
    VALUES( v_Counter);
v_Counter:=v_Counter+1;
--当v_Counter:=20时退出循环
IF v_Counter=20 THEN
    GOTO Loop_End;
END IF;
END LOOP;
<<Loop_End>>
END;

注意

使用GOTO语句时要十分谨慎，有过编程经验的读者都知道，GOTO跳转对代码的理解和维护会造成很大的困难，所以尽量不要使用GOTO语句。

6.3 过程和函数

前面已经提到PL/SQL语言和第三代语言有许多相似的地方，本节介绍的函数和过程就是其中的一部分。PL/SQL中的过程和函数（通称为子程序）是PL/SQL块的一种特殊类型，这种类型的子程序可以以编译的形式存放在数据库中，并为后续的程序块调用。

下例是一个简单的过程，该过程使用DBMS_OUTPUT包将所有学生的姓名以定制的格式显示在屏幕上。

CREATE OR REPLACE PROCEDURE PrintStudents
(p_Major IN students.major%TYPE) AS
CURSOR c_Students IS
SELECT first_name, last_name
FROM students
WHERE major = p_Major;
BEGIN
FOR v_StudentRec IN c_Students LOOP
DBMS_OUTPUT.PUT_LINE(v_StudentRec.first_name || ' ' ||
v_StudentRec.last_name);
END LOOP;
END;

一旦创建了该过程并将其存储在数据库中，就可以用如下的程序块来调用该过程。

BEGIN
PrintStudents('Computer Science');
END;

6.3.1 过程

创建过程的基本语法如下：

CREATE [ OR REPLACE ] PROCEDURE procedure_name
[ ( argument [ { IN | OUT | IN OUT}] type ,
…
argument [ { IN | OUT | IN OUT}] type ) ] { IS | AS}
procedure_body

其中：

◎ procedure_name为要创建的过程的名称；

◎ OR REPLACE表示若名为procedure_name的过程存在，就覆盖该过程；

◎ argument为过程参数的名称；

◎ type为参数的类型；

◎ IN、OUT和IN OUT为形式参数。

注意

过程的创建过程中是没有DECLARE关键字的，而是使用IS或者AS关键字来代替。

下面来看一个具体的子过程。

CREATE OR REPLACE PROCEDURE AddNewCountries (
  c_Id countries.country_id%TYPE,
  v_Name countries. country_name%TYPE,
  v_ Subregion countries. country_subregion%TYPE,
  v_Region countries. country_region%TYPE,
  v_Total countries. country_total%TYPE) AS
BEGIN
  -- 在countries表中插入一个新的记录值
  INSERT INTO countries (country_id, country_name, country_subregion,
                    country_region, country_total)
    VALUES (c_Id, v_Name, v_ Subregion,
v_Region, v_Total);
END AddNewCountries;

过程创建完毕后，就可以在语言块中调用了，具体如下：

BEGIN
AddNewCountries('CH', 'China', 'East Asia', 'Asia', VWorld Total');
END;

对于一个子过程来说，还有另外一种形式，那就是不带参数的形式。不带参数的子过程在其声明和调用时都是没有括号的，如下例所示。

CREATE OR REPLACE PROCEDURE AddNewCountries
AS
BEGIN
  -- 在countries表中插入一个记录值
  INSERT INTO countries (country_id, country_name, country_subregion,
                     country_region, country_total)
    VALUES ('CH', 'China', 'East Asia', 'Asia', 'World Total');
  DBMS_OUTPUT.PUT_LINE('The Record has insert !');
END AddNewCountries;
CREATE OR REPLACE PROCEDURE GetDate
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('The Date is :' ||
TO_CHAR(SYSDATE,'DD-MM-YYYY'));
END GetDate;

过程创建完毕后，就可以在其他PL/SQL语言块中调用它了，具体如下：

BEGIN
AddNewCountries;
GetDate;
END;

系统执行结果如下：

The Record has insert !
The Date is :18-08-2002

可以看出，当过程没有带参数时，只要过程本身没有错误，在PL/SQL中还是可以的。

6.3.2 函数

创建函数的基本语法如下：

CREATE [ OR REPLACE ] FUNCTION function_name
[ ( argument [ { IN | OUT | IN OUT}] type ,
…
argument [ { IN | OUT | IN OUT}] type ) ]
RETURN { IS | AS}
function_body

其中：

◎ function _name为要创建的过程的名称；

◎ OR REPLACE表示如果名为procedure_name的函数存在，就覆盖该函数；

◎ argument为函数参数的名称；

◎ type为参数的类型；

◎ IN，OUT和IN OUT为形式参数；

◎ RETURN为函数的返回值。

注意

函数中的RETURN子句是必须存在的，一个函数如果没有执行RETURN子句就结束将会发生错误，这一点与过程有所不同。

下面是一个创建函数的例子。

CREATE OR REPLACE FUNCTION CountryNumber (
  n_MaxNumber NUMBER,
  n_MinNumber NUMBER)
RETURN VARCHAR2 IS
  n_Result NUMBER(5);
  v_ReturnValue VARCHAR2(50);
BEGIN
--从COUNTRIES表中选择记录的数量
  SELECT COUNT ( * ) INTO n_Result FROM COUNTRIES;
  IF n_Result > n_MaxNumber THEN
  v_ReturnValue:='The number is too big ';
  ELSIF n_Result < n_MinNumber THEN
v_ReturnValue:='The number is so little';
  ELSE
  v_ReturnValue:='Maybe right enough ';
END IF;
RETURN v_ReturnValue;
END CountryNumber;

我们可以通过以下语句得到函数的执行值。

DECLARE
v_MaxNumber Number:=100000;
v_MinNumber Number:=10;
v_Result VARCHAR2(50);
BEGIN
  v_Result:= CountryNumber (v_MaxNumber,v_MinNumber);
  DBMS_OUTPUT.PUT_LINE(v_Result);
END;

此时系统执行结果如下：

The number is so little

如果将v_MaxNumber赋值为10，v_MinNumber赋值为1，则系统执行结果如下：

The number is too big

同样，如果将v_MaxNumber赋值为100，v_MinNumber为1，则系统执行结果如下：

Maybe right enough

在函数声明过程中可以接受默认值，如果函数在声明过程中带有默认值，那么，在函数调用的过程中就可以不传值，下例是带有默认值的函数。

CREATE OR REPLACE FUNCTION CountryNumber (
  n_MaxNumber NUMBER DEFAULT 10000,
  n_MinNumber NUMBER DEFAULT 10)
RETURN VARCHAR2 IS
n_Result NUMBER(5);
v_ReturnValue VARCHAR2(50);
BEGIN
--从COUNTRIES表中选择记录的数量
  SELECT COUNT ( * ) INTO n_Result FROM COUNTRIES;
  IF n_Result > n_MaxNumber THEN
  v_ReturnValue:='The number is too big ';
  ELSIF n_Result < n_MinNumber THEN
v_ReturnValue:='The number is so little';
  ELSE
v_ReturnValue:='Maybe right enough ';
END IF;
RETURN v_ReturnValue;
END CountryNumber;

可以通过下面的语句来得到函数的执行值。

DECLARE
v_Result VARCHAR2(50);
BEGIN
  v_Result:= CountryNumber;
  DBMS_OUTPUT.PUT_LINE(v_Result);
END;

此时，系统执行结果如下：

The number is so little

注意

过程也有默认值，其形式和函数几乎相同，具体形式请参照函数的形式，这里就不重复了。

6.4 错误处理

PL/SQL能够检测和处理预定义和用户定义的错误条件，称为异常。当错误发生时，就会出现异常。若要处理出现的异常，可以编写不同的执行路径，称为异常处理器。

PL/SQL的异常有两种类型：用户定义异常和系统预定义异常。

6.4.1 预定义异常

预定义异常是由运行系统产生的。例如，如果出现除零错误，那么，PL/SQL就会产生一个预定义的ZERO_DIVIDE异常。例如：

DECLARE
  v_Number Number(2):=10;
  v_Zero Number(2):=0;
  v_Result Number(5);
BEGIN
  --用v_Number除以v_Zero，即10/0从而产生除零错误
  v_Result:=v_Number/v_Zero;
END;

系统运行结果如下：

DECLARE
*
ERROR 位于第 1 行:
ORA-01476: 除数为 0
ORA-06512: 在line 8

下例是使用系统预定义的异常处理，使用该处理后，程序运行时系统就不会提示出现错误。

DECLARE
  v_Number Number(2):=10;
  v_Zero Number(2):=0;
  v_Result Number(5);
BEGIN
  --用v_Number除以v_Zero，即10/0从而产生除零错误
  v_Result:=v_Number/v_Zero;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
  DBMS_OUTPUT.PUT_LINE('DIVIDE ZERO');
END;

系统运行结果为：

DIVIDE ZERO
PL/SQL 过程已成功完成。

此外，PL/SQL还有很多系统预定义异常，下面是部分比较常用的预定义异常。

◎ DUP_VAL_INDEX：违反唯一性；

◎ LOGIN_DENIED：登录失败，用户名和密码错误；

◎ NO_DATA_FOUND：没有发现数据；

◎ TOO_MANY_ROWS：数据行太多，即一个SELECT … INTO语句匹配多个数据行；

◎ ZERO_DIVIDE：除数为零错误；

◎ VALUE_ERROR：算法或转换错误；

◎ CASE_NOT_FOUND：在case语句中发现不匹配的When语句。

下例是一个转换错误处理的例子。

DECLARE
  v_Number Number(5);
  v_Result Char(5):='2d';
BEGIN
  --使用To_Number函数转换产生错误
  v_Number:=To_Number(v_Result);
EXCEPTION
  WHEN VALUE_ERROR THEN
  --错误处理，输出相关信息
  DBMS_OUTPUT.PUT_LINE('CONVERT TYPE ERROR!');
END;

下例是一个联合错误处理的例子。

DECLARE
  v_Result countries.country_name%TYPE;
BEGIN
  SELECT country_name INTO v_Result
  FROM countries WHERE country_name='Brazil';
  DBMS_OUTPUT.PUT_LINE('The country name is '||v_Result);
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('There has TOO_MANY_ROWS error');
  WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('There has NO_DATA_FOUND error');
END;

由于countries表中存在country_name='Brazil'的记录，所以，系统会显示如下：

The country name is Brazil

如果将SELECT子句中的where country_name='Brazil'删掉，则由于记录集不是一个值，这时系统就会产生数据行太多的错误，如下所示：

There has TOO_MANY_ROWS error

如果将SELECT子句中的country_name='Brazil' 改为country_name='Bra'，显然countries表中没有这条记录，这时系统就会产生没有发现记录的错误，结果如下：

There has NO_DATA_FOUND error

6.4.2 用户定义异常

用户可以通过自定义异常来处理错误的发生，调用异常处理需要使用Raise语句，异常处理的语法如下：

EXCEPTION
WHEN exception_name THEN
sequence_of_statements1;
WHEN THEN
sequence_of_statements2;
[WHEN OTHERS THEN
sequence_of_statements3;]
END;

每个异常处理部分都是由WHEN子句和相应的执行语句组成的。通过下例可以清楚地了解异常处理的执行过程。

DECLARE
  e_OverNumber EXCEPTION; -- 定义异常处理变量
  v_Sales_Number NUMBER(9); --当前的订单数
  v_Max_Sales_Number NUMBER(9) :=500; --定义最大允许的订单数
BEGIN
--在sales表中取出订单的数量
  SELECT count(*) INTO
v_Sales_Number FROM sales;
  --对比当前订单数和最大订单数，如果当前订单数超过最大订单数，就使用异常处理部分
  IF v_Max_Sales_Number < v_Sales_Number THEN
    /* 使用异常处理部分 */
    RAISE e_OverNumber;
  END IF;
EXCEPTION
  WHEN e_OverNumber THEN
        --处理异常，本例中是将异常结果输出，读者可以自己使用其他方式定义以下执行语句
  DBMS_OUTPUT.PUT_LINE('Current Sales Number is : ' || v_Sales_Number ||
    ' Max allowed is :' || v_Max_Sales_Number);
END;

可以同时使用几个WHEN子句捕捉几个异常情况，而且可以结合系统预定义的异常处理来操作，下面的代码演示了几个WHEN子句同时存在的情况。

DECLARE
e_EXCEPTION1 EXCEPTION;
e_EXCEPTION2 EXCEPTION;
e_EXCEPTION3 EXCEPTION;
BEGIN
… --当出现某种条件时使用e_EXCEPTION1
RAISE e_EXCEPTION1;
… --当出现某种条件时使用e_EXCEPTION2
RAISE e_EXCEPTION2;
… --当出现某种条件时使用e_EXCEPTION3
RAISE e_EXCEPTION3;
EXCEPTION
                WHEN e_EXCEPTION1 THEN
                … --出现e_EXCEPTION1执行某些语句
                WHEN e_EXCEPTION2 THEN
                … --出现e_EXCEPTION2执行某些语句
                WHEN e_EXCEPTION3 THEN
                … --出现e_EXCEPTION3执行某些语句
END;

此外，单个WHEN子句允许处理多个异常，也就是说，下面的形式是合法的。

EXCEPTION
WHEN e_EXCEPTION1 OR e_EXCEPTION3 THEN
… --出现e_EXCEPTION1或者e_EXCEPTION3执行某些语句
WHEN e_EXCEPTION2 THEN
… --出现e_EXCEPTION2执行某些语句
END;

上例中当出现异常e_EXCEPTION1或者e_EXCEPTION3时，其处理方法是相同的，这样是可以的。但是对于一个异常不允许多个WHEN子句来处理时，如下的形式是不合法的。

EXCEPTION
WHEN e_EXCEPTION1 THEN
… --出现e_EXCEPTION1执行某些语句
WHEN e_EXCEPTION2 THEN
… --出现e_EXCEPTION2执行某些语句
WHEN e_EXCEPTION1 OR e_EXCEPTION3 THEN
… --出现e_EXCEPTION1或者e_EXCEPTION3执行某些语句
END;

上例中对于e_EXCEPTION1和e_EXCEPTION2的处理有两种方法，因此，系统会认为是不合法的。

使用OTHERS异常处理：OTHERS异常处理用来处理那些不能有异常部分的其他WHEN子句处理的异常，它总是位于EXCEPTION语句的最后。

DECLARE
  v_Result NUMBER; --这里刻意采用数字型，使得异常处理时捕捉到错误
BEGIN
  SELECT country_name INTO v_Result
  FROM countries WHERE country_name='Brazil';
  DBMS_OUTPUT.PUT_LINE('The country name is '||v_Result);
EXCEPTION
  --异常处理时没有使用VALUE_ERROR异常捕捉
WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('There has TOO_MANY_ROWS error');
  WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('There has NO_DATA_FOUND error');
  WHEN OHTERS THEN
  DBMS_OUTPUT.PUT_LINE('UNKOWN error');
END;

其实OTHERS异常处理可以借助两个函数来说明捕捉到的异常类型，这两个函数分别是SQLCODE和SQLERRM。其中，SQLCODE用来说明当前错误的代码，如果是用户自定义的异常，则返回“1”；SQLERRM返回的是当前错误的信息。为了说明这两个函数的使用方法，我们将上例中WHEN OTHERS子句中的执行语句换成如下的语句：

DBMS_OUTPUT.PUT_LINE('The SQLCODE is : '|| SQLCODE);
DBMS_OUTPUT.PUT_LINE('The SQLERRM is : '|| SQLERRM);

系统则会输出如下信息：

The SQLCODE is : -6502
The SQLERRM is : ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误

6.5 本章小结

本章介绍了PL/SQL基础编程，内容涵盖了PL/SQL概述、PL/SQL编程、过程和函数、错误处理等。

上一章目录下一章