PLS-00382:通过执行函数并尝试将返回类型放入变量中，表达式的类型错误 [英] PLS-00382: expression is of wrong type by executing function and try to put the returntype in a variable

问题描述

当我执行函数时，我发现表达式的类型错误，但我不知道为什么.我在函数中使用的返回类型与我在函数执行后尝试放入的返回类型相同.

When I execute the function I get that the expression is of the wrong type but I don't know why. The return type that I use in the function is the same as where I try to put it in after the function is executed.

您可以在下方找到记录和表格类型.

Below you find the record and table type.

TYPE department_id_table_type IS TABLE of DEPARTMENTS.DEPARTMENT_ID%TYPE;

TYPE managers_rec_type IS RECORD (
  employee_id   employees.employee_id%TYPE, 
  first_name employees.first_name%TYPE,
  last_name employees.last_name%TYPE,
  department_id_table_type DEPARTMENTS.DEPARTMENT_ID%TYPE);

TYPE managers_table_type IS TABLE OF managers_rec_type INDEX BY BINARY_INTEGER;

在下面找到函数

 FUNCTION managers_multiple_departments RETURN managers_table_type 
IS
cursor department_curs is SELECT DEPARTMENT_NAME,MANAGER_ID,DEPARTMENT_ID FROM DEPARTMENTS WHERE MANAGER_ID IN (SELECT MANAGER_ID FROM DEPARTMENTS dep GROUP BY (MANAGER_ID) HAVING COUNT(MANAGER_ID) >1);


    department_name departments.department_name%TYPE;
    department_id departments.department_id%TYPE;
    managerid departments.manager_id%TYPE;
    employeeid employees.employee_id%TYPE;
    firstname employees.first_name%TYPE;
    lastname employees.last_name%TYPE;
    count NUMBER;       
    rec managers_rec_type;            
    managers_rec managers_rec_type;
    teller NUMBER := 1;
    managers_table managers_table_type;         

BEGIN           
    OPEN department_curs;
    LOOP                 
        FETCH department_curs INTO department_name, managerid,department_id;
        EXIT WHEN department_curs%NOTFOUND;
        Select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,department_id into managers_rec from EMPLOYEES where MANAGER_ID = managerid; 
        managers_table(managers_rec.employee_id) := managers_rec;
        FOR i IN managers_table.FIRST .. managers_table.LAST LOOP
            IF managers_table.EXISTS(i) THEN
                DBMS_OUTPUT.PUT_LINE(managers_table(i).first_name);
            END IF;

        END LOOP;

        IF teller = 1 THEN
            DBMS_OUTPUT.PUT_LINE(managers_rec.first_name ||' '|| managers_rec.last_name || ' Lijst van departments:');
            teller := 2;
        END IF;
        DBMS_OUTPUT.PUT_LINE(department_id ||' '|| department_name);


    END LOOP;
        FOR i IN managers_table.FIRST .. managers_table.LAST LOOP
            IF managers_table.EXISTS(i) THEN
                DBMS_OUTPUT.PUT_LINE(managers_table(i).first_name);
                DBMS_OUTPUT.PUT_LINE('test');
            END IF;

        END LOOP; 
    return managers_table;
END managers_multiple_departments;`enter code here`

下面是我执行函数的地方，但这是它给我错误的地方: manager := hr_package.managers_multiple_departments;

Below is where I execute the function but this is where it is giving me the error on: managers := hr_package.managers_multiple_departments;

DECLARE
    TYPE managers_rec_type IS RECORD (
      employee_id   employees.employee_id%TYPE, 
      first_name employees.first_name%TYPE,
      last_name employees.last_name%TYPE,
      department_id_table_type DEPARTMENTS.DEPARTMENT_ID%TYPE);

    TYPE managers_table_type IS TABLE OF managers_rec_type INDEX BY BINARY_INTEGER;    
    man_rec managers_rec_type; 
    managers managers_table_type;
    twee NUMBER;

BEGIN

    managers := hr_package.managers_multiple_departments;
END;

推荐答案

您在同一个包中声明记录类型、集合/表类型和函数.

You are declaring the record type, collection/table type and function all within the same package.

当您调用该函数时，您必须使用该包中的相同类型.

When you call the function you have to use the same type, from that package.

我在函数中使用的返回类型与我在函数执行后尝试放入的返回类型相同.

The return type that I use in the function is the same as where I try to put it in after the function is executed.

但事实并非如此.它具有相同的结构——字段和数据类型——但就 Oracle 而言并不相同.Oracle 需要知道完全正在使用相同的类型，部分原因是它可以跟踪对象之间的依赖关系.

But it isn't. It has the same structure - fields and datatypes - but is not the same as far as Oracle is concerned. Oracle needs to know that exactly the same type is being used, partly so that it can keep track of dependencies between objects.

您的匿名块需要引用包类型，而不是声明自己的 - 相似但有冲突的 - 类型:

Your anonymous block needs to refer to the package types, rather than declaring its own - similar but conflicting - type(s):

DECLARE
    managers hr_package.managers_table_type;
BEGIN
    managers := hr_package.managers_multiple_departments;
END;

作为奖励，它涉及的输入要少得多，这意味着您不必管理重复的类型.

As a bonus it involves much less typing, and means you don't have to manage duplicate types.

不过，这也意味着类型声明必须在包规范中 - 当然，对于任何您想要公开可见的内容都是如此.

It does also mean, though, that the type declarations have to be in the package specification - which is the case for anything you want to be publicly visible, of course.

这篇关于PLS-00382:通过执行函数并尝试将返回类型放入变量中，表达式的类型错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！