oracle PLS-00386：在 FETCH 游标和 INTO 变量之间发现类型不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15503720/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PLS-00386: type mismatch found between FETCH cursor and INTO variables
提问by Vaishali Bulusu
The following package throws : PLS-00386: type mismatch found at 'V_STUDYTBL' between FETCH cursor and INTO variables
以下包抛出： PLS-00386：在 FETCH 游标和 INTO 变量之间的“V_STUDYTBL”处发现类型不匹配
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
代码用途：在包外定义两种类型，一种用于将一串数字发送到存储的proc中，另一种用于从my_table中返回相应的行
Thank you in advance for the inputs.
预先感谢您的投入。
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;
/
回答by DazzaL
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)
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;
回答by Egor Skriptunoff
Try declaring your cursor as:
尝试将光标声明为：
CURSOR c_StudyTbl
IS
SELECT OBJTYP(A, B, C)
FROM my_table
WHERE Study_Number = p_StudyNum(i);
