带游标的pl/sql varray

Question

谁能告诉我这段代码出了什么问题？我的目标是编写一个向其传递雇员姓名的过程，将employee表中的所有姓名和薪水加载到一个VARRAY中，然后在屏幕上打印出姓名和薪水。

CREATE OR REPLACE PROCEDURE VARRAY_Q2 
(
  PNAME IN VARCHAR2  
, PSAL OUT NUMBER  
) AS 

--declare and create cursor

CURSOR emp_cur IS
SELECT ename,sal
FROM EMP;

  TYPE varray_emp IS VARRAY(14) OF emp_Cur%ROWTYPE;

  --Creating new instance of varray
  x_varray_emp varray_emp := varray_emp();
  v_counter NUMBER := 0;

BEGIN

  x_varray_emp.EXTEND;

  FOR empRecs IN emp_Cur LOOP

  --Insert data into the varray
  x_varray_emp(v_counter) := empRecs;

  dbms_output.put_line(v_counter);

  v_counter := v_counter + 1;
  END LOOP;

  --Loop through the varray and print out all the elements
  FOR i IN x_varray_emp.FIRST .. x_varray_emp.LAST 
  LOOP
  dbms_output.put_line(x_varray_emp(i));    
  END LOOP;

END;

Answer 1

首先: PL/SQL集合不是从0开始的。这将抛出下标超出限制的错误，因为您的计数器盯着0。对于此解决方案，您也不需要计数器。如果您想继续使用它，请参阅第一个示例。但如果我是你，我会用第二个例子。

其次，在loop.That抛出下标超出计数错误之前，您扩展了varray，因为varray只被扩展为容纳一行。您需要在循环开始时扩展它，以便在每次迭代时扩展varray。

因此，您不能对整个varray执行PUT_LINE。您必须对varray中的元素进行put_line。因此，在本例中，使用dbms_output.put_line(x_varray_emp(i).ename || ' makes $' || x_varray_emp(i).sal)而不是dbms_output.put_line(x_varray_emp(i))

第四:如果你的emp表有超过14条记录，你的VARRAY(14)会导致下标超出限制错误。PLS_INTEGER有一个最大大小设置(有界限制)，在你的例子中是14。在这个例子中，我将使用一个常规的嵌套表(TYPE nested_emp IS TABLE OF emp_cur%ROWTYPE)，这样就不用担心有界限制(从技术上讲，一个嵌套表的最大值是2147483647，也就是varray)。如果您希望使用NT而不是VA，请使用下面的第三种解决方案。

执行以下操作，它就会起作用。

将您的v_counter NUMBER := 0更改为v_counter NUMBER := 1；

将主体中的内容更改为以下内容：

BEGIN
    FOR empRecs IN emp_Cur LOOP
        x_varray_emp.EXTEND;
        --Insert data into the varray 
        x_varray_emp(v_counter) := empRecs;

        dbms_output.put_line(v_counter);

        v_counter := v_counter + 1; 
    END LOOP;
    --Loop through the varray and print out all the elements 
    FOR i IN x_varray_emp.FIRST .. x_varray_emp.LAST LOOP 
        dbms_output.put_line(x_varray_emp(i).ename || ' Makes $' || x_varray_emp(i).sal);
    END LOOP;
END;

实际上，你不需要柜台。如果您愿意放弃它，请改用varray的COUNT方法：

BEGIN


    FOR empRecs IN emp_Cur LOOP
        x_varray_emp.EXTEND;

        --Insert data into the varray 
        x_varray_emp(x_varray_emp.count) := empRecs;

        dbms_output.put_line(x_varray_emp.count);


    END LOOP;

    --Loop through the varray and print out all the elements 
    FOR i IN x_varray_emp.FIRST .. x_varray_emp.LAST LOOP 
        dbms_output.put_line(x_varray_emp(i).ename || ' Makes $' || x_varray_emp(i).sal);
    END LOOP;


END;

我更喜欢使用嵌套表来解决这个问题，而不是使用varray。如果你愿意，下面是解决方案：

DECLARE

    CURSOR emp_cur IS SELECT ename,sal FROM EMP;
    TYPE nestedtable_emp IS TABLE OF emp_cur%ROWTYPE;

    x_nestedtable_emp nestedtable_emp := nestedtable_emp();
BEGIN


    FOR empRecs IN emp_Cur LOOP
        x_nestedtable_emp.EXTEND;

        --Insert data into the varray 
        x_nestedtable_emp(x_nestedtable_emp.count) := empRecs;

        dbms_output.put_line(x_nestedtable_emp.count);


    END LOOP;

    --Loop through the varray and print out all the elements 
    FOR i IN x_nestedtable_emp.FIRST .. x_nestedtable_emp.LAST LOOP 
        dbms_output.put_line(x_varray_emp(i).ename || ' Makes $' || x_varray_emp(i).sal);
    END LOOP;


END;