Oracle中构造数组

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

 

 

在Oracle中很容易构造数组：

SQL> SET SERVEROUT ON SIZE 100000
!/"@tK.e0X@0SQL> DECLARE
R5~@M2Ww;/x-W0  2   TYPE T_VARRAY IS VARRAY(5) OF NUMBER;
'z.~ r)CN2NQ [V5XZ(a0  3   V_VAR T_VARRAY := T_VARRAY(1,2,3,4,5);
t)f9AZH*/-Nf&R9J:B+spf0  4  BEGINITPUB个人空间^E [_r&~1Y
  5   FOR I IN 1..V_VAR.COUNT LOOPITPUB个人空间8].S Dk8c ts#f.m
  6    DBMS_OUTPUT.PUT_LINE(V_VAR(I));
0N4kt1K_ o)g0  7   END LOOP;
m3VkiCt p"P J0  8  END;
!x`l2P!cwy0  9  /
iwDT|01
rF8y$aA02ITPUB个人空间eB0w[%^wO^ m9U5w
3ITPUB个人空间N"anb.zP/3V;~
4ITPUB个人空间*c6duEBC2tE.R
5

PL/SQL过程已成功完成。

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

SQL> DECLARE
xh*aImC D$m0  2   TYPE T_VARRAY IS VARRAY(5) OF NUMBER;ITPUB个人空间,o3vP+HrK
  3   TYPE T_VARRAY_VARRAY IS VARRAY(4) OF T_VARRAY;ITPUB个人空间SJZ#cGW
  4   V_VAR T_VARRAY_VARRAY := T_VARRAY_VARRAY
z%i^6/9V&{3B)hZJ0  5    (ITPUB个人空间:a ON9a)hH,k
  6     T_VARRAY(1,2,3,4,5),ITPUB个人空间sD/o8d!n
  7     T_VARRAY(1,2,3,4,5),ITPUB个人空间2^5Vc3a/{"~6})/c
  8     T_VARRAY(1,2,3,4,5),ITPUB个人空间 i){7q-YR
  9     T_VARRAY(1,2,3,4,5)ITPUB个人空间{;BZ8p7R^1e gz"qW
 10    );ITPUB个人空间iy4cYY5g/q
 11  BEGINITPUB个人空间ezr6~ rC*N y`5D
 12   FOR I IN 1..V_VAR.COUNT LOOP
AVl[.i2y:q+Iz,U3Z0 13    FOR J IN 1..V_VAR(I).COUNT LOOP
e8{3W;Bp`!|lcH]0 14     DBMS_OUTPUT.PUT_LINE(V_VAR(I)(J));
q7N4^!EI?4sF9E8vu0 15    END LOOP;
$WoK,ChD3v5T0 16   END LOOP;
A-lv/T*?D:N0 17  END;ITPUB个人空间 u,@mw*sA3w
 18  /ITPUB个人空间*Z.E3ft``6Ou
1
Y3hV:Kcqy'H02ITPUB个人空间yls^9Q
3
.L*b%vv-B$@ z%nV$D04
'M oB9M+@YvF,U2v05ITPUB个人空间,e`-E x+jT @)|0N
1
+W| c^lv4i)O,t^02ITPUB个人空间*n4] K"p)?*R+e
3ITPUB个人空间?#y,Yu)W
4
.~q yo*D7g"_u05
.Dy-AI4o/_{Ga01
C s!q/X5OeH:s2P02ITPUB个人空间b:hE3}M.E4^./TX
3
0}:@$Qct%PsY04ITPUB个人空间`(YAm(qu X1X s
5
/F5j9n R_01ITPUB个人空间6K/so&A
2ITPUB个人空间Xh'O#Wm|J!j
3
;`0g |C*G04
nq|ba:EXB05

PL/SQL过程已成功完成。

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

SQL> DECLAREITPUB个人空间z1M9jrK)H
  2   TYPE T_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;ITPUB个人空间/@8B#O i`)B;`
  3   V_VAR T_TAB;ITPUB个人空间/v!J:A4w w"N$Up k
  4  BEGINITPUB个人空间-Ehg6d:?Z#d
  5   SELECT ROWNUM BULK COLLECT INTO V_VAR FROM USER_OBJECTSITPUB个人空间b/D/~ O*M!Z
  6   WHERE ROWNUM <= 5;ITPUB个人空间!r)YG,/rf
  7   FOR I IN 1..V_VAR.COUNT LOOP
8seAf8y/C7n2zC0  8    DBMS_OUTPUT.PUT_LINE(V_VAR(I));ITPUB个人空间#hGTr[ E;ZH
  9   END LOOP;
wp#].wpw5[{0 10  END;
/i6e)PR,np3{*D$I0 11  /ITPUB个人空间J]/](o4X M.?` d
1ITPUB个人空间 y2SHyv.Q
2ITPUB个人空间Rh$W9Ml @
3
:c:{Qef5Bu#X'E-T04ITPUB个人空间J5Cw;Z?(nY
5

PL/SQL过程已成功完成。

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

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

SQL> DECLARE
8anfkM{.V(|0  2   TYPE T_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(30);ITPUB个人空间 j,Q!`y///YK9d6ch
  3   V_VAR T_TAB;ITPUB个人空间,v#DME}$k
  4   V_STR VARCHAR2(30);ITPUB个人空间t-H-?(l2y+ye
  5  BEGIN
3v!RnLU;c0  6   FOR I IN (SELECT OWNER, COUNT(*) CN FROM DBA_TABLES GROUP BY OWNER) LOOP
&FhU H7F C0  7    V_VAR(I.OWNER) := I.CN;ITPUB个人空间.FX9x?Wm7E Q:U
  8   END LOOP;
J_ E:Qa#do1l0  9   V_STR := V_VAR.FIRST;
J/ud&R)kS0 10   WHILE (V_VAR.EXISTS(V_STR)) LOOPITPUB个人空间#^C.C9c-n|#I
 11    DBMS_OUTPUT.PUT_LINE(RPAD(V_STR, 20, ' ') || ':' || V_VAR(V_STR));
SiKa!w1Oq+J0 12    V_STR := V_VAR.NEXT(V_STR);ITPUB个人空间-WkT(}6F8_j
 13   END LOOP;
U$IW^;w_0 14  END;ITPUB个人空间th `z-`/^$z)D
 15  /ITPUB个人空间#e&A$R.pA,I'H0`f
CATA_LOG            :37ITPUB个人空间0kDB iR%J./@+Y0|
CTXSYS              :37
W.EViL'E0DBSNMP              :21ITPUB个人空间:r;d"_$]3I#}nqBu#d
DMSYS               :2
"m@)M`BkZ6VQ4L0EXFSYS              :44
TIJC6Pe LA0HR                  :7ITPUB个人空间*[ XNc)i"v$`
IX                  :15ITPUB个人空间P3~V%Uv!c[K8@)H
MDSYS               :49
-O9s L'J3AU0OE                  :12ITPUB个人空间!Al{/RX~c
OLAPSYS             :126
?x5H]4]#oq0ORDSYS              :4
xn@%L9Z0OUTLN               :3ITPUB个人空间_ ll3~}0P1P#rWU
PM                  :2
}bb&@] D ?X0SCOTT               :4
'G_*h{TfUkcq7n)jo0SH                  :17
PZf8dTB:UQ0SYS                 :706
l#C(~)koE0[7uW~0SYSMAN              :337ITPUB个人空间 ~JHVY
SYSTEM              :141ITPUB个人空间:b'f5/ kdu'h-P
TSMSYS              :1ITPUB个人空间eF)DM}!Wv/H
WMSYS               :40ITPUB个人空间 H"|%`mD-a^/m-boE
XDB                 :11
7g!b-g$_7mz0YANGTK              :6

PL/SQL过程已成功完成。

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

SQL> DECLARE
rg C1PtK0  2   TYPE T_NUM_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
[5u9f#J `]z ]6e$?6H0  3   TYPE T_VAR_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
l"Y^&[#u/_ D0  4   TYPE T_NUM_VAR IS TABLE OF T_NUM_TAB INDEX BY VARCHAR2(10);ITPUB个人空间(A)UyrzBG!Jo]R
  5   TYPE T_VAR_NUM IS TABLE OF T_VAR_TAB INDEX BY BINARY_INTEGER;ITPUB个人空间*K)JQ)zDp S/n
  6   V_NUM_VAR T_NUM_VAR;
} u/FG'eg5u'S0  7   V_VAR_NUM T_VAR_NUM;
B ^O"xG-q(dW0  8  BEGINITPUB个人空间!n%fs;Jq(Y{
  9   V_NUM_VAR(5)('A') := 1;ITPUB个人空间tCp/%|'|
 10   V_VAR_NUM('A')(5) := 1;ITPUB个人空间6ty+I!SI8V*[Mn
 11  END;
TC L:K J I3h4N0 12  /
^Tn)K$T!v e8e0DECLARE
4U4]y2K3u0*
Fk6y%vX)Jrh:f3T0第1行出现错误:ITPUB个人空间$Q2g9aav
ORA-06502: PL/SQL:数字或值错误: 字符到数值的转换错误
f"N C^ U}.~(L0ORA-06512:在line 9

qi5ezmw A~0SQL> DECLARE
L-M3J|e `4rc.wh0  2   TYPE T_NUM_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;ITPUB个人空间tf$QrB2pR-a
  3   TYPE T_VAR_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
9c;]Cf2^%]Q0  4   TYPE T_NUM_VAR IS TABLE OF T_NUM_TAB INDEX BY VARCHAR2(10);ITPUB个人空间 Y:v4F R ~0z d
  5   TYPE T_VAR_NUM IS TABLE OF T_VAR_TAB INDEX BY BINARY_INTEGER;
/0}s N @B(G6t/k _D0  6   V_NUM_VAR T_NUM_VAR;ITPUB个人空间)n-^:Nbp7wa
  7   V_VAR_NUM T_VAR_NUM;
-k ^^6QuPe2X5w0  8  BEGINITPUB个人空间v%nT v r
  9   V_NUM_VAR('A')(5) := 1;ITPUB个人空间h.D w QE.^zLd
 10   V_VAR_NUM('A')(5) := 1;
U)`&])Q3FE&{]?0 11  END;
fkp-[ua3o q0 12  /ITPUB个人空间(j%?9R!w(| JXF8s,eV
DECLARE
S G({|'J jP"I0L&O.T0*ITPUB个人空间8Ovd*F'@)za fPn
第1行出现错误:
2a-c*yA0[_0ORA-06502: PL/SQL:数字或值错误: 字符到数值的转换错误ITPUB个人空间#O%s c+UYh
ORA-06512:在line 10

J&d&ksUC Pu5tL0SQL> DECLARE
/~+T0oLs(| j9WD0  2   TYPE T_NUM_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;ITPUB个人空间/@^;}Q Ir9wB9J j
  3   TYPE T_VAR_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
dx8U(B4R0  4   TYPE T_NUM_VAR IS TABLE OF T_NUM_TAB INDEX BY VARCHAR2(10);ITPUB个人空间'_~&zQ Dogs`
  5   TYPE T_VAR_NUM IS TABLE OF T_VAR_TAB INDEX BY BINARY_INTEGER;
-pb3^tp-h0  6   V_NUM_VAR T_NUM_VAR;ITPUB个人空间)H9?e%sA;_
  7   V_VAR_NUM T_VAR_NUM;
oU7K&/rlR/9@0  8  BEGIN
6~4_3h G0AD0  9   V_NUM_VAR('A')(5) := 1;ITPUB个人空间]3X_1U-b*E4l
 10   V_VAR_NUM(5)('A') := 1;ITPUB个人空间 n*}uP3W+V
 11  END;ITPUB个人空间vD @y/YC-j1L
 12  /

PL/SQL过程已成功完成。

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