Geek Answers Handbook

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

The example below works fine and returns multiple rows. But I need a summary of the lines.

DECLARE
    x number;
Cursor c1 is
    select sal,deptno from emp;
    rw c1%rowtype;
BEGIN
    x:=0;
    open c1;
    LOOP
     fetch c1 into rw;
     FOR i 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 you have three employees and each employee has a different salary. Payment is due for 10 months and 20 months and 30 months. Salary is a long time. Thus, you want to add a 2% bonus amount with a salary for each month as follows:

The following is a description for one employee for 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

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

-10 = 956,07 = > 956,07 *% = 19,12 = > = 956,07 + 19,12 = 975,20

-1 = 816. , -2 = 816. 10 . . . .

+4
2

SUM ( , ), NULL. SUM . , @DavidAldridge, , NULL, NULL. , : coalesce(sum(sal),0)

select SUM(sal) TotalSal from emp;

SUM

select SUM(sal) TotalDeptSal, deptno 
from emp
group by deptno;

, , . , return . Oracle, refcursor

CREATE OR REPLACE PROCEDURE Get_TotalSal_ByDept (
    p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
    OPEN p_recordset FOR
        select SUM(sal) TotalDeptSal, deptno 
        from emp
        group by deptno;
END;

Edit

, - . . , . 2 2 , , - .

CREATE OR REPLACE PROCEDURE Get_SalByDept_WithTotal (
    p_total OUT NUMBER,
    p_recordset OUT SYS_REFCURSOR) AS
BEGIN 
    select SUM(sal) INTO p_total from emp;

    OPEN p_recordset FOR
        select SUM(sal) TotalDeptSal, deptno 
        from emp
        group by deptno;
END;
+2

, ? ?

SELECT totals.deptNo, totals.depttotal, SUM(totals.depttotal) OVER (ORDER BY totals.id)
FROM (
      select deptNo, deptTotal, rownum id
      from ( 
            select deptNo, sum(sal * deptNo) deptTotal
            from emp
            group by deptNo)
     ) totals
ORDER BY totals.id;

- , ROWNUM

+1

More articles:


All Articles