145.Oracle数据库SQL开发之 集合——10g对集合的增强
时间:2025-07-10
时间:2025-07-10
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')
);