CODEWITHSUNDEEP
sqloracle-databaseplsql
user2805507
user2805507

Reputation:

PL/SQL Defining the select statement for sum in If and loop

For an example, we have employer salary and department based in emp table.

In SQL , we can retrieve the total salary per department just by doing this 

SELECT SUM(SAL) FROM EMP WHERE DEPTNO = 20 ; //Lets put it we finding sum for dept 20.

But where else for pl/sql , I think I am syntactically wrong . I am using cursor to store all the datas. in my if statement which is inside a loop below , I tried my logic with 

if deptno = 20 THEN
   totalSalary = sum(salary);
   DBMS_OUTPUT.PUT_LINE('Department : 20' || total Salary);

This is my actual code .

DECLARE 
msal emp.salary%TYPE;
mdept emp.departmentNo%TYPE; 
new_salary number(10,2);

CURSOR E1 IS select salary , departmentNo from emp;


BEGIN
OPEN E1;
  LOOP
    FETCH E1 into msal , mdeptno;       
      IF mdeptno = 20 THEN
          ---- I cant seems to find the right logic here  to print out the total sum for department 20---
      END IF;   
    EXIT WHEN E1%NOTFOUND;
  END LOOP;

CLOSE E1;
END;
/

Thanks in advance

Upvotes: 0

Views: 2566

Answers (4)

user2805507
user2805507

Reputation:

thank you for all your response , I am assigned to do this in pl/sql ,

i appreciate all the response from you guys.
I have come out with the easiest technique in getting the result for sum, min and max and avg.

What i basically did was using the inbuild function that was already in sql and implement it in my select statement . So i have solve it by 

DECLARE 
totalSal emp.salary%TYPE;
maxSal emp.salary%TYPE;
minSal emp.salary%TYPE;
mdept emp.departmentNo%TYPE := 20; /* for an example getting all values for dept 20 */ 

CURSOR E1 IS select sum(salary) , max(salary) , min(sal) from emp where
departmentNo = mdept group by departmentNo;


BEGIN

   OPEN E1;
       LOOP
        FETCH E1 into totalSal , maxSal , minSal;
        EXIT WHEN E1%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE('...' || maxsal); /*...so on for display*/
       END LOOP;

   CLOSE E1;

  END; /

Upvotes: 0

Dmitriy
Dmitriy

Reputation: 5565

There is no need to use PL/SQL for thing which you can do with SQL. But if you are really need it, use following:

DECLARE 
  msal emp.salary%TYPE;
  mdept emp.departmentNo%TYPE; 
  new_salary number(10,2);

  CURSOR E1 (p_dept number) IS 
    select sum(salary) from emp where departmentNo = p_dept;


BEGIN
  OPEN E1(20);
  FETCH E1 into msal;       
  dbms_output.put_line(msal);
  CLOSE E1;
END;
/

Upvotes: 1

Patrick Bacon
Patrick Bacon

Reputation: 4660

Keep a running total with the variable, totalSalary

The algorithm is as follows:

-fetch values from cursor, e1, and assign to msal and mdeptno

-for each fetched value of msal (within the loop), keep a running total using the variable, tot_sal (or totalSalary as you originally posted)

With your approach, the assignment,totalSalary := sum(salary);, does nothing but throws errors because salary is not defined as a variable. The cursor fetches the value of salary (mine is sal) and it is assigned to msal.

If the assignment was totalSalary := sum(msal);, then it would writes over the previous fetched value and does not keep track of a running total.

The standard way to do this is initialize tot_sal to zero (or totalSalary) before the loop and then keep a running total for each fetched record:

tot_sal := tot_sal + msal;

Here is the simple sql result juxtaposed with the anonymous block:

         SCOTT@dev>SELECT
      2      SUM(sal)
      3  FROM
      4      emp
      5  WHERE
      6      deptno = 20;
    SUM(SAL)  
    10875     


    SCOTT@dev>DECLARE
  2      msal      emp.sal%TYPE;
  3      mdeptno   emp.deptno%TYPE;
  4      tot_sal   emp.sal%TYPE;
  5      CURSOR e1 IS
  6          SELECT
  7              sal,
  8              deptno
  9          FROM
 10              emp;
 11  
 12  BEGIN
 13      tot_sal := 0;
 14      OPEN e1;
 15      LOOP
 16          FETCH e1 INTO msal,mdeptno;
 17          IF
 18              mdeptno = 20
 19          THEN
 20              tot_sal := tot_sal + msal;
 21          END IF;
 22  
 23          EXIT WHEN e1%notfound;
 24      END LOOP;
 25  
 26      CLOSE e1;
 27      dbms_output.put_line(tot_sal);
 28  END;
 29  /
10875


PL/SQL procedure successfully completed.

Upvotes: 0

fg78nc
fg78nc

Reputation: 5232

You can introduce another variable for sum aggregation and in each loop iteration add it up. And then print out once required.

DECLARE 
msal emp.salary%TYPE;
mdept emp.departmentNo%TYPE; 
sum_salary number(10,2) := 0;

CURSOR E1 IS select salary , departmentNo from emp;


BEGIN
OPEN E1;
  LOOP
    FETCH E1 into msal , mdeptno;
     sum_salary := sum_salary + msal;
      IF mdeptno = 20 THEN
          dbms_output.put_line(sum_salary);   
    EXIT WHEN E1%NOTFOUND;
  END LOOP;

CLOSE E1;

PL/SQL allows you to access row by row, ( that is done by SQL behind the scenes for you)

Upvotes: 0

Related Questions