Oracle: returning multiple values ​​to a function

Question

I am trying to return multiple values ​​in% rowtype from a function using two tables (employees and departments), but it does not work for me.

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

The above function compiled without errors? What is the type of MV_EMP ? Ideally, it should be something like below.

 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; 

Answer 2

 create type t_row as object (a varchar2(10)); create type t_row_tab as table of t_row; 

Now we will create a function that splits the input string.

 create or replace function get_number(pv_no_list in varchar2) return t_row_tab is lv_no_list t_row_tab := t_row_tab(); begin for i in (SELECT distinct REGEXP_SUBSTR(pv_no_list, '[^,]+', 1, LEVEL) no_list FROM dual CONNECT BY REGEXP_SUBSTR(pv_no_list, '[^,]+', 1, LEVEL) IS NOT NULL) loop lv_no_list.extend; lv_no_list(lv_no_list.last) := t_row(i.no_list); end loop; return lv_no_list; end get_number; 

Once the function is in place, we can use the table clause of the sql operator to get the desired result. At will we received some values ​​returned by function.

 SQL> select * from table(get_number('1,2,3,4')); A ---------- 1 3 2 4 

So, now our function just behaves like a table. There may be times when you want these comma separated values ​​to be part of the "IN" clause.

For instance:

 select * from dummy_table where dummy_column in ('1,2,3,4'); 

But the above request will not work, since "1,2,3,4" is a string, not separate numbers. To solve this problem, you can simply use the following query.

 select * from dummy_table where dummy_column in ( select * from table(get_number('1,2,3,4')) ); 

Links: http://www.oraclebin.com/2012/12/returning-multiple-values-from-function.html