create and RETURN a list of numbers in a PL/SQL procedure/function

Question

I have a function where i am summing the value of all sale_lines attached to a sale_head by sale_number. The problem is, the function will only return one sum, whereas I need it to return all sale_head sums in a specific time frame...

    create or replace function sale_line_accounts
      return number is 
       total_value_all number(38, 2);
       value_sales number(12, 2);
       last_month date;
       this_month date;
       sumPrice number(12,2);
--    type array_s is varray(200) of varchar2(30);
--    sums array_s := array_s();
begin
--------------------------------------
last_month := to_date('01/08/16');
this_month := to_date('03/08/16');
--------------------------------------    
  FOR headers IN
  (
    select sale_num, sale_date, status
    from sale_head
    where status = 'S' AND sale_date BETWEEN last_month AND this_month
    ) LOOP
------------------------------------------- 
--sums.extend();--extend array
sumPrice:= 0;
      FOR lines IN
      (
        select sale_num, quantity, actual_price
        from  sale_line
        where sale_num = headers.sale_num
      )LOOP

      sumPrice := sumPrice + (lines.quantity * lines.actual_price);
      dbms_output.put_line(sumPrice);

--      sums(sumPrice) := lines.sale_num;
      END LOOP;

------------------------------------------   
   END LOOP;
-------------------------------------------
  return sumPrice;
END;
/

the commented code was meant to create a collection or something? Please help!!

What sort of collection do you want to return? A nested table, an associative array, or a varray? Or do you want to return a `sys_refcursor`? What do you want that collection to look like? What are you going to do with it when you call this function? I would guess that you want to return a `sys_refcursor` or create a pipelined table function that returns objects that have the `sale_num` and `sumPrice`. But that's just me guessing-- there are lots of alternative signatures possible. — Justin Cave, Sep 12 '16 at 21:21
Have you tried something like this [How to return multiple rows from SP Oracle](http://stackoverflow.com/questions/101033/how-to-return-multiple-rows-from-the-stored-procedure-oracle-pl-sql)? — Angel Doza, Sep 12 '16 at 21:22

score 0 · Answer 1 · answered Sep 13 '16 at 07:27

You can probably get your answer by just using a single SQL query:

select headers.sale_num
      ,sum(lines.quantity * lines.actual_price) price
  from sale_head headers
  join sale_line lines
    on lines.sale_num = headers.sale_num
 where headers.status = 'S'
   and headers.sale_date between to_date('01/08/2016', 'DD/MM/YYYY') and to_date('03/08/2016', 'DD/MM/YYYY')
 group by headers.sale_num

create and RETURN a list of numbers in a PL/SQL procedure/function

1 Answers1