Oracle 11g-如何使用表联接从函数返回记录 [英] Oracle 11g - how to return record from function with table join

问题描述

在Oracle 11g中，我试图从与表连接的函数调用中返回多个列.此函数以employee_id作为输入，并且应从雇员表中返回first_name和last_name作为两个单独的列.

In Oracle 11g , I am trying to return multiple columns from a function call which is joined with a tables. This function takes employee_id as input and should return first_name and last_name as two separate columns from employees table.

我创建了一个类型

create or replace
type mytype as object
  ( val_1 varchar2(100),
    val_2 number
  );
/

和功能

create or replace
function myfunc(p_in number) return mytype is
    v_deptname varchar2(100);
    v_mgrid number;
begin
    select department_name,manager_id into v_deptname,v_mgrid from DEPARTMENTS  where department_id = p_in;
return
 mytype(v_deptname,v_mgrid);
end;
/

两个都成功创建.但是当我执行功能时，

Both got created successfully. But when I execute function ,

select employee_id, salary, myfunc(department_id) from EMPLOYEES where employee_id in(100,101); 

它给出如下结果，

EMPLOYEE_ID     SALARY
----------- ----------
MYFUNC(DEPARTMENT_ID)(VAL_1, VAL_2)
--------------------------------------------------------------------------------
        100      24000
MYTYPE('Executive', 100)

        101      17000
MYTYPE('Executive', 100)

但是我希望我的结果像，

But i want my result to be like ,

EMPLOYEE_ID     SALARY VAL_1                               VAL_2
----------- ---------- ------------------------------ ----------
        100      24000 Executive                             100
        101      17000 Executive                             100

请帮助实现这一目标. 谢谢

Please help to achieve this. Thanks

推荐答案

好吧，您可能还缺少基于MYTYPE的另一种类型.

Well, you might be missing yet another type, based on MYTYPE.

这是一个示例(由于没有您的表，因此我使用的是Scott的架构).我已将DEPTNO添加到MYTYPE中，以便能够将结果(由函数返回)与EMP表联接.

Here's an example (I'm using Scott's schema as I don't have your tables). I've added DEPTNO into MYTYPE so that I'd be able to join the result (returned by the function) with the EMP table.

这是您拥有的:

SQL> create or replace type mytype as object
  2    (deptno number,
  3     dname  varchar2(20),
  4     loc    varchar2(20));
  5  /

Type created.

这是您所缺少的:

SQL> create or replace type mytab as table of mytype;
  2  /

Type created.

一个功能:注释行9:

SQL> create or replace function myfunc (p_in number) return mytab is
  2    v_dname varchar2(20);
  3    v_loc   varchar2(20);
  4  begin
  5    select dname, loc
  6      into v_dname, v_loc
  7      from dept
  8      where deptno = p_in;
  9    return mytab(mytype(p_in, v_dname, v_loc));
 10  end myfunc;
 11  /

Function created.

测试:

SQL> select * from table(myfunc(10));

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 ACCOUNTING           NEW YORK

SQL>
SQL> select e.ename, e.sal, m.dname, m.loc
  2  from emp e join table(myfunc(e.deptno)) m on m.deptno = e.deptno
  3  where e.deptno = 10
  4  order by m.dname, e.ename;

ENAME             SAL DNAME                LOC
---------- ---------- -------------------- --------------------
CLARK            2450 ACCOUNTING           NEW YORK
KING            10000 ACCOUNTING           NEW YORK
MILLER           1300 ACCOUNTING           NEW YORK

SQL>

这篇关于Oracle 11g-如何使用表联接从函数返回记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！