pl/sql if loop not working

Question

I have to alter the sal but it is choosing the wrong else statement when running the PL/SQL

Here is my newemp3 table:

SQL> select *
  2  from newemp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30

      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30

     7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10

here is my code:

SET VERIFY OFF
DECLARE
   v_ename    newemp3.ename%TYPE; 
   v_sal      newemp3.sal%TYPE;
   v_newgoal  newemp3.sal%TYPE;
   v_deptno   newemp3.deptno%TYPE;
BEGIN
   --assign some value
   v_ename := null; --if null - it updates all records in table

   for rc in (SELECT ename, sal, deptno
     FROM newemp3
     WHERE ename = nvl(v_ename, ename)) loop

     IF rc.deptno = 10 AND rc.sal > 2000 then 
       v_newgoal := rc.sal * 1.06;
     else 
       v_newgoal := rc.sal * 1.07; 

       IF rc.deptno = 20 AND rc.sal > 2500 then 
         v_newgoal := rc.sal * 1.05;
       else 
         v_newgoal := rc.sal * 1.055;

         IF rc.deptno = 30 AND rc.sal > 1000 then 
           v_newgoal := rc.sal * 1.07;
         else 
           v_newgoal := rc.sal * 1.065;
         END IF;

       END IF; 

     END IF; 

     UPDATE newemp3
     SET sal = v_newgoal
     WHERE ename = rc.ename;

   end loop;

COMMIT;
END;
/
SET VERIFY ON

The last row is miller which has a 1300 sal. So that should be 1300 * 1.07 which equals 1391. The problem is, it is coming up with 1384.5, which is using the 1.065 from the dept 30 if statement.

If you formatted your code you'd see the problem more easily. Your IF statements are nested. MILLER is dept 10 salary 1300. The first IF statement returns FALSE so your code uses 1.07, then the next IF statement returns FALSE and recalcs using 1.055, and then the final IF statement returns FALSE and recalcs using 1.065. — Jeffrey Kemp
– Jeffrey Kemp, Commented Nov 27, 2015 at 3:36
P.S. you should replace this entire PL/SQL program with a single UPDATE statement. — Jeffrey Kemp
– Jeffrey Kemp, Commented Nov 27, 2015 at 3:41
I agree with Jeffrey. Doing this in a PL/SQL loop is slow, inefficient and will not scale. A single UPDATE (or even several UPDATE statements) would be a lot faster, easier to understand and easier to debug. — user330315
– user330315, Commented Nov 27, 2015 at 6:50

Mr Rubix · Accepted Answer · 2015-11-27 01:18:08Z

2

Im not sure of your logic but maybe u should try with this kind of structure for your IF Then ELSE

IF(rc.deptno = 10)Then
    IF(rc.sal > 2000)Then
        v_newgoal := rc.sal * 1.06;
    ELSE 
        v_newgoal := rc.sal * 1.07; 
    END IF;    
END IF;

IF(rc.deptno = 20)Then
    IF(rc.sal > 2500)Then
        v_newgoal := rc.sal * 1.05;
    ELSE 
        v_newgoal := rc.sal * 1.055; 
    END IF;
END IF;

IF(rc.deptno = 30)Then
    IF(rc.sal > 1000)Then     
        v_newgoal := rc.sal * 1.07;
    ELSE 
        v_newgoal := rc.sal * 1.065; 
    END IF;    
END IF;

answered Nov 27, 2015 at 1:18

Mr Rubix

1,52716 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeffrey Kemp Over a year ago

Maybe, but this doesn't answer the question.

Bob Jarvis - Слава Україні · Accepted Answer · 2015-11-27 21:31:54Z

Let's follow the logic for the row you're interested in for Miller, who has a salary of 1300 and a DEPTNO of 10:

Is Miller's deptno = 10 and his salary > 2000? No, so we take the first ELSE
v_newgoal := 1300 * 1.07 = 1391.
Is Miller's deptno = 20 and his salary > 2500? No, so we take the second ELSE
v_newgoal := 1300 * 1.055 = 1371.50
Is Miller's deptno = 30 and his salary > 1000? No, so we take the third ELSE
v_newgoal := 1300 * 1.065 = 1384.50

So the result your code will produce given this input is 1384.50, which is precisely what you're getting.

Obviously something needs to be changed here, but since we don't have the full statement of the problem you're trying to solve there's not much we can do to suggest what those changes need to be. But hopefully this points you in the right direction.

Best of luck.

Collectives™ on Stack Overflow

pl/sql if loop not working

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related