PL/SQL中集合出现“ORA-06533:下表超出数量”的问题解决
VARRAY是在Oracle8G的时候引入的一个类型,是一种密集填充数组,更容易被理解成为一个传统的数组。在创建这些数组的时候,都有一个尺寸,需要非常注意的是VARRAY的下表是从1开始的,在其他很多变成语言中,数组的下表都是从0开始的,比如JAVA。在实际的使用中,初学者经常会出现如下的错误提示:ORA-06533:下表超出数量
例如下面的代码就会出现该错误:
EXTEND Collection Method
EXTEND
is a procedure that adds elements to the end of a varray or nested table. The collection can be empty, but not null. (To make a collection empty or add elements to a null collection, use a constructor. For more information, see "Collection Constructors".)The
EXTEND
method has these forms:
EXTEND
appends one null element to the collection.
EXTEND(
n
)
appends n null elements to the collection.
EXTEND(
n
,i
)
appends n copies of the ith element to the collection.
Note:
EXTEND(
n
,i
)
is the only form that you can use for a collection whose elements have the NOT
NULL
constraint.EXTEND
operates on the internal size of a collection. That is, if DELETE
deletes an element but keeps a placeholder for it, then EXTEND
considers the element to exist.
Example 5-20 declares a nested table variable, initializing it with three elements; appends two copies of the first element; deletes the fifth (last) element; and then appends one null element. Because EXTEND
considers the deleted fifth element to exist, the appended null element is the sixth element. The procedureprint_nt
prints the nested table variable after initialization and after the EXTEND
and DELETE
operations. The type nt_type
and procedure print_nt
are defined inExample 5-6.
Example 5-20 EXTEND Method with Nested Table
DECLARE nt nt_type := nt_type(11, 22, 33);BEGIN print_nt(nt); nt.EXTEND(2,1); -- Append two copies of first element print_nt(nt); nt.DELETE(5); -- Delete fifth element print_nt(nt); nt.EXTEND; -- Append one null element print_nt(nt);END;/
Result:
nt.(1) = 11nt.(2) = 22nt.(3) = 33---nt.(1) = 11nt.(2) = 22nt.(3) = 33nt.(4) = 11nt.(5) = 11---nt.(1) = 11nt.(2) = 22nt.(3) = 33nt.(4) = 11---nt.(1) = 11nt.(2) = 22nt.(3) = 33nt.(4) = 11nt.(6) = NULL---上面的大意是:
DECLARE TYPE NUM_VARRAY IS VARRAY(100) OF NUMBER NOT NULL; V_NUM_VARRAY NUM_VARRAY:=NUM_VARRAY();BEGIN FOR J IN 1 .. 100 LOOP V_NUM_VARRAY.EXTEND; V_NUM_VARRAY(J) := J; END LOOP; FOR I IN 1 .. V_NUM_VARRAY.COUNT LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_NUM_VARRAY(I))); END LOOP;END;