变长数组集合(Varray Collections)
变长数组集合的原型:
TYPE type_name IS {VARRAY | VARYING ARRAY}(size_limit) OF data_type
[NOT NULL];
示例:
1)在SQL中定义长度限度为3的变长字符串变长数组:
SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
SQL> CREATE OR REPLACE
2 TYPE sql_varray IS VARRAY(3) OF VARCHAR2(20) NOT NULL;
3 /
类型已创建。
SQL> --上述变长数组创建语法等同于以下语法
SQL> CREATE OR REPLACE
2 TYPE sql_varying IS VARRAY(3) OF VARCHAR2(20) NOT NULL;
3 /
类型已创建。
SQL> desc sql_varray
sql_varray VARRAY(3) OF VARCHAR2(20)
SQL> desc sql_varying
sql_varying VARRAY(3) OF VARCHAR2(20)
2)使用column_value伪列(该伪列保存ADT集合返回值的聚合结果)
SQL> SELECT column_value AS "Three Stooges"
2 FROM TABLE(sql_varray('Moe','Larry','Curly'));
Three Stooges
--------------------
Moe
Larry
Curly
变长数组赋值时,不能超过最大长度,否则会报错
SQL> SELECT column_value AS "Three Stooges"
2 FROM TABLE(sql_varray('Moe','Larry','Curly','PD'));
FROM TABLE(sql_varray('Moe','Larry','Curly','PD'))
*
第 2 行出现错误:
ORA-22909: 超出最大的 VARRAY 限制
但是可以少于最大长度(这就是所谓变长数组的“变长”的意思吧,不超过限度即可)
SQL> SELECT column_value AS "Three Stooges"
2 FROM TABLE(sql_varray('Moe','Larry'));
Three Stooges
--------------------
Moe
Larry
3)在PL/SQL的声明块中定义长度限度为3的变长字符串数组
SQL> DECLARE
2 lv_collection SQL_VARRAY := sql_varray('Moe','Larry');
3 BEGIN
4 /* Print the number and limit of elements. */
5 dbms_output.put_line(
6 'Count ['||lv_collection.COUNT||']'||
7 'Limit ['||lv_collection.LIMIT||']');
8
9 /* Extend space and assign to the new index. */
10 lv_collection.EXTEND;
11
12 /* Print the number and limit of elements. */
13 dbms_output.put_line(
14 'Count ['||lv_collection.COUNT||'] '||
15 'Limit ['||lv_collection.LIMIT||']');
16
17 /* Assign a new value. */
18 lv_collection(lv_collection.COUNT) := 'Curly';
19
20 /* Iterate across the collection to the total number of elements. */
21 FOR i IN 1..lv_collection.COUNT LOOP
22 dbms_output.put_line(lv_collection(i));
23 END LOOP;
24 END;
25 /
Count [2]Limit [3]
Count [3] Limit [3]
Moe
Larry
Curly
PL/SQL 过程已成功完成。
如果不使用EXTEND方法,尝试扩展变长数组时会出错:
SQL> ed
已写入 file afiedt.buf
1 DECLARE
2 lv_collection SQL_VARRAY := sql_varray('Moe','Larry');
3 BEGIN
4 /* Print the number and limit of elements. */
5 dbms_output.put_line(
6 'Count ['||lv_collection.COUNT||']'||
7 'Limit ['||lv_collection.LIMIT||']');
8 /* Extend space and assign to the new index. */
9 --lv_collection.EXTEND;
10 /* Print the number and limit of elements. */
11 dbms_output.put_line(
12 'Count ['||lv_collection.COUNT||'] '||
13 'Limit ['||lv_collection.LIMIT||']');
14 /* Assign a new value. */
15 --lv_collection(lv_collection.COUNT) := 'Curly';
16 lv_collection(3) := 'Curly';
17 /* Iterate across the collection to the total number of elements. */
18 FOR i IN 1..lv_collection.COUNT LOOP
19 dbms_output.put_line(lv_collection(i));
20 END LOOP;
21* END;
22 /
Count [2]Limit [3]
Count [2] Limit [3]
DECLARE
*
第 1 行出现错误:
ORA-06533: 下标超出数量
ORA-06512: 在 line 16
变长数组的意义:选择使用变长数组数据类型意味着你希望当程序为超出变长数组长度部分赋值时,应该抛出异常。