Oracle从新手到高手
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

6.1 PL/SQL结构

PL/SQL称为过程化SQL语言（Procedural Language/SQL），是一种程序语言，也是Oracle数据库对SQL语句的扩展，在其基础上增加了编程语言的特点。

PL/SQL语言是将数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作，实现复杂的功能或者计算的程序语言。

6.1.1 PL/SQL概述

由于SQL只是一种声明式语言，它没有流程控制，也不存在变量，只有表或者列，所以不能将某个SQL语句的执行结果传递给另外一个语句。为了实现该目的，用户不得不使用一条更复杂的语句。而且，SQL语句中更没有可以控制程序流程的IF或LOOP语句。

PL/SQL是过程化的结构查询语言（Procedural Language/Structured Query Language），它可以弥补SQL语句的不足。在PL/SQL中可以通过IF和LOOP语句控制程序的执行流程，并且可以定义变量，以便利用这些变量在语句之间传递数据信息。PL/SQL是Oracle的专用语言，它是对标准SQL语言的扩展，并且SQL语句可以嵌套在PL/SQL程序代码中，将SQL的数据处理能力和PL/SQL的过程处理能力结合在一起。

在Oracle数据库内置了PL/SQL处理引擎，常用的开发工具是Oracle附带的SQL*Plus。实际上，SQL*Plus只是用于将SQL语句或PL/SQL程序发送到数据库，并将处理后的结果显示在屏幕上的工具，并且用户也可以选择其他的工具运算SQL和PL/SQL程序。

PL/SQL程序的基本结构称为“块”，每个块都包含了PL/SQL语句和SQL语句。典型的PL/SQL块的结构如下：

      [declare
      declareation_statements]
      begin
      executable_statements
      [exception
      exception_handing_statements]
      end;

PL/SQL程序块中的每一条语句都必须以分号结束，SQL语句可以是多行的，但分号表示该语句的结束。一行中可以有多条SQL语句，但是它们之间必须以分号分隔。PL/SQL程序的注释用“--”表示。

从上面的语法规则中可以发现，一个PL/SQL程序块分为3部分：声明部分、执行部分和异常处理部分。声明部分是可选的，它由关键字DECLARE开始，到关键字BEGIN结束。在这部分可以声明一些PL/SQL变量、常量、游标和异常的定义等。当基本块结束后，声明部分中的所有内容均不复存在。在某个基本块中所声明的内容只能在该块中使用，其他的基本块不能使用。

执行部分以关键字BEGIN开始，可以以两种不同的方式结束。如果存在异常处理，则以关键字EXCEPTION结束。如果没有使用异常处理，则以关键字END结束。在执行部分包含多个PL/SQL语句和SQL语句。

在PL/SQL语句的执行过程中，由于各种原因会产生一些错误，这些错误的发生会导致程序被迫中断运行。这样PL/SQL程序开发人员就必须设法向用户发出一些有用的出错信息，或者在错误发生后采取某些措施进行纠正并继续运行程序。另外，所有相关数据库操作可能需要回退到异常产生之前的状况，这就需要在PL/SQL程序中提供异常处理能力。

PL/SQL的异常处理部分是以关键字EXCEPTION开始的，它的结束就是整个基本块的结束。每个异常均由WHEN语句开头，接着就是这种异常出现时相应的处理动作。

为了更好地理解PL/SQL程序，下面将通过一个具体的实例介绍PL/SQL的结构特点，使用户对PL/SQL程序的特点有一个感性的认识。

在上面的程序中，为了在服务器端显示执行结果，使用了SET SERVEROUTPUT ON命令。在DECLARE关键字表示的声明块中声明了3个变量—A、B和C，其数据类型为NUMBER，并且为变量A和B赋予了初始值3和4。

接下来以BEGIN关键字标识可执行块的开始，在可执行块中包含了两条PL/SQL语句。第一条语句计算A*B/(A+B)的值，并将计算值赋予变量C；随后的语句则使用DBMS_OUTPUT. PUT_LINE(C)；语句显示计算结果。EXCEPTION关键字表示异常处理块的开始。在这里捕获的异常是ZERO_DIVIDE，表示出现了除数为0的错误。出现错误时显示的错误信息为“除数不能为0！”

6.1.2 PL/SQL变量的声明

变量本质上是一种用名称进行标记的容器，它们可以包含或保存不同类型的数据。根据不同的数据类型，变量可以存储不同类型的数据，并且可以彼此通过变量名进行区分。

可以使用下面两种语法声明PL/SQL变量：

      variable_name data_type [ [NOT NULL]:=default_value_expression];
      variable_name data_type [ [NOT NULL] DEFAULT default_value_expression];

变量名variable_name可以是任何合法的PL/SQL标识符，合法的PL/SQL标识符必须满足如下条件。

※ 长度不能超过30个字符，而且中间不能有空格。

※ 由字母、0~9的数字、下画线“_”、美元符号“$”以及符号“#”组成。

※ 必须以字母开始。

※ 不能使用PL/SQL或SQL中的关键字。例如，BEGIN、END不能作为变量名，因为它在PL/SQL程序中有特殊的意义（表示块的开始和结束）。

可以在SQL*Plus使用如下命令获得SQL和PL/SQL中的关键字。

     help reserved words

变量类型DATA_TYPE必须是合法的SQL或PL/SQL数据类型，变量的类型决定了其中存储的数据类型。如果变量只能存储一个单独的值，则该变量称为“标量变量”。如果变量中可以存储多个值（如表中一行记录），则该变量称为“复合类型的变量”。

标量变量所使用的数据类型包括字符、数字、日期和布尔类型等，标量变量所使用的数据类型见下表所示。

NOT NULL表示变量必须是非空的，需要指定初始值。当变量被创建后，可以以值表达式的方式对其赋初始值。在声明变量时，还可以使用DEFAULT关键字指定变量的默认值，这样如果未向变量赋值时，变量的值就是设置的默认值。

下面介绍几种常用的数据类型，这些常用的数据类型包括NUMBER、VARCHAR2、DATE和BOOLEAN等。

VARCHAR2是一种变长的数据类型。在PL/SQL中，该类型的最大长度为32767。VARCHAR2类型变量的语法形式如下所示。

     char_variable varchar2(max_length);

其中，MAX_LENGTH参数是正整数，表示该变量最大可以容纳的字符数。

NUMBER数据类型表示所有的数字数据，声明NUMBER数据类型变量的格式如下所示。

     number_variable number(length,decimal_places);

其中，LENGTH参数的取值范围为1~38，DECIMAL_PLACES参数用于指定数字的小数点后面的位数。

DATE数据类型用于存储日期数据和时间数据，声明该类型变量的格式如下所示。

     date_variable date;

BOOLEAN数据类型用于声明布尔值，该类型的变量只能存储TRUE、FALSE或NULL值。

下面的代码在程序块的定义部分声明了4种类型的变量。

这些标量变量只可以在该程序块中使用，并且在BEGIN部分开始执行前，每个变量都将包含一个NULL值。

6.1.3 %TYPE变量

在声明变量时，除了可以使用Oracle规定的数据类型外，还可以使用%TYPE关键字定义变量类型。%TYPE关键字的含义是声明一个与指定列名称相同的数据类型。例如，下面的语句声明了一个与EMP表中ENAME列完全相同的数据类型。

      declare
      var_name emp.ename%type;

如果ENAME列的数据类型为VARCHAR2(40)，那么变量VAR_NAME的数据类型就是VARCHAR2(40)。

下面的示例演示了如何使用%TYPE类型的变量从数据库中检索数据。

在上面的PL/SQL程序中，主要使用了SELECT语句从EMP中检索数据。该SELECT语句与前面使用的SQL查询语句非常相似，唯一不同之处在于它多了一个INTO子句。INTO子句跟在SELECT子句后，表示从数据库检索的数值将保存在哪个变量中。

提示：

需要注意的是，因为定义的变量只可以存储一个单独的值，所以使用WHERE子句限定返回结果集中只包含一行数据。如果返回的结果集中包含多行数据，则由于实际返回的行数超出请求的行数而产生错误。

使用%TYPE定义变量有两个好处：首先，用户不必查看表中各个列的数据类型，即可确保所定义的变量能够存储检索的数据；其次，如果对表的结构进行修改（例如，改变某一个列的数据类型），那么用户不必考虑对所定义的变量进行更改，而%TYPE类型的变量会自动调整。使用%TYPE类型的变量也有一个缺点，在程序的执行过程中，系统必须查看数据字典，以确定变量的数据类型，因此它会对程序的性能产生影响。

提示：

对于刚接触PL/SQL程序的用户而言，经常犯的一个错误就是遗漏赋值符号“：=”中的冒号，这就是PL/SQL程序与其他程序中赋值符号的区别。

6.1.4 复合变量

很多结构化程序设计语言都提供了记录类型的数据类型，在PL/SQL中，也支持将多个基本数据类型捆绑在一起的记录数据类型，即复合变量。相对于标量变量，复合变量一次可以存储多个数值。

对于复合变量，用户可以根据需要定义其结构，可以使用由系统自动决定变量的结构。下面列出了PL/SQL提供的几种常用的复合类型。

※ 自定义记录类型：在记录类型的复合变量中可以存储多个标量值，它的结构通常与数据库行相似。

※ %ROWTYPE类型：该类型的复合变量可以根据指定的表结构，由系统决定其结构。

1. 自定义记录类型

使用自定义记录数据类型的变量可以存储由多个列值组成的一行数据。当使用记录类型的变量时，首先需要定义记录的结构，然后才可以声明记录类型的变量。定义记录数据类型时必须使用TYPE语句，在这个语句中指出将在记录中包含的字段及其数据类型。使用TYPE语句定义记录数据类型的语法形式如下。

      type record_name is record(
      field1_name  data_type  [not null] [:=default_value],
      ……
      fieldn_name data_type [not null] [:=default_value]);

在上面的语句中，RECORD_NAME为自定义的记录数据类型名，例如数值数据类型的名称为NUMBER。FILED1_NAME为记录数据类型中的字段名，DATA_TYPE为该字段的数据类型，从这里可以看出，字段的声明与标量变量的声明类似。

下面的程序代码定义了名为EMPLOYEE_TYPE的记录类型，该记录类型由整数型的NO_ NUMBER、字符型的NAME_STRING和整数的SAL_NUMBER基本类型变量组成，EMPLOYEE是该类型的变量，引用记录型变量的方法是“记录变量名.字段名”。

程序的执行部分是从SCOTT.EMP数据表中提取EMPNO列为7369的记录，并将数据存放在复合变量EMPLOYEE中，然后输出复合变量的各个字段值，实际上就是数据表中相应记录的值。

提示：

SELECT语句检索的列是以固定的顺序赋予记录变量中各个字段。即查询的第一个列值赋予记录变量中第一个字段，第二个列值赋予记录变量的第二个字段。因此，在SELECT语句查询的列值数量和顺序中，用户必须与定义记录类型中定义的字段数量和顺序相匹配。

如果两个记录变量的数据类型相同，那么可以将记录变量中的值直接赋予另一个记录变量。如果记录数据类型不同，那么无论记录类型的结构是否相同，都不可以直接将一个记录变量赋予另一个记录变量。

2. %ROWTYPE类型变量

与%TYPE类型的变量和自定义记录类型变量相比，%ROWTYPE类型的变量结合了这两者的特点，它可以根据数据表中行的结构定义数据类型，用于存储从数据表中检索到的一行数据。下面的示例，使用%ROWTYPE类型的变量存储查询的数据。

在上面的程序中定义了一个%ROWTYPE类型的变量，该变量的结构与EMP表的结构完全相同。因此，可以将检索到的一行数据保存到该类型的变量中，并且根据表中列的名称引用对应的数值。

上一章目录下一章