PLS-00386：在FETCH游标和INTO变量之间发现类型不匹配 [英] PLS-00386: type mismatch found between FETCH cursor and INTO variables

问题描述

以下程序包引发：
PLS-00386：在FETCH游标和INTO变量之间在V_STUDYTBL处发现类型不匹配

代码的目的：
定义包外部的两个类型，一个用于将一组数字发送到存储过程，另一个用于从my_table返回相应的行

Purpose of the code: Define two types outside the package, one is used to send a bunch of numbers into the stored proc and the other is used to return the corresponding rows from my_table

提前感谢您的输入。

Create OR REPLACE Type InputTyp  AS VARRAY(200) OF VARCHAR2 (1000);

CREATE  TYPE OBJTYP AS OBJECT
   (
    A            NUMBER,
    B             VARCHAR2 (1000),       
    C        VARCHAR2 (100)       
 );
CREATE TYPE OutputTyp IS VARRAY (2000) OF   OBJTYP;
/

CREATE OR REPLACE PACKAGE my_package
AS
   PROCEDURE my_procedure(p_StudyNum   IN     InputTyp,
                                     p_StdyDtl        OutputTyp);
END my_package;
/
CREATE OR REPLACE PACKAGE BODY my_package
AS
   PROCEDURE MyProcedure(p_StudyNum   IN     InputTyp,
        p_StdyDtl        OutputTyp)
IS
  i            BINARY_INTEGER := 1;
  j            BINARY_INTEGER := 1;
  CURSOR c_StudyTbl
  IS
     SELECT A,  B, C
       FROM my_table
      WHERE Study_Number = p_StudyNum(i);

  v_StudyTbl   OBJTYP;
BEGIN
  p_StdyDtl := OutputTyp ();
  LOOP
     --  This is the first cursor opened for each of the items in the list.
      EXIT WHEN i > p_StudyNum.count;

     OPEN c_StudyTbl;
     LOOP

        FETCH c_StudyTbl INTO v_StudyTbl;
        EXIT WHEN c_StudyTbl%NOTFOUND;

        p_StdyDtl.EXTEND ();
        p_StdyDtl (j).A := v_StudyTbl.A;
        p_StdyDtl (j).B := v_StudyTbl.B;
        p_StdyDtl (j).C := v_StudyTbl.C;
        j := j + 1;
     END LOOP;
     CLOSE c_StudyTbl;
    i := i + 1;
  END LOOP;

  IF c_StudyTbl%ISOPEN
  THEN
     CLOSE c_StudyTbl;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
     NULL;
END;
END my_package;
/

推荐答案

对象构造函数在select上：

you'd need to use the object constructor on the select:

SELECT OBJTYP(A, B, C)
   FROM my_table
  WHERE Study_Number = p_StudyNum(i)

但您可以将过程简化为this而不是所有这些循环：

but you can simplify the procedure to this instead of all those loops:

begin
select cast(multiset(select /*+ cardinality(s, 10) */ a, b, c
                        from my_table t, table(p_StudyNum) s
                      where t.study_number = s.column_value) as OutputTyp)
   into p_StdyDtl
   from dual;
end;

这篇关于PLS-00386：在FETCH游标和INTO变量之间发现类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！