sql - 从 Oracle PL/SQL 中的函数返回记录时出错

Question

DECLARE
TYPE type_supplier IS
RECORD(supp_phone supplier.supplier_phone%TYPE,
        supp_status supplier.supplier_status%TYPE);

FUNCTION fn_supplier(supp_name supplier.supplier_name%TYPE)
RETURN type_supplier
IS supp type_supplier;

BEGIN
    select supplier_phone, supplier_status
    into supp.supp_phone, supp.supp_status
    from supplier 
    where supplier_name = supp_name;
    RETURN supp;
END;

问题是：创建一个函数，返回输入的供应商名称的电话号码和状态。供应商表有供应商 ID、供应商名称、供应商地址、供应商电子邮件、供应商电话、供应商状态。我在上面的代码中遇到错误：

Error report -
ORA-06550: line 16, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   begin function pragma procedure
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

我不知道从 PL/SQL 函数返回多个值。我为此使用记录类型，但收到错误。

我愿意接受任何解决方案、更改或替代方法..

标签： sqloracleplsqlsql-function

Answer 1

您的声明TYPE不正确。

您可以使用该OBJECT类型并按如下方式使用它：

create TYPE type_supplier IS
OBJECT(supp_phone supplier.supplier_phone%TYPE,
        supp_status supplier.supplier_status%TYPE);
/

FUNCTION fn_supplier(supp_name supplier.supplier_name%TYPE)
RETURN type_supplier
IS supp type_supplier;

BEGIN
    select supplier_phone, supplier_status
    into supp.supp_phone, supp.supp_status
    from supplier 
    where supplier_name = supp_name;
    RETURN supp;
END;
/