ORACLE PL/SQL 集合学习笔记(一)

一、集合简介

记录是管理当行数据所必需的结构体，而集合是管理多行数据所必需的结构体。

集合就是列表，可能有序也可能无序。有序列表的索引是唯一性的数字下标；而无序列表的索引时唯一性的标识符，这些标识符可以是数字、哈西值，也可以是一些字符串名。

ORACLE 10g提供了3种集合类型，下面给出三种集合类型的对照说明表。

集合类型	可用下标	大小
联合数组（INDEX-BY表）	唯一性的数字序列或字符串	动态分配
嵌套表	数字序列	动态分配
Varrays	数字序列	固定

要确定具体哪一种集合类型最适合我们应用程序的需求是非常困难的，需要仔细的考虑每一种集合类型的优点和缺点。下面一些建议有助于我们选取恰当的集合类型：

当集合的物理大小是静态的且集合可能要存储在表中时，一般使用varray类型的集合。

由于运行时存在的变化，集合的物理大小未知，但是集合肯定不会存储在表中时，一般使用联合数组。

由于运行时存在的变化，集合的物理大小未知，并且集合可能会存储在表中时，一般使用嵌套表。

二、Varrays集合的使用方法

1、定义varrays并用作PL/SQL的程序构造块

在PL/SQL程序单元中定义一个varray的语法如下：

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)

OF element_type[NOT NULL];

下标索引从1开始，而不是0。

如下例所示：

DECLARE

-- Define a varray of integer with 3 rows.

TYPE integer_varray IS VARRAY(3) OF INTEGER;

-- Declare and initialize a varray that allows nulls.

varray_integer INTEGER_VARRAY :=

integer_varray(NULL,NULL,NULL);

BEGIN

-- Print title.

dbms_output.put_line('Varray initialized as nulls.');

dbms_output.put_line('----------------------------');

-- Loop through the three records.

FOR i IN 1..3 LOOP

-- Print the contents.

dbms_output.put ('Integer Varray ['||i||'] ');

dbms_output.put_line('['||varray_integer(i)||']');

END LOOP;

-- Assign values to subscripted members of the varray.

varray_integer(1) := 11;

varray_integer(2) := 12;

varray_integer(3) := 13;

-- Print title.

dbms_output.put (CHR(10)); -- Visual line break.

dbms_output.put_line('Varray initialized as values.');

dbms_output.put_line('-----------------------------');

-- Loop through the three records to print the varray contents.

FOR i IN 1..3 LOOP

dbms_output.put_line('Integer Varray ['||i||'] '

|| '['||varray_integer(i)||']');

END LOOP;

END;

/

2、将varrays定义和用作PL/SQL中的对象类型

CREATE OR REPLACE TYPE type_name

AS {VARRAY | VARYING ARRAY} (size_limit)

OF element_type [NOT NULL];

如下例所示：

CREATE OR REPLACE TYPE integer_varray

AS VARRAY(100) OF INTEGER NOT NULL;

/

DECLARE

-- Declare and initialize a null set of rows.

varray_integer INTEGER_VARRAY := integer_varray();

BEGIN

-- Loop through all records to print the varray contents.

FOR i IN 1..varray_integer.LIMIT LOOP

-- Initialize row.

varray_integer.EXTEND;

END LOOP;

-- Print to console how many rows are initialized.

dbms_output.put ('Integer Varray Initialized ');

dbms_output.put_line('['||varray_integer.COUNT||']');

END;

/

定义varray对象类型的好处是：它可以从任何有权使用它的程序中进行引用。

前面两个例子中我们使用的VARRAY都直接支持默认的设置而可以接受NULL值。那么，什么时候，为什么，以及怎样允许或不允许NULL行值？

这是一个很好的问题。在一些小的示例程序中，它看起来好像无关紧要。实际上，它还是非常关键的。VARRAYS是与标准程序设计语言中的数组最为相近的一种数据结构。数组是一种需要专门进行管理的结构。一个基本原则：数组应该是密集的。密集就意味着在索引值序列中间不应该有断电。这也就意味着数据不应该有断层。当我们希望VARRAY像一个标准数组结构一样时，就不能允许VARRAY中存在NULL值。

3、将VARRAY定义和用作表列的数据类型

CREATE OR REPLACE TYPE address_varray

AS VARRAY(3) OF VARCHAR2(30 CHAR);

/

CREATE TABLE addresses

(address_id INTEGER NOT NULL

,individual_id INTEGER NOT NULL

,street_address ADDRESS_VARRAY NOT NULL

,city VARCHAR2(20 CHAR) NOT NULL

,state VARCHAR2(20 CHAR) NOT NULL

,postal_code VARCHAR2(20 CHAR) NOT NULL

,country_code VARCHAR2(10 CHAR) NOT NULL

,CONSTRAINT address_pk PRIMARY KEY (address_id)

,CONSTRAINT addr_indiv_fk FOREIGN KEY (individual_id)

REFERENCES individuals (individual_id));

BEGIN

FOR i IN (SELECT null

FROM user_tables

WHERE table_name = 'TELEPHONES') LOOP

EXECUTE IMMEDIATE 'DROP TABLE telephones';

END LOOP;

END;

/

4、在数据库的表中使用VARRAYS

创建包含一个VARRAY数据类型列的数据表以后，我们就需要知道怎样使用这个列。使用这种列需要我们理解数据操纵语言DML访问VARRAYS的方法。VARRAYS没有给出专门的删除条件，因为删除操作是行级操作。另外，在使用插入和更新语句时，操作VARRAYS也与一般列有很大不同。

1）INSERT

插入语句只有一种数据访问方式。它是以一种要么全有要么全无的方式处理这种数据类型的。

-- Insert into address using the varray structure.

INSERT

INTO addresses

VALUES

(11

,11

,address_varray

('Office of Senator McCain'

,'450 West Paseo Redondo'

,'Suite 200')

,'Tucson'

,'AZ'

,'85701'

,'USA');

这时如果使用如下的普通查询来查询ADDRESSES表的STREET_ADDRESS列，就会得到一个构造函数的返回值集。

-- Use an ordinary select statement.

SELECT   street_address

FROM     addresses;

可是这个时候的输出结果并不是我们想要的。使用数据查询语言从VARRAY数据类型中查询结果需要专门的语法。我们需要定义一个嵌套表集合结构，才能真正访问到VARRAY类型的数据。

-- Create a PL/SQL table data type.

CREATE OR REPLACE TYPE varray_nested_table

IS TABLE OF VARCHAR2(30 CHAR);

/

-- Use SQL*Plus to format the output.

COL column_value FORMAT A30

-- Print a list of the varray elements.

SELECT column_value

FROM THE (SELECT CAST(street_address AS

varray_nested_table)

FROM addresses

WHERE address_id = 11);

在上面的示例程序中，构造了一个嵌套表集合来镜像VARRAYS的元素定义。嵌套表不具有VARRAYS那样的元素数目的上限，但是它可以用于临时存储VARRAYS的内容。使用嵌套表是通过查询语句真正显示VARRAYS内容的唯一方法。

下面简要介绍一下这个查询语句中的基本组成元素：

• 保留字column_value是访问嵌套表中记录行的一种方法。
• THE是SQL中的一个保留字，利用它可以从嵌套表的一个查询中检索出column_value列。
• 对象方法CAST显式地将VARRAY类型的STREET_ADDRESS转换为嵌套表。

我们必须保证VARRAY是我们嵌套表结构的镜像。如果它们不是数据类型的镜像，就会发生ORA-00932错误。该错误告诉我们转换到嵌套表的CAST源类型是个错误的数据类型。

2）UPDATE

在SQL中，不能使用任何直接或间接的方法更改VARRAY列的部分值，只能使用PL/SQL程序才能更改VARRAY集合的部分值。

如下示例程序所示：

DECLARE

-- Define a record type for a row of the addresses table.

TYPE address_type IS RECORD

(address_id INTEGER

,individual_id INTEGER

,street_address ADDRESS_VARRAY

,city VARCHAR2(20 CHAR)

,state VARCHAR2(20 CHAR)

,postal_code VARCHAR2(20 CHAR)

,country_code VARCHAR2(10 CHAR));

-- Define a variable of the addresses table record type.

address ADDRESS_TYPE;

-- Define a cursor to return the %ROWTYPE value.

CURSOR get_street_address

(address_id_in INTEGER) IS

SELECT *

FROM addresses

WHERE address_id = address_id_in;

BEGIN

-- Open the cursor.

OPEN get_street_address(11);

-- Fetch a into the record type variable.

FETCH get_street_address

INTO address;

-- Close the cursor.

CLOSE get_street_address;

-- Reset the first element of the varray type variable.

address.street_address(1) :=

'Office of Senator John McCain';

-- Update the varray column value.

UPDATE addresses

SET street_address = address.street_address

WHERE address_id = 11;

END;

/

再举一个例子，这里例子先往VARRAY类型列STREET_ADDRESS上插入一个元素，这时仅初始化了该行记录VARRAY集合中的一个元素，接下来用UPDATE添加另外两条元素。

INSERT

INTO addresses

VALUES

(22

,22

,address_table('Office of Senator Kennedy')

,'Boston'

,'MA'

,'02203'

,'USA');

-- Anonymous block using PL/SQL nested table element update.

DECLARE

-- Define a record type for a row of the addresses table.

TYPE address_type IS RECORD

(address_id INTEGER

,individual_id INTEGER

,street_address ADDRESS_TABLE

,city VARCHAR2(20 CHAR)

,state VARCHAR2(20 CHAR)

,postal_code VARCHAR2(20 CHAR)

,country_code VARCHAR2(10 CHAR));

-- Define a variable of the addresses table record type.

address ADDRESS_TYPE;

-- Define a cursor to return the %ROWTYPE value.

CURSOR get_street_address

(address_id_in INTEGER) IS

SELECT *

FROM addresses

WHERE address_id = address_id_in;

BEGIN

-- Open the cursor.

OPEN get_street_address(22);

-- Fetch a into the record type variable.

FETCH get_street_address

INTO address;

-- Close the cursor.

CLOSE get_street_address;

-- Add element space.

FOR i IN 2..3 LOOP

address.street_address.EXTEND;

END LOOP;

-- Reset the first element of the varray type variable.

address.street_address(2) := 'JFK Building';

address.street_address(3) := 'Suite 2400';

-- Update the varray column value.

UPDATE addresses

SET street_address = address.street_address

WHERE address_id = 22;

END;

/