plsql在第一个循环后停止运行(错误:“subscript outside of limit”)

eufgjt7s  于 2021-08-13  发布在  Java
关注(0)|答案(2)|浏览(517)
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 然后显示它。
感谢您的帮助!

9njqaruj

9njqaruj1#

这些行中有错误( 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
zvokhttg

zvokhttg2#

因为您显然没有将数组写入表,所以只需将它与关联数组结合起来。这并没有限制参赛人数。

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

相关问题