PL/SQL中构造数组(orcal)

原文出处：http://blog.sina.com.cn/s/blog_5eae21900100ptbf.html

最近在写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语言中的数组是有区别的。