CODEWITHSUNDEEP
oracle-databaseplsqlstored-functions
krise
krise

Reputation: 535

Having a function return an updated part of a table

Lets say I have a table A. I want to write a function that creates a temporary table that contains all entries from A that match a given condition. Then, I want to update values in that temporary table without updating table A, and then return the temporary table as the result of the function. I already have defined row and table types for the return value of my function.

Now the question is how do I create, fill and update a temporary table in a function?

I have stumbled upon Common Table Expressions which seem to do what I want but I have also read that updating an CTE also updates the table behind it.

Any ideas?

Upvotes: 0

Views: 374

Answers (1)

Alex Poole
Alex Poole

Reputation: 191425

As you already have row and table types defined, you can use a (pipelined) collection return type. You haven't shown your table or types so with some simple invented ones:

create table a (col1, col2, col3) as
select 1, 'First', date '2019-01-01' from dual
union all
select 2, 'Second', date '2019-01-01' from dual
union all
select 3, 'Third', date '2019-01-01' from dual;

create type t_row as object (
  x varchar2(10),
  y date
)
/

create type t_table as table of t_row
/

you can then have a function like this:

create or replace function foo
return t_table pipelined as
  l_table t_table;
begin
  select t_row(col2, col3)
  bulk collect into l_table
  from a
  where col1 > 1;

  for i in l_table.first..l_table.last loop
    -- do any updates you need
    l_table(i).y := l_table(i).y + i * interval '1' day;
  end loop;

  -- do any thing else you need

  for i in l_table.first..l_table.last loop
    -- return modified data
    pipe row (l_table(i));
  end loop;

  return;
end;
/

You don't need multiple loops but I've done it like that to show the stages.

how do I create, fill and update a temporary table in a function?

Creation is by declaring l_table using your table type, and filling is with bulk collect into that local collection. Updating is then by simply assigning new values to fields in your 'row' type, which can be based on exiting values or from some other source. Here I've just incremented the dates in my dummy data, inside the first loop over all the elements in the collection.

Then to return the modified table data to the caller, I've used a second loop which pipes each row out. Calling the function then returns:

select * from table(foo);

X          Y         
---------- ----------
Second     2019-01-02
Third      2019-01-03

The original table is unmodified:

select * from a;

      COL1 COL2   COL3      
---------- ------ ----------
         1 First  2019-01-01
         2 Second 2019-01-01
         3 Third  2019-01-01

You don't have to use a pipelined function, you can just return the collection in one go:

create or replace function foo
return t_table as
  l_table t_table;
begin
  select t_row(col2, col3)
  bulk collect into l_table
  from a
  where col1 > 1;

  for i in l_table.first..l_table.last loop
    -- do any updates you need
    l_table(i).y := l_table(i).y + i * interval '1' day;
  end loop;

  -- do any thing else you need

  return l_table;
end;
/

select * from table(foo);

X          Y         
---------- ----------
Second     2019-01-02
Third      2019-01-03

which looks the same to the caller; piping rows can be more efficient though, and makes it easier to work with bulk collect's limit.

db<>fiddle

With something this trivial you obviously don't need a function at all, and can just manipulate the data in your original table as part of a plain SQL statement (as @Boneist mentioned in a comment). You generally need to be doing something quite complicated to make this sort of approach worthwhile.

Upvotes: 1

Related Questions