在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 /
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 /
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 /
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语言中的数组是有区别的。
No comments:
Post a Comment