如何将两列返回到一个函数中

Question

如何将两列返回到一个函数中？

例如："EmployeeName | salary"，值为"Jonas Daniel | $2500$

Answer 1

使用ref光标是一个选项：

SQL> create or replace function f_test (par_empno in number)
2  return sys_refcursor
3  is
4    rc sys_refcursor;
5  begin
6    open rc for select ename, sal from emp
7                where empno = par_empno;
8    return rc;
9  end;
10  /
Function created.
SQL> select f_test(7839) from dual;
F_TEST(7839)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
ENAME             SAL
---------- ----------
KING            10000

SQL>

或者，你可以尝试这种方法：

SQL> create or replace type t_test_row is object
2    (ename     varchar2(20),
3     sal       number);
4  /
Type created.
SQL> create or replace type t_test_tab is table of t_test_row;
2  /
Type created.
SQL> create or replace function f_test (par_empno in number)
2    return t_test_tab
3  is
4    l_tab t_test_tab := t_test_tab();
5  begin
6    select t_test_row(ename, sal)
7    bulk collect into l_tab
8    from emp
9    where empno = par_empno;
10
11    return l_tab;
12  end;
13  /
Function created.
SQL> select f_test(7839) from dual;
F_TEST(7839)(ENAME, SAL)
--------------------------------------------------------------------------------
T_TEST_TAB(T_TEST_ROW('KING', 10000))
SQL> -- Or, a nice presentation
SQL> select * from table(f_test(7839));
ENAME                       SAL
-------------------- ----------
KING                      10000
SQL>

或者，您可以创建一个过程：

SQL> set serveroutput on
SQL> create or replace procedure p_test (par_empno in number,
2                                      par_ename out varchar2,
3                                      par_sal   out number)
4  is
5  begin
6    select ename, sal
7      into par_ename, par_sal
8      from emp
9      where empno = par_empno;
10  end;
11  /
Procedure created.
SQL> declare
2    l_ename varchar2(20);
3    l_sal   number;
4  begin
5    p_test(7839, l_ename, l_sal);
6    dbms_output.put_line('Name = ' || l_ename ||', salary = ' || l_sal);
7  end;
8  /
Name = KING, salary = 10000
PL/SQL procedure successfully completed.
SQL>

但是，如果您打算返回两个标量值，那么您可能运气不好。