plsql - How to use sum() function inside stored procedure in oracle? -

the example below works fine , return rows. need summary of rows.

declare     x number; cursor c1     select sal,deptno emp;     rw c1%rowtype; begin     x:=0;     open c1;     loop      fetch c1 rw;      in 1..rw.deptno loop        x:=x+rw.sal;      end loop;      exit when c1%notfound;      dbms_output.put_line(x);     end loop;     close c1; end; / 

suppose have 3 employees , every employee's has different salary. salary has due 10 months , 20 months , 30 months. salary due long time. want add 2% bonus amount salary every month way:

the below description single employee 10 months:

month-1 salary = 800 => 800*2% = 16.00 => total = 800+16 =816

month-2 salary = 816 => 816*2% = 16.32 => total = 816+16.32 =832.32

............................................................................

month-10 salary = 956.07 => 956.07*% = 19.12 => total = 956.07+19.12 =975.20

the months-1 total salary=816. month-2 salary=816. continue 10 months.every employee has same condition. need summary of total column. , best regards.

when use aggregate function sum in query (unlike, when adding yourself), don't need convert null. sum takes care of it. although, @davidaldridge pointed, if expect rows in summarized group of records may contain null, sum null. if want return value, can wrap sum follows coalesce(sum(sal),0)

this give sum of salaries

select sum(sal) totalsal emp; 

this give sum department

select sum(sal) totaldeptsal, deptno  emp group deptno; 

in question posted need execute in stored procedure while code anonymous block. if want return single value stored procedure have choice declare function return parameter or stored procedure output parameter. return recordset stored procedure in oracle need declare refcursor output parameter

create or replace procedure get_totalsal_bydept (     p_recordset out sys_refcursor)  begin      open p_recordset         select sum(sal) totaldeptsal, deptno          emp         group deptno; end; 

edit

i see added row - total. not changing original question. still, using cursor not needed. can run 2 queries , return 2 output parameters, 1 data department , total.

create or replace procedure get_salbydept_withtotal (     p_total out number,     p_recordset out sys_refcursor) begin      select sum(sal) p_total emp;      open p_recordset         select sum(sal) totaldeptsal, deptno          emp         group deptno; end;