How to Add Percentages in PL/SQL?-CodePudding

Probably a stupid question but I am unsure on how I should add 5% to an existing value in pl/sql. I thought it was as simple as 1000 10% but I guess not.

`SET SERVEROUTPUT ON

DECLARE
v_sal emp.sal%TYPE;
v_deptno emp.deptno%TYPE;

BEGIN
SELECT sal, deptno INTO v_sal, v_deptno
FROM emp;
LOOP
 IF v_deptno = 10 THEN
    v_sal   5%;
 ELSIF v_deptno = 20 THEN
    v_sal   7.5%;
 ELSIF v_deptno = 30 THEN
    v_sal   10%;

 ELSE
   dbms_output.put_line('Deptno not found.');
 END IF;
 UPDATE emp
   SET sal = v_sal
   WHERE deptno = v_deptno;
 EXIT;
 END LOOP;
END;
/
SET SERVEROUTPUT OFF
`

Only thing i tried so far is trying to use the % symbol but it doesn't like that for some reason because it throws this error, anything i search online about this is not helpful.

    v_sal 5%;
         *
ERROR at line 10:
ORA-06550: line 10, column 10:
PLS-00103: Encountered the symbol " " when expecting one of the following:
:= . ( @ % ;

CodePudding user response：

This is how; you don't need PL/SQL at all.

Before:

SQL> select deptno, ename, sal from emp order by deptno, ename;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 KING             5000
        10 MILLER           1300
        20 ADAMS            1100
        20 FORD             3000
        20 JONES            2975
        20 SCOTT            3000
        20 SMITH             800
        30 ALLEN            1600
        30 BLAKE            2850
        30 JAMES             950
        30 MARTIN           1250
        30 TURNER           1500
        30 WARD             1250

14 rows selected.

Update:

SQL> update emp set
  2    sal = case when deptno = 10 then sal * 1.050
  3               when deptno = 20 then sal * 1.075
  4               when deptno = 30 then sal * 1.100
  5          end;

14 rows updated.

After:

SQL> select deptno, ename, sal from emp order by deptno, ename;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK          2572.5
        10 KING             5250
        10 MILLER           1365
        20 ADAMS          1182.5
        20 FORD             3225
        20 JONES         3198.13
        20 SCOTT            3225
        20 SMITH             860
        30 ALLEN            1760
        30 BLAKE            3135
        30 JAMES            1045
        30 MARTIN           1375
        30 TURNER           1650
        30 WARD             1375

14 rows selected.

SQL>

If you really need PL/SQL (education?), use a cursor for loop:

SQL> rollback;

Rollback complete.

SQL> declare
  2    l_new_sal number;
  3  begin
  4    for cur_r in (select deptno, empno, sal from emp) loop
  5      if cur_r.deptno = 10 then
  6         l_new_sal := cur_r.sal * 1.050;
  7      elsif cur_r.deptno = 20 then
  8         l_new_sal := cur_r.sal * 1.075;
  9      elsif cur_r.deptno = 30 then
 10         l_new_sal := cur_r.sal * 1.100;
 11      else
 12         dbms_output.put_line('Deptno not in IF');
 13      end if;
 14
 15      update emp set
 16        sal = l_new_sal
 17        where empno = cur_r.empno;
 18    end loop;
 19  end;
 20  /

PL/SQL procedure successfully completed.

Result:

SQL> select deptno, ename, sal from emp order by deptno, ename;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK          2572.5
        10 KING             5250
        10 MILLER           1365
        20 ADAMS          1182.5
        20 FORD             3225
        20 JONES         3198.13
        20 SCOTT            3225
        20 SMITH             860
        30 ALLEN            1760
        30 BLAKE            3135
        30 JAMES            1045
        30 MARTIN           1375
        30 TURNER           1650
        30 WARD             1375

14 rows selected.

SQL>

CodePudding user response：

Just simply divide the value by 100.

As in your case v_sal 5%, replace it with

v_sal 5/100;

It will work fine.