第7章 PL/SQL高级应用_ORACLE 11g权威指南-QQ阅读男生轻小说网

上QQ阅读APP看本书，新人免费读10天

设备和账号都新为新人

第7章 PL/SQL高级应用

本章将在前一章介绍PL/SQL基础知识的基础上介绍PL/SQL的一些高级应用。包（package）可以将一些过程和函数组织到一起，通过这种方式将PL/SQL代码模块化，可以构建供其他编程人员重用的代码库。从Oracle 8i开始，引入了新的数据库类型——集合（collection），后续版本进一步改进了集合，如多级集合。游标（cursor）是一个指向上下文区的指针，可以理解为能够一次访问一组记录，通过游标，PL/SQL程序可以控制上下文区。

本章将介绍PL/SQL的一些高级知识，主要内容包括：

◎ 包

◎ 集合

◎ 游标

7.1 包

包是由存储在一起的相关对象组成的PL/SQL结构。包有两个独立的部分，即说明部分和包体，这两部分独立存储在数据字典中。与可以位于本地块或数据库中的过程和函数不同，包只能存储，并且不能在本地存储。除了允许相关的对象结为组之外，包与依赖性较强的存储子程序相比，其所受的限制较少。除此之外，包的效率比较高。

从本质上讲，包就是一个命名的声明部分。任何可以出现在块声明中的语句都可以在包中使用，这些语句包括过程、函数、游标、类型及变量。把上述内容放入包中的好处是我们可以从其他PL/SQL块中对其进行引用，因此，包为PL/SQL提供了全程变量。

7.1.1 包头

包的说明（也叫做包头）包含了有关包内容的信息。然而，该部分中不包括包的代码部分。下面是一个包的说明部分。

CREATE OR REPLACE PACKAGE ClassPackage AS
-- Add a new student into the specified class.
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE);
-- Removes the specified student from the specified class.
PROCEDURE RemoveStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE);
-- Exception raised by RemoveStudent.
e_StudentNotRegistered EXCEPTION;
-- Table type used to hold student info.
TYPE t_StudentIDTable IS TABLE OF students.id%TYPE
INDEX BY BINARY_INTEGER;
-- Returns a PL/SQL table containing the students currently
-- in the specified class.
PROCEDURE ClassList(p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE,
p_IDs OUT t_StudentIDTable,
p_NumStudents IN OUT BINARY_INTEGER);
END ClassPackage;

上面的包说明ClassPackage包括三个过程、一个类型说明和一个异常说明。创建包头的语法如下所示。

CREATE [OR REPLACE] PACKAGE package_name{IS | AS}
type_definition|
procedure_specification|
function_specification|
variable_declaration|
exception_declaration|
cursor_declaration|
pragma_declaration
END [ package_name];

其中，package_name是包的名称。该包内的各种元素的说明语法（即过程说明、函数说明、变量说明等）与匿名块中的同类元素的说明使用的语法完全相同。也就是说，除了过程和函数的声明外，我们在前面介绍的用于过程声明部分的语法也适用于包头的说明部分。此类语法的规则如下：

◎ 包元素的位置可以任意安排，但在声明部分，对象必须在引用前进行声明。例如，如果一个游标使用了作为其WHERE子句一部分的变量，则该变量必须在声明游标之前声明。

◎ 包头可以不对任何类型的元素进行说明。例如，包可以只带有过程和函数说明语句，而不声明任何异常和类型。

◎ 对过程和函数的任何声明都必须是前向说明。所谓前向说明，就是只对子程序及其参数进行描述，但不带有任何代码的说明。该声明的规则不同于块声明语法，在块声明中，过程或函数的前向声明和代码同时出现在其声明部分，而实现包所说明的过程或函数的代码则只能出现在包体中。

7.1.2 包体

包体是一个独立于包头的数据字典对象，只能在包头完成编译后才能进行编译。包体中带有实现包头中描述的前向子程序的代码段。除此之外，包体还可以包括具有包体全局属性的附加声明部分，但这些附加说明对于说明部分是不可见的。下面的例子演示了包ClassPackage的包体部分。

CREATE OR REPLACE PACKAGE BODY ClassPackage AS
-- Add a new student for the specified class.
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
INSERT INTO registered_students (student_id, department, course)
VALUES (p_StudentID, p_Department, p_Course);
END AddStudent;
-- Removes the specified student from the specified class.
PROCEDURE RemoveStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
DELETE FROM registered_students
WHERE student_id = p_StudentID
AND department = p_Department
AND course = p_Course;
-- Check to see if the DELETE operation was successful. If
-- it didn't match any rows, raise an error.
IF SQL%NOTFOUND THEN
RAISE e_StudentNotRegistered;
END IF;
END RemoveStudent;
-- Returns a PL/SQL table containing the students currently
-- in the specified class.
PROCEDURE ClassList( p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE,
p_IDs OUT t_StudentIDTable,
p_NumStudents IN OUT BINARY_INTEGER) IS
v_StudentID registered_students.student_id%TYPE;
-- Local cursor to fetch the registered students.
CURSOR c_RegisteredStudents IS
SELECT student_id
FROM registered_students
WHERE department = p_Department
AND course = p_Course;
BEGIN
/* p_NumStudents will be the table index. It will start at
* 0, and be incremented each time through the fetch loop.
* At the end of the loop, it will have the number of rows
* fetched, and therefore the number of rows returned in
* p_IDs.
*/
p_NumStudents := 0;
OPEN c_RegisteredStudents;
LOOP
FETCH c_RegisteredStudents INTO v_StudentID;
EXIT WHEN c_RegisteredStudents%NOTFOUND;
p_NumStudents := p_NumStudents + 1;
p_IDs(p_NumStudents) := v_StudentID;
END LOOP;
END ClassList;
END ClassPackage;

该包体部分包括了实现包头过程的前向说明的代码。在包头中没有进行前向说明的对象（如异常e_StudentNotRegistered）可以在包体中直接引用。

包体是可选的。如果包头中没有说明任何过程或函数（只有变量声明、游标、类型等），则该包体就不必存在。由于包中的所有对象在包外都是可见的，所以，这种说明方法可用来声明全局变量。

包头中的任何前向说明都不能出现在包体中。包头和包体中的过程和函数的说明必须一致，其中包括子程序名和其参数名，以及参数的模式。例如，由于下面的包体对函数FunctionA使用了不同的参数表，因此，其包头与包体不匹配。

CREATE ORREPLACEP ACKAGE PackageA AS
FUNCTION FunctionA(p_Parameter1 IN NUMBER,
p_Parameter2 IN DATE)
RETURN VARCHAR2;
END PackageA;
CREATE OR REPLACE PACKAGE BODY PackageA AS
FUNCTION FunctionA(p_Parameter1 IN CHAR)
RETURN VARCHAR2;
END PackageA;

如果按上面的说明来创建包PacgageA，编译程序将给包体提出下列错误警告。

PLS-00328: A subprogram body must be defined for the forward
declaration of FUNCTIONA.
PLS-00323: subprogram or cursor 'FUNCTIONA' is declared in a
package specification and must be defined in the package body.

包头中声明的任何对象都是在其作用域中的，并且可在其外部使用包名作为前缀对其进行引用。例如，我们可以从下面的PL/SQL块中调用对象ClassPackage.RemoveStudent。

BEGIN
ClassPackage.RemoveStudent(10006, 'HIS', 101);
END;

上面的过程调用的格式与调用独立过程的格式完全一致，唯一不同的是在被调用的过程名的前面使用了包名作为其前缀。打包的过程可以具有默认参数，并且这些参数可以通过按位置或按名称对应的方式进行调用，就像独立过程的参数的调用方式一样。

上述调用方法还可以适用于包中用户定义的类型。例如，为了调用过程ClassList，我们需要声明一个类型为ClassPackage.t_StudentIDTable的变量。

DECLARE
v_HistoryStudents ClassPackage.t_StudentIDTable;
v_NumStudents BINARY_INTEGER := 20;
BEGIN
-- Fill the PL/SQL table with the first 20 History 101
-- students.
ClassPackage.ClassList('HIS', 101, v_HistoryStudents,
v_NumStudents);
-- Insert these students into temp_table.
FOR v_LoopCounter IN 1..v_NumStudents LOOP
INSERT INTO temp_table (num_col, char_col)
VALUES (v_HistoryStudents(v_LoopCounter),
'In History 101');
END LOOP;
END;

在包体内，包头中的对象可以直接引用，可以不用包名为其前缀。例如，过程RemoveStudent可以简单地使用e_StudentNotRegistered来引用异常，而不是用ClassPackage.e_StudentNotRegistered来引用。当然，如果需要，也可以使用全名进行引用。

包体中对象的作用域按照目前的程序，过程ClassPackage.AddStudent和ClassPackage。RemoveStudent只是简单地对表registered_student进行更新。实际上，该操作还不完整。这两个过程还要更新表students和classes以反映新增或删除的学生情况。如下所示，我们可以在包体中增加一个过程来实现上述操作。

CREATE OR REPLACE PACKAGE BODY ClassPackage AS
-- Utility procedure that updates students and classes to reflect
-- the change. If p_Add is TRUE, then the tables are updated for
-- the addition of the student to the class. If it is FALSE,
-- then they are updated for the removal of the student.
PROCEDURE UpdateStudentsAndClasses(
p_Add IN BOOLEAN,
p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
-- Number of credits for the requested class
v_NumCredits classes.num_credits%TYPE;
BEGIN
-- First determine NumCredits.
SELECT num_credits
INTO v_NumCredits
FROM classes
WHERE department = p_Department
AND course = p_Course;
IF (p_Add) THEN
-- Add NumCredits to the student's course load
UPDATE STUDENTS
SET current_credits = current_credits + v_NumCredits
WHERE ID = p_StudentID;
-- And increase current_students
UPDATE classes
SET current_students = current_students + 1
WHERE department = p_Department
AND course = p_Course;
ELSE
-- Remove NumCredits from the students course load
UPDATE STUDENTS
SET current_credits = current_credits - v_NumCredits
WHERE ID = p_StudentID;
-- And decrease current_students
UPDATE classes
SET current_students = current_students - 1
WHERE department = p_Department
AND course = p_Course;
END IF;
END UpdateStudentsAndClasses;
-- Add a new student for the specified class.
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
INSERT INTO registered_students (student_id, department, course)
VALUES (p_StudentID, p_Department, p_Course);
UpdateStudentsAndClasses(TRUE, p_StudentID, p_Department, p_Course);
END AddStudent;
-- Removes the specified student from the specified class.
PROCEDURE RemoveStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
DELETE FROM registered_students
WHERE student_id = p_StudentID
AND department = p_Department
AND course = p_Course;
-- Check to see if the DELETE operation was successful. If
-- it didn't match any rows, raise an error.
IF SQL%NOTFOUND THEN
RAISE e_StudentNotRegistered;
END IF;
UpdateStudentsAndClasses(FALSE, p_StudentID, p_Department,
p_Course);
END RemoveStudent;
...
END ClassPackage;

过程UpdateStudentAndclasses声明为包体的全局量，其作用域是包体本身。该过程可以由该包中的其他过程调用（如AddStudent和RemoveStudent），但是该过程在包体外是不可见的。

...
END ClassPackage;
BEGIN

7.1.3 重载

在包的内部，过程和函数可以被重载（Overloading）。也就是说，可以有一个以上的名称相同、但参数不同的过程或函数。由于重载允许将相同的操作施加在不同类型的对象上，因此，它是PL/SQL语言的一个重要特点。例如，假设我们要使用学生ID或该学生的姓和名来把一个学生加入到班级中，可以对包ClassPackage修改如下：

CREATE OR REPLACE PACKAGE ClassPackage AS
-- 将新的学生添加到指定的班级中
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE);
-- Also adds a new student, by specifying the first and last
-- names, rather than ID number.
PROCEDURE AddStudent(p_FirstName IN students.first_name%TYPE,
p_LastName IN students.last_name%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE);
...
END ClassPackage;
CREATE OR REPLACE PACKAGE BODY ClassPackage AS
-- Add a new student for the specified class.
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
INSERT INTO registered_students (student_id, department, course)
VALUES (p_StudentID, p_Department, p_Course);
END AddStudent;
-- Add a new student by name, rather than ID.
PROCEDURE AddStudent(p_FirstName IN students.first_name%TYPE,
p_LastName IN students.last_name%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
v_StudentID students.ID%TYPE;
BEGIN
/* First we need to get the ID from the students table. */
SELECT ID
INTO v_StudentID
FROM students
WHERE first_name = p_FirstName
AND last_name = p_LastName;
-- Now we can add the student by ID.
INSERT INTO registered_students (student_id, department, course)
VALUES (v_StudentID, p_Department, p_Course);
END AddStudent;
...
END ClassPackage;

对Music410增加一名学生如下：

BEGIN
ClassPackage.AddStudent(10000, 'MUS', 410);
END;

或

BEGIN
ClassPackage.AddStudent('Rita', 'Razmataz', 'MUS', 410);
END;

可以看到，同样的操作可以通过不同类型的参数实现，这就说明重载是非常有用的技术。虽然如此，但重载仍要受到一些限制。

如果两个子程序的参数仅在名称和模式上不同，那么，这两个过程不能重载。例如，下面的两个过程是不能重载的。

PROCEDURE overloadMe(p_TheParameter IN NUMBER);
PROCEDURE overloadMe(p_TheParameter OUT NUMBER);

也不能仅根据两个过程不同的返回类型对其进行重载。例如，下面的函数是不能进行重载的。

FUNCTION overloadMeToo RETURN DATE;
FUNCTION overloadMeToo RETURN NUMBER;

最后，重载函数的参数的类族（type family）必须不同，也就是说，不能对同类族的过程进行重载。例如，CHAR和VARCHAR2属于同一类族，所以不能重载下面的过程。

PROCEDURE OverloadChar(p_TheParameter IN CHAR);
PROCEDURE OverloadChar(p_TheParameter IN VARCHAR2);

注意

PL/SQL编译器实际上允许程序员创建违反上述限制的带有子程序的包。然而，PL/SQL运行时系统将无法解决引用问题并将引发“PLS-307:toomanydeclarationof‘subprogram’matchthiscall”的运行错误。

根据用户定义的对象类型，打包子程序也可以重载。例如，假设要创建下面两个对象类型。

CREATE OR REPLACE TYPE t1 AS OBJECT (f NUMBER）
CREATE OR REPLACE TYPE t2 AS OBJECT (f NUMBER);

我们可以创建一个包和一个带有根据其参数的对象类型重载的两个过程的包体。

CREATE OR REPLACE PACKAGE Overload AS
PROCEDURE Proc(p_Parameter1 IN t1);
PROCEDURE Proc(p_Parameter1 IN t2);
END Overload;
CREATE OR REPLACE PACKAGE BODY Overload AS
PROCEDURE Proc(p_Parameter1 IN t1) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Proc(t1): ' || p_Parameter1.f);
END Proc;
PROCEDURE Proc(p_Parameter1 IN t2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Proc(t2): ' || p_Parameter1.f);
END Proc;
END Overload;

如下例所示，根据参数的类型对过程进行正确的调用。

DECLARE
2 v_Obj1 t1 := t1(1);
3 v_OBj2 t2 := t2(2);
4 BEGIN
5 Overload.Proc(v_Obj1);
6 Overload.proc(v_Obj2);
7 END;
8 /
Proc(t1): 1
Proc(t2): 2
PL/SQL procedure successfully completed.

7.1.4 包的初始化

当第一次调用打包子程序时，该包将进行初始化。也就是说，将该包从硬盘中读入到内存，并启动调用的子程序的编译代码。这时，系统为该包中定义的所有变量分配内存单元。每个会话都有其打包变量的副本，以确保执行同一包子程序的两个对话使用不同的内存单元。

大多数情况下，初始化代码要在包第一次初始化时运行。要实现这种功能，可以在包体中所有对象之后加入初始化部分，其语法如下：

CREATE OR REPLACE PACKAGE BODY package_name{IS | AS}
...
BEGIN
initialization_code;
END [ package_name];

其中，package_name是包的名称，initialization_code是要运行的初始化代码。例如，下面的包实现了一个随机数函数。

CREATE OR REPLACE PACKAGE Random AS
-- Random number generator. Uses the same algorithm as the
-- rand() function in C.
-- Used to change the seed. From a given seed, the same
-- sequence of random numbers will be generated.
PROCEDURE ChangeSeed(p_NewSeed IN NUMBER);
-- Returns a random integer between 1 and 32767.
FUNCTION Rand RETURN NUMBER;
-- Same as Rand, but with a procedural interface.
PROCEDURE GetRand(p_RandomNumber OUT NUMBER);
-- Returns a random integer between 1 and p_MaxVal.
FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER;
-- Same as RandMax, but with a procedural interface.
PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,
p_MaxVal IN NUMBER);
END Random;
CREATE OR REPLACE PACKAGE BODY Random AS
/* Used for calculating the next number. */
v_Multiplier CONSTANT NUMBER := 22695477;
v_Increment CONSTANT NUMBER := 1;
/* Seed used to generate random sequence. */
v_Seed number := 1;
PROCEDURE ChangeSeed(p_NewSeed IN NUMBER) IS
BEGIN
v_Seed := p_NewSeed;
END ChangeSeed;
FUNCTION Rand RETURN NUMBER IS
BEGIN
v_Seed := MOD(v_Multiplier * v_Seed + v_Increment,
(2 ** 32));
RETURN BITAND(v_Seed/(2 ** 16), 32767);
END Rand;
PROCEDURE GetRand(p_RandomNumber OUT NUMBER) IS
BEGIN
-- Simply call RandMax and return the value.
p_RandomNumber := Rand;
END GetRand;
FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN MOD(Rand, p_MaxVal) + 1;
END RandMax;
PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,
p_MaxVal IN NUMBER) IS
BEGIN
-- Simply call RandMax and return the value.
p_RandomNumber := RandMax(p_MaxVal);
END GetRandMax;
BEGIN
/* Package initialization. Initialize the seed to the current
time in seconds. */
ChangeSeed(TO_NUMBER(TO_CHAR(SYSDATE, 'SSSSS')));
END Random;

为了检索随机数，我们可以直接调用函数Random.Rand。随机数序列是由其初始种子控制的，对于给定的种子可以生成相应的随机数序列。因此，为了提供更多的随机数值，我们要在每次实例化该包时，把随机数种子初始化为不同的值。要实现上述功能，就需要从包的初始部分调用过程ChangeSeed。

7.1.5 Oracle内置包

除了上述PL/SQL语言提供的功能外，Oracle也提供了若干具有特殊功能的内置包。这些特殊功能的包描述如下。

◎ DBMS_ALERT包：用于数据库报警，允许会话间通信。

◎ DBMS_JOB包：用于任务调度服务。

◎ DBMS_LOB包：用于大型对象操作。

◎ DBMS_PIPE包：用于数据库管道，允许会话间通信。

◎ DBMS_SQL包：用于执行动态SQL。

◎ UTL_FILE包：用于文本文件的输入与输出。

总的来说，所有的DBMS包都存储在服务器中，只有包UTL_FILE既存储在服务器端又存储在客户端，此外，在某些客户环境，Oracle还提供了一些额外的包。

7.2 集合

读者可能会问，为什么要使用集合？毕竟，使用带有外键的两个表已经允许创建数据之间的关联。答案是：相对于使用两个表，存储在集合中的数据可以被数据库更快地访问。通常情况下，如果数据只被一个表使用，那么就使用集合。PL/SQL提供了三种不同的集合类型：index-by表（PL/SQL 2.0及更高版本）、嵌套表（PL/SQL 8.0及更高版本）和可变数组（PL/SQL 8.0及更高版本）。如下所示的程序介绍了上述3种集合类型的使用方法。

DECLARE
TYPE Staff IS TABLE OF Employee;
staffer Employee;
FUNCTION new_hires (hiredate DATE) RETURN Staff IS
BEGIN ... END;
BEGIN
staffer := new_hires(’10-NOV-98’)(5);
...
END;

7.2.1 index-by表

index-by表类似于C语言中的数组，声明index-by表的方法如下：

TYPE tabletype IS TABLE OF type INDEX BY BINARU_INTEGER;

其中，tabletype是指所定义的新类型的类型名；type是要定义的index-by表的类型。

定义index-by表的代码如下：

TYPE Country_Name IS TABLE OF COUNTRIES.COUNTRIES_NAME%TYPE
        INDEX BY BINARY_INTEGER; --声明类型
v_Name Country_Name; --声明变量

声明了类型和变量后，就可以用下面的语句使用index-by表中的单个元素。

v_Name(index)

其中，index是指表中的第几个元素。

注意

index的数据类型为BINARY_INTEGER类型。

1．元素赋值

可以使用下面的语句给表中的元素赋值。

BEGIN
v_Name(1):=’China’;
v_Name(2):=’USA’;
END;

注意

index-by表中的元素不是按特定顺序排列的，这与C语言数组不同。在C语言中，数组在内存中是顺序存储的，因此，元素的下标也是有序的。

也就是说，下面的元素赋值是合法的。

BEGIN
v_Name(1):=’China’;
v_Name(-2):=’USA’;
v_Name(5):=’Italy’;
END;

注意

index-by表的元素的个数只受BINARY_INTEGER类型的限制，即index的范围为-2147483647～+2147483647。因此，只要在此范围内给元素赋值都是合法的。

2．调用不存在的元素

需要注意的是，调用index-by表的元素前必须首先给该元素赋值，否则，系统就会提示出错。

DECLARE
TYPE StudyTab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
v_StudyTab StudyTab;
BEGIN
FOR v_Count IN 1..5 LOOP
  v_StudyTab(v_Count):=v_Count*100;
END LOOP;
FOR v_Count IN 1..5 LOOP
  DBMS_OUTPUT.PUT_LINE(v_StudyTab(v_Count));
END LOOP;
END;

在上面的代码中，我们首先给各个元素赋值，然后将其输出，结果如下：

100
200
300
400
500
PL/SQL 过程已成功完成。

当我们将第二个FOR循环中的循环范围设置为1..6时，由于v_StudyTab(6)元素没有赋值，因此，系统会出现如下的错误信息。

100
200
300
400
500
DECLARE
*
ERROR 位于第 1 行:
ORA-01403: 未找到数据
ORA-06512: 在line 10

7.2.2 嵌套表

嵌套表的声明和index-by表的声明类似，具体声明如下：

TYPE table_name is TABLE OF table_type [NOT NULL];

注意

嵌套表与index-by表的唯一不同是没有了INDEX BY BINARU_INTEGER子句，因此，区别这两种类型的唯一方法就是看是否含有INDEX BY BINARU_INTEGER子句。

1．嵌套表初始化

嵌套表的初始化与index-by表的初始化是完全不同的。声明了类型后，再声明一个index-by表变量类型，如果没有给该表赋值，那么该表就是一个空index-by表，但是，在以后的语句中可以继续向index-by表中添加元素；而声明嵌套表变量类型时如果嵌套表中没有任何元素，那么，它会自动初始化为NULL，并且是只读的，如果还想向这个嵌套表中添加元素，系统就会提示出错。下面的代码很好地说明了嵌套表的初始化。

DECLARE
TYPE StudyTab IS TABLE OF VARCHAR2(20);
v_StudyTab StudyTab:=StudyTab('Tom','Jack','Rose');
BEGIN
FOR v_Count IN 1..3 LOOP
  DBMS_OUTPUT.PUT_LINE(v_StudyTab(v_Count));
END LOOP;
END;

以上是嵌套表正确初始化的过程，系统会输出如下信息：

Tom
Jack
Rose
PL/SQL 过程已成功完成。

当我们嵌套表初始化时没有元素，之后再向其中添加元素时，系统就会提示出错。

DECLARE
TYPE StudyTab IS TABLE OF VARCHAR2(20);
v_StudyTab StudyTab;
BEGIN
v_StudyTab(4):='Ronaldo';
END;
DECLARE
*
ERROR 位于第 1 行:
ORA-06531: 引用未初始化的收集
ORA-06512: 在line 5

如果对嵌套表初始化了，但是还向其中添加元素，则出错信息如下：

DECLARE
*
ERROR 位于第 1 行:
ORA-06533: 下标超出数量
ORA-06512: 在line 5

2．元素序列

嵌套表与index-by表十分相似，只是嵌套表在结构上是有序的，而index-by表是无序的，也就是说，如果给一个嵌套表赋值，那么嵌套表元素的index将会从1开始依次递增，代码如下所示。

DECLARE
TYPE NumTab IS TABLE OF NUMBER(4);
v_Num NumTab:=NumTab(1,3,4,5,7,9,11);
BEGIN
FOR v_Count IN 1..7 LOOP
  DBMS_OUTPUT.PUT_LINE( 'v_Num(' || v_Count || ')=' || v_Num(v_Count));
END LOOP;
END;

系统执行结果如下：

v_Num(1)=1
v_Num(2)=3
v_Num(3)=4
v_Num(4)=5
v_Num(5)=7
v_Num(6)=9
v_Num(7)=11

可以清楚地看到，嵌套表是有序的。

7.2.3 可变数组

可变数组的声明如下：

TYPE type_name IS {VARRAY|VARYING ARRAY}(maximum_size)
   OF element_type[NOT NULL];

其中：

◎ type_name是可变数组的类型名；

◎ maximum_size是指可变数组元素个数的最大值；

◎ element_type是指数组元素的类型。

可变数组的可变是指当定义了数组的最大上限后，数组元素的个数可以在此最大上限内变化，但不能超过最大上限，当数组元素的个数超过了最大上限后，系统就会提示出错。可变数组的存储和C语言的数组是相同的，各个元素在内存中是连续存储的。

下面是一个合法的可变数组的声明。

DECLARE
   --定义一个包含星期的可变数组
TYPE Dates IS VARRAY(7) OF VARCHAR2(10);
   --定义一个包含月份的可变数组
TYPE Months IS VARRAY(12) OF VARCHAR2(10);

与嵌套表一样，可变数组也需要初始化，初始化时需要注意的是，赋值的数量必须保证不大于可变数组的最大上限。下面是可变数组初始化的实例。

DECLARE
   TYPE Dates IS VARRAY(7) OF VARCHAR2(10);
v_Dates Dates:=Dates('Monday','Tuesday','Wednesday');
BEGIN
DBMS_OUTPUT.PUT_LINE(v_Dates(1));
DBMS_OUTPUT.PUT_LINE(v_Dates(2));
DBMS_OUTPUT.PUT_LINE(v_Dates(3));
END;

与嵌套数组一样，当元素个数超出了最大上限后，系统会提示出错，这里就不重复介绍了，读者可以将上例中最后一个输出语句的值改为v_Dates(4)。

7.2.4 集合的属性和方法

index-by表、嵌套表和可变数组都是对象类型，因此，其本身就有属性或方法，集合的属性和方法的调用与其他对象类型的调用是相同的：Object.Attribute或者Object.Method。

下面介绍集合类型的几种常用的属性和方法。

（1）COUNT：COUNT是一个整数，它是用来返回集合中的数组元素的个数，下例说明了该属性的用法。

DECLARE
  --定义三种集合类型
TYPE Name IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
  TYPE Pwd IS TABLE OF VARCHAR(20);
  TYPE Dates IS VARRAY(7) OF VARCHAR2(10);
  --声明集合并初始化后两种集合类型
v_Name Name;
  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no');
  v_Dates Dates:=Dates('Monday','Sunday');
BEGIN
  --初始化Index-By表
v_Name(1):='Tom';
  v_Name(-1):='Jack';
  v_Name(4):='Rose';
  --输出三种集合类型的元素个数
DBMS_OUTPUT.PUT_LINE('The Index-By Count is : '||v_Name.Count);
  DBMS_OUTPUT.PUT_LINE('The Nested Count is : '||v_Pwd.Count);
  DBMS_OUTPUT.PUT_LINE('The Varray Count is : '||v_Dates.Count);
END;

系统运行结果如下：

The Index-By Count is : 3
The Nested Count is : 5
The Varray Count is : 2
PL/SQL 过程已成功完成。

COUNT属性在PL/SQL编程中是十分有用的属性，对于那些集合元素的个数未知，而又想对其进行操作的模块是十分方便的。

（2）DELETE：DELETE用来删除集合中的一个或者多个元素。需要注意的是，由于DELETE方法执行的删除操作是大小固定的，所以，对于可变数组来说是没有DELETE方法的（如果对可变数组执行DELETE方法，将会出错）。DELETE有3种方式。

◎ DELETE：不带参数的DELETE方法，即将整个集合删除。

◎ DELETE(x)：即将位于集合表中第x个位置的元素删除。

◎ DELETE(x,y)：即将集合表中从第x个元素到第y个元素之间的所有元素删除。

注意

执行DELETE方法后，集合的COUNT值将会立刻发生变化；而且当要删除的元素不存在时，DELETE不会报错，而是跳过该元素，继续执行下一步操作。

下面是一个DELETE方法和COUNT属性联合使用的例子。

DECLARE
  TYPE Pwd IS TABLE OF VARCHAR(20);
  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left',
                'FootBall');
BEGIN
--输出表的原始数据元素的个数
DBMS_OUTPUT.PUT_LINE('The original table count is : ');
DBMS_OUTPUT.PUT_LINE(v_Pwd.count);
--删除一个元素后再输出表的元素的个数
v_Pwd.DELETE(4);
DBMS_OUTPUT.PUT_LINE('After delete a element, table count is : ');
DBMS_OUTPUT.PUT_LINE(v_Pwd.count);
--删除一些元素后输出表的元素的个数
v_Pwd.DELETE(6,8);
DBMS_OUTPUT.PUT_LINE('After delete some element, table count is : ');
DBMS_OUTPUT.PUT_LINE(v_Pwd.count);
END;

（3）EXISTS：EXISTS用来判断集合中的元素是否存在。具体用法如下：

EXISTS(X)，即判断位于位置X处的元素是否存在，如果存在，则返回TRUE；如果X大于集合的最大范围，则返回FALSE。

注意

使用EXISTS判断时，只要在指定位置处有元素存在即可，即使该处的元素为NULL，EXISTS也会返回TRUE。

（4）EXTEND：EXTEND用来将元素添加到集合末端，具体形式有以下几种。

◎ EXTEND：不带参数的EXTEND，将一个NULL元素添加到集合的末端。

◎ EXTEND(X)：该形式是将X个NULL元素添加到集合的末端。

◎ EXTEND(X,Y)：该形式是将X个位于Y的元素添加到集合的末端。

代码如下：

DECLARE
  TYPE Pwd IS TABLE OF VARCHAR(20);
  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left',
                'FootBall');
v_Count INTEGER;
BEGIN
--输出初始集合的最后一个元素
v_Count:=v_Pwd.LAST;
DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count));
--向集合添加了两个'12345'（位于第2的元素）后，输出集合的最后一个元素
v_Pwd.EXTEND(2,'2');
v_Count:=v_Pwd.LAST;
DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count));
--在集合末端添加两个NULL值，再将最后一个位置赋值，并输出
v_Pwd.EXTEND(2);
v_Count:=v_Pwd.LAST;
v_Pwd(15):='Basketball';
DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count));
END;

系统运行结果如下：

FootBall
12345
Basketball
PL/SQL 过程已成功完成。

注意

由于Index-by表元素的随意性，因此，EXTEND方法只对嵌套表和可变数组有效。

（5）FIRST和LAST：FIRST用来返回集合中第一个元素的位置，而LAST则是返回集合的最后一个元素的位置。

（6）LIMIT：LIMIT用来返回集合中的最大元素的个数。由于嵌套表没有上限，所以，当嵌套表使用LIMIT时，总是返回NULL值。示例如下：

DECLARE
  TYPE Pwd IS TABLE OF VARCHAR(20);
  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left',
                 'FootBall');
  TYPE Name IS VARRAY(20) OF VARCHAR(20);
  v_Name Name:=Name('10000','12345','22','yes','no','OK','All','Hello','Right','Left',
                    'FootBall');
BEGIN
--分别输出嵌套表和可变数组的LIMIT值
DBMS_OUTPUT.PUT_LINE('The NestedTable Limit is : '||v_Pwd.LIMIT);
DBMS_OUTPUT.PUT_LINE('The VarrayTable Limit is : '||v_Name.LIMIT);
END;

系统运行结果如下：

The NestedTable Limit is :
The VarrayTable Limit is : 20
PL/SQL 过程已成功完成。

（7）NEXT和PRIOR：NEXT和PRIOR使用时后面会接一个参数，形式如下：

NEXT(X),PRIOR(X)

其中，NEXT(X)返回的是位置为X处的元素后的元素，而PRIOR(X)则刚好相反，它返回的是X处的元素前的元素。

通常NEXT和PRIOR与FIRST和LAST一起使用，用来进行循环处理。

DECLARE
  TYPE Pwd IS TABLE OF VARCHAR(20);
  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left', 'FootBall');
  v_Count INTEGER;
BEGIN
v_Count:=v_Pwd.FIRST;
WHILE v_Count<=v_Pwd.LAST LOOP
  DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count));
  v_Count:=v_Pwd.NEXT(v_Count);
END LOOP;
END;

系统执行结果如下：

10000
12345
22
yes
no
OK
All
Hello
Right
Left
FootBall
PL/SQL 过程已成功完成。

上例中，我们是顺序打印集合中的元素，其实通过灵活运用这4个函数，我们还可以反向打印集合中的各个元素，具体代码如下：

DECLARE
  TYPE Pwd IS TABLE OF VARCHAR(20);
  v_Pwd Pwd:=Pwd('10000','12345','22','yes','no','OK','All','Hello','Right','Left','FootBall');
  v_Count INTEGER;
BEGIN
v_Count:=v_Pwd.LAST;
WHILE v_Count>=v_Pwd.FIRST LOOP
  DBMS_OUTPUT.PUT_LINE(v_Pwd(v_Count));
  v_Count:=v_Pwd.PRIOR(v_Count);
END LOOP;
END;

（8）TRIM：TRIM方法用来删除集合末端的元素。

其具体形式如下。

◎ TRIM：不带参数的TRIM是从集合末端删除一个元素。

◎ TRIM(X)：该形式是从集合末端删除X个元素，其中，X要小于集合的COUNT数，否则，系统会提示出错。

注意

与EXTEND相同的是，由于Index-by表元素的随意性，因此，TRIM方法只对嵌套表和可变数组有效。

7.3 PL/SQL游标

游标是用来处理使用SELECT语句从数据库中检索到的多行记录的工具。借助于游标，数据库应用程序可以对一组记录逐个进行处理，每次处理一行。

下面是一个简单的游标实例，其功能是检索数据库中所有学生的名和姓。

DECLARE
v_FirstName VARCHAR2(20);
v_LastName VARCHAR2(20);
--游标声明
--返回行
CURSOR c_Students IS
SELECT first_name, last_name
FROM students;
BEGIN
-- 启动游标进程
OPEN c_Students;
LOOP
-- 获取一行
FETCH c_Students INTO v_FirstName, v_LastName;
-- Exit the loop after all rows have been retrieved.
EXIT WHEN c_Students%NOTFOUND;
/* 数据过程 */
END LOOP;
-- 过程结束
CLOSE c_Students;
END;

7.3.1 创建游标

Oracle使用工作区执行SQL语句、存储处理信息，PL/SQL可以构造游标、命名工作区、访问存储的信息。PL/SQL用游标来管理SQL的select语句，游标是为处理这些语句而分配的一大块内存。用户也可以手工定义游标。游标定义类似于其他PL/SQL变量，并且必须遵守同样的命名规则。本节将介绍显式游标（Explicit Cursor）和隐式游标（Implicit Cursor）。显式游标必须声明（Declare），并且在使用前要打开（Open），使用完毕要关闭（Close）。使用隐式游标时，用户无须执行上述步骤，只要简单地编码Select语句并让PL/SQL根据需要处理游标即可。

与循环结构结合的显式游标处理返回多于一行的Select语句，与循环结合的游标将允许每次处理一行。当Select语句预计只返回一行时，隐式游标将更合适。

显式游标是作为declare段中的一部分进行定义的，所定义的SQL语句必须只包含select语句，并且不能用insert,update或delete关键字。当select语句可能返回零或多于一行时，就必须用显式游标。本节主要介绍以下内容：

◎ 命名自己的显式游标；

◎ 准备（或打开）显式游标；

◎ 使用显式游标取数据；

◎ 用完后，释放游标所占内存。

1．显式游标

在使用显式游标时，必须编写4部分代码：

◎ 在PL/SQL块的declare段中定义游标；

◎ 在PL/SQL块中初始begin后打开游标；

◎ 取游标到一个或多个变量中，在接收游标的fetch语句中，接收变量的数目必须与游标的select列表中的表列数目一致；

◎ 使用完游标后要关闭。

下例综合了上述4个部分。

...
. . .
declare
fname varchar2(10);
lname varchar2(30);
ssec_num varchar2(8);
cursor region_cur is
select first_name,last_name,ssn
from person
where region_number = region_number_in;
begin
open region_cur;
fetch region_cur into fname,lname,ssec_num;
while region_cur%found
loop
if ssec_num is null then
insert into e_msg values(pin_in,'no ssnum');
else
insert into e_tab values(pin_in,sysdate);
end if;
fetch region_cur into fname,lname,ssec_num;
end loop;
close region_cur;
end;
. . .
. . .
/

关于显式游标要注意以下几点。

（1）用“%found”和“%notfound”检验游标（此处为“mycur”）成功与否。如果游标按照其选择条件从数据库中查询出一行数据，则返回成功。该测试必须在游标关闭前执行。

if mycur%found then
. . .
end if;
if mycur%notfound then
. . .
. . .
end if;
. . .
. . .
fetch mycur into temp_buffer;
close mycur;
--由于游标已被关闭，下面的语句不被执行
if mycur%found then
. . .
. . .
end if;

（2）循环执行游标取数操作时，检索出的总数据行数存放在系统变量“%rowcount”中。

while counter<100 loop
fetch mycur into temp_buffer;
if mycur%rowcount <=50 then
. . .
else
. . .
end if;
counter := counter+1;
end loop;

（3）所有游标必须被取至一个或多个变量（取决于游标Select列表中的列数）。下面的语句是非法的。

open mycur;
fetch mycur;
if mycur%found then
. . .

（4）游标的目标变量必须与游标Select表中表列的数据类型一致。

--以下用法正确
declare
cursor mycur is
select pin,/* pin是数字型数据* /
last_name/*last_name是字符型数据* /
from person
where pin = pin_in;
field1 varchar2(10);
field2 number;
begin
open mycur;
fetch mycur into field2,field1 ;
. . .
--以下用法不正确，因为变量数据类型同游标Select列表中表列的类型不一致
declare
cursor mycur is
select pin,/* pin是数字型数据 * /
last_name/* last_name是字符型数据 */
from person
where pin = pin_in;
field1 varchar2(10);
field2 number;
begin
open mycur;
fetch mycur into field1,field2;
. . .

（5）如果试图打开一个已打开的游标或关闭一个已关闭的游标，将会出现错误。因此，用户在打开或关闭游标前，若不清楚其状态，应该用“%isopen”进行检查。根据其返回值为true或false，采取相应的动作。

. . .
. . .
if mycur%isopen then
null;
else
open mycur;
end if;

如果一个PL/SQL块中用了多个游标，那么每个游标的名字必须唯一。

2．隐式游标

下述代码段使用了隐式游标。如果把select语句直接安排在行中，PL/SQL会隐式地处理游标定义。在declare段中无隐式游标说明。

. . .
begin
if counter >= 20 then
select last_name
into lname
from person
where pin = pin_in;
else
…
end if;
…
end ;
/

使用隐式游标要注意以下几点。

（1）每个隐式游标必须有一个into。

--以下不正确
if this_value > 0 THEN
select count(*) from person;
end if;
--以下正确
if this_value > 0 then
select count(*) into cnter from person;
end if;

（2）与显式游标一样，带有关键字“into”接收数据的变量时，数据类型要与表列的类型一致。

（3）隐式游标一次仅返回一行，使用时必须检查表给出的异常。最常见的异常为“no_data_found”和“too_many_rows”。

. . .
if counter>=10 then
begin
select age
into v_age
from person
where pin =pin_value;
exception
when too_many_rows then
insert into taba values(pin_value,sysdate);
when no_data_found then
end;
end if;
. . .
. . .
/

推荐使用显式游标，原因如下：

◎ 通过检查PL/SQL的系统变量“%found”或“%notfound”确认成功或失败。使用显式游标的代码段简单地检测这些系统变量以确定使用显示游标的select语句成功或失败。

◎ 显式游标是在declare段中由人工定义的，这样，PL/SQL块的结构化程度更高，因为定义和使用分离开了。

◎ 游标的FOR循环减少了代码的数量，更容易按过程化处理。

7.3.2 使用游标的FOR循环

本节介绍游标的FOR循环，这部分要求用户忽视显式游标。游标的FOR循环的优点是用户不需打开游标、取数据、测试数据的存在（%found）、关闭游标或定义存放数据的变量。当游标被调用时，用select语句的元素创建一条记录。对于由游标检索出的每一行继续执行循环内的全部代码，当没有数据发现时，游标自动关闭。该方法要求最少的编程，得到很少游标结构错误的结果。该方法可以减少编程代码，并产生较少的游标结构错误。

前面的例子用游标的FOR循环重写如下：

. . .
. . .
declare
cursor region_cur is
select first_name,last_name,ssn
from person
where region_number = region_number_in;
begin
for region_rec in region_cur
loop
--请注意：在Select语句中所有列自动地产生record_name.column_name(即:region_rec.last_name)
if region_rec.ssn is null then
insert into e_msg values(pin_in,'no ssnum');
e l se
insert into e_tab values(pin_in,sysdate);
end if;
end loop;
end;
. . .
. . .
/

下面的PL/SQL块，用于计算结果，并将其存入到临时表中。FOR循环指示c1_rec是隐式声明的，作为一个记录，其域用来存储从游标c1提取出来的所有列值。

-- available online in file ’examp7’
DECLARE
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
FOR c1_rec IN c1 LOOP
/*计算和存储结果 */
result := c1_rec.n2/(c1_rec.n1 + c1_rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
COMMIT;
END;

当输入游标FOR循环时，游标名已经不输入一个使用OPEN贷款的游标。每个FOR循环交互时，PL/SQL将数据提取到隐式声明的记录中，该记录只能在循环内部定义，不能在循环外面参考域。

我们不需要声明游标，因为PL/SQL能够替换子查询。下面的游标FOR循环计算奖金，将结果插入到表中。

DECLARE
bonus REAL;
BEGIN
FOR emp_rec IN (SELECT empno, sal, comm FROM emp) LOOP
bonus := (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25);
INSERT INTO bonuses VALUES (emp_rec.empno, bonus);
END LOOP;
COMMIT;
END;

可以为游标FOR循环中的表达式值定义别名，使用隐式声明记录的域可以保存最近提取的列值。该域与对应的SELECT查询列中的列名一致，但是，如果选择项是一个表达式，会发生什么样的情况？例如下面的示例。

CURSOR c1 IS
SELECT empno, sal+NVL(comm,0), job FROM ...

在这种情况下，必须为所选择的项目定义一个别名，下面的示例中，wages就是所选项目sal+NVL(comm,0)的别名。

CURSOR c1 IS
SELECT empno, sal+NVL(comm,0) wages, job FROM ...

若要引用相应的域，可以使用别名。

IF emp_rec.wages < 1000 THEN ...

在游标FOR循环中，可以传递参数给游标。下面的示例中，传递了部门号作为游标参数。

-- available online in file ’examp8’
DECLARE
CURSOR emp_cursor(dnum NUMBER) IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11,2) := 0;
high_paid NUMBER(4) := 0;
higher_comm NUMBER(4) := 0;
BEGIN
/* The number of iterations will equal the number of rows
returned by emp_cursor. */
FOR emp_record IN emp_cursor(20) LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal +
emp_record.comm;
IF emp_record.sal > 2000.00 THEN
high_paid := high_paid + 1;
END IF;
IF emp_record.comm > emp_record.sal THEN
higher_comm := higher_comm + 1;
END IF;
END LOOP;
INSERT INTO temp VALUES (high_paid, higher_comm,
’Total Wages: ’ || TO_CHAR(total_wages));
COMMIT;
END;

7.3.3 使用游标变量

与游标类似，游标变量指向多行查询的结果集的当前行，但是，游标与游标变量是不同的，类似常量和变量之间的关系。游标是静态的，游标变量是动态的，因为它并不与特定的查询绑定在一起。可以为任何兼容的查询打开游标变量，从而提供更好的灵活性。而且，我们可以将新的值赋予游标变量，将它作为参数传递给本地和存储子程序。

游标变量在每个PL/SQL客户都是可用的，可以在PL/SQL主环境中，如OCI和Pro*C程序中声明游标变量，然后作为输入变量传递给PL/SQL。而且，应用开发工具，如Oracle Forms和Oracle Report，包含PL/SQL引擎，也可以在客户端完全使用游标变量。

Oracle服务器同样包含PL/SQL引擎，因此，可以将游标变量在应用和服务器之间进行传递。

1．游标变量

游标变量就像C和Pascal指针一样，保存某个项目的内存位置，而不是项目本身。因此，声明游标实质是创建一个指针，而不是项目。在PL/SQL中，指针具有数据类型REF X，REF是REFERENCE，X表示类对象。因此，游标变量具有数据类型REF CURSOR。

为了执行多行查询，Oracle打开未命名工作域，存储处理信息。若要访问这些信息，可以使用显示游标，它对工作区命名，或者使用游标变量指向工作区。游标总是指向相同的查询工作区，游标变量能够指向不同的工作区，因此，游标和游标变量不能互操作。

2．使用游标变量

使用游标变量能够在PL/SQL存储子程序和大量的客户之间传递查询结果集。PL/SQL和任何客户都不拥有一个结果集；它们只是共享一个查询工作区，在这个工作区中存储了查询结果。例如，OCI客户、Oracle Form应用和Oracle服务器都能指向相同的工作区。

只要游标执行查询工作区，它就可以被访问，因此，可以从一个范围到另一个范围之间传递游标变量的值。例如，如果将主游标变量嵌套在Pro*C程序的PL/SQL中，游标指向的工作区能够被访问。

3．定义REF CURSOR类型

创建游标变量包含两个步骤，第一个是定义REF CURSOR类型，然后声明这种类型的游标变量，可以在PL/SQL块、子程序和包中定义REF CURSOR类型，语法如下：

TYPE ref_type_name IS REF CURSOR [RETURN return_type];

其中，ref_type_name是在游标变量中使用的类型，return_type必须表示一个记录或者是数据库表的一行。下面的示例中，定义了返回类型，表示数据库表dept的一行。

DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

REF CURSOR类型可以是strong，也可以是weak。在下面的示例中，一个强REF CURSOR类型定义了返回类型，但是弱定义没有。

DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong
TYPE GenericCurTyp IS REF CURSOR; -- weak

4．声明游标变量

一旦定义了REF CURSOR类型，就可以在PL/SQL块或者子程序中声明游标变量。例如：

DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
dept_cv DeptCurTyp; -- declare cursor variable

在REF CURSOR类型定义的RETURN字句中，可以使用%ROWTYPE定义记录类型，表示强类型的游标变量返回的行，代码如下：

DECLARE
TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
tmp_cv TmpCurTyp; -- declare cursor variable
TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
emp_cv EmpCurTyp; -- declare cursor variable

同样，可以使用%TYPE提供记录变量的数据类型，格式如下：

DECLARE
dept_rec dept%ROWTYPE; -- declare record variable
TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
dept_cv DeptCurTyp; -- declare cursor variable

最后，在RETURN字句中定义了用户自定义的RECORD类型。

DECLARE
TYPE EmpRecTyp IS RECORD (
empno NUMBER(4),
ename VARCHAR2(1O),
sal NUMBER(7,2));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
emp_cv EmpCurTyp; -- declare cursor variable

还可以声明游标变量作为函数和过程的参数。在下面的示例中，定义了REF CURSOR 类型EmpCurTyp，然后声明这种类型的游标变量作为过程的参数。

DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS ...

5．控制游标变量

可以使用三种语句控制游标变量：OPEN-FOR、FETCH和CLOSE。首先，使用OPEN打开游标变量。然后，使用FETCH从结果集中提取行，当所有行都处理完毕后，使用CLOSE关闭游标变量。

OPEN-FOR语句与多行查询的游标相关联，执行查询，标识结果集，语法如下：

OPEN {cursor_variable | :host_cursor_variable}FOR
{ select_statement
| dynamic_string [USING bind_argument[, bind_argument]...]};

在下面的示例中，首先打开游标变量emp_cv。注意：可以将游标属性（%FOUND，%NOTFOUND，%ISOPEN和%ROWCOUNT）应用到游标变量。

IF NOT emp_cv%ISOPEN THEN
/* Open cursor variable. */
OPEN emp_cv FOR SELECT * FROM emp;
END IF;

其他OPEN-FOR语句可以为不同的查询打开相同的游标变量，因此，在重新打开之前，不要关闭游标变量。而且，可以打开游标将其作为参数传给存储过程。例如，下面的示例表示存储过程打开游标变量emp_cv。

CREATE PACKAGE emp_data AS
...
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);
END emp_data;
CREATE PACKAGE BODY emp_data AS
...
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
END emp_data;

当声明一个游标变量作为打开游标变量子程序的参数时，必须定义IN OUT模式。也就是说，子程序可以将一个打开的游标传递给调用者。

可以使用单独的过程打开游标变量，在不同的包中定义REF CURSOR类型，然后在单独的过程中引用这种类型。例如，如果创建了下面的包体，可以创建一个引用它定义的类型的独立过程。

CREATE PACKAGE cv_types AS
TYPE GenericCurTyp IS REF CURSOR;
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
...
END cv_types;

在下面的示例中，可以创建一个独立的过程，引用REF CURSOR类型empCurTyp，它是在cv_types中定义的。

CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS
BEGIN
OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;

为了集中提取数据，可以将兼容的查询分组，在下面的示例中，包过程用一个选择因子作为参数。调用时，过程打开游标变量emp_cv。

CREATE PACKAGE emp_data AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT);
END emp_data;
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
ELSIF choice = 2 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
ELSIF choice = 3 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
END IF;
END;
END emp_data;

为了更灵活，可以将游标变量和选择因子作为参数传给存储过程。例如：

CREATE PACKAGE admin_data AS
TYPE GenCurTyp IS REF CURSOR;
PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT);
END admin_data;
CREATE PACKAGE BODY admin_data AS
PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
END;
END admin_data;

可以在PL/SQL主环境中声明游标变量，要使用游标变量，必须将它作为主变量传递给PL/SQL。如下面的Pro*C示例，将主游标变量和选择因子传给PL/SQL块。

EXEC SQL BEGIN DECLARE SECTION;
...
/*声明主游标变量 */
SQL_CURSOR generic_cv;
int choice;
EXEC SQL END DECLARE SECTION;
...
/*初始化主游标变量 */
EXEC SQL ALLOCATE :generic_cv;
...
/*将主游标变量和选择因子传给PL/8QL块 */
EXEC SQL EXECUTE
BEGIN
IF :choice = 1 THEN
OPEN :generic_cv FOR SELECT * FROM emp;
ELSIF :choice = 2 THEN
OPEN :generic_cv FOR SELECT * FROM dept;
ELSIF :choice = 3 THEN
OPEN :generic_cv FOR SELECT * FROM salgrade;
END IF;
END;
END-EXEC;

主游标变量域任何查询的返回类型都是兼容的，就像弱类型的PL/SQL游标变量一样。

使用FETCH语句从多行查询的结果集中提取行，语法格式如下：

FETCH {cursor_variable_name | :host_cursor_variable_name}
[BULK COLLECT]
INTO {variable_name[, variable_name]... | record_name};

在下面的示例中，一次提取一行。

LOOP
/*从游标变量中提取行 */
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched
-- process data record
END LOOP;

使用BULK COLLECT字句，可以从游标变量中提取多行，放入一个或多个集合中。例如：

DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE NameList IS TABLE OF emp.ename%TYPE;
TYPE SalList IS TABLE OF emp.sal%TYPE;
emp_cv EmpCurTyp;
names NameList;
sals SalList;
BEGIN
OPEN emp_cv FOR SELECT ename, sal FROM emp;
FETCH emp_cv BULK COLLECT INTO names, sals;
...
END;

使用完游标变量后，需要管理游标变量，语法格式如下：

CLOSE {cursor_variable_name | :host_cursor_variable_name};

在下面的示例中，当处理完最后一行后，关闭游标变量。

LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
-- process data record
END LOOP;
/*关闭游标变量 */
CLOSE emp_cv;

6．将主游标变量传递给PL/SQL，减少网络通信量

当向PL/SQL传递游标变量时，可以将OPEN-FOR语句分组，减少网络通信。例如，下面的PL/SQL块打开5个游标变量。

/* anonymous PL/SQL block in host environment */
BEGIN
OPEN :emp_cv FOR SELECT * FROM emp;
OPEN :dept_cv FOR SELECT * FROM dept;
OPEN :grade_cv FOR SELECT * FROM salgrade;
OPEN :pay_cv FOR SELECT * FROM payroll;
OPEN :ins_cv FOR SELECT * FROM insurance;
END;

在Oracle Form中是非常有用的，可以生成多块表单。

当将主游标变量传递于PL/SQL块时，查询工作区可访问。例如：

BEGIN
OPEN :c1 FOR SELECT 1 FROM dual;
OPEN :c2 FOR SELECT 1 FROM dual;
OPEN :c3 FOR SELECT 1 FROM dual;
OPEN :c4 FOR SELECT 1 FROM dual;
OPEN :c5 FOR SELECT 1 FROM dual;
...
END;

赋予C1,C2,C3,C4和C5的游标能够正常工作，可以使用，使用完毕后，将其释放即可。

BEGIN
CLOSE :c1;
CLOSE :c2;
CLOSE :c3;
CLOSE :c4;
CLOSE :c5;
...
END;

7．避免游标变量中的错误

如果游标变量包含一个强类型，则必须具有相同的数据类型。在下面的示例中，即使游标变量具有相同的返回类型，赋值也会产生异常，因为它们具有不同的数据类型。

DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (
emp_cv IN OUT EmpCurTyp,
tmp_cv IN OUT TmpCurTyp) IS
BEGIN
...
emp_cv := tmp_cv; -- causes ’wrong type’ error
END;

如果从不指向查询工作区的游标变量提取数据、关闭和应用游标属性，PL/SQL将产生INVALID_ CURSOR异常，可以使用两种方式指向查询工作区。

◎ 为查询打开一个游标变量；

◎ 将已经打开的主游标变量的值赋予游标变量。

下面的示例显示了这些方法是如何交互的。

DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
emp_cv1 EmpCurTyp;
emp_cv2 EmpCurTyp;
emp_rec emp%ROWTYPE;
BEGIN
/* The following assignment is useless because emp_cv1
does not point to a query work area yet. */
emp_cv2 := emp_cv1; -- useless
/* Make emp_cv1 point to a query work area. */
OPEN emp_cv1 FOR SELECT * FROM emp;
/* Use emp_cv1 to fetch first row from emp table. */
FETCH emp_cv1 INTO emp_rec;
/* The following fetch raises an exception because emp_cv2
does not point to a query work area yet. */
FETCH emp_cv2 INTO emp_rec; -- raises INVALID_CURSOR
EXCEPTION
WHEN INVALID_CURSOR THEN
/* Make emp_cv1 and emp_cv2 point to same work area. */
emp_cv2 := emp_cv1;
/* Use emp_cv2 to fetch second row from emp table. */
FETCH emp_cv2 INTO emp_rec;
/* Reuse work area for another query. */
OPEN emp_cv2 FOR SELECT * FROM old_emp;
/* Use emp_cv1 to fetch first row from old_emp table.
The following fetch succeeds because emp_cv1 and
emp_cv2 point to the same query work area. */
FETCH emp_cv1 INTO emp_rec; -- succeeds
END;

传递作为参数的游标变量时需要小心，如果实际参数和正常参数不兼容，PL/SQL将会产生ROWTYPE_ MISMATCH的异常。

在下面的Pro*C示例中，定义了REF CURSOR及返回类型emp%ROWTYPE，然后，创建了一个引用新类型的独立过程，接着，在PL/SQL块中，打开了主游标变量，用于查询dept表，最后将打开的游标变量作为参数传给了存储过程，由于实际返回类型与参数类型不兼容，PL/SQL将产生ROWTYPE_ MISMATCH。

CREATE PACKAGE cv_types AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
...
END cv_types;
/
CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS
BEGIN
OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
/
-- anonymous PL/SQL block in Pro*C program
EXEC SQL EXECUTE
BEGIN
OPEN :cv FOR SELECT * FROM dept;
...
open_emp_cv(:cv); -- raises ROWTYPE_MISMATCH
END;
END-EXEC;

7.3.4 游标变量实例

由于游标和游标变量的使用十分灵活，而且其功能也十分强大，因此，下面将介绍几个游标和游标变量的应用实例。

1．主表和细节表

下面的存储过程搜索主图书馆的数据库，查询数、日期和磁带。主表存储了title和category代码，三个细节表存储了与类别相关的信息。调用时，过程搜索主表，使用相关的类别代码，提取OPEN-FOR语句，然后打开游标变量。

CREATE PACKAGE cv_types AS
TYPE LibCurTyp IS REF CURSOR;
...     
END cv_types;
CREATE PROCEDURE find_item (
title VARCHAR2(100),
lib_cv IN OUT
cv_types.LibCurTyp)
AS
code BINARY_INTEGER;
BEGIN
SELECT item_code FROM titles INTO code
WHERE item_title = title;
IF code = 1 THEN
OPEN lib_cv FOR SELECT * FROM books
WHERE book_title = title;
ELSIF code = 2 THEN
OPEN lib_cv FOR SELECT * FROM periodicals
WHERE periodical_title = title;
ELSIF code = 3 THEN
OPEN lib_cv FOR SELECT * FROM tapes
WHERE tape_title = title;
END IF;
END find_item;

2．客户端PL/SQL块

在分支图书馆的客户端应用可以使用下面的PL/SQL块显示提取的信息。

DECLARE
lib_cv cv_types.LibCurTyp;
book_rec books%ROWTYPE;
periodical_rec periodicals%ROWTYPE;
tape_rec tapes%ROWTYPE;
BEGIN
get_title(:title); -- title is a host variable
find_item(:title, lib_cv);
FETCH lib_cv INTO book_rec;
display_book(book_rec);
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN
BEGIN
FETCH lib_cv INTO periodical_rec;
display_periodical(periodical_rec);
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN
FETCH lib_cv INTO tape_rec;
display_tape(tape_rec);
END;
END;

3．Pro*C程序

下面的Pro*C程序提示用户选择数据库表，打开游标变量，提取查询返回的行。

#include <stdio.h>
#include <sqlca.h>
void sql_error();
main()
{
char temp[32];
EXEC SQL BEGIN DECLARE SECTION;
char * uid = "scott/tiger";
SQL_CURSOR generic_cv; /*游标变量 */
int table_num; /*选择因子 */
struct /* EMP record */
{
int emp_num;
char emp_name[11];
char job_title[10];
int manager;
char hire_date[10];
float salary;
float commission;
int dept_num;
}emp_rec;
struct /* DEPT 记录*/
{
int dept_num;
char dept_name[15];
char location[14];
}dept_rec;
struct /* BONUS 记录 */
{
char emp_name[11];
char job_title[10];
float salary;
}bonus_rec;
EXEC SQL END DECLARE SECTION;
/*处理Oracle错误 */
EXEC SQL WHENEVER SQLERROR DO sql_error();
/*连接Oracle */
EXEC SQL CONNECT :uid;
/*初始化游标变量 */
EXEC SQL ALLOCATE :generic_cv;
/* Exit loop when done fetching. */
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
printf("\n1 = EMP, 2 = DEPT, 3 = BONUS");
printf("\nEnter table number (0 to quit): ");
gets(temp);
table_num = atoi(temp);
if (table_num <= 0) break;
/*打开游标变量 */
EXEC SQL EXECUTE
BEGIN
IF :table_num = 1 THEN
OPEN :generic_cv FOR SELECT * FROM emp;
ELSIF :table_num = 2 THEN
OPEN :generic_cv FOR SELECT * FROM dept;
ELSIF :table_num = 3 THEN
OPEN :generic_cv FOR SELECT * FROM bonus;
END IF;
END;
END-EXEC;
for (;;)
{
switch (table_num)
{
case 1: /* Fetch row into EMP record. */
EXEC SQL FETCH :generic_cv INTO :emp_rec;
break;
case 2: /* Fetch row into DEPT record. */
EXEC SQL FETCH :generic_cv INTO :dept_rec;
break;
case 3: /* Fetch row into BONUS record. */
EXEC SQL FETCH :generic_cv INTO :bonus_rec;
break;
}
/*处理数据记录 */
}
/*关闭游标变量 */
EXEC SQL CLOSE :generic_cv;
}
exit(0);
}
void sql_error()
{
/* Handle SQL error here. */
}

4．在SQL*Plus中操纵主变量

主变量是在主环境中声明的变量，可以传给一个或者多个PL/SQL程序，与其他任何变量一样使用。在SQL*Plus中，声明主变量使用VARIABLE命令。例如：

VARIABLE return_code NUMBER

SQL*Plus和PL/SQL都能引用主变量，SQL*Plus能够显示它的值。然而，在PL/SQL中引用主变量，必须带有冒号前缀。例如：

DECLARE
...
BEGIN
:return_code := 0;
IF credit_check_ok(acct_no) THEN
:return_code := 1;
END IF;
...
END;

要在SQL*Plus中显示主变量的值，需使用PRINT命令，格式如下：

SQL> PRINT return_code
RETURN_CODE
-----------
1

SQL*Plus数据类型REFCURSOR用来声明游标变量，能够从存储的子程序中返回查询结果，下面的代码中，声明了一个主变量REFCURSOR，使用SQL*Plus命令SET AUTOPRINT ON自动显示查询结果。

CREATE PACKAGE emp_data AS
TYPE EmpRecTyp IS RECORD (
emp_id NUMBER(4),
emp_name VARCHAR2(10),
job_title VARCHAR2(9),
dept_name VARCHAR2(14),
dept_loc VARCHAR2(13));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
PROCEDURE get_staff (
dept_no IN NUMBER,
emp_cv IN OUT EmpCurTyp);
END;
/
CREATE PACKAGE BODY emp_data AS
PROCEDURE get_staff (
dept_no IN NUMBER,
emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR
SELECT empno, ename, job, dname, loc FROM emp, dept
WHERE emp.deptno = dept_no AND emp.deptno = dept.deptno
ORDER BY empno;
END;
END;
/
COLUMN EMPNO HEADING Number
COLUMN ENAME HEADING Name
COLUMN JOB HEADING JobTitle
COLUMN DNAME HEADING Department
COLUMN LOC HEADING Location
SET AUTOPRINT ON
VARIABLE cv REFCURSOR
EXECUTE emp_data.get_staff(20, :cv)

7.3.5 使用游标表达式

一个游标表达式返回一个嵌套游标，结果集中的每行都包含值加上子查询生成的游标。然而，单个查询能够从多个表中提取相关的值。可以使用嵌套循环处理这个结果集，首先提取结果集的行，然后提取这些行中的嵌套游标。

PL/SQL支持使用游标表达式的查询，作为游标声明的一部分，可以在动态SQL查询中使用游标表达式，语法格式如下：

CURSOR ( subquery )

嵌套游标在包含的行从父游标中提取时被打开。只有在下面的情形下，嵌套游标才被关闭。

◎ 嵌套游标被用户显式关闭；

◎ 父游标被重新执行；

◎ 父游标被关闭；

◎ 父游标被取消；

◎ 提取父游标时出现错误。

下面是一个嵌套游标表达式的示例，在这个例子中，我们定义了位置ID和一个游标，能够从中提取该位置的所有部门。当提取部门名称时，还可以获得另一个游标，使得我们能够从另一个表中提取雇员信息。

CREATE OR REPLACE procedure emp_report(p_locid number) is
TYPE refcursor is ref cursor;
-- The query returns only 2 columns, but the second column is
-- a cursor that lets us traverse a set of related information.
CURSOR c1 is
SELECT l.city,
CURSOR(SELECT d.department_name,
CURSOR(SELECT e.last_name
FROM employees e
WHERE e.department_id = d.department_id) as ename
FROM departments d where l.location_id = d.location_id) dname
FROM locations l
WHERE l.location_id = p_locid;
loccur refcursor;
deptcur refcursor;
empcur refcursor;
V_city locations.city%type;
V_dname departments.department_name%type;
V_ename employees.last_name%type;
BEGIN
OPEN c1;
LOOP
FETCH C1 INTO v_city, loccur;
EXIT WHEN c1%notfound;
-- We can access the column C1.city, then process the results of
-- the nested cursor.
LOOP
FETCH loccur INTO v_dname, deptcur; -- No need to open
EXIT WHEN loccur%notfound;
LOOP
FETCH deptcur into v_ename; -- No need to open
EXIT WHEN deptcur%notfound;
DBMS_OUTPUT.PUT_LINE(v_city ||' '||v_dname||' '||v_ename);
END LOOP;
END LOOP;
END LOOP;
close c1;
END;
/

7.4 Oracle 11g中PL/SQL的新特性

Oracle 11g中PL/SQL新增了很多特性，在性能和易用性上做了不少改进，功能上也有许多增强。

1．SQL语句的缓存结果集功能

DB_BUFFER只能缓存访问过的BLOCK，部分解决了物理读的问题，查询仍然需要大量的逻辑读。

物化视图提供了查询重写的功能，对于大部分的情况，只是简化了SQL的复杂度，即使是完全满足查询的条件，物化视图的扫描也是不可避免的。

而缓存结果集——RESULT CACHE则截然不同，它缓存的是查询的结果。不再需要大量的逻辑读，也不再需要任何的复杂计算，而是直接将已经缓存的结果返回。

Oracle新增了两个HINT，RESULT_CACHE和NO_RESULT_CACHE。通过这两个提示，可以明确指出下面的语句是否进行RESULT CACHE。

Oracle还增加了几个初始化参数来管理RESULT CACHE功能，如：RESULT_CACHE_MODE, RESULT_CACHE_MAX_SIZE等。RESULT_CACHE_MAX_SIZE指明SGA中RESULT CACHE功能可以使用的最大内存容量。如果这个参数设置为0，则关闭RESULT CACHE功能。RESULT_CACHE_MODE参数设置Oracle如何使用RESULT CACHE，该参数有三个值：MANUAL,AUTO,FORCE。

Oracle提供了DBMS_RESULT_CACHE包来管理和维护RESULT CACHE。

Oracle还新增了几个关于RESULT CACHE的系统视图，用户可以看到与RESULT CACHE相关的各种信息，视图包括：

◎ V$RESULT_CACHE_DEPENDENCY

◎ V$RESULT_CACHE_MEMORY

◎ V$RESULT_CACHE_OBJECTS

◎ V$RESULT_CACHE_STATISTICS

2．SEQUENCE的改进、inlining优化SEQUENCE的改进及inlining优化

以前取SEQUENCE的NEXTVAL和CURRVAL只能通过SQL语句，现在可以在PL/SQL中通过赋值语句获取：

SQL> CREATE SEQUENCE S_ID;
序列已创建。
SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_ID NUMBER;
3 BEGIN
4 V_ID := S_ID.NEXTVAL;
5 DBMS_OUTPUT.PUT_LINE(V_ID);
6 END;
7 /
1
PL/SQL 过程已成功完成。

这种方法比使用SELECT INTO方便，更重要的是，由于不执行SQL语句，这种方式的效率更高。

Oracle 11g提供的PRAGMA INLINE，可以对函数强制进行INLINE优化，Oracle在调用函数时可以将调用过程直接替换为子查询的代码，这样就可以避免在运行过程中调用开销。Oracle提供了一个新的PRAGMA INLINE来指示函数是否进行INLINE优化。

除了强制使用PRAGMA INLINE方法外，Oracle还提供了自动INLINE的方式，只需要将初始化参数PLSQL_OPTIMIZE_LEVLE设置为3，Oracle就会自动选择合适的过程进行INLINE优化。

Oracle的inline操作将调用操作直接替换为程序的代码，避免了调用的开销，明显地提升了性能，但是，这种方法消耗了更多的PGA内容。

对于特别复杂的调用，或者递归、循环调用次数过多，可能会造成INLINE调用方式的内存占用过大，从而导致性能下降甚至出现内存耗尽的情况。INLINE带来的并非总是性能的提升，使用不当也可能造成严重的性能问题。

Oracle提供的PRAGMA INLINE同样可以强制禁止过程INLINE优化的发生。

3．新的复合触发器与新的控制触发器的触发顺序功能

Oracle 11g对触发器部分有了一定的增强，主要表现在两方面：一是对触发器的触发顺序可以进行控制。二是可以定义一个复合触发器。

复合触发器包括BEFORE STATEMENT,BEFORE EACH ROW,AFTER EACH ROW和AFTER STATEMENT 4部分，将4种类型的触发器集成在一个触发器中，如果需要多个类型的触发器配合使用，采用复合触发器会显得逻辑更加清晰，而且不容易出现错误。在复合触发器中定义的变量可以在不同类型的触发语句中使用，不再需要使用外部包存储中间结果，而且，利用复合触发器的批量操作还可以提高触发器的性能。

4．新的PL/SQL数据类型

Oracle 11g推出了一个新的数据类型SIMPLE_INTEGER，这种数据类型表示-2147483648到2147483647，该数据类型不为空。

对于该数据类型，Oracle就不能进行空判断，也不会处理溢出的情况。因此，Oracle可以将这个数据类型的操作直接作用于硬件，从而提高性能。

5．函数指定参数调用，增加了continue关键字

在Oracle 11g以前，如果一个函数在SQL中被调用，则不能通过指定参数的方式调用。如果一个函数有10个输入参数，前面9个都可以使用默认值，只有最后一个需要指定，那么必须把前面9个参数补齐。没有办法通过指定参数的方法调用。

Oracle 11g另外增加的一个新功能是添加了CONINUE语句，虽然没有CONINUE语句也可以达到程序的逻辑效果，但是，使用CONINUE语句会更加自然易读。

6．动态SQL方面的增强和改进了的DBMS_SQL包

Oracle 11g中在动态sql方面也做了很多功能性的增强。

其中最明显的一个增强就是，EXECUTE IMMEDIATE和DBMS_SQL包的PARSE都支持CLOB字段作为输入，这就彻底解决了EXECUTE IMMEDIATE语法不支持32k以上sql语句的限制。同时，DBMS_SQL包对于32k以上SQL的处理也得到了简化。

如果希望通过动态SQL重建，原来只能使用DBMS_SQL的VARCHAR2S接口，但是这种方面比较麻烦，现在可以直接通过CLOB的方式来实现。

除了增加对CLOB类型的支持外，DBMS_SQL包还增加了将REF CURSOR和DBMS_SQL包的CURSOR进行相互转化的功能。在打开CURSOR且提前数据前，可以利用DBMS_SQL包将CURSOR类型进行转化。

7.5 本章小结

本章重点介绍了Oracle PL/SQL的高级编程，内容涵盖了游标、异常处理等，面向PL/SQL的高级开发人员。