plsql 在第一次循环后停止运行(错误："Subscript outside of limit")

Question

DECLARE
TYPE Lname IS VARRAY(15)
OF employees.last_name%TYPE;
v1 Lname:= Lname();
CURSOR c1 IS
SELECT employee_id
FROM employees
WHERE department_id = 50;
LEN NUMBER(2);
I NUMBER(2);
BEGIN
-- Print out the contents of this Varray while populating
DBMS_OUTPUT.PUT_LINE('This printout is in order of populating:');
DBMS_OUTPUT.PUT_LINE(CHR(9) || 'Name');
DBMS_OUTPUT.PUT_LINE(CHR(9) || '----');
I := 1;
FOR E IN c1 LOOP
v1.EXTEND();
v1(I) := e.employee_id;
DBMS_OUTPUT.PUT_LINE(CHR(9) || v1(I));
I := I + 1;
END LOOP;
-- Display the maximum size of thisi Varray
LEN := v1.LIMIT;
DBMS_OUTPUT.PUT_LINE('Max size of Varray: ' || LEN);
--Display the total number of the elements with populated values (value is not null)
DBMS_OUTPUT.PUT_LINE('Total elements: ' || v1.COUNT);
--Display the value of the last index
DBMS_OUTPUT.PUT_LINE('Last index value: ' || v1(v1.LAST));
--Print out all of the contents of this Varray (including null elements)
DBMS_OUTPUT.PUT_LINE('This printout is all of the contents of this Varray:');
DBMS_OUTPUT.PUT_LINE(CHR(9) || 'Name');
DBMS_OUTPUT.PUT_LINE(CHR(9) || '----');
FOR I IN 1 .. LEN LOOP
IF v1.EXISTS(I) THEN
DBMS_OUTPUT.PUT_LINE(CHR(9) || v1(I));
ELSE
v1.EXTEND;
DBMS_OUTPUT.PUT_LINE(CHR(9) || v1(I));
END IF;
END LOOP;
END;
/

当我运行上述内容时，它会运行到第一个循环并显示名称，并在它下面显示数字，然后它停止。

它会引发以下错误：

Error report -
ORA-06532: Subscript outside of limit
ORA-06512: at line 19
ORA-06512: at line 19
06532. 00000 -  "Subscript outside of limit"
*Cause:    A subscript was greater than the limit of a varray
or non-positive for a varray or nested table.
*Action:   Check the program logic and increase the varray limit
if necessary.

我不明白为什么会抛出这个错误。 我想有一个大小为 15 的数组，并显示表中的前 12 个员工姓名以填充v1然后显示它。

任何帮助不胜感激！

Answer 1

错误在这些行中(DECLARE部分(：

TYPE Lname IS VARRAY(15)        --> 15
LEN NUMBER(2);                  -->  2
I NUMBER(2);                    -->  2

如果数组元素的数量小于15(您在上面的第一行中设置(，则工作正常。但是，如果元素数大于该数字，则您正在尝试使用其数量超过该限制的元素。

---

怎么办？放大所有内容，例如

• 将 varray 设置为至少count(*) from employees where department = 50(我建议更多(
• 删除这些变量的精度

例如：

TYPE Lname IS VARRAY(1000)             --> 1000
OF employees.last_name%TYPE;
v1 Lname:= Lname();
CURSOR c1 IS
SELECT employee_id
FROM employees
WHERE department_id = 50;
LEN NUMBER;                           --> no precision
I NUMBER;                             --> no precision

Answer 2

由于您显然没有将数组写入表，因此只需将其与关联数组相关联即可。这不会对条目数量施加限制。

type lname is table of employees.lname%type
index by pls_integer;
i pls_integer;