流式数据库_AntDB数据库关闭SELinux的步骤

概述

本章介绍了存储过程语言 —— SPL，SPL 是一种高效，面向过程的编程语言。用来为 AntDB 编写用户自定义的存储过程，函数，触发器和包。

下面是 SPL 语言提供的一些对于开发应用程序有用的特性：

作为 SQL 语言的补充，SPL 增加完全面向过程的编程功能特性。
为 AntDB 提供了一种单一通用的编程语言来创建存储过程，函数，触发器和包。
提升了代码的复用率。
容易使用。

本章首先介绍了 SPL 程序的基本组成部分，然后提供了关于 SPL 程序的结构以及如何创建过程或者函数的概要。关于如何利用 SPL 创建触发器的内容与本章的内容不同，需要一个单独的章节进行讨论。

在本章的其它章节中深入研究了 SPL 语言的细节并提供了相关应用程序的示例。

SPL 的基本组成部分

本节讨论了开发一个 SPL 程序所需要最基本的组成部分。

字符集

SPL 程序使用到下面这些字符：

大写字母 A 到 Z，小写字母 a 到 z。
数字 0 到 9。
符号 ( ) + - * / < > = ! ~ ^ ; : . ' @ % , " # $ & _ | { } ? [ ]。
Tab 键，空格字符和回车键。

SPL 语言所包含的标识符，表达式，语句，控制流程结构使用上面这些字符组成的。

注意：SPL 程序可以操作的数据是由数据库编码所支持的字符集决定。

大小写区分

从下面这个示例中可以看到：在SPL程序中使用的关键字和用户定义的标识符是不区分大小写的。

示例：

DBMS_OUTPUT.PUT_LINE('Hello World'); 可以等同于下面这些语句：

dbms_output.put_line('Hello World');
Dbms_Output.Put_Line('Hello World');
DBMS_output.Put_line('Hello World');

但是，字符和字符串常量以及任何从 AntDB 数据库或外部数据源所取得数据是区分大小写的。

语句DBMS_OUTPUT.PUT_LINE('Hello World!')；产生了如下的输出：

Hello World!

但是语句 DBMS_OUTPUT.PUT_LINE('HELLO WORLD!')；的输出着这样的：

HELLO WORLD!

标识符

标识符是用户定义的名称，用来标识组成SPL程序的各个部分，包括变量、游标、标签、程序和参数。

在 SPL 中有效标识符的语法规则与在 SQL 语言中的规则是一样的，具体请参见在章节 2.1.2 中关于 SQL 语句标识符的讨论。

一个标识符不能定义为与 SPL 语言和 SQL 语言中关键字相同名字。

下面这些示例是一些正确定义的标识符：

x
last___name
a_$_Sign
Many$$$$$$$$signs_____
THIS_IS_AN_EXTREMELY_LONG_NAME
A1

限定词

限定词是一个指定对象的所有者或者是使用这个对象时所在环境的名称。所谓对象就是属于限定词实体的名称。通常来说，一个限定词拥有的实体就是一个限定词后面跟随一个'.'，后面再跟着这个限定词所拥有对象的名称。要注意的是'.'的前后都没有空格。

语法：

qualifier. [ qualifier. ]... object

qualifier 是对象的所有者，object 就是属于限定词的实体的名称。有一种情况是前一个限定词所拥有的实体，会被这个实体之后限定词和对象标识为限定词。

几乎所有的标识符都可以被限定。一个标识符是否可以被限定取决于标识符所表示的含义和使用这个标识符的环境。

下面是一些限定词的示例：

由存储过程和函数所属的模式来限定的名称 -例如，schema_name.procedure_name(...)
由触发器所属的模式来限定的名称 - 例如，schema_name.trigger_name
由列所属的数据表来限定的列名 - 例如，emp.empno
由数据表和模式所限定的列名 - 例如，public.emp.empno

一条通用的规则是，无论在一个 SPL 语句语法中的任何地方出现名称，它的被限定名称也都会被用到。

通常来说当像名称相同且属于两个不同的模式的两个存储过程在一个程序中被调用，或者在一个程序中 SPL 变量和一个数据表的列相同等这样的标识符命名冲突的情况出现时，会用到被限定的名称。

在实际应用中建议应该尽量避免使用限定词。在本章中，可以使用下面这些书写约定来避免命名冲突：

在 SPL 程序声明的所有的变量以 v_ 的前缀开头 - 例如， v_empno
在存储过程过程或者函数中定义的参数以 p_ 的前缀开头 - 例如，p_empno
列名和表名不用特定的前缀开头 - 例如，在数据表 emp 中的列名 empno

常量

在 SPL 程序中可以使用常量（或称为直接变量）表示不同数据类型的值。例如 - 数值，字符串，日期等。常量一般可以是下面这些类型：

数值（包括整型和实数型）
字符和字符串
日期/时间

SPL 程序

SPL 是一种面向过程，以代码块为结构的编程语言。可以使用 SPL 创建四种不同类型的程序，分别是存储过程，函数，触发器和包。关于存储过程和函数的内容会在本节中后面的部分详细讨论。

SPL 块结构

对于一个 SPL 程序来说，无论是存储过程，函数，它的代码块结构都是相同的。但是在 AntDB 中，不支持直接执行代码块，必须把代码块放入一个函数或者存储过程中执行。一个代码块由三个部分组成 —— 一个可选的变量声明部分，一个必选的命令执行部分和一个可选的捕获异常部分。一个最简单的代码块由命令执行部分构成，包含一条或多条写在关键字 BEGIN 和 END 中间的 SPL 语句。

代码块中可以有一个可选的变量声明部分，用来声明那些在执行和捕获异常部分中使用到的变量，游标和类型。声明部分是在执行部分中 BEGIN 关键字前面出现。声明部分以关键词 DECLARE 开始，这取决于当时使用代码块的情况。

最后，在 BEGIN-END 块里面可以有一个可选的，用于捕获异常的部分。捕获异常的部分以关键字 EXCEPTION 开始，在代码块的结束位置结束。如果在代码块中的一条语句产生了一个异常，程序控制流程就会转到捕获异常的部分，在这个部分中是否对异常进行处理取决于异常和异常处理流程的内容。

下面就是一个代码块的通用结构：

[ [ DECLARE ]

declarations ]

BEGIN

statements

[ EXCEPTION

WHEN exception_condition THEN

statements [, ...] ]

END;

declarations 是指在代码块中的一个或多个变量，游标或类型的声明。每一个声明必须以一个分号结束。关键字 DECLARE 的使用取决于在代码块所出现的环境。

statements 是一条或多条的 SPL 语句。每一条语句必须以一个分号结束。以关键词 END 标识的块结束的位置也要以分号结束。

代码块中出现的关键字 EXCEPTION 表示捕获异常部分的开始。exception_condition 是指用来测试一种或多种类型异常的条件表达式。如果一个产生的异常与 exception_condition 中一个异常类型相匹配，那么将执行跟随在 WHEN exception_condition 子句后面的 statements。这里可以有一个或多个 WHEN exception_condition 子句，每一个子句后面都会跟随相应的语句。

注意：一个 BEGIN/END 块本身就是一个语句，因此代码块是可以嵌套的。捕获异常部分可以包含嵌套的代码块。

下面是一个示例，这是一个最简单的代码块。在这个代码块中的执行部分包含了一个 NULL 语句，NULL 语句是一个可执行的语句中，不做任何工作：

BEGIN
NULL;
END;

下面这个代码块包含了变量声明和命令执行这两个部分：

DECLARE
v_numerator	NUMBER(2);
v_denominator	NUMBER(2);
v_result	NUMBER(5,2);
BEGIN
v_numerator := 75;
v_denominator := 14;
v_result := v_numerator / v_denominator;
DBMS_OUTPUT.PUT_LINE(v_numerator || ' divided by ' || v_denominator ||
' is ' || v_result);
END;

在上面的示例中，首先将3个数值变量声明为 NUMBER 数据类型，在执行部分中对两个变量分配了数值，然后使其中一个数值被另外一个数值整除。所得的结果存放在第三个变量中，最后使用第三个变量用来显示结果。

在下面的示例中显示了一个代码块，它包含了变量声明，命令执行和捕获异常这三个部分：

DECLARE
v_numerator	 NUMBER(2);
v_denominator	 NUMBER(2);
v_result	 NUMBER(5,2);
BEGIN
v_numerator := 75;
v_denominator := 0;
v_result := v_numerator / v_denominator;
DBMS_OUTPUT.PUT_LINE(v_numerator || ' divided by ' || v_denominator ||
' is ' || v_result);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An exception occurred');
END;

下面的这个输出结果显示了当代码块中的执行了除零操作时，在捕获异常部分中执行相关语句的结果：

An exception occurred

存储过程概述

存储过程是一种 SPL 程序，可以做为一个单独的 SPL 应用来调用。当调用存储过程时，它可以用输入参数的形式从调用者接收数值，并且可以用输出参数的方式向调用者返回最终执行结果。在 AntDB 中，要使用 PLSQL 语言必须先打开 plsql_mode。

创建存储过程

CREATE PROCEDURE 命令定义并且命名了将要存放在数据库中的存储过程。

语法：

CREATE [ OR REPLACE ] PROCEDURE name [ (parameters) ]

{ IS | AS }

[ declarations ]

BEGIN

statements

END [ name ];

name 是存储过程的标识符，如果定义了 [ OR REPLACE ] 并且在模式中已经存在了有相同名字的存储过程，那么新创建的存储过程将替代已存在的存储过程。反之，新创建的存储过程将无法替代在同一模式下已存在的存储过程。parameters 是一个正式参数的列表。declarations 是变量，游标或者类型的声明。statements 是 SPL 应用程序所使用的语句。BEGIN-END 块包含一个捕获异常的部分。

下面是一个简单的存储过程，它不带有任何参数。注意，在 AntDB 中，即使不带任何参数，也要在函数或者存储过程名后加一个空括号，这一点与 Oracle 不兼容：

\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE simple_procedure()
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('That''s all folks!');
END;

通过在 AntDB 中编写代码，把这个存储过程存放到了数据库中。

关于创建存储过程的详细信息，请参见 CREATE PROCEDURE 命令。

调用存储过程

通过指定存储过程的名称，及其后面的参数，最后加上一个分号。就可以使用一个 SPL 应用调用另外一个存储过程了。

语法：

name [ (parameters) ];

name 是存储过程的标识，parameters 指的是实际参数的列表。

注意：如果存储过程没有实际参数传递，那么运行该存储过程的时候不应该带有括号。

下面是一个从匿名代码块中调用存储过程的示例：

BEGIN
simple_procedure;
END;

That's all folks!

注意：每种应用程序都有它们自己的方式来调用存储过程。例如：在 Java 应用程序中，对存储过程的调用是通过 JDBC 编程接口来实现的。

删除存储过程

可以使用 DROP PROCEDURE 从数据库中删除一个存储过程。

语法：

DROP PROCEDURE name;

name 是将要被删除掉存储过程的名称

在下面这个示例中，使用 DROP PROCEDURE 命令删除掉一个先前已创建的存储过程：

DROP PROCEDURE simple_procedure;

更多的信息参见 DROP PROCEDURE 命令。

函数概述

函数是一种以表达式的方式调用的 SPL 程序。当函数运行完毕后，它将返回一个值代替在表达式中调用函数的地方。函数可以以输入参数的形式从调用该函数的程序接收数值。函数除了可以自己返回数值外，还可以以输出参数的方式向函数的调用者返回数值。但是应该注意，在实际编写程序的过程中，不提倡在函数中使用输出参数。

创建函数

CREATE FUNCTION 命令定义和命名了一个将要存放在数据库中的函数。

语法：

CREATE [ OR REPLACE ] FUNCTION name [ (parameters) ]

RETURN data_type

{ IS | AS }

[ declarations ]

BEGIN

statements

END [ name ];

name 就是函数的标识符。如果定义了 [ OR REPLACE ] 并且在模式中存在了有相同名字的函数，那么新创建的函数将替代已存在的同名函数。反之，新创建的函数则无法替代在同一模式下已存在的函数。parameters 是一个正式参数的列表。data_type 是函数返回值的类型。

declarations 就是变量，游标或者类型的声明。statements 是 SPL 应用程序所使用的语句。

BEGIN-END 块包含一个捕获异常的部分。

下面是一个函数的示例，这个函数很简单，它不带任何参数：

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION simple_function()
RETURN VARCHAR2
IS
BEGIN
RETURN 'That''s All Folks!';
END simple_function;
/

下面是另外一个函数的示例，这个函数带有两个输入参数。关于参数的详细信息在本章节的后面将会讨论到。

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION emp_comp (
p_sal      NUMBER,
p_comm   NUMBER
) RETURN NUMBER
IS
BEGIN
RETURN (p_sal + NVL(p_comm, 0)) * 24;
END emp_comp;
/

详细信息请参见 CREATE FUNCTION 命令。

调用函数

在 SPL 语句中表达式可以出现的地方，都可以使用函数。可以通过指定函数名称和名称后面括号中的参数，来调用一个函数。

name [ (parameters) ]

name 是函数的名称。parameters 是实际参数的列表。

注意：如果没有实际参数，函数可以以空参数列表的方式调用，函数后面的括号不能省略。

下面的示例显示如何调用函数：

SELECT simple_function();

  SIMPLE_FUNCTION  
-------------------
 That's All Folks!
(1 row)

函数通常是在如下所示的 SQL 语句中调用（emp 表格定义见【示例参考表格】）：

SELECT empno "EMPNO", ename "ENAME", sal "SAL", comm "COMM",
emp_comp(sal, comm) "YEARLY COMPENSATION" FROM emp;

 EMPNO | ENAME  | SAL  | COMM | YEARLY COMPENSATION 
-------+--------+------+------+----------------------
  7369 | SMITH  |  800 |      |               19200
  7499 | ALLEN  | 1600 |  300 |               45600
  7521 | WARD   | 1250 |  500 |               42000
  7566 | JONES  | 2975 |      |               71400
  7654 | MARTIN | 1250 | 1400 |               63600
  7698 | BLAKE  | 2850 |      |               68400
  7782 | CLARK  | 2450 |      |               58800
  7788 | SCOTT  | 3000 |      |               72000
  7839 | KING   | 5000 |      |              120000
  7844 | TURNER | 1500 |    0 |               36000
  7876 | ADAMS  | 1100 |      |               26400
  7900 | JAMES  |  950 |      |               22800
  7902 | FORD   | 3000 |      |               72000
  7934 | MILLER | 1300 |      |               31200
(14 rows)

删除函数

DROP FUNCTION 命令可以从数据库中删除一个函数。

语法：

DROP FUNCTION name;

name 是要删除函数的名称。

在下面这个示例中，删除了一个已创建的函数：

DROP FUNCTION simple_function;

详细信息参见 DROP FUNCTION 命令。

存储过程和函数的参数

在使用存储过程和函数的过程中，很重要的一个功能就是应用程序向存储过程或者函数传递数值，并且从存储过程和函数中接收返回的数据。这个功能可以通过使用参数来完成。

参数是在存储过程或函数后面的括号中声明的。在存储过程或函数中定义的参数称为形式参数。当调用存储过程或者函数的时候，应用程序向被调用的函数/存储过程提供实际数值，并且通过变量取得被调用的函数/存储过程的返回结果。当调用存储过程或函数时，由调用它们的应用程序所提供的数值和变量称为实际参数。

语法：

(name [ IN | OUT | IN OUT ] data_type [ DEFAULT value ])

name 是分配给形式参数的标识符。如果指定了 IN 子句，参数就会接收输入数据供存储过程或函数使用。可以使用缺省值来初始化输入参数。如果指定了 OUT 子句，那么参数将存储过程或函数中的执行结果返回到调用它们的应用程序中。如果指定了 IN OUT 子句，那么参数可以同时当作输入和输出参数。如果省略对 IN，OUT 和 IN OUT 的指定，那么参数以缺省方式被指定为输入参数。参数的用途由 IN，OUT， IN OUT 来确定。 data_type 定义了参数的数据类型。如果在调用过程中没有为实际参数指定数值，那么将把参数 value 的值作为缺省值分配给输入参数。

下面是一个存储过程的示例，这个存储过程带有参数（emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE emp_query (
p_deptno	IN	NUMBER,
p_empno	IN OUT NUMBER,
p_ename	IN OUT VARCHAR2,
p_job	OUT	VARCHAR2,
p_hiredate	OUT	DATE,
p_sal	OUT	NUMBER
)
IS
BEGIN
SELECT empno, ename, job, hiredate, sal
INTO p_empno, p_ename, p_job, p_hiredate, p_sal
FROM emp
WHERE deptno = p_deptno
AND (empno = p_empno
OR	ename = UPPER(p_ename));
END;
/

在这个示例中， p_deptno 是输入形式参数，p_empno 和 p_ename 是 IN OUT 形式参数。p_job， p_hiredate 和 p_sal 是输出形式参数。

注意：在前面的这个示例中，没有指定 VARCHAR2 类型参数的最大长度和 NUMBER 类型参数的数值范围和小数位数。在实际的参数声明中，不允许指定参数的长度，精度，和数值范围或者其他限定条件。这些约束条件实际上是在调用存储过程或者函数时，从用到的实际参数上自动继承的。

其它应用程序调用可以存储过程 emp_query，向它传送实际参数。下面是一个示例，演示了如何通过调用存储过程 emp_query。

如下所示，第 4、第 5、第 6 个参数是 OUT 类型，调用的时候不需要传入，参数只写前 3 个，这一点和 Oracle 不兼容。

SELECT emp_query(30, 7900, ‘’);

注意，在 AntDB 中，调用函数或者存储过程的时候，out 类型的参数不能直接作为函数的入参。在上面这个示例中，v_deptno， v_empno， v_ename 是IN类型或者IN OUT类型参数，而 v_job， v_hiredate 和 v_sal 是 OUT 类型参数。因此，调用函数的时候，只传递前三个参数。这一点和 Oracle 不兼容。

输出结果显示如下：

                  EMP_QUERY                   
-----------------------------------------------
 (7900,JAMES,CLERK,"1981-12-03 00:00:00",950)
(1 row)

参数模式

正如前面所讨论的，一个参数的模式可以是 IN，OUT 或者 IN OUT 这三种模式中的其中一种。下面这些形式参数的特性取决于它的模式：

当调用存储过程或函数时如何对形参进行初始化。
被调用的存储过程或函数是否可以修改形式参数。
实际参数如何从调用程序中将值传递到被调用程序中。
当被调用的程序出现一个未处理的异常时如何处理形式参数。

在下面的表中根据参数的模式汇总了各个参数的行为：

模式属性	输入参数	输出参数	输入输出参数
形式参数被初始化为	实际参数值	实际参数值	实际参数值
形式参数是否可被被调用的程序修改	否	是	是
在被调用程序正常结束后实际参数包含的内容	在调用之前的原有的实际参数的值	上一次的形式参数值	上一次的形式参数值
在被调用的程序遇到一个被处理异常后实际参数包含的内容	在调用之前的原有的实际参数的值	上一次的形式参数值	上一次的形式参数值
在被调用的程序遇到一个没有被处理异常后实际参数包含的内容	在调用之前的原有的实际参数的值	在调用之前的原有的实际参数的值	在调用之前的原有的实际参数的值

正如上面的表所示，一个IN形式参数只有在调用的时候才被初始化为实际参数，除非它被明确使用缺省值初始化，IN参数可以在被调用的程序中引用，被调用的程序也可以不分配新的值给输入参数。当被调用的应用运行结束，返回到调用程序时，实际参数包含着和应用被调用前相同的值。

OUT 形式参数只有在调用时才被初始化为实际参数。被调用的程序可以引用并且分配一个新的值给形式参数。如果被调用的程序正常结束，没有产生异常。那么实际参数的值就是在最后一次分配给形式参数的值。如果遇到一个被处理的异常，实际参数的值就是最近一次分配给形式参数的值。

如果一个无法被处理的异常产生，实际参数的值仍然是在调用前被分配的值。

和IN参数一样，一个 IN OUT 形式参数在被调用的时候初始化为实际参数。和一个 OUT 参数一样，一个 IN OUT 形参数可以由被调用的程序修改，并且如果被调用的应用正常结束，没有异常，先前被设定形式参数的值会被传递到调用程序的实际参数中。如果遇到一个可处理的异常，实际参数的值是上一次分配给形式参数的值。如果一个无法处理的异常产生，实际参数的值仍然是在调用前被分配的值。

程序的安全性

涉及到哪些用户可以执行 SPL 程序，正在执行 SPL 程序的用户可以访问哪些数据库对象等等这些安全问题是以下这些因素决定的：

用来执行程序的权限。
在一个应用程序需要访问的数据库对象（包括 SPL 应用程序）上已经授予的权限。
应用程序是否以定义者或调用者权限来定义的。

下面将讨论这些问题。

EXECUTE 权限

只有当以下条件中的任何一条为真的时候，一个 SPL 应用程序（包括函数，过程，或者是包）才能够开始执行：

当前调用 SPL 程序的用户为超级用户。
当前调用 SPL 程序的用户已经授予了 SPL 程序的执行权限。
当前调用 SPL 程序的用户通过成为拥有 SPL 程序执行权限的组的成员，继承了相关的权限。
EXECUTE 权限已经授予了 PUBLIC 组。

当 SPL 程序在 AntDB 创建时，EXECUTE 权限在缺省状态下自动被授予了 PUBLIC 组，因此任何用户都可以马上执行该程序。

通过运行 REVOKE EXECUTE 命令，可以收回这个缺省的权限。更详细的信息参见 REVOKE 命令。

下面是一个关于这个命令的示例：

REVOKE EXECUTE ON PROCEDURE list_emp FROM PUBLIC;

可以把 SPL 程序上的 EXECUTE 权限明确地授予指定的用户或者组：

GRANT EXECUTE ON PROCEDURE list_emp TO john;

现在，用户 john 能够执行 list_emp 程序，但是那些没有符合本节开头所列出来条件的用户不能执行这个程序。

当程序开始执行后试图在任何一种数据库对象上执行下列这些操作时，需要进行安全方面的权限检查。

读或者修改表或者视图的数据。
创建，修改或删除数据库对象，诸如表，视图，索引或者序列等等。
从序列中获取当前或者下一个数值。
调用另外一个程序（函数，存储过程或包）。

通过对数据库对象上权限上限制，可以很好的控制上面的这些操作。

需要注意的是一个数据库有可能拥有多个具有相同名称和类型的对象，在数据库中这些对象属于不同的模式。在这种情况下，哪个对象将会被 SPL 应用程序引用？这就是下一节要讨论的内容了。

数据对象命名解析

在 SPL 程序中的数据库对象既可以被限定名称所引用也可以被非限定名称所引用。一个限定名称的构成形式为 schema.name，其中shcema 是带有标识名“name”的数据库对象所属模式的名称。非限定名称没有“schema.”部分。当对限定名称进行引用时，不会出现对象引用不明确的情况，在指定的模式中，对象要么存在，要么不存在。

如果使用非限定名称来定位对象，就会要求使用当前用户的搜索路径。当一个用户变成了会话的当前用户时，会使用缺省的搜索路径与这个用户相关联。搜索路径由模式列表组成，在定位对带有非限定名称的数据对象引用时的搜索顺序为从左到右。如果在搜索路径的所有的模式中没有找到相应的对象，那么就会认为这个对象不存在。在 SQL 中可以使用 SHOW search_path 命令来显示缺省搜索路径。

SHOW search_path;
search_path
----------------------
$user,public,sys,dbo
(1 row)

在上面显示的搜索路径中，$user 是指向当前会话用户的普通占位符，所以如果上面的会话中的当前用户 AntDB。那么在下列模式中以如下顺序搜索一个带有非限定名称的数据库对象 - 首先是 AntDB，然后 public，随后 sys，最后是 dbo。

当在搜索路径上能够解析到一个非限定的数据库对象名称时，就能够确认当前用户在指定的对象上是否具有执行相应操作的权限。

注意：搜索路径这个概念与 Oracle 不兼容。对于一个非限定的引用，Oracle 只在当前用户的模式中搜索相关命名的数据库对象。还有一点很重要的是，在 Oracle 中，一个用户和其所属的模式属于同一个实体，然而在 AntDB，用户和模式属于两个不同的对象。

数据库对象权限

当开始执行 SPL 程序时，在访问在程序中出现的数据库对象之前，都要先执行权限检查操作，来确保当前用户在所引用的对象上具有相应操作权限。GRANT 和 REVOKE 命令分别用于在数据库对象上授予和取消相关的权限。如果当前用户试图访问一个未授权的数据对象，那么程序将产生一个异常。

变量声明

SPL 是一种以代码块为结构的编程语言。在代码块中出现的第一个部分是声明部分。声明部分包含着变量、游标、以及其他可以在这个块中包含的 SPL 语句的类型的定义。在这个章节中，将更详细讨论变量的声明。

声明一个变量

一般来说，所有在代码块中使用到的变量都必须在声明部分进行声明。变量声明是由变量名称和它的数据类型所组成（参见章节 2.2 关于数据类型的讨论）。在变量声明的同时可以使用一个缺省值对变量进行初始化。

语法：

name type [ { := | DEFAULT } { expression | NULL } ];

name 是分配给变量的标识符，type 是分配给变量的数据类型。如果出现 [ := expression ]，那么当流程进入代码块的时候，就会为变量分配一个初始化值，反之，变量就会自动初始化为 SQL 语言中的空值。

每一次进入代码块，都会计算出缺省值。例如，当把 SYSDATE 分配给类型为 DATE 的变量时，这个变量的值就是当前时间，而不是过程或者函数被预编译完成后的时间值。

下面的存储过程演示了一些变量声明，这些变量声明用到的缺省值由字符串和数值表达式组成：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_salary_rpt (p_deptno NUMBER)
IS
todays_date DATE := SYSDATE;
rpt_title VARCHAR2(60) := 'Report For Department # ' || p_deptno|| ' on ' || todays_date;
base_sal INTEGER := 35525;
base_comm_rate NUMBER := 1.33333;
base_annual NUMBER := ROUND(base_sal * base_comm_rate, 2);
BEGIN
DBMS_OUTPUT.PUT_LINE(rpt_title);
DBMS_OUTPUT.PUT_LINE('Base Annual Salary: ' || base_annual);
END;
/
\set PLSQL_MODE off

上面这个存储过程的输出结果如下，它显示了在变量声明时使用到的缺省值已经分配给了变量：

antdb=# SELECT dept_salary_rpt(20);
NOTICE:  Report For Department # 20 ON 2020-09-01 10:31:00
NOTICE:  Base Annual Salary: 47366.55
 DEPT_SALARY_RPT
-----------------

(1 row)

在变量声明中使用 %TYPE

通常在 SPL 程序中声明的变量用于存放从数据表中取得的数据。为了确保列值和 SPL 变量的兼容性，这两者的数据类型应该是相同的。然而，表定义发生改变的情况经常会发生。如果表中列的数据类型发生改变，那么与之相对应 SPL 程序中变量的类型也应该改变为相应的类型。

可以在声明变量时将类型指定为列属性加上 %TYPE，而不是在编写程序的时候将变量声明为数据列的属性。一个在“.”后面被限定的列名或者是已经声明过的变量名称必须指定为 %TYPE的前缀。所声明变量的数据类型是以列或者变量为前缀的 %TYPE数据类型。

注意：%TYPE 属性也可以用作形式参数的声明。

语法：

name { { table | view }.column | variable }%TYPE;

name 是分配给所声明变量的标识，column 是在数据表或者视图中列的名称，variable 是使用参数 name 标识变量前已声明的变量。

**注意：**变量不继承列的其他属性，例如在列上定义的 NOT NULL 子句或者 DEFAULT 子句。

在下面的示例中的存储过程使用雇员编号查询并显示了表 emp 中雇员的相关数据，雇员所属部门中所有雇员的平均工资，并且比较了所选定雇员的薪水和部门的平均薪水：

准备测试表和测试数据：

CREATE TABLE emp (
	empno NUMBER(4),
	ename VARCHAR2(10),
	job VARCHAR2(9),
	mgr NUMBER(4),
	hiredate DATE,
	sal NUMBER(7,2),
	comm NUMBER(7,2),
	deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,20);

创建存储过程：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
IS
	v_ename VARCHAR2(10);
	v_job VARCHAR2(9);
	v_hiredate DATE;
	v_sal NUMBER(7,2);
	v_deptno NUMBER(2);
	v_avgsal NUMBER(7,2);
BEGIN
	SELECT ename, job, hiredate, sal, deptno
		INTO v_ename, v_job, v_hiredate, v_sal, v_deptno
		FROM emp WHERE empno = p_empno;
	DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
	DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
	DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
	DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate);
	DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
	DBMS_OUTPUT.PUT_LINE('Dept # : ' || v_deptno);

	SELECT AVG(sal) INTO v_avgsal
		FROM emp WHERE deptno = v_deptno;
	IF v_sal > v_avgsal THEN
		DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '|| 'department average of ' || v_avgsal);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '|| 'department average of ' || v_avgsal);
END IF;
END;
/
\set PLSQL_MODE off

下面是另外一种方法，在存储过程的声明部分以隐含状态指定表emp的数据类型：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
IS 
	v_ename emp.ename%TYPE;
	v_job emp.job%TYPE;
	v_hiredate emp.hiredate%TYPE;
	v_sal emp.sal%TYPE;
	v_deptno emp.deptno%TYPE;
	v_avgsal v_sal%TYPE;
BEGIN
	SELECT ename, job, hiredate, sal, deptno
		INTO v_ename, v_job, v_hiredate, v_sal, v_deptno
		FROM emp WHERE empno = p_empno;
	DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
	DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
	DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
	DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate);
	DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
	DBMS_OUTPUT.PUT_LINE('Dept # : ' || v_deptno);

	SELECT AVG(sal) INTO v_avgsal
		FROM emp WHERE deptno = v_deptno;
	IF v_sal > v_avgsal THEN
		DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '|| 'department average of ' || v_avgsal);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '|| 'department average of ' || v_avgsal);
END IF;
END ;

/
\set PLSQL_MODE off

v_avgsal 说明了用一个变量而不是用表的列名加上 %TYPE 的使用方法。

下面是存储过程执行后的输出结果：

antdb=# SELECT emp_sal_query(7369);
NOTICE:  Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Hire Date : 1980-12-17 00:00:00
NOTICE:  Salary : 800
NOTICE:  Dept # : 20
NOTICE:  Employee's salary does not exceed the department average of 800
 EMP_SAL_QUERY
---------------

(1 row)

在记录声明中使用 %ROWTYPE

通过使用 %TYPE 属性，可以用一种简便的方法创建由列数据类型所决定的变量。通过使用 %ROWTYPE 属性，可以定义一个记录类型，它包含的字段与给定表中每一列对应。每一个字段的数据类型与对应列的数据类型相同。

注意：在记录中的字段不继承列中的任何其他属性，例如 NOT NULL 子句或 DEFAULT 子句。

记录是被命名且顺序存放的字段集合。字段与变量相似，有标识名称和数据类型，但是它也同时拥有属于记录的附加属性，并且必须通过同时带有‘.'的记录名称作为限定词的方式来引用。

通过使用 %ROWTYPE 属性，可以声明一个记录类型。%ROWTYPE 属性是以表名为前缀。在被命名表的每一列都定义了记录中相同名称，数据类型的字段。

语法：

record table%ROWTYPE;

record 是分配给记录的标识符。table 是一个表名，将使用这个表中的列定义记录中字段，视图也可以用来定义记录。下面的示例显示了使用 emp%ROWTYPE 修改上一节中存储过程 emp_sal_query 来创建一个命名为 r_emp 的记录，而不是为表 emp 中的每一列声明一个单独的变量（emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
IS
	r_emp emp%ROWTYPE;
	v_avgsal emp.sal%TYPE;
BEGIN
	SELECT ename, job, hiredate, sal, deptno
		INTO r_emp.ename, r_emp.job, r_emp.hiredate, r_emp.sal, r_emp.deptno
		FROM emp WHERE empno = p_empno;
	DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
	DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
	DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
	DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate);
	DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
	DBMS_OUTPUT.PUT_LINE('Dept # : ' || r_emp.deptno);

	SELECT AVG(sal) INTO v_avgsal
		FROM emp WHERE deptno = r_emp.deptno;
	IF r_emp.sal > v_avgsal THEN
		DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '|| 'department average of ' || v_avgsal);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '|| 'department average of ' || v_avgsal);
END IF;
END;
/
\set PLSQL_MODE off

基本语句

本节开始讨论在 SPL 程序使用到的编程语句。

NULL

最简单的语句是 NULL 语句。这个语句是执行语句，不产生实际操作。

语法：

NULL;

下面是一个最简单的 SPL 程序。

BEGIN  
  NULL;
END;

当像 IF-THEN-ELSE 这样的语句执行时，NULL 语句可以以一个占位符方式的操作。

示例：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE divide_it (
	p_numerator IN NUMBER,
	p_denominator IN NUMBER,
	p_result OUT NUMBER
)
IS
BEGIN
	IF p_denominator = 0 THEN
		NULL;
	ELSE
		p_result := p_numerator / p_denominator;
END IF;
END;
/
\set PLSQL_MODE off

赋值

赋值语句的功能是把赋值符号左边的计算完毕表达式赋给赋值符号 := 右边的变量或者是一个 IN 或者 IN OUT 模式的形式参数。

语法：

variable := expression;

参数 variable 是已声明变量的标识，这个变量可以是 OUT，或 IN OUT 模式的形式参数。参数 expression 是产生单一值的表达式。由表达式产生的值必须和变量的数据类型相兼容。

关于 dept_salary_rpt 的示例显示了在变量声明时的赋值语句。在这个示例中有所不同的是在存储过程执行部分中赋值语句的使用方法。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_salary_rpt (p_deptno NUMBER)
IS
	todays_date DATE;
	rpt_title VARCHAR2(60);
	base_sal INTEGER;
	base_comm_rate NUMBER;
	base_annual NUMBER;
BEGIN
	todays_date := SYSDATE;
	rpt_title := 'Report For Department # ' || p_deptno || ' on '|| todays_date;
	base_sal := 35525;
	base_comm_rate := 1.33333;
	base_annual := ROUND(base_sal * base_comm_rate, 2);

	DBMS_OUTPUT.PUT_LINE(rpt_title);
	DBMS_OUTPUT.PUT_LINE('Base Annual Salary: ' || base_annual);
END;
/
\set PLSQL_MODE off

SELECT INTO

在 SPL 中 SELECT INTO 语句和标准 SQL 中的 SELECT 命令不是完全一样的，如下所示：

SELECT INTO 语句的作用是将结果分配给在 SPL 程序中使用到的变量或者记录。
SELECT INTO 只能访问到结果集中一条记录。

除了上面所提到的，标准 SQL 中 SELECT 命令的子句如 WHERE、ORDER BY、GROUP BY、HAVING，在 SELECT INTO 中均可以使用。下面是两种不同 SELECT INTO 语句的用法：

SELECT SELECT_expressions INTO target FROM ...;

target 是逗号隔开的简单变量列表。SELECT_expressions 和语句的其它部分与标准 SQL 中 SELECT 命令的一样。所选择出来值的数据类型，数量，顺序和目标变量的结构必须一致，否则在程序运行期间就会出错。

SELECT * INTO record FROM table ... ;

record 是先前声明好的记录型变量。

如果查询没有返回记录，那么将空值赋给目标变量。如果查询返回多条记录，那么第一条记录将分配给目标变量，其余的记录将被丢弃。

（需要注意的是除非使用了 ORDER BY 语句，否则“第一条数据”将不是真正的第一条数据）

（注意：无论是没有记录返回还是有超过一条记录返回，SPL 都将产生一个异常）

（注意：这里有一种 SELECT INTO 的不同的用法，通过使用 BULK COLLECT 子句，可以将结果集中的多条记录返回到一个集合中）

在处理异常的代码块中可以使用 WHEN NO_DATA_FOUND 子句来确认赋值是否成功（也就是查询至少返回了一条记录）

这个版本的存储过程 emp_sal_query 使用了另一种 SELECT INTO 语句的用法，将结果集返回到一个记录中（emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
IS
	r_emp emp%ROWTYPE;
	v_avgsal emp.sal%TYPE;
BEGIN
	SELECT ename, job, hiredate, sal, deptno
		INTO r_emp.ename, r_emp.job, r_emp.hiredate, r_emp.sal, r_emp.deptno
		FROM emp WHERE empno = p_empno;
	DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
	DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
	DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
	DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate);
	DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
	DBMS_OUTPUT.PUT_LINE('Dept # : ' || r_emp.deptno);

	SELECT AVG(sal) INTO v_avgsal
		FROM emp WHERE deptno = r_emp.deptno;
	IF r_emp.sal > v_avgsal THEN
		DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '|| 'department average of ' || v_avgsal);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '|| 'department average of ' || v_avgsal);
	END IF;

END;
/
\set PLSQL_MODE off

INSERT

在标准 SQL 中的 INSERT 命令同样可在 SPL 程序中使用。

标准 SQL 的 INSERT 命令中表达式出现的地方同样可以使用在 SPL 中的表达式。因此，SPL 变量和参数可以用来为插入操作提供数值。

在下面的示例中一个存储过程所执行的操作是将调用程序中传递过来的值作为新的雇员记录插入到 emp 数据表中（emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_insert (
	p_empno IN NUMBER(4),
	p_ename IN VARCHAR2(10),
	p_job IN VARCHAR2(9),
	p_mgr IN NUMBER(4),
	p_hiredate IN DATE,
	p_sal IN NUMBER(7,2),
	p_comm IN NUMBER(7,2),
	p_deptno IN NUMBER(2)
)
IS
BEGIN
	INSERT INTO emp VALUES (
		p_empno,
		p_ename,
		p_job,
		p_mgr,
		p_hiredate,
		p_sal,
		p_comm,
		p_deptno);

	DBMS_OUTPUT.PUT_LINE('Added employee...');
	DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
	DBMS_OUTPUT.PUT_LINE('Name : ' || p_ename);
	DBMS_OUTPUT.PUT_LINE('Job : ' || p_job);
	DBMS_OUTPUT.PUT_LINE('Manager : ' || p_mgr);
	DBMS_OUTPUT.PUT_LINE('Hire Date : ' || p_hiredate);
	DBMS_OUTPUT.PUT_LINE('Salary : ' || p_sal);
	DBMS_OUTPUT.PUT_LINE('Commission : ' || p_comm);
	DBMS_OUTPUT.PUT_LINE('Dept # : ' || p_deptno);
	DBMS_OUTPUT.PUT_LINE('----------------------');

END;
/
\set PLSQL_MODE off

如果存储过程在执行中遇到异常，那么所有对数据库修改操作都将会自动回滚。在这个示例中带有 WHEN OTHERS 子句的异常部分捕获所有的异常。将显示两个变量做为输出结果，其中 SQLCODE 是一个数值用于标识一个遇到的异常，而 SQLERRM 是一条文本消息用来解释所发生的异常错误。

下面是存储过程执行后所产生的输出（emp 表格定义见【示例参考表格】）：

antdb=# SELECT emp_insert(9503,'PETERSON','ANALYST',7902,'31-MAR-05',5000,NULL,40);
NOTICE:  Added employee...
NOTICE:  Employee # : 9503
NOTICE:  Name : PETERSON
NOTICE:  Job : ANALYST
NOTICE:  Manager : 7902
NOTICE:  Hire Date : 2005-03-31 00:00:00
NOTICE:  Salary : 5000
NOTICE:  Commission :
NOTICE:  Dept # : 40
NOTICE:  ----------------------
 EMP_INSERT
------------

(1 row)

antdb=# SELECT * FROM emp WHERE empno = 9503;
 EMPNO |  ENAME   |   JOB   | MGR  |      HIREDATE       | SAL  | COMM | DEPTNO
-------+----------+---------+------+---------------------+------+------+--------
  9503 | PETERSON | ANALYST | 7902 | 2005-03-31 00:00:00 | 5000 |      |     40
(1 row)

注意：可以将 INSERT 命令包含在一个 FORALL 的语句中。FORALL 语句允许一条 INSERT 命令使用一个或者多个集合所提供的值插入多条新的记录。

UPDATE

在标准 SQL 中的 UPDATE 命令同样可在 SPL 程序使用。

标准 SQL 中在 UPDATE 命令中出现表达式的地方同样可使用 SPL 语言中的表达式。因此，SPL 变量和参数可以用来为更新操作提供数值。

准备测试数据：

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

创建存储过程：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_comp_update (
	p_empno IN NUMBER,
	p_sal IN NUMBER(7,2),
	p_comm IN NUMBER(7,2)
)
IS
BEGIN
	UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno;

	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || p_empno);
		DBMS_OUTPUT.PUT_LINE('New Salary : ' || p_sal);
		DBMS_OUTPUT.PUT_LINE('New Commission : ' || p_comm);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
	END IF;
END;
/
\set PLSQL_MODE off

如果成功更新一条记录， SQL%FOUND 条件表达式返回 “true”，否则返回“false”。

下面的这个存储过程，在表 employee中执行了更新操作：

antdb=# SELECT emp_comp_update(7369, 6540, 1200);
NOTICE:  Updated Employee # : 7369
NOTICE:  New Salary : 6540
NOTICE:  New Commission : 1200
 EMP_COMP_UPDATE
-----------------

(1 row)

antdb=# SELECT * FROM emp WHERE empno=7369;
 EMPNO | ENAME |  JOB  | MGR  |      HIREDATE       | SAL  | COMM | DEPTNO
-------+-------+-------+------+---------------------+------+------+--------
  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 6540 | 1200 |     20
(1 row)

注意：可以在一个 FORALL 的语句中包含 UPDATE 命令。FORALL 语句允许一条 UPDATE 命令使用一个或者多个集合所提供的值更新多条记录。

DELETE

在标准 SQL 中的 DELETE 命令同样可以在 SPL 程序使用。标准 SQL 中在 DELETE 命令中出现表达式的地方同样可使用 SPL 语言中的表达式。因此，可以使用 SPL 变量和参数为删除操作提供值。

准备测试数据：

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

创建存储过程：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_delete (p_empno IN NUMBER)
IS
BEGIN
	DELETE FROM emp WHERE empno = p_empno;
	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Deleted Employee # : ' || p_empno);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END IF;
END;
/
\set PLSQL_MODE off

如果成功删除一条记录， SQL%FOUND 条件表达式返回 “true”，否则返回 “false”。

下面的这个存储过程，在表 employee 中执行删除操作。

antdb=# SELECT emp_delete(7369);
NOTICE:  Deleted Employee # : 7369
 EMP_DELETE
------------

(1 row)
antdb=# SELECT * FROM emp WHERE empno=7369;
 EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

使用 RETURNING INTO 子句

在 INSERT，UPDATE 和 DELETE 命令后面可以添加一个 RETURNING INTO 子句。这个子句的作用是允许 SPL 程序分别捕获 INSERT，UPDATE 或 DELETE 子句的最新插入、修改、或者删除的值。

语法：

{ insert | update | delete }

RETURNING { * | expr_1 [, expr_2 ] ...}

INTO { record | field_1 [, field_2 ] ...};

insert，update，delete 分别是有效的 INSERT，UPDATE 和 DELETE 命令。如果指定了'*’，那么由 INSERT，UPDATE 或者 DELETE 命令所操作成功的记录可以分配给记录类型或 INTO 关键字右边的字段。（注意“*”只是一个 AntDB 的扩展，而不与 Oracle 兼容）。expr_1，expr_2... 是以 INSERT，UPDATE 或 DELETE 命令操作的记录来计算的表达式。最后的计算结果分配给 INTO 关键字右边的记录或者字段。record 是记录的标识符。这个记录中的字段必须和 RETURNING INTO 子句中返回值在数量和出现顺序相匹配，并且和它们的数据类型相兼容。field_1， field_2...是变量，这些变量必须与 RETURNING INTO 子句中数值集的数量，顺序相匹配，数据类型兼容。

如果 INSERT，UPDATE 或者 DELETE 命令返回的结果集中有多条记录，就会产生 SQLCODE 为 01422 的异常，表示查询返回多条记录。如果在结果集中没有记录，INTO 关键词后面的变量将就会被设置为空值。

注意：这里有另外一种 RETURNING INTO 子句的使用方法，通过使用 BULK COLLECT 子句可以允许将包含多条记录的结果集返回到一个集合中。

示例：

准备测试数据：

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

创建存储过程：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_comp_update (
	p_empno IN NUMBER,
	p_sal IN  NUMBER(7,2),
	p_comm IN NUMBER(7,2)
)
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_job emp.job%TYPE;
	v_sal emp.sal%TYPE;
	v_comm emp.comm%TYPE;
	v_deptno emp.deptno%TYPE;
BEGIN
	UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno
		RETURNING
			empno,
			ename,
			job,
			sal,
			comm,
			deptno
		INTO
			v_empno,
			v_ename,
			v_job,
			v_sal,
			v_comm,
			v_deptno;

	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || v_empno);
		DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
		DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
		DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
		DBMS_OUTPUT.PUT_LINE('New Salary : ' || v_sal);
		DBMS_OUTPUT.PUT_LINE('New Commission : ' || v_comm);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
	END IF;
END;
/
\set PLSQL_MODE off

下面是这个存储过程的调用和输出结果：

antdb=# SELECT  emp_comp_update(7369, 6540, 1200);
NOTICE:  Updated Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Department : 20
NOTICE:  New Salary : 6540
NOTICE:  New Commission : 1200
 EMP_COMP_UPDATE
-----------------

(1 row)

在下面这个示例中，在这个存储过程上加上一个使用记录类型的 RETURNING INTO 子句：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_delete (p_empno IN NUMBER)
IS
	r_emp emp%ROWTYPE;
BEGIN
	DELETE FROM emp WHERE empno = p_empno
		RETURNING
			empno,
			ename,
			job,
			sal,
			comm,
			deptno
		INTO
			r_emp.empno,
			r_emp.ename,
			r_emp.job,
			r_emp.sal,
			r_emp.comm,
			r_emp.deptno;

	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Deleted Employee # : ' || r_emp.empno);
		DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
		DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
		DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
		DBMS_OUTPUT.PUT_LINE('Commission : ' || r_emp.comm);
		DBMS_OUTPUT.PUT_LINE('Department : ' || r_emp.deptno);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
	END IF;
END;
/
\set PLSQL_MODE off

下面是这个过程的执行结果：

antdb=# SELECT  emp_delete(7369);
NOTICE:  Deleted Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Salary : 6540
NOTICE:  Commission : 1200
NOTICE:  Department : 20
 EMP_DELETE
------------

(1 row)

获取结果状态

这里有一些属性能够用来确认命令的执行效果。SQL%FOUND 是一个布尔型的属性，当 INSERT， UPDATE 或者 DELETE 命令对一条记录进行有效的操作，或者 SELECT INTO 命令取出了超过一条记录，这个变量返回值为“true”。

下面这个匿名代码块插入了一条记录，然后显示出插入记录操作的结果（emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_insert() 
IS
BEGIN
	INSERT INTO emp (empno,ename,job,sal,deptno) VALUES (9001, 'JONES', 'CLERK', 850.00, 40);
	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Row has been inserted');
	END IF;
END;
/
\set PLSQL_MODE off

antdb=# SELECT emp_insert();
NOTICE:  Row has been inserted
 EMP_INSERT
------------

(1 row)

SQL%ROWCOUNT 提供了 INSERT，UPDATE 或者 DELETE 命令对记录进行有效操作的数量。

下面这个示例更新了上一个示例插入的记录，并且显示了 SQL%ROWCOUNT 的值（emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_update() 
IS
BEGIN
	UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9001;
	DBMS_OUTPUT.PUT_LINE('# rows updated: ' || SQL%ROWCOUNT);
END;

/
\set PLSQL_MODE off

antdb=# SELECT emp_update();
NOTICE:  # rows updated: 1
 EMP_UPDATE
------------

(1 row)

SQL%NOTFOUND 与 SQL%FOUND 的含义相反，如果 INSERT，UPDATE 或者 DELETE 命令对记录的操作不成功，或者 SELECT INTO 命令没有取出任何数据，这个属性返回值为‘true’（emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_update() 
IS
BEGIN
	UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9000;
	IF SQL%NOTFOUND THEN
		DBMS_OUTPUT.PUT_LINE('No rows were updated');
	END IF;
END;
/
\set PLSQL_MODE off

antdb=# SELECT emp_update();
NOTICE:  No rows were updated
 EMP_UPDATE
------------

(1 row)

编程语言控制结构

在下面的章节中，介绍了 SPL 编程语言如何实现对标准 SQL 进行完全面向过程的特性补充。

IF 语句

可以使用IF命令根据指定的条件来执行语句。SPL 提供了四种方式的 IF：

IF ... THEN
IF ... THEN ... ELSE
IF ... THEN ... ELSE IF
IF ... THEN ... ELSIF ... THEN ... ELSE

准备测试数据：

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);
INSERT INTO emp VALUES (1005,'JANE','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1006,'JANE','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1007,'JANE','CLERK',7912,'19-DEC-80',800,452,20);
INSERT INTO emp VALUES (1008,'JANE','CLERK',7912,'19-DEC-80',800,2500,20);
INSERT INTO emp VALUES (1009,'JANE','CLERK',7912,'19-DEC-80',800,1500,20);

IF-THEN

语法：

IF boolean-expression THEN

statements

END IF;

IF-THEN 语句是最简单的一种 IF 语句。如果条件为真的话，将执行在 THEN 和 END IF 之间的语句，否则，将不会执行这些语句。

在下面的示例中，使用 IF-THEN 语句测试和显示有佣金的雇员：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_1()
IS
	v_empno emp.empno%TYPE;
	v_comm emp.comm%TYPE;
	CURSOR emp_cursor IS SELECT empno, comm FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO COMM');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_comm;
		EXIT WHEN emp_cursor%NOTFOUND;
		--
		--  Test whether or not the employee gets a commission
		--
		IF v_comm IS NOT NULL AND v_comm > 0 THEN
			DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_comm,'$99999.99'));
		END IF;
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

下面是这个程序的输出结果：

antdb=# SELECT emp_test_1();
NOTICE:  EMPNO COMM
NOTICE:  ----- -------
NOTICE:  1001 $   855.90
NOTICE:  1002 $   300.00
NOTICE:  1007 $   452.00
NOTICE:  1008 $  2500.00
NOTICE:  1009 $  1500.00
 EMP_TEST_1
------------

(1 row)

IF-THEN-ELSE

语法：

IF boolean-expression THEN

statements

ELSE

statements

END IF;

如果 IF-THEN-ELSE 语句中条件表达式的返回值为 FALSE，那么将执行 ELSE 后面的语句。

现在对前面的示例进行了修改，用 IF-THEN-ELSE 语句替代了 IF-THEN 语句。这样当检测到雇员没有佣金的时候，会显示文本消息 'Non-commission'（emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_2()
IS
	v_empno emp.empno%TYPE;
	v_comm emp.comm%TYPE;
	CURSOR emp_cursor IS SELECT empno, comm FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO COMM');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_comm;
		EXIT WHEN emp_cursor%NOTFOUND;
		--
		--  Test whether or not the employee gets a commission
		--
		IF v_comm IS NOT NULL AND v_comm > 0 THEN
			DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_comm,'$99999.99'));
		ELSE
			DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || 'Non-commission');
		END IF;
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

下面是这个程序输出结果：

antdb=# SELECT emp_test_2();
NOTICE:  EMPNO COMM
NOTICE:  ----- -------
NOTICE:  1001 $   855.90
NOTICE:  1002 $   300.00
NOTICE:  1003 Non-commission
NOTICE:  1004 Non-commission
NOTICE:  1005 Non-commission
NOTICE:  1006 Non-commission
NOTICE:  1007 $   452.00
NOTICE:  1008 $  2500.00
NOTICE:  1009 $  1500.00
 EMP_TEST_2
------------

(1 row)

IF-THEN-ELSE IF

IF 语句可以嵌套使用，这样可以根据外层 IF 语句的条件返回值，来调用不同的内层 IF 语句。

在下面的示例中，外层的 IF-THEN-ELSE 语句测试一个雇员是否有佣金。内层的 IF-THEN-ELSE 语句测试雇员总的赔偿金额是否超过或者小于公司的平均水平（emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_3()
IS
	v_empno emp.empno%TYPE;
	v_sal emp.sal%TYPE;
	v_comm emp.comm%TYPE;
	v_avg NUMBER(7,2);
	CURSOR emp_cursor IS SELECT empno, sal, comm FROM emp ORDER BY 1;
BEGIN
--
--  Calculate the average yearly compensation in the company
--
	SELECT AVG((sal + NVL(comm,0)) * 24) INTO v_avg FROM emp;
	DBMS_OUTPUT.PUT_LINE('Average Yearly Compensation: ' ||TO_CHAR(v_avg,'$999,999.99'));
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO YEARLY COMP');
	DBMS_OUTPUT.PUT_LINE('----- -----------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_sal, v_comm;
		EXIT WHEN emp_cursor%NOTFOUND;
		--
		--  Test whether or not the employee gets a commission
		--
		IF v_comm IS NOT NULL AND v_comm > 0 THEN
			--
			--  Test if the employee's compensation with commission exceeds the average
			--
			IF (v_sal + v_comm) * 24 > v_avg THEN
				DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||' Exceeds Average');
			ELSE
				DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||' Below Average');
			END IF;
		ELSE
			--
			--  Test if the employee's compensation without commission exceeds the average
			--
			IF v_sal * 24 > v_avg THEN
				DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_sal * 24,'$999,999.99') || ' Exceeds Average');
			ELSE
				DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_sal * 24,'$999,999.99') || ' Below Average');
			END IF;
		END IF;
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

注意：也可以使用游标的 SELECT 命令中的 NVL 函数计算雇员年度赔偿金额，这样可以简化程序的逻辑。这个示例的目的是为了演示 IF 语句可达到相同的功能。

下面是这个程序的输出结果：

antdb=# SELECT emp_test_3();
NOTICE:  Average Yearly Compensation: $  36,287.73
NOTICE:  EMPNO YEARLY COMP
NOTICE:  ----- -----------
NOTICE:  1001 $  39,741.60 Exceeds Average
NOTICE:  1002 $  45,600.00 Exceeds Average
NOTICE:  1003 $  19,200.00 Below Average
NOTICE:  1004 $  19,200.00 Below Average
NOTICE:  1005 $  19,200.00 Below Average
NOTICE:  1006 $  19,200.00 Below Average
NOTICE:  1007 $  30,048.00 Below Average
NOTICE:  1008 $  79,200.00 Exceeds Average
NOTICE:  1009 $  55,200.00 Exceeds Average
 EMP_TEST_3
------------

(1 row)

当使用这种形式 IF 语句的时候，实际上在外层 IF 语句的 ELSE 内部嵌套了一个 IF 语句。因此需要为每一个嵌套的 IF 语句提供相对应的一个 END IF 语句，为最外层的 IF-ELSE 提供一个 END IF 语句。

IF-THEN-ELSIF-ELSE

语法：

IF boolean-expression THEN

statements

[ ELSIF boolean-expression THEN

statements

[ ELSIF boolean-expression THEN

statements ] ...]

[ ELSE

statements ]

END IF;

IF-THEN-ELSIF-ELSE 的作用是在 IF 语句中通过检测多个条件来执行相对应的语句。一般情况下，它等同于对 IF-THEN-ELSE-IF-THEN 命令的嵌套使用，不同之处是只需要一个 END IF 语句。

下面这个示例使用 IF-THEN-ELSIF-ELSE 语句来计算赔偿金范围在 25000 美元雇员的数量（emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_4()
IS
	v_empno emp.empno%TYPE;
	v_comp NUMBER(8,2);
	v_lt_25K SMALLINT := 0;
	v_25K_50K SMALLINT := 0;
	v_50K_75K SMALLINT := 0;
	v_75K_100K SMALLINT := 0;
	v_ge_100K SMALLINT := 0;
	CURSOR emp_cursor IS SELECT empno, (sal + NVL(comm,0)) * 24 FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cursor;
	LOOP
		FETCH emp_cursor INTO v_empno, v_comp;
		EXIT WHEN emp_cursor%NOTFOUND;
		IF v_comp < 25000 THEN
			v_lt_25K := v_lt_25K + 1;
		ELSIF v_comp < 50000 THEN
			v_25K_50K := v_25K_50K + 1;
		ELSIF v_comp < 75000 THEN
			v_50K_75K := v_50K_75K + 1;
		ELSIF v_comp < 100000 THEN
			v_75K_100K := v_75K_100K + 1;
		ELSE
			v_ge_100K := v_ge_100K + 1;
		END IF;
	END LOOP;
	CLOSE emp_cursor;
	DBMS_OUTPUT.PUT_LINE('Number of employees by yearly compensation');
	DBMS_OUTPUT.PUT_LINE('Less than 25,000 : ' || v_lt_25K);
	DBMS_OUTPUT.PUT_LINE('25,000 - 49,9999 : ' || v_25K_50K);
	DBMS_OUTPUT.PUT_LINE('50,000 - 74,9999 : ' || v_50K_75K);
	DBMS_OUTPUT.PUT_LINE('75,000 - 99,9999 : ' || v_75K_100K);
	DBMS_OUTPUT.PUT_LINE('100,000 AND over : ' || v_ge_100K);
END;

/
\set PLSQL_MODE off

下面是这个程序的输出：

antdb=# SELECT emp_test_4();
NOTICE:  Number of employees by yearly compensation
NOTICE:  Less than 25,000 : 4
NOTICE:  25,000 - 49,9999 : 3
NOTICE:  50,000 - 74,9999 : 1
NOTICE:  75,000 - 99,9999 : 1
NOTICE:  100,000 AND over : 0
 EMP_TEST_4
------------

(1 row)

CASE 表达式

CASE 表达式返回一个数值，用来替代在一个表达式中的 CASE 表达式。

CASE 表达式有两种格式。一种称为被动搜索型 CASE 表达式，另外一种是主动选择型 CASE 表达式。

准备测试数据：

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,10);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);
INSERT INTO emp VALUES (1005,'JANE','CLERK',7912,'19-DEC-80',800,null,40);
INSERT INTO emp VALUES (1006,'MILLER','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1007,'ADAMS','CLERK',7912,'19-DEC-80',800,452,30);
INSERT INTO emp VALUES (1008,'JONES','CLERK',7912,'19-DEC-80',800,2500,10);
INSERT INTO emp VALUES (1009,'FORD','CLERK',7912,'19-DEC-80',800,1500,30);

主动选择型 CASE 表达式

主动选择型 CASE 表达式使用一个被称为选择者的表达式匹配一个或多个在 WHEN 子句中指定的表达式。result 是与 CASE 表达式类型相兼容的表达式。如果相匹配，那么 CASE 表达式返回对应 THEN 子句中的值。如果这里没有匹配的话，那么 ELSE 子句后面的值将被返回。如果省略了 ELSE 子句，那么 CASE 表达式返回空值。

语法：

CASE SELECTor-expression

WHEN match-expression THEN

result

[ WHEN match-expression THEN

result

[ WHEN match-expression THEN

result ] ...]

[ ELSE

result ]

END;

match-expression 是根据在 CASE 表达式出现的顺序来计算的。result 是一个与 CASE 表达式类型相兼容的表达式。当遇到第一个等于selector-expression 的 match-expression，对应 THEN 子句的 result 作为 CASE 表达式的值返回。如果没有 match-expression 与 selector-expression 相等，那么将返回 ELSE 后面的参数 result。如果没有指定 ELSE，那么 CASE 表达式返回空值。

下面的示例用主动选择型 CASE 表达式根据部门号码把部门名称分配给一个变量：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_1()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_deptno emp.deptno%TYPE;
	v_dname VARCHAR2(20);
	CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME');
	DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
		EXIT WHEN emp_cursor%NOTFOUND;
		v_dname :=
			CASE v_deptno
				WHEN 10 THEN 'Accounting'
				WHEN 20 THEN 'Research'
				WHEN 30 THEN 'Sales'
				WHEN 40 THEN 'Operations'
				ELSE 'unknown'
			END;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || v_dname);
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

下面是程序的输出结果：

antdb=# SELECT emp_case_1();
NOTICE:  EMPNO ENAME DEPTNO DNAME
NOTICE:  ----- ------- ------ ----------
NOTICE:  1001 SMITH      10 Accounting
NOTICE:  1002 ALLEN      30 Sales
NOTICE:  1003 SMITH      20 Research
NOTICE:  1004 JACK       20 Research
NOTICE:  1005 JANE       40 Operations
NOTICE:  1006 MILLER     20 Research
NOTICE:  1007 ADAMS      30 Sales
NOTICE:  1008 JONES      10 Accounting
NOTICE:  1009 FORD       30 Sales
 EMP_CASE_1
------------

(1 row)

被动搜索型 CASE 表达式

一个被动搜索型 CASE 表达式是用一个或多个 Boolean 表达式确认结果值，然后将其返回。

语法：

CASE WHEN boolean-expression THEN

result

[ WHEN boolean-expression THEN

result

[ WHEN boolean-expression THEN

result ] ...]

[ ELSE

result ]

END;

boolean-expression 是根据在 CASE 表达式中出现的顺序来进行计算的。result 是在 CASE 表达式中类型相兼容的表达式。当遇到第一个计算为”true”的布尔表达式，那么在对应的 THEN 子句中 result 做为 CASE 表达式的值返回。如果没有遇到计算值为真 boolean-expression，那么返回 ELSE 后面的值。如果没有指定 ELSE 子句，那么 CASE 表达式返回为空。

在下面的示例中，使用了被动搜索型 CASE 表达式根据部门号码把部门名称分配给一个变量。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_2()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_deptno emp.deptno%TYPE;
	v_dname VARCHAR2(20);
	CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME');
	DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
		EXIT WHEN emp_cursor%NOTFOUND;
		v_dname :=
			CASE
				WHEN v_deptno = 10 THEN 'Accounting'
				WHEN v_deptno = 20 THEN 'Research'
				WHEN v_deptno = 30 THEN 'Sales'
				WHEN v_deptno = 40 THEN 'Operations'
				ELSE 'unknown'
			END;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || v_dname);
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

下面是程序的输出结果：

antdb=# SELECT emp_case_2();
NOTICE:  EMPNO ENAME DEPTNO DNAME
NOTICE:  ----- ------- ------ ----------
NOTICE:  1001 SMITH      10 Accounting
NOTICE:  1002 ALLEN      30 Sales
NOTICE:  1003 SMITH      20 Research
NOTICE:  1004 JACK       20 Research
NOTICE:  1005 JANE       40 Operations
NOTICE:  1006 MILLER     20 Research
NOTICE:  1007 ADAMS      30 Sales
NOTICE:  1008 JONES      10 Accounting
NOTICE:  1009 FORD       30 Sales
 EMP_CASE_2
------------

(1 row)

CASE 语句

当指定的搜索条件为真的时候 CASE 语句执行一个或多个的语句集。CASE 语句自己是一个单独的语句，前面讨论的 CASE 表达式作为整个表达式的一部分在 CASE 语句中出现。

CASE 语句有两种格式，一种被称为被动搜索型 CASE 语句，另外一种被称为主动选择型 CASE 语句。

主动选择型 CASE 语句

主动选择型 CASE 语句尝试去匹配在 WHEN 子句中指定的表达式。当找到匹配条件，将执行相对应的语句。

语法：

CASE selector-expression

WHEN match-expression THEN

statements

[ WHEN match-expression THEN

statements

[ WHEN match-expression THEN

statements ] ...]

[ ELSE

statements ]

END CASE;

selector-expression 返回一个与每一个匹配表达式数据类型相兼容的值。match-expression 是按照在 CASE 语句中出现的顺序来进行计算的。statements 是 SPL 语句，每一个 statement 以一个分号结束。当遇到第一个与参数 selector-expression 的值相等的 match-expression，执行对应的 THEN 子句中的语句，然后流程就运行到 END CASE 关键字的后面。如果这里没有匹配，那么将执行 ELSE 后面的语句。如果没有找到匹配的表达式并且没有 ELSE 子句，那么就会产生一个异常。

下面的示例根据一个部门号使用主动选择型的 CASE 语句将一个部门名称和地理位置分配给一个变量：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_3()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_deptno emp.deptno%TYPE;
	v_dname VARCHAR2(20);
	v_loc VARCHAR2(20);
	CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME '|| ' LOC');
	DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'|| ' ---------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
		EXIT WHEN emp_cursor%NOTFOUND;
		CASE v_deptno
			WHEN 10 THEN v_dname := 'Accounting';
			v_loc := 'New York';
			WHEN 20 THEN v_dname := 'Research';
			v_loc := 'Dallas';
			WHEN 30 THEN v_dname := 'Sales';
			v_loc := 'Chicago';
			WHEN 40 THEN v_dname := 'Operations';
			v_loc := 'Boston';
			ELSE v_dname := 'unknown';
			v_loc := '';
		END CASE;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || RPAD(v_dname, 14) || ' ' ||v_loc);
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

下面是这个程序的输出结果：

antdb=# SELECT emp_case_3();
NOTICE:  EMPNO ENAME DEPTNO DNAME  LOC
NOTICE:  ----- ------- ------ ---------- ---------
NOTICE:  1001 SMITH      10 Accounting     New York
NOTICE:  1002 ALLEN      30 Sales          Chicago
NOTICE:  1003 SMITH      20 Research       Dallas
NOTICE:  1004 JACK       20 Research       Dallas
NOTICE:  1005 JANE       40 Operations     Boston
NOTICE:  1006 MILLER     20 Research       Dallas
NOTICE:  1007 ADAMS      30 Sales          Chicago
NOTICE:  1008 JONES      10 Accounting     New York
NOTICE:  1009 FORD       30 Sales          Chicago
 EMP_CASE_3
------------

(1 row)

被动搜索型 CASE 语句

被动搜索型 CASE 语句使用一个或多个布尔型表达式来决定要执行的语句。

语法：

CASE WHEN boolean-expression THEN

statements

[ WHEN boolean-expression THEN

statements

[ WHEN boolean-expression THEN

statements ] ...]

[ ELSE

statements ]

END CASE;

boolean-expression 是以在 CASE 语句中出现的顺序来进行计算的。当遇到第一个计算为”true”的 boolean-expression，那么就执行对应 THEN 子句中的语句，然后流程就会运行到关键字 END CASE 的后面。如果没有 boolean-expression 计算为真的话，那么将执行在 ELSE 后面的语句。如果没有没有 boolean-expression 计算为真并且没有 ELSE 子句，那么将会产生异常。

下面的示例根据部门编号使用被动搜索型 CASE 语句把部门名称和位置分配给变量：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_4()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_deptno emp.deptno%TYPE;
	v_dname VARCHAR2(20);
	v_loc VARCHAR2(20);
	CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME '|| ' LOC');
	DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'|| ' ---------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
		EXIT WHEN emp_cursor%NOTFOUND;
			CASE
				WHEN v_deptno = 10 THEN v_dname := 'Accounting';
					v_loc := 'New York';
				WHEN v_deptno = 20 THEN v_dname := 'Research';
					v_loc := 'Dallas';
				WHEN v_deptno = 30 THEN v_dname := 'Sales';
					v_loc := 'Chicago';
				WHEN v_deptno = 40 THEN v_dname := 'Operations';
					v_loc := 'Boston';
				ELSE v_dname := 'unknown';
					v_loc := '';
			END CASE;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || RPAD(v_dname, 14) || ' ' ||v_loc);
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

下面是这个程序的输出结果：

antdb=# SELECT emp_case_4();
NOTICE:  EMPNO ENAME DEPTNO DNAME  LOC
NOTICE:  ----- ------- ------ ---------- ---------
NOTICE:  1001 SMITH      10 Accounting     New York
NOTICE:  1002 ALLEN      30 Sales          Chicago
NOTICE:  1003 SMITH      20 Research       Dallas
NOTICE:  1004 JACK       20 Research       Dallas
NOTICE:  1005 JANE       40 Operations     Boston
NOTICE:  1006 MILLER     20 Research       Dallas
NOTICE:  1007 ADAMS      30 Sales          Chicago
NOTICE:  1008 JONES      10 Accounting     New York
NOTICE:  1009 FORD       30 Sales          Chicago
 EMP_CASE_4
------------

(1 row)

循环语句

可以使用 LOOP、 EXIT、 CONTINUE、WHILE 和 FOR 语句，使 SPL 程序重复执行一个命令序列。

LOOP

语法：

LOOP

statements

END LOOP;

LOOP 语句定义了一个无条件循环，这个循环进行无限次循环，当遇到 EXIT 或者 RETURN 命令时终止循环。

EXIT

语法：

EXIT [ WHEN expression ];

这个语句的作用是结束最内层的循环，然后执行在 END LOOP 后面的语句。如果出现了 WHEN 子句，那么只有当指定的条件为真的时候，才能退出循环，否则流程将会走到 EXIT 后面的语句。可以使用 EXIT 语句，提前从所有类型的循环中退出，这个语句在无条件循环中的使用不受限制。

下面是一个循环的简单示例，这个循环重复执行 10 次，然后使用 EXIT 语句退出循环。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_1 ()
IS
	v_counter NUMBER(2);
BEGIN
	v_counter := 1;
	LOOP
		EXIT WHEN v_counter > 10;
		DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
		v_counter := v_counter + 1;
	END LOOP;
END;
/
\set PLSQL_MODE off

下面是程序的输出结果：

antdb=# SELECT LOOP_PRO_1();
NOTICE:  Iteration # 1
NOTICE:  Iteration # 2
NOTICE:  Iteration # 3
NOTICE:  Iteration # 4
NOTICE:  Iteration # 5
NOTICE:  Iteration # 6
NOTICE:  Iteration # 7
NOTICE:  Iteration # 8
NOTICE:  Iteration # 9
NOTICE:  Iteration # 10
 LOOP_PRO_1
------------

(1 row)

CONTINUE

CONTINUE 语句提供一种方式可以跳开循环中间的语句，而重复下一次循环。

当在循环中遇到 CONTINUE 语句的时候，跳过所有在 CONTINUE 语句后面的语句，开始重复在最内层的循环，直到循环结束。如果循环体内部进行了重新计算，那么流程就传递给了循环控制表达式。

如果使用了 WHEN 子句，只有当 WHEN 子句中指定的表达式为真的情况下才重复下一次循环，否则控制流程就是运行到 CONTINUE 后面的语句。

COTNIUE 语句不能在循环的外部使用。

下面这个示例与前面的不同，它使用了 CONTINUE 语句来跳过奇数数值的显示：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_2 ()
IS
	v_counter NUMBER(2);
BEGIN
	v_counter := 0;
	LOOP
		v_counter := v_counter + 1;
		EXIT WHEN v_counter > 10;
		CONTINUE WHEN MOD(v_counter,2) = 1;
		DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
	END LOOP;
END;
/
\set PLSQL_MODE off

下面是以上程序的输出：

antdb=# SELECT LOOP_PRO_2();
NOTICE:  Iteration # 2
NOTICE:  Iteration # 4
NOTICE:  Iteration # 6
NOTICE:  Iteration # 8
NOTICE:  Iteration # 10
 LOOP_PRO_2
------------

(1 row)

WHILE

语法：

WHILE expression LOOP

statements

END LOOP;

当条件表达式的值为真，WHILE 语句会重复循环体中语句。在每次进入循环主体前，都会先检查条件。

下面的示例和前面示例中的程序逻辑相同，不同之处是用 WHILE 语句替代 EXIT 语句来决定什么时候退出循环。

注意：必须在循环中修改用来决定何时退出循环的条件表达式，当 EXIT 语句的条件表达式为真的时候，将退出循环。当 WHILE 语句的条件表达式为假的时候，会结束循环（或者从来不进入循环）。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_3 ()
IS
	v_counter NUMBER(2);
BEGIN
	v_counter := 1;
	WHILE v_counter <= 10 LOOP
		DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
		v_counter := v_counter + 1;
	END LOOP;
END;
/
\set PLSQL_MODE off

这个示例产生了和上一个示例一样的结果：

antdb=# SELECT LOOP_PRO_3();
NOTICE:  Iteration # 1
NOTICE:  Iteration # 2
NOTICE:  Iteration # 3
NOTICE:  Iteration # 4
NOTICE:  Iteration # 5
NOTICE:  Iteration # 6
NOTICE:  Iteration # 7
NOTICE:  Iteration # 8
NOTICE:  Iteration # 9
NOTICE:  Iteration # 10
 LOOP_PRO_3
------------

(1 row)

FOR (根据整数循环)

语法：

FOR name IN expression .. expression LOOP

statements

END LOOP;

这种类型的 FOR 语句，创建了一个循环，循环次数由一个范围的整数来确定。变量 name 自动定义为整型，只在循环中有效。在开始进入循环的时候，对确定循环范围的表达式进行计算。重复的步骤为‘+1’，并且名字是以在‘..'左边的表达式的值开始。当变量 name 超过了'..'右边表达式的值，结束循环，因此两个表达式带有两个角色，开始值..结束值。

下面的示例使用了从 1 重复到 10 的 FOR 循环，这样进一步简化了 WHILE 循环：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_4 ()
IS
BEGIN
	FOR i IN 1 .. 10 LOOP
		DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
	END LOOP;
END;
/
\set PLSQL_MODE off

下面是使用 FOR 语句的程序输出结果：

antdb=# SELECT LOOP_PRO_4();
NOTICE:  Iteration # 1
NOTICE:  Iteration # 2
NOTICE:  Iteration # 3
NOTICE:  Iteration # 4
NOTICE:  Iteration # 5
NOTICE:  Iteration # 6
NOTICE:  Iteration # 7
NOTICE:  Iteration # 8
NOTICE:  Iteration # 9
NOTICE:  Iteration # 10
 LOOP_PRO_4
------------

(1 row)

如果开始值大于结束值，那么循环主体的语句不会执行，而不会产生任何错误。如下面这个示例所示：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_5 ()
IS
BEGIN
	FOR i IN 10 .. 1 LOOP
		DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
	END LOOP;
END;
/
\set PLSQL_MODE off

由于循环主体没有被执行，因此这个示例没有任何输出。

注意：SPL 也支持 CURSOR FOR 循环。

异常处理

在缺省状态下，在一个 SPL 程序中遇到任何错误都会中断程序的执行。可以使用带有 EXCEPTION 部分的 BEGIN 代码块来捕获错误，并且恢复程序的处理流程。具体语法就是 BEGIN 代码块语法的扩展。

语法：

[ IS

declarations ]

BEGIN

statements

EXCEPTION

WHEN condition [ OR condition ]... THEN

handler_statements

[ WHEN condition [ OR condition ]... THEN

handler_statements ]...

END;

如果没有遇到错误，那么这种形式的代码块执行所有的 statements，然后控制流程就转到 END 后面的语句。但是，如果在语句内出现了运行错误的情况，那么就会被放弃这个 statements 后面的处理工作，控制流程就会转到异常列表中。这个列表用来搜索匹配发生错误的第一个条件，如果找到一个匹配条件，那么将会执行相应的处理语句，然后控制流程会转到 END 后面的语句。如果没有找到匹配的条件，那么错误将会传送，就好像这里没有 EXCEPTION 子句。错误可以被封闭的处理异常的代码块捕获，否则的话，将在终止子程序的执行。

名称为 OTHERS 的指定条件匹配每一种错误类型，条件名称不区分大小写。

如果在异常处理中的语句产生了新的错误，那么 EXCEPTION 子句不能捕获这个错误，但是该错误将会继续传递，后面的 EXCEPTION 子句能够捕获这个异常。

在下面的表中列出了可以被使用的条件名称。

异常条件名称：

条件名称	描述
NO_DATA_FOUND	没有满足查询条件的记录
TOO_MANY_ROWS	当只有一行记录被允许返回时，返回多条满足查询条件的记录
ZERO_DIVIDE	除数为零

示例：

准备测试数据：

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE EXCEP_PRO_1 ()
IS
	v_ename emp.ename%TYPE;
BEGIN
	SELECT ename INTO STRICT v_ename FROM emp WHERE deptno = 20 ORDER BY ename;
		DBMS_OUTPUT.PUT_LINE('employee is ' || v_ename);
	EXCEPTION
		WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('More than one employee found');
		DBMS_OUTPUT.PUT_LINE('First employee returned is ' || v_ename);
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE EXCEP_PRO_2 (p_empno IN NUMBER)
IS
    r_emp emp%ROWTYPE;
BEGIN
    SELECT * INTO STRICT r_emp FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
    DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
    DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
    DBMS_OUTPUT.PUT_LINE('Dept # : ' || r_emp.deptno);

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END;
/
\set PLSQL_MODE off

抛出程序异常

通过使用存储过程 RAISE_APPLICATION_ERROR，可以主动的产生异常，使 SPL 程序内部流程中断处理。除此之外，存储过程RAISE_APPLICATION_ERROR 为能够用来标识异常的程序提供了用户自定义的代码和相关的错误信息。

语法：

RAISE_APPLICATION_ERROR(error_number, message);

当执行存储过程时，err_number 是一个整数值或者是返回名称为 SQLCODE 的变量的表达式。message 是一个字符串常量或者一个返回一个命名为 SQLERRM 的变量的表达式。

在下面的示例中根据雇员的缺失信息使用 RAISE_APPLICATION_ERROR 过程来显示不同的代码和信息：

准备测试数据：

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,20);
INSERT INTO emp VALUES (1004,'JOHN','CLERK',null,'17-DEC-80',800,855.90,20);

创建存储过程：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE verify_emp (p_empno NUMBER)
IS
	v_ename emp.ename%TYPE;
	v_job emp.job%TYPE;
	v_mgr emp.mgr%TYPE;
	v_hiredate emp.hiredate%TYPE;
BEGIN
	SELECT ename, job, mgr, hiredate INTO v_ename, v_job, v_mgr, v_hiredate FROM emp WHERE empno = p_empno;
	
	IF v_ename IS NULL THEN
		RAISE_APPLICATION_ERROR(-20010, 'No name for ' || p_empno);
	END IF;
	IF v_job IS NULL THEN
		RAISE_APPLICATION_ERROR(-20020, 'No job for' || p_empno);
	END IF;
	IF v_mgr IS NULL THEN
		RAISE_APPLICATION_ERROR(-20030, 'No manager for ' || p_empno);
	END IF;
	IF v_hiredate IS NULL THEN
		RAISE_APPLICATION_ERROR(-20040, 'No hire date for ' || p_empno);
	END IF;
		DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||' validated without errors');
		
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('OTHER EXCEPTION.');
			DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
			DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);

END;
/
\set PLSQL_MODE off

下面是当经理编号在雇员记录中不存在的情况下，存储过程的输出：

antdb=# SELECT verify_emp(1004);
NOTICE:  OTHER EXCEPTION.
NOTICE:  SQLCODE: 16777248
NOTICE:  SQLERRM: ORA-20030: No manager for 1004
 VERIFY_EMP
------------

(1 row)

动态 SQL 语句

通过使用动态 SQL 语句，可以在程序运行的时候确定具体要执行的 SQL 命令。在前面曾经详细阐述过在 SPL 程序中如何使用静态 SQL 命令--在程序执行之前，必须确定完整的 SQL 命令（其中包含异常变量），并且在程序实际运行前明确地写在了程序中。与之相反，通过使用动态 SQL，可以在程序的执行过程中改变要执行的 SQL 语句。

除此之外，动态 SQL 是唯一种在 SPL 程序中执行例如 CREATE TABLE 等的 DDL 命令的方法。

需要注意的是在运行时间这个性能指标上，动态 SQL 将比静态 SQL 慢些。

可以使用 EXECUTE IMMEDIATE 命令来动态执行 SQL 命令。

语法：

EXECUTE IMMEDIATE sql_expression;

[ INTO { variable [, ...] | record } ]

[ USING expression [, ...] ]

sql_expression 是一个字符串表达式，包含在程序运行期间可动态执行的 SQL 命令。sql_expression 中的动态语句执行后，将结果集的输出传送到 variable 所代表的变量中。变量的数量，顺序和数据类型必须与结果集的字段相匹配。也可以定义一个记录，这个记录字段的数量，顺序与结果集相匹配，数据类型也要相兼容。当使用 INTO 子句的时候，从结果集中只能返回一条记录，否则就会产生异常。当使用了 USING 子句的时候，将表达式的值传递到 SQL 命令的占位符。占位符是嵌入在 SQL 命令中的，包含 SQL 命令的sql_expression 能够使用变量。占位符是以"："为前置的标识符-:name。所计算表达式的数量，顺序，和结果数据的类型必须与在sql_exoression 中的的数量，顺序和类型相匹配。需要注意的是，占位符不是在 SPL 程序中声明的，它只出现在 sql_expression 中。

下面的示例是以字符串常量方式使用动态 SQL 命令的方法：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE imme_pro_1 ()
IS
	v_sql VARCHAR2(50);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE imme_job (jobno NUMBER(3), jname VARCHAR2(9))';
	v_sql := 'INSERT INTO imme_job VALUES (100, ''ANALYST'')';
	EXECUTE IMMEDIATE v_sql;
	v_sql := 'INSERT INTO imme_job VALUES (200, ''CLERK'')';
	EXECUTE IMMEDIATE v_sql;
END;
/
\set PLSQL_MODE off

下面的示例演示了如何使用 USING 子句把值传递到 SQL 字符串中占位符：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE imme_pro_2 ()
IS
	v_sql VARCHAR2(50) := 'INSERT INTO imme_job VALUES ($1, $2)';
	v_jobno imme_job.jobno%TYPE;
	v_jname imme_job.jname%TYPE;
BEGIN
	v_jobno := 300;
	v_jname := 'MANAGER';
	EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
	v_jobno := 400;
	v_jname := 'SALESMAN';
	EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
	v_jobno := 500;
	v_jname := 'PRESIDENT';
	EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
END;

/
\set PLSQL_MODE off

下面的示例使用了 INTO 和 USING 这两个子句：

注意最后一次执行的 SELECT 命令，是将结果返回到一个记录，而不是返回到一个变量中。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE imme_pro_3 ()
IS
	v_sql VARCHAR2(60);
	v_jobno imme_job.jobno%TYPE;
	v_jname imme_job.jname%TYPE;
BEGIN
	DBMS_OUTPUT.PUT_LINE('JOBNO JNAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	v_sql := 'SELECT jobno, jname FROM imme_job WHERE jobno = $1';
	EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 100;
	DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
	EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 200;
	DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
	EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 300;
	DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
	EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 400;
	DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
END;
/
\set PLSQL_MODE off

下面是上一个匿名代码块的输出结果：

antdb=# SELECT imme_pro_1 ();
 IMME_PRO_1
------------

(1 row)
antdb=# SELECT imme_pro_2 ();
 IMME_PRO_2
------------

(1 row)
antdb=# SELECT imme_pro_3 ();
NOTICE:  JOBNO JNAME
NOTICE:  ----- -------
NOTICE:  100 ANALYST
NOTICE:  200 CLERK
NOTICE:  300 MANAGER
NOTICE:  400 SALESMAN
 IMME_PRO_3
------------

(1 row)

静态游标

可以设置一个游标来封装一个查询，然后每一次从查询结果集中读取一条记录。而不是一次执行整个查询过程，这种方式允许创建如下 SPL 程序逻辑，一次从结果集中读取一条记录，对这条记录的数据进行处理，然后读取下面一条记录，重复相同的过程。

游标通常在 FOR 或者 WHILE 循环中使用。在 SPL 程序逻辑中应该包含条件测试，来检测结果集末尾是否已达到，这样程序可以退出循环。

示例：

准备测试数据：

CREATE TABLE emp (
empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7319,'JACK','CLERK',7922,'18-DEC-80',800,null,20);

声明游标

游标必须在 SPL 程序的声明部分首先声明，然后才能使用。

语法：

CURSOR name IS query;

name 是一个标识符，用来在程序中引用的游标以及相关的结果集。query 是一条 SELECT 命令，用来决定游标能够取回的结果集。

下面是一些游标声明的示例：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	CURSOR emp_cur_1 IS SELECT * FROM emp;
	CURSOR emp_cur_2 IS SELECT empno, ename FROM emp;
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
...
END;

/
\set PLSQL_MODE off

打开一个游标

首先必须打开游标，才能用它获取记录。这个操作是由 OPEN 语句完成的。

语法：

OPEN name;

name 是一个标识符，表示在 SPL 程序中的声明部分已经声明好的一个游标。不应使用 OPEN 语句打开一个已经存在并已打开的游标。

下面显示了一个与游标声明相对应的 OPEN 语句：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
	OPEN emp_cur_3;
...
END;
/
\set PLSQL_MODE off

从游标中获取记录

当打开游标后，可以使用 FETCH 语句获取游标结果集中的记录。

语法：

FETCH name INTO { record | variable [, variable_2 ]... };

name 是一个已经打开游标的标识符。record 是已经定义好记录（例如，使用到了 table@ROWTYPE）的标识符。variable，variable_2...是从记录中得到字段数据的 SPL 程序变量。在记录中的字段或者 variable，variable_2...必须和游标声明中查询语句的 SELECT 列表字段的数量，顺序相匹配。SELECT 列表中字段的数据类型和记录中的字段或者 variable，variable2..的数据类型必须是匹配或者能够隐式地进行自动转换。

注意：这里另外有一种 FETCH INTO 语句的使用方法，在 FETCH INTO 语句中使用 BULKCOLLECT 子句，可以允许一次将多条记录返回到一个集合中。

下面是 FETCH 语句的使用方法：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	v_empno	NUMBER(4);
	v_ename	VARCHAR2(10);
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
	OPEN emp_cur_3;
	FETCH emp_cur_3 INTO v_empno, v_ename;
	...
END;
/
\set PLSQL_MODE off

可以在 SPL 程序中将变量的类型声明为 %TYPE，这样如果数据表中列的属性发生变化，就不需要在程序中显式地改变的目标变量的数据类型，因为 %TYPE 可以自动的获取指定列的数据类型。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	v_empno	emp.empno%TYPE;
	v_ename	emp.ename%TYPE;
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
	OPEN emp_cur_3;
	FETCH emp_cur_3 INTO v_empno, v_ename;
	...
END;

/
\set PLSQL_MODE off

如果表中的所有列都以在表中定义的顺序获取，那么可以用 %ROWTYPE 定义一个记录，这样 FETCH 语句可以将获取数据存放在这个记录中，可以使用'.'号来访问记录中的每个字段。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
	OPEN emp_cur_1;
	FETCH emp_cur_1 INTO v_emp_rec;
	DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno);
	DBMS_OUTPUT.PUT_LINE('Employee Name	: ' || v_emp_rec.ename);
	...
END;
/
\set PLSQL_MODE off

关闭游标

当所有需要的记录都从游标结果集中获取出来后，就必须关闭游标。游标关闭后，将无法访问结果集。

语法：

CLOSE name;

name 是当前打开的游标的标识符。关闭游标后，不应该再次进行相同的关闭操作。在关闭游标后，可以在这个游标上再次使用 OPEN 语句。当使用 FETCH 语句获取新结果集中记录后，查询结果集将重新创建。

下面的示例演示了 CLOSE 语句的使用方法：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cur_1;
	FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
	DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno);
	DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_emp_rec.ename);
	CLOSE emp_cur_1;
END;
/
\set PLSQL_MODE off

下面是执行完上面的存储过程后的输出结果。在结果集中雇员编号为 7369，姓名是 SMITH 的记录是第一条记录：

antdb=# SELECT cursor_example();
NOTICE:  Employee Number: 7319
NOTICE:  Employee Name : JACK
 CURSOR_EXAMPLE
----------------

(1 row)

在游标中使用 %ROWTYPE

通过使用 %ROWTYPE 属性，可以定义一个记录，这个记录包含的字段与游标或者游标变量中所获取的所有列一一对应。每个字段的数据类型就是所对应列的数据类型。%ROWTYPE 属性以游标名或者游标型变量的名称为前缀。

语法：

record cursor%ROWTYPE;

record 是分配给变量的标识符。cursor 是在当前范围内显式声明的游标。

在下面的这个示例中演示了如何使用带有 %ROWTYPE 的游标获取雇员所在的部门：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_info()
IS
	CURSOR empcur IS SELECT ename, deptno FROM emp ORDER BY 2;
	myvar empcur%ROWTYPE;
BEGIN
	OPEN empcur;
	LOOP
		FETCH empcur INTO myvar.ename,myvar.deptno;
		EXIT WHEN empcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE( myvar.ename || ' works in department '|| myvar.deptno );
	END LOOP;
	CLOSE empcur;
END;
/
\set PLSQL_MODE off

下面是这个过程的输出结果：

antdb=# SELECT emp_info();
NOTICE:  SMITH works in department 20
NOTICE:  JACK works in department 30
NOTICE:  JANE works in department 40
 EMP_INFO
----------

(1 row)

游标属性

每个游标都有一个属性集，程序可以使用这些属性去检测游标的状态。这些属性是 %ISOPEN，%FOUND， %NOTFOUND 和 %ROWCOUNT，在下面的章节中将会详细介绍这些属性。

%ISOPEN

属性 %ISOPEN 用于测试游标是否处于打开状态。

语法：

cursor_name%ISOPEN

cursor_name 是游标的名称，如果游标的属性 %ISOPEN 返回为”true”，那么表示游标是处于打开状态，反之，表示该游标处于未打开状态。

下面是一个使用属性 %ISOPEN 的示例：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_1()
IS
	CURSOR empcur IS SELECT ename, deptno FROM emp ORDER BY 2;
BEGIN
	OPEN empcur;
	
	IF empcur%ISOPEN THEN
		DBMS_OUTPUT.PUT_LINE( 'empcur is open ');
	ELSE
		DBMS_OUTPUT.PUT_LINE( 'empcur is close ');
	END IF;
	
	CLOSE empcur;

END;
/
\set PLSQL_MODE off

%FOUND

当游标的 FETCH 操作执行完成后，可以使用属性 %FOUND 测试是否从游标的结果集中取出了一条记录。

语法：

cursor_name%FOUND

cursor_name 是游标的名称。如果这个 BOOLEAN 变量返回“true”，那么表示在 FETCH 操作后取出至少一条记录。

从结果集中取出最后一条记录后，下一次的 FETCH 操作时 %FOUND 返回”false”。如果第一次 FETCH 操作完成后，没有从结果集中返回记录，那么 %FOUND 也会返回”false”。

在游标打开前或者关闭后，引用属性 %FOUND 会产生一个 INVALID_CURSOR 异常。

如果打开游标后，没有执行 FETCH 操作，那么这时引用 %FOUND 将返回空值。

下面是一个使用属性 %FOUND 的示例：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_2()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cur_1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
	WHILE emp_cur_1%FOUND LOOP
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
		FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
	END LOOP;
	CLOSE emp_cur_1;
END;

/
\set PLSQL_MODE off

上面这个存储过程的输出结果显示如下：

antdb=# SELECT cursor_test_2();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
 CURSOR_TEST_2
---------------

(1 row)

%NOTFOUND

属性 %NOFOUND 与 %FOUND 在逻辑上是相反的。

语法：

cursor_name%NOTFOUND

cursor_name 是游标的名称。当 FETCH 操作从结果集中取出记录后，这个布尔型变量将返回”false”。

从结果集中取出最后一条记录后，下一次 FETCH 操作后，%NOFOUND 返回”true”，当第一次 FETCH 操作完成后，在结果集中没有记录，该变量也会返回”true”。

在一个未打开或者已经关闭的游标上引用属性 %NOFOUND，会产生一个 INVALID_CURSOR 异常。

如果一个游标已经打开，但是未执行 FETCH 操作。在这个游标上引用 %NOTFOUND，将返回 NULL。

在下面的示例中使用了属性 %NOTFOUND：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_3()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cur_1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
		EXIT WHEN emp_cur_1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
	END LOOP;
	CLOSE emp_cur_1;
END;
/
\set PLSQL_MODE off

这个存储过程的执行输出结果与上面示例中的相同：

antdb=# SELECT cursor_test_3();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
 CURSOR_TEST_3
---------------

(1 row)

%ROWCOUNT

属性 %ROWCOUNT 用于返回一个整数值，显示迄今为止从游标结果集中获取记录的总数。

语法：

cursor_name%ROWCOUNT

cursor_name 是游标的名称，%ROWCOUNT 返回迄今为止通过游标所获取记录的总数。当取出一条记录后，在游标关闭之前，%ROWCOUNT 保持取出记录的总数。当游标关闭后，再次引用属性 %ROWCOUNT，那么会产生 INVALID_CURSOR 异常。

当一个游标未打开或已关闭，这时引用 %ROWCOUNT，会产生一个 INVALID_CURSOR 异常。

当游标已经打开，但尚未执行 FETCH 操作，这时引用 %ROWCOUNT，那么该属性返回 0。同样，如果在第一次执行 FETCH 后，在结果集中没有记录返回，那么这个属性同样会返回"0"。

下面的示例使用了属性 %ROWCOUNT：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_4()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cur_1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
		EXIT WHEN emp_cur_1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('**********************');
	DBMS_OUTPUT.PUT_LINE(emp_cur_1%ROWCOUNT || ' rows were retrieved');
	CLOSE emp_cur_1;
END;
/
\set PLSQL_MODE off

下面这个存储过程在雇员列表的最后部分打印出了所获取记录的总数：

antdb=# SELECT cursor_test_4();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
NOTICE:  **********************
NOTICE:  0 rows were retrieved
 CURSOR_TEST_4
---------------

(1 row)

游标状态和属性的总结

下面的表总结了所有游标状态和游标属性的返回值：

游标状态	%ISOPEN	%FOUND	%NOFOUND	%ROWCOUNT
打开前	是	INVALID_CURSOR 异常	INVALID_CURSOR 异常	INVALID_CURSOR 异常
游标打开后在执行第一次FETCH 前	是	空值	空值	0
第一次执行 FETCH成功后	是	是	否	1
成功执行完n次操作FECTH后（包括最后一行）	是	是	否	n
取出最后一行记录，再次执行 FETCH操作	是	否	是	n
关闭游标后	否	INVALID_CURSOR 异常	INVALID_CURSOR 异常	INVALID_CURSOR异常

用于游标的循环语句

在迄今所出现关于使用游标的示例中，处理游标结果集的程序逻辑都是按照如下顺序进行：打开一个游标，用循环结构获取结果集中的每一条记录，测试确认结果集中已经没有记录，最后使用语关闭游标。可以使用循环结构 cursor FOR loop，这样可以不用上面列出的语句单独编写代码。

cursor FOR loop 打开一个已经声明的游标，将结果集中的所有记录取出，然后关闭游标。

语法：

FOR record IN cursor

LOOP

statements

END LOOP;

record 是一个标识符，分配给隐式声明且带有 cursor%ROWTYPE 定义的记录。cursor 是已声明游标的名称。statements 是 SPL 语句。在循环中必须至少有一个语句。

在下面的示例中，使用了 cursor FOR loop：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_5()
IS
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp ORDER BY 1;
BEGIN
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	FOR v_emp_rec IN emp_cur_1 LOOP
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
	END LOOP;
END;
/
\set PLSQL_MODE off

如下所示，修改存储过程的执行结果相同：

antdb=# SELECT cursor_test_5();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
 CURSOR_TEST_5
---------------

(1 row)

参数化游标

用户可以声明一个用于接收参数的静态游标。这样当打开游标时，可以为游标的这些参数传递数值。在下面的示例中，创建了一个参数化游标。这个游标首先接收一个指定值作为参数，然后显示了从表 emp 中所有薪水小于参数值的雇员姓名和薪水。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_6(v_sal in number)
IS
	my_record emp%ROWTYPE;
	CURSOR c1 (max_wage NUMBER) IS SELECT ename,sal FROM emp WHERE sal < max_wage;
BEGIN
	OPEN c1(v_sal);
	LOOP
		FETCH c1 INTO my_record.ename,my_record.sal;
		EXIT WHEN c1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('Name = ' || my_record.ename || ', salary = '|| my_record.sal);
	END LOOP;
	CLOSE c1;
END;
/
\set PLSQL_MODE off

例如，传递了数值 2000 作为了最大工资，那么就只能显示所有薪水少于 2000 的所有雇员的姓名和薪水。

下面是上面查询的执行输出结果：

antdb=# SELECT cursor_test_6(2001);
NOTICE:  Name = SMITH, salary = 1000
NOTICE:  Name = JANE, salary = 2000
 CURSOR_TEST_6
---------------

(1 row)

REF CURSORS 和游标变量

本节讨论了另外一种类型的游标，与先前讨论的静态游标相比较，它能够提供更大的灵活性。

REF CURSOR 概述

一个游标型变量是一个游标，包含一个指向查询结果集的指针。通过执行游标变量的 OPEN FOR 语句，可以决定结果集的内容。

与静态游标不同的，游标型变量不与特定的 SQL 绑定。可以使用 OPEN FOR 语句多次打开同一个游标变量，每次与不同的查询语句绑定。这样每一次查询会创建一个新的结果集，然后通过游标变量在程序中生效。

REF CURSOR 类型可以通过参数方式传递，或者从存储过程和函数中返回。一个函数的返回类型可以是一个 REF CURSOR 类型，通过在程序之间游标类型变量的传递，提供了将游标上的操作模块化到不同程序中的能力。

声明游标变量

SPL 使用两种方式声明游标变量，方法是创建一个 REF CURSOR 的类型，然后使用这个类型声明游标变量。

声明一个用户定义的 REF CURSOR 型变量

为使用用户定义的 REF CURSOR 型变量，需要执行两个不同的步骤进行声明。

创建一个被引用的游标类型。
在这个游标类型的基础上声明一个实际游标变量。

语法：

TYPE cursor_type_name IS REF CURSOR [ RETURN return_type];

下面是一个声明游标型变量的示例：

IS
TYPE emp_cur_type IS REF CURSOR ;
my_rec emp_cur_type;
...

打开游标变量

当声明一个游标型变量后，必须用一个与相关联的 SELECT 命令打开这个游标变量。OPEN FOR 语句指定了用于创建结果集的 SELECT 命令。

语法：

OPEN name FOR query;

name 是一个已定义好的游标型变量的标识符。query 是一条 SELECT 命令，用来确定结果集。当 OPEN FOR 语句执行后，使用游标变量的值标识了结果集。

在下面的示例中，结果集是一个由部门中雇员号，和名称组成的列表：

注意在 SELECT 命令中出现变量和参数的地方都可以使用表达式。在这种情况下，在等式中可以使用一个参数来测试是否与部门号相等。（emp 表格定义见【示例参考表格】）

CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno	emp.deptno%TYPE
)
IS
type emp_cur is ref cursor;
emp_refcur emp_cur;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
...

从游标变量中取回记录

当打开游标型变量后，使用 FETCH 语句，从结果集中取出记录。

在下面的示例中，在上个示例中加上了 FETCH 语句，这样会将结果集返回到 2 个变量中，然后显示出来：

需要注意的是，用来确认静态游标状态的游标属性也可以在游标型变量中使用。

CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno	emp.deptno%TYPE
)
IS
type emp_cur is ref cursor;
emp_refcur emp_cur;
v_empno	emp.empno%TYPE;
v_ename	emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
DBMS_OUTPUT.PUT_LINE('EMPNO	ENAME');
DBMS_OUTPUT.PUT_LINE('-----	-------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || '	' || v_ename);
END LOOP;
...

关闭一个游标型变量

CLOSE 语句可以用来释放一个结果集。

注意：与静态游标不同，游标型变量在重新打开使用前不是必须处于关闭状态的。游标型变量打开后，上一次打开时所产生的结果集会将会丢失。

在下面这个示例中加上了 CLOSE 语句（emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE ref_cursor_test_1 (p_deptno NUMBER)
IS
	type emp_cur is ref cursor;
	emp_refcur emp_cur;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno ORDER BY 1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;

/
\set PLSQL_MODE off

下面是这个存储过程的执行输出：

antdb=# SELECT ref_cursor_test_1(20);
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7329 AMY
NOTICE:  7369 SMITH
 REF_CURSOR_TEST_1
-------------------

(1 row)

使用限制

下面是关于游标型变量使用时的一些限制：

不能使用比较类型操作符来测试游标类型是否相等，不等，空或者非空。
不能将空值分配给游标型变量。
不能在数据库列中存放游标型标量的值。
静态游标和游标变量不能互换使用。例如，静态游标不能在 OPEN FOR 语句中使用。

除此之外，下面的这个表根据在存储过程或者函数内可以对游标变量进行的操作，显示作为存储过程或者函数参数的游标变量所被允许使用的参数模式。

操作	输入参数	输入输出参数	输出参数
OPEN	不可以	可以	不可以
FETCH	可以	可以	不可以
CLOSE	可以	可以	不可以

例如，如果存储过程带有游标变量的形式参数，并且在游标型变量上执行了 OPEN FOR，FETCH 和 CLOSE 这三个操作，那么这个形式参数必须声明为 IN OUT 模式。

示例

下面是一些使用游标型变量的示例：

从函数中返回一个 REF CURSOR 型变量

预置测试数据：

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',1000,NULL,20);
INSERT INTO emp VALUES (7389,'JANE','CLERK',7912,'17-DEC-80',2000,NULL,40);
INSERT INTO emp VALUES (7319,'JACK','CLERK',7922,'18-DEC-80',5000,null,20);
INSERT INTO emp VALUES (7329,'AMY','MASTER',7722,'18-DEC-80',3000,null,20);

在下面的示例中，使用将工作类型作为条件的查询语句打开了一个游标型变量来选择雇员相关信息。需要注意的是，游标变量是在这个函数返回语句中指定的，所以函数的调用者可以访问到游标变量的结果集。

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION emp_by_job (p_job VARCHAR2)
RETURN REFCURSOR
IS
	type emp_cur is ref cursor;
	emp_refcur emp_cur;
BEGIN
	OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job;
	RETURN emp_refcur;
END;
/
\set PLSQL_MODE off

在下面存储过程中，首先通过将函数的返回值分配给已声明的游标型变量来调用上面示例中的函数，然后使游标变量取回查询结果集，最后关闭游标型变量。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_job emp.job%TYPE := 'CLERK';
	type emp_cur is ref cursor;
	v_emp_refcur emp_cur;
BEGIN
	DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB ' || v_job);
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	v_emp_refcur := emp_by_job (v_job);
	LOOP
		FETCH v_emp_refcur INTO v_empno, v_ename;
		EXIT WHEN v_emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE v_emp_refcur;
END;
/
\set PLSQL_MODE off

下面就是匿名代码块执行后的输出结果：

antdb=# SELECT test();
NOTICE:  EMPLOYEES WITH JOB CLERK
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
NOTICE:  7319 JACK
B019_TEST
-----------

(1 row)

模块化游标操作

在下面的几个示例中演示了如何以模块化操作的方式将游标型变量上的不同操作放置在不同的程序中。

在下面这个存储过程中，通过用一条查询所有记录的 SELECT 命令打开游标变量：

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION open_all_emp (p_emp_refcur REFCURSOR) RETURN REFCURSOR
IS
BEGIN
	OPEN p_emp_refcur FOR SELECT empno, ename FROM emp;
	return p_emp_refcur;
END;
/
\set PLSQL_MODE off

在下面这个示例中根据指定的部门编号，通过使用查询所有符合条件的记录的 SQL 命令来打开游标型变量：

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION open_emp_by_dept (p_emp_refcur REFCURSOR,p_deptno NUMBER)
IS
BEGIN
	OPEN p_emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
	return p_emp_refcur;
END;
/
\set PLSQL_MODE off

在下面的示例中通过从另外一张数据表中取得所有记录的 SELECT 命令打开游标变量。注意，函数的返回值是一个已经打开的游标型变量（dept 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION open_dept (p_dept_refcur REFCURSOR) RETURN REFCURSOR
IS
	v_dept_refcur REFCURSOR;
BEGIN
	v_dept_refcur := p_dept_refcur;
	OPEN v_dept_refcur FOR SELECT deptno, dname FROM dept;
	RETURN v_dept_refcur;
END;
/
\set PLSQL_MODE off

在下面的示例中使用存储过程取回并显示了一个由雇员号和名称组成的游标变量结果集：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE fetch_emp (p_emp_refcur REFCURSOR)
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
		FETCH p_emp_refcur INTO v_empno, v_ename;
		EXIT WHEN p_emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
END;
/
\set PLSQL_MODE off

在下面这个示例中使用存储过程取回并显示了包含部门号和名称的游标变量结果集：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE fetch_dept ( p_dept_refcur REFCURSOR)
IS
	v_deptno dept.deptno%TYPE;
	v_dname dept.dname%TYPE;
BEGIN
	DBMS_OUTPUT.PUT_LINE('DEPT DNAME');
	DBMS_OUTPUT.PUT_LINE('---- ---------');
LOOP
	FETCH p_dept_refcur INTO v_deptno, v_dname;
	EXIT WHEN p_dept_refcur%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE(v_deptno || ' ' || v_dname);
END LOOP;
END;
/
\set PLSQL_MODE off

下面的这个存储过程关闭了一个指定的游标变量：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE close_refcur (p_refcur REFCURSOR)
IS
BEGIN
	CLOSE p_refcur;
END;
/
\set PLSQL_MODE off

下面的匿名代码块执行了先前所描述的所有程序：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	gen_refcur REFCURSOR;
BEGIN
	DBMS_OUTPUT.PUT_LINE('ALL EMPLOYEES');
	gen_refcur:=open_all_emp(gen_refcur);
	fetch_emp(gen_refcur);
	DBMS_OUTPUT.PUT_LINE('****************');
	close_refcur(gen_refcur);
	
	DBMS_OUTPUT.PUT_LINE('EMPLOYEES IN DEPT #10');
	gen_refcur:=open_emp_by_dept(gen_refcur, 10);
	fetch_emp(gen_refcur);
	DBMS_OUTPUT.PUT_LINE('****************');
	close_refcur(gen_refcur);
	
	DBMS_OUTPUT.PUT_LINE('DEPARTMENTS');
	fetch_dept(open_dept(gen_refcur));
	DBMS_OUTPUT.PUT_LINE('*****************');

	close_refcur(gen_refcur);

END;
/
\set PLSQL_MODE off

下面是匿名代码块的输出结果：

antdb=# SELECT test();
NOTICE:  ALL EMPLOYEES
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7369 SMITH
NOTICE:  7499 ALLEN
NOTICE:  7521 WARD
NOTICE:  7566 JONES
NOTICE:  7654 MARTIN
NOTICE:  7698 BLAKE
NOTICE:  7782 CLARK
NOTICE:  7788 SCOTT
NOTICE:  7839 KING
NOTICE:  7844 TURNER
NOTICE:  7876 ADAMS
NOTICE:  7900 JAMES
NOTICE:  7902 FORD
NOTICE:  7934 MILLER
NOTICE:  ****************
NOTICE:  EMPLOYEES IN DEPT #10
NOTICE:  7782 CLARK
NOTICE:  7839 KING
NOTICE:  7934 MILLER
NOTICE:  ****************
NOTICE:  DEPARTMENTS
NOTICE:  DEPT DNAME
NOTICE:  ---- ---------
NOTICE:  10 ACCOUNTING
NOTICE:  20 RESEARCH
NOTICE:  30 SALES
NOTICE:  40 OPERATIONS
NOTICE:  *****************
 TEST
------

(1 row)

执行动态查询语句的 REF CURSORS

在 AntDB 中可以通过 OPEN FOR USING 语句来实现动态查询的功能。在 OPEN FOR USING 语句中，可以提供一个字符串常量或者字符串变量，来做为 SELECT 命令。

语法：

OPEN name FOR dynamic_string

[ USING bind_arg [, bind_arg_2 ] ...];

name 是一个已经声明的游标型变量。dynamic_string 是一个字符串常量或者变量包，它包含不以分号结束的 SELECT 命令。bind_arg，bind_arg_2... 是绑定参数。当打开游标变量后，这些参数会传递到 SELECT 命令中的对应占位符中。占位符就是前缀为冒号的标识符。

在下面的示例中，以字符串常量的方式实现了动态查询的功能（emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_query()
IS
	emp_refcur REFCURSOR;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	OPEN emp_refcur FOR 'SELECT empno, ename FROM emp WHERE deptno = 30' ||' AND sal >= 1500';
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;
/
\set PLSQL_MODE off

下面是存储过程执行后的输出结果：

antdb=# SELECT dept_query();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7499 ALLEN
NOTICE:  7698 BLAKE
NOTICE:  7844 TURNER
DEPT_QUERY
------------

(1 row)

在下个示例中，前面的那个查询被修改为通过绑定参数来传递查询参数（emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_query (p_deptno NUMBER,p_sal NUMBER)
IS
	emp_refcur REFCURSOR;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno AND sal >= p_sal;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;

/
\set PLSQL_MODE off

下面是输出结果：

antdb=# SELECT dept_query(30,1500);
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7499 ALLEN
NOTICE:  7698 BLAKE
NOTICE:  7844 TURNER
DEPT_QUERY
------------

(1 row)

在最后这个示例中，使用字符串变量来传递 SELECT 命令，这样就使程序具备了最大程度的灵活性（emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_query (p_deptno NUMBER,p_sal NUMBER)
IS
	emp_refcur REFCURSOR;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	p_query_string VARCHAR2(100);
BEGIN
	p_query_string := 'SELECT empno, ename FROM emp WHERE ' ||'deptno = $1 AND sal >= $2';
	OPEN emp_refcur FOR p_query_string USING p_deptno, p_sal;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;
/
\set PLSQL_MODE off

集合

集合是一个拥有相同数据类型，且排序好的数据集。一般来说，数据项是一个关于标量的字段，但是只要包含在用户定义类型中每个字段的结构和数据类型与结果集中的每个元组相同，它也可以是用户自定义的类型-例如记录类型或对象类型（关于对象类型的介绍，参见第 8 章）。通过使用括号内下标，可以引用在结果集中每一个特定的数据项。

最熟悉的集合类型是数组，在 AntDB 中，所支持的集合类型是以前在 Oracle 中称为索引表，现在称为联合型数组，以及嵌套表。

嵌套表

嵌套表是一种集合类型，用于把一个正整数和一个值关联起来。在很多方面，它和联合型数组非常相似。

嵌套表的特性如下：

必须定义嵌套表类型后，才能将嵌套表变量声明为嵌套表类型。数据操作是在嵌套表变量（简称为“表”）中进行的。
不需要对嵌套表变量进行初始化-只要直接为嵌套表成员分配值就可以了，需要注意的是，在 Oracle 中，必须使用构造函数对嵌套表变量进行初始化。而在 Postgres Plus Advance Server 中，SPL 当前还不支持嵌套表构造函数。
嵌套表中的键值是一个正整数。
对于嵌套表成员的数量，没有预先定义的限制-嵌套表成员的数量是随着成员的增加而动态增长的。需要注意的是，在 Oracle 中，必须先使用构造函数设置嵌套表变量中成员的数量，或者使用函数 EXTEND 为嵌套表变量增加成员。当前 SPL 不支持构造函数和函数EXTEND。
嵌套表变量可以是以稀疏方式存在-这就说分配给嵌套表变量键的值可以不是连续的。
如果试图引用没有分配值的嵌套表变量成员，将会导致一个 SQLCODE 为 1403 的异常，含义为"访问未初始化的索引"。

TYPE IS TABLE 语句用于在 SPL 程序的声明部分中定义一个嵌套表类型。

语法：

TYPE tbltype IS TABLE OF { datatype | rectype | objtype };

tbltype 是分配给嵌套表类型的标识符。datatype 是像 VARCHAR2 或 NUMBER 这样的标量数据类型。rectype 是先前已定义的记录类型。objtype 是先前已定好的对象类型。

为了使用嵌套表变量，必须声明一个类型为嵌套表的变量。下面就是声明嵌套表变量的语法：

table tbltype

table 是分配给嵌套表的标识符。tbltype 是先前已声明为嵌套表类型的标识符。

可以使用下面这种语法来引用嵌套表的成员。

table(n) [ element ]

table 是先前已声明为嵌套表变量的标识符。n 是一个正整数。如果嵌套表变量的类型是通过记录类型或对象类型定义的，那么 [.element] 分别必须引用在记录类型中的单独字段，或者是对象类型的属性。另外也可以通过省略 [.element] 来引用整个记录或对象。

在下面的示例中，对介绍联合数组的章节中第一个示例进行了修改，这样就可以从表 emp 中读取第一个雇员名称，把它存放在一个嵌套表中，然后显示嵌套表中的结果：

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	TYPE dname_tbl_typ IS TABLE OF VARCHAR2(14);
	dname_tbl dname_tbl_typ;
	CURSOR dept_cur IS SELECT dname FROM b021_dept ORDER BY dname;
	i INTEGER := 1;
BEGIN
	FOR r_dept IN dept_cur LOOP
		dname_tbl(i) := r_dept.dname;
		i := i + 1;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('DNAME');
	DBMS_OUTPUT.PUT_LINE('----------');
	FOR j IN 1..i-1 LOOP
		DBMS_OUTPUT.PUT_LINE(dname_tbl(j));
	END LOOP;
END;
/
\set PLSQL_MODE off

上面的示例产生的输出如下：

antdb=# SELECT test();
NOTICE:  DNAME
NOTICE:  ----------
NOTICE:  ACCOUNTING
NOTICE:  OPERATIONS
NOTICE:  RESEARCH
NOTICE:  SALES
 TEST
------

(1 row)

集合的方法

集合的方法是提供关于集合关于信息的函数，可以用来帮助在集合中对数据进行的处理。下面的这些示例讨论了这些方法。

COUNT

COUNT 是一个方法用来返回集合中成员的数量。

语法：

collection.COUNT

collection 是一个集合型变量的标识。

下面的示例显示一个能够稀疏分布的联合型数组（例如，被分配的序列成员是不连续的）。COUNT 只包含了已赋值成员的数量。（dept 表格定义见【示例参考表格】）

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	TYPE dname_tbl_typ IS TABLE OF VARCHAR2(14);
	dname_tbl dname_tbl_typ;
	CURSOR dept_cur IS SELECT dname FROM dept ORDER BY dname;
	i INTEGER := 1;
BEGIN
	FOR r_dept IN dept_cur LOOP
		dname_tbl(i) := r_dept.dname;
		i := i + 1;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('COUNT='||dname_tbl.count);

END;
/
\set PLSQL_MODE off

输出结果如下：

antdb=# SELECT test();
NOTICE:  COUNT=4
 TEST
------------

(1 row)

使用 BULK COLLECT 子句

SQL 命令在返回包含大量记录的结果集时候，运行效率不会很高。这是因为在传送整个结果集的时候，服务器端和客户之间的固定状态会发生切换。通过使用集合，将整个结果集放到客户端可访问的内存中，可以减轻这种低效率操作的情况。BULK COLLECT 子句用来将结果集的集合放到一个集合中。

BULK COLLECT 子句可以和 SELECT INTO 和 FETCH INTO 命令一起使用，也可以和带有 RETURNING INTO 子句的 DELETE， INSERT 和 UPDATE 命令一起使用。在下面的章节中将对上面提到的使用方法进行详细的介绍。

SELECT BULK COLLECT

如下面所述，BULK COLLECT 子句可以和 SELECT INTO 语句一起使用。

语法：

SELECT select_expressions BULK COLLECT INTO collection FROM ...;

如果定义了一个集合，那么这个集合可以是单独字段的集合，也可以是一个记录类型的集合。如果指定了多个集合，每一个集合必须由一个单独的字段组成。select_expressions 必须和目标集合在数量，顺序和数据类型兼容性上相匹配。

下面的这个示例演示了 BULK COLLECT 子句使用方法，其中目标集合是一个包含单一字段的联合型数组（dept 表格定义见【示例参考表格】）。

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	TYPE dname_tbl_typ IS TABLE OF VARCHAR2(14);
	dname_tbl dname_tbl_typ;
	TYPE deptno_tbl_typ IS TABLE OF NUMBER;
	deptno_tbl deptno_tbl_typ;
	TYPE loc_tbl_typ IS TABLE OF VARCHAR2(14);
	loc_tbl loc_tbl_typ;
BEGIN
	SELECT deptno BULK COLLECT INTO deptno_tbl FROM dept;
	SELECT dname BULK COLLECT INTO dname_tbl FROM dept;
	SELECT loc BULK COLLECT INTO loc_tbl FROM dept;
	
	DBMS_OUTPUT.PUT_LINE('DEPTNO DNAME LOC');
	DBMS_OUTPUT.PUT_LINE('-----  -------  ---------');
	
	FOR i IN 1..deptno_tbl.COUNT LOOP
		DBMS_OUTPUT.PUT_LINE(deptno_tbl(i) || ' ' ||RPAD(dname_tbl(i),8) || ' ' ||RPAD(loc_tbl(i),10));
	END LOOP;

END;
/
\set PLSQL_MODE off

antdb=# SELECT test();
NOTICE:  DEPTNO DNAME LOC
NOTICE:  -----  -------  ---------
NOTICE:  10 ACCOUNTI NEW YORK
NOTICE:  20 RESEARCH DALLAS
NOTICE:  30 SALES    CHICAGO
NOTICE:  40 OPERATIO BOSTON
 TEST
------

(1 row)

RETURNING BULK COLLECT

BULK COLLECT 子句可以被加到带有 RETURNING INTO 子句的 DELETE，INSERT 或者 UPDATE 命令中。

语法：

{ insert | update | delete }

RETURNING { * | expr_1 [, expr_2 ] ... }

BULK COLLECT INTO collection [ , ...];

如果指定了一个集合，那么这个集合可以是单独字段的集合，也可以是记录类型的集合。如果定义了多个集合，那么每一个集合都应该由一个单独的字段组成。在 RETURNING 关键字后面的表达式必须匹配目标集合中所有字段的定义的数量，顺序并且类型兼容。（dept 表格定义见【示例参考表格】）

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	TYPE dname_tbl_typ IS TABLE OF VARCHAR2(14);
	dname_tbl dname_tbl_typ;
BEGIN
	WHERE FROM dept WHERE deptno > 20 RETURNING dname BULK COLLECT INTO dname_tbl;
	DBMS_OUTPUT.PUT_LINE('DNAME');
	DBMS_OUTPUT.PUT_LINE('-----');
	
	FOR i IN 1..dname_tbl.COUNT LOOP
		DBMS_OUTPUT.PUT_LINE(dname_tbl[i]);
	END LOOP;
END;
/
\set PLSQL_MODE off

antdb=# SELECT test();
NOTICE:  DNAME
NOTICE:  -----
NOTICE:  SALES
NOTICE:  OPERATIONS
TEST
--------

(1 row)

在下面这个示例中代码块执行和上一个示例中相同的功能，但是使用了一个自定义类型（dept 表格定义见【示例参考表格】）：

set grammar to postgres;
CREATE type dept_type AS(
deptno integer,
dname VARCHAR(14),
loc VARCHAR(14)
);

set grammar to oracle;

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	TYPE dept_tbl_typ IS TABLE OF dept_type;
	dept_tbl dept_tbl_typ;
BEGIN
	UPDATE dept set loc=loc||'_2' WHERE deptno <= 20 RETURNING deptno,dname,loc BULK COLLECT INTO dept_tbl;

	FOR i IN 1..dept_tbl.COUNT LOOP
		DBMS_OUTPUT.PUT_LINE('DNAME='||dept_tbl[i].dname);
		DBMS_OUTPUT.PUT_LINE('DEPTNO='||dept_tbl[i].deptno);
		DBMS_OUTPUT.PUT_LINE('LOC='||dept_tbl[i].loc);
		DBMS_OUTPUT.PUT_LINE('-----------------');
	END LOOP;
END;
/
\set PLSQL_MODE off

antdb=# SELECT test();
NOTICE:  DNAME=ACCOUNTING
NOTICE:  DEPTNO=10
NOTICE:  LOC=NEW YORK_2
NOTICE:  -----------------
NOTICE:  DNAME=RESEARCH
NOTICE:  DEPTNO=20
NOTICE:  LOC=DALLAS_2
 TEST
--------

(1 row)

错误与消息

可以使用 DBMS_OUTPUT.PUT_LINE 来显示相关信息。

语法：

DBMS_OUTPUT.PUT_LINE(message);

message 就是以字符串方式显示的表达式。

下面这个示例在用户输出中显示了消息：

DBMS_OUTPUT.PUT_LINE('My name is John');

特定变量 SQLCODE 和 SQLERRM 分别包含了一个数值代码和一个文本信息，用来表示上一条 SQL 命令的执行结果。如果在程序中出现了诸如除零的错误，那么这些变量中就包含了与错误相关的信息。