小议Oracle中构造数组

===========================================================

小议Oracle中构造数组

===========================================================

作者: yangtingkun(http://yangtingkun.itpub.net)
发表于: 2008.03.11 16:38
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/457003
---------------------------------------------------------------

最近在写PL/SQL中使用Oracle提供的索引表构造了一个二维数组。发现这里还包含了不少平时没有太关注的东西，简单总结一下。

在Oracle中很容易构造数组：

SQL> SET SERVEROUT ON SIZE 100000
SQL> DECLARE
2 TYPE T_VARRAY IS VARRAY(5) OF NUMBER;
3 V_VAR T_VARRAY := T_VARRAY(1,2,3,4,5);
4 BEGIN
5 FOR I IN 1..V_VAR.COUNT LOOP
6 DBMS_OUTPUT.PUT_LINE(V_VAR(I));
7 END LOOP;
8 END;
9 /
1
2
3
4
5

PL/SQL 过程已成功完成。

对于二维数组也是很容易实现的：

SQL> DECLARE
2 TYPE T_VARRAY IS VARRAY(5) OF NUMBER;
3 TYPE T_VARRAY_VARRAY IS VARRAY(4) OF T_VARRAY;
4 V_VAR T_VARRAY_VARRAY := T_VARRAY_VARRAY
5 (
6 T_VARRAY(1,2,3,4,5),
7 T_VARRAY(1,2,3,4,5),
8 T_VARRAY(1,2,3,4,5),
9 T_VARRAY(1,2,3,4,5)
10 );
11 BEGIN
12 FOR I IN 1..V_VAR.COUNT LOOP
13 FOR J IN 1..V_VAR(I).COUNT LOOP
14 DBMS_OUTPUT.PUT_LINE(V_VAR(I)(J));
15 END LOOP;
16 END LOOP;
17 END;
18 /
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5

PL/SQL 过程已成功完成。

Oracle除了数组类型VARRAY之外，还有嵌套表和索引表也都可以实现类似数组的功能，其中索引表的使用更加灵活，方便。

SQL> DECLARE
2 TYPE T_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_VAR T_TAB;
4 BEGIN
5 SELECT ROWNUM BULK COLLECT INTO V_VAR FROM USER_OBJECTS
6 WHERE ROWNUM <= 5;
7 FOR I IN 1..V_VAR.COUNT LOOP
8 DBMS_OUTPUT.PUT_LINE(V_VAR(I));
9 END LOOP;
10 END;
11 /
1
2
3
4
5

PL/SQL 过程已成功完成。

使用索引表定义数组，不需要指定数组的上限，数组的大小只与内存限制有关。

而且索引组织表定义是指定的索引项并一定要是数值，举个简单的例子：

SQL> DECLARE
2 TYPE T_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
3 V_VAR T_TAB;
4 V_STR VARCHAR2(30);
5 BEGIN
6 FOR I IN (SELECT OWNER, COUNT(*) CN FROM DBA_TABLES GROUP BY OWNER) LOOP
7 V_VAR(I.OWNER) := I.CN;
8 END LOOP;
9 V_STR := V_VAR.FIRST;
10 WHILE (V_VAR.EXISTS(V_STR)) LOOP
11 DBMS_OUTPUT.PUT_LINE(RPAD(V_STR, 20, ' ') || ':' || V_VAR(V_STR));
12 V_STR := V_VAR.NEXT(V_STR);
13 END LOOP;
14 END;
15 /
CATA_LOG :37
CTXSYS :37
DBSNMP :21
DMSYS :2
EXFSYS :44
HR :7
IX :15
MDSYS :49
OE :12
OLAPSYS :126
ORDSYS :4
OUTLN :3
PM :2
SCOTT :4
SH :17
SYS :706
SYSMAN :337
SYSTEM :141
TSMSYS :1
WMSYS :40
XDB :11
YANGTK :6

PL/SQL 过程已成功完成。

如果构造二维以上的数组，且维度不全是数值，那么就需要注意了：

SQL> DECLARE
2 TYPE T_NUM_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 TYPE T_VAR_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
4 TYPE T_NUM_VAR IS TABLE OF T_NUM_TAB INDEX BY VARCHAR2(10);
5 TYPE T_VAR_NUM IS TABLE OF T_VAR_TAB INDEX BY BINARY_INTEGER;
6 V_NUM_VAR T_NUM_VAR;
7 V_VAR_NUM T_VAR_NUM;
8 BEGIN
9 V_NUM_VAR(5)('A') := 1;
10 V_VAR_NUM('A')(5) := 1;
11 END;
12 /
DECLARE
*第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误
ORA-06512: 在 line 9

SQL> DECLARE
2 TYPE T_NUM_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 TYPE T_VAR_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
4 TYPE T_NUM_VAR IS TABLE OF T_NUM_TAB INDEX BY VARCHAR2(10);
5 TYPE T_VAR_NUM IS TABLE OF T_VAR_TAB INDEX BY BINARY_INTEGER;
6 V_NUM_VAR T_NUM_VAR;
7 V_VAR_NUM T_VAR_NUM;
8 BEGIN
9 V_NUM_VAR('A')(5) := 1;
10 V_VAR_NUM('A')(5) := 1;
11 END;
12 /
DECLARE
*第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误
ORA-06512: 在 line 10

SQL> DECLARE
2 TYPE T_NUM_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 TYPE T_VAR_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
4 TYPE T_NUM_VAR IS TABLE OF T_NUM_TAB INDEX BY VARCHAR2(10);
5 TYPE T_VAR_NUM IS TABLE OF T_VAR_TAB INDEX BY BINARY_INTEGER;
6 V_NUM_VAR T_NUM_VAR;
7 V_VAR_NUM T_VAR_NUM;
8 BEGIN
9 V_NUM_VAR('A')(5) := 1;
10 V_VAR_NUM(5)('A') := 1;
11 END;
12 /

PL/SQL 过程已成功完成。

从这个例子可以看出，用索引表构造的数组和C语言中的数组是有区别的。

yangtingkun 发表于:2008.03.11 16:38 ::分类: ( ORACLE ) ::阅读:(880次) :: 评论 (4)

re: 小议Oracle中构造数组 [回复]

索引表构造的其实是个类似B-TREE的结构，你通过first，next，prior，last可以发现key是存放在一个类似有序的双向链表中（类似B-TREE的叶子节点）。

当然也可能是存放在一个AVL树之类的，不好说，但和一般按插入顺序存放的数组是不一样的。

adam 评论于: 2008.03.11 20:26

re: 小议Oracle中构造数组 [回复]

从索引表支持VARCHAR类型作为索引类型就可以看到，它的结构和数组肯定是不一样的，不过至于是树还是链表，就不清楚了。个人认为是链表的可能性更大一些

yangtingkun 评论于: 2008.03.11 23:35

re: 小议Oracle中构造数组 [回复]

oracle称索引表为Associative Arrays，从数据结构的角度来说Associative Arrays一般使用hash或者类平衡搜索树实现。

因为有序所以不会是hash，而存链表的检索效率又太低，所以考虑是类平衡搜索树的结构。

adam 评论于: 2008.03.12 08:39

re: 小议Oracle中构造数组 [回复]

嗯，有道理，如果是链表的话，对于指定记录的访问还要对链表进行遍历，效率确实会很低。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/219982/viewspace-573307/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/219982/viewspace-573307/