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
然后显示它。
感谢您的帮助!
2条答案
按热度按时间9njqaruj1#
这些行中有错误(
DECLARE
节):如果数组元素的数目小于15(您在上面的第一行中设置),它就可以正常工作。但是,如果元素的数目大于该数目,则尝试使用其数目超出该限制的元素。
怎么办?放大一切,例如。
将varray设置为至少
count(*) from employees where department = 50
(我建议更多)删除这些变量的精度
例如:
zvokhttg2#
因为您显然没有将数组写入表,所以只需将它与关联数组结合起来。这并没有限制参赛人数。