sql - Oracle:在一个函数中返回多个值

Question

我正在尝试从使用两个表(员工和部门)的函数返回 %rowtype 中的多个值，但它对我不起作用。

create or replace function get_employee
 (loc in number)
return mv_emp%rowtype
as  
   emp_record mv_emp%rowtype;
begin
   select a.first_name, a.last_name, b.department_name into emp_record 
   from employees a, departments b 
   where a.department_id=b.department_id and location_id=loc;

   return(emp_record);  
end;

Answer 1

上面的函数编译没有错误？ MV_EMP的类型是什么?理想情况下，它应该如下所示。

create or replace type emp_type
(
first_name varchar2(20)
, last_name varchar2(20)
, depart_name varchar2(20)
)
/
create or replace function get_employee
 (loc in number)
return emp_type
as  
   emp_record emp_type;
begin
   select a.first_name, a.last_name, b.department_name into emp_record 
   from employees a, departments b 
   where a.department_id=b.department_id and location_id=loc;

   return(emp_record);  
end;