CODEWITHSUNDEEP
sqloracle-databaseplsql
Rey Libutan
Rey Libutan

Reputation: 5314

How to return record from an Oracle function with JOIN query?

I can easily create function that returns a myTable%ROWTYPE but what if my query (in the function) as another column from ANOTHER table?

t1_rec t1%ROWTYPE;

Do I append new fields to the major table or what?

SELECT t1.col1, t1.col2, t1.col3, t2.col4...

Question:

How do I efficiently create a record type to contain the result set of the above query?

What I want is something like

t1_rec t1%ROWTYPE (plus another column for t2.col4%TYPE inside t1_rec)

Sorry for the vague question at first.

Upvotes: 2

Views: 1220

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30845

You can use a strongly typed cursor and its rowtype:

-- example data
create table t1(pk number not null primary key, val varchar2(30));
create table t2(
  pk number not null primary key, 
  t1_fk references t1(pk), 
  val varchar2(30));

insert into t1(pk, val) values(1, 'value1');
insert into t2(pk, t1_fk, val) values(1, 1, 'value2a');
insert into t2(pk, t1_fk, val) values(2, 1, 'value2b');

declare
  cursor cur is 
  select t1.*, t2.val as t2_val 
  from t1
  join t2 on t1.pk = t2.t1_fk;

  function get_data(arg in pls_integer) return cur%rowtype is
      l_result cur%rowtype;
    begin
      select t1.*, t2.val as t2_val 
        into l_result 
        from t1 
        join t2 on t1.pk = t2.t1_fk
        where t2.pk = arg;
      return l_result;
    end;
begin
  dbms_output.put_line(get_data(2).t2_val);
end;

UPDATE: you can easily wrap the cursor and function inside a PL/SQL package:

create or replace package pkg_get_data as 

  cursor cur is 
  select t1.*, t2.val as t2_val 
  from t1
  join t2 on t1.pk = t2.t1_fk;

  function get_data(arg in pls_integer) return cur%rowtype;
end;

(package body omitted)

Upvotes: 2

Related Questions