CODEWITHSUNDEEP
oracle-databaseplsql
Coat
Coat

Reputation: 717

Sum of Nested Table

I have a nested table of items and I'd like to use sql statements like sum on it if possible. Something like...

create type SumMe as object
(
  my_num number
);
/
create type SumThese as table of SumMe;
/
declare
  local_summation SumThese;
  l_totalSum number;
begin
  --populate local_summation
  select sum(my_num) into l_totalSum from local_summation;
end;

Is this possible? Nested tables aren't required but I do need to store the array type into a database table.

Upvotes: 1

Views: 938

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You can use a table collection expression to treat your table type as a table in a query:

select sum(my_num) into l_totalSum from table(local_summation);

Quick demo:

set serveroutput on;
declare
  local_summation SumThese;
  l_totalSum number;
begin
  --populate local_summation
  local_summation := SumThese();
  local_summation.extend(2);
  local_summation(1) := SumMe(22);
  local_summation(2) := SumMe(20);

  select sum(my_num) into l_totalSum from table(local_summation);
  dbms_output.put_line('Total: ' || l_totalSum);
end;
/

PL/SQL procedure successfully completed.
Total: 42

Upvotes: 3

Related Questions