145.Oracle数据库SQL开发之 集合——10g对集合的增强

145.Oracle数据库SQL开发之 集合——10g对集合的增强 10g对集合的增强，包括：

支持关联数组

更改元素类型的大小或精度的能力

增加变长数组中元素数目的能力

在临时表中使用变长数组列的能力

为嵌套表的存储表使用不同表空间的能力

实现嵌套表对ANSI的支持

1. 关联数组

关联数组是一个键值对集合。可以使用键或一个整数获得数组中的值。 CREATE PROCEDURE customers_associative_array AS

-- define an associative array type named t_assoc_array;

-- the value stored in each array element is a NUMBER,

-- and the index key to access each element is a VARCHAR2

TYPE t_assoc_array IS TABLE OF NUMBER INDEX BY VARCHAR2(15);

-- declare an object named v_customer_array of type t_assoc_array;

-- v_customer_array will be used to store the ages of customers

v_customer_array t_assoc_array;

BEGIN

-- assign the values to v_customer_array; the VARCHAR2 key is the

-- customer name and the NUMBER value is the age of the customer

v_customer_array('Jason') := 32;

v_customer_array('Steve') := 28;

v_customer_array('Fred') := 43;

v_customer_array('Cynthia') := 27;

-- display the values stored in v_customer_array

DBMS_OUTPUT.PUT_LINE(

'v_customer_array[''Jason''] = ' || v_customer_array('Jason')

);

DBMS_OUTPUT.PUT_LINE(

'v_customer_array[''Steve''] = ' || v_customer_array('Steve')

);

DBMS_OUTPUT.PUT_LINE(

'v_customer_array[''Fred''] = ' || v_customer_array('Fred')

);

DBMS_OUTPUT.PUT_LINE(

'v_customer_array[''Cynthia''] = ' || v_customer_array('Cynthia')

);