PL/SQL PROBLEM for increasing the salary of every emp that work in specif dept-CodePudding

Increase the Salary of employees working in deptno 10 by 15%, Deptno 20 by 15% and others by 5% Also display the corresponding the employees working in that Dept. Use a parameter Cursor and Cursor with Update clause.

i tried doing it but not getting the solution. this is my code.

DECLARE
   CURSOR V_C1 (V_DEPTNO EMP.DEPTNO%TYPE)
   IS
          SELECT ENAME, SAL, DEPTNO
            FROM EMP
           WHERE DEPTNO = V_DEPTNO
      FOR UPDATE OF SAL NOWAIT;

   V_REC  V_C1%ROWTYPE;
BEGIN
   FOR V_REC IN V_C1 ('V_DEPTNO')
   LOOP
      IF V_REC.DEPTNO = 10
      THEN
         UPDATE EMP
            SET SAL = V_REC.SAL   V_REC.SAL * 15 / 100
          WHERE DEPTNO = 'V_DEPTNO';

         DBMS_OUTPUT.PUT_LINE (
            V_REC.ENAME || ' ' || V_REC.SAL || ' ' || V_REC.DEPTNO);
      ELSIF V.REC.DEPTNO = 20
      THEN
         UPDATE EMP
            SET SAL = V_REC.SAL   V_REC.SAL * 20 / 100
          WHERE DEPTNO = 'V_DEPTNO';

         DBMS_OUTPUT.PUT_LINE (
            V_REC.ENAME || ' ' || V_REC.SAL || ' ' || V_REC.DEPTNO);
      ELSE
         UPDATE EMP
            SET SAL = V_REC.SAL   V_REC.SAL * 5 / 100
          WHERE DEPTNO = 'V_DEPTNO';

         DBMS_OUTPUT.PUT_LINE (
            V_REC.ENAME || ' ' || V_REC.SAL || ' ' || V_REC.DEPTNO);
      END IF;
   END LOOP;
END;

note- i need to increase the salary of all the employees that work in each department without explicitly giving dept no i.e the program should take all the deptno and increase the salary of their emp that work in that dept automatically

must be in pl/sql

CodePudding user response：

OK, that's for educational purposes so you need to use cursors and loops and whatnot. Otherwise, you'd just

update emp set
  sal = sal * case when e.deptno in (10, 20) then 1.15
                   else 1.05
              end;

Anyway: if you have to use a parametrized cursor (for employees), then you first have to fetch department - that's another cursor. Don't hardocde departments (in IF-THEN-ELSE), use CASE expression instead. Then you could do it as follows:

SQL> DECLARE
  2     CURSOR c_dept IS SELECT deptno FROM dept;
  3
  4     cr_dept  dept.deptno%TYPE;
  5
  6     CURSOR c_emp (par_deptno IN dept.deptno%TYPE)
  7     IS
  8        SELECT empno, ename, sal old_sal
  9          FROM emp
 10         WHERE deptno = par_deptno;
 11
 12     cr_emp   c_emp%ROWTYPE;
 13     new_sal  emp.sal%TYPE;
 14  BEGIN
 15     OPEN c_dept;
 16
 17     LOOP
 18        FETCH c_dept INTO cr_dept;
 19
 20        EXIT WHEN c_dept%NOTFOUND;
 21
 22        DBMS_OUTPUT.put_line ('Department ' || cr_dept);
 23
 24        OPEN c_emp (cr_dept);
 25
 26        LOOP
 27           FETCH c_emp INTO cr_emp;
 28
 29           EXIT WHEN c_emp%NOTFOUND;
 30
 31              UPDATE emp e
 32                 SET e.sal =
 33                        ROUND (
 34                             e.sal
 35                           * CASE
 36                                WHEN e.deptno IN (10, 20) THEN 1.15
 37                                ELSE 1.05
 38                             END)
 39               WHERE e.empno = cr_emp.empno
 40           RETURNING ROUND (sal)
 41                INTO new_sal;
 42
 43           DBMS_OUTPUT.put_line (
 44                 RPAD (cr_emp.ename, 15, ' ')
 45              || ': old salary = '
 46              || TO_CHAR (cr_emp.old_sal, '9990')
 47              || ', new salary = '
 48              || TO_CHAR (new_sal, '9990'));
 49        END LOOP;
 50
 51        CLOSE c_emp;
 52     END LOOP;
 53
 54     CLOSE c_dept;
 55  END;
 56  /

which results in

Department 10
CLARK          : old salary =  2450, new salary =  2818
KING           : old salary =  5000, new salary =  5750
MILLER         : old salary =  1300, new salary =  1495
Department 20
SMITH          : old salary =   800, new salary =   920
JONES          : old salary =  2975, new salary =  3421
SCOTT          : old salary =  3000, new salary =  3450
ADAMS          : old salary =  1100, new salary =  1265
FORD           : old salary =  3000, new salary =  3450
Department 30
ALLEN          : old salary =  1600, new salary =  1680
WARD           : old salary =  1250, new salary =  1313
MARTIN         : old salary =  1250, new salary =  1313
BLAKE          : old salary =  2850, new salary =  2993
TURNER         : old salary =  1500, new salary =  1575
JAMES          : old salary =   950, new salary =   998
Department 40

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

You can do it in a single SQL update. If you want to use PL/SQL to display the changes then:

DECLARE
  TYPE name_list IS TABLE OF EMP.ENAME%TYPE;
  TYPE sal_list  IS TABLE OF EMP.SAL%TYPE;
  TYPE dept_list IS TABLE OF EMP.DEPTNO%TYPE;
  
  names name_list;
  sals  sal_list;
  depts dept_list;
BEGIN
  UPDATE EMP
  SET sal = sal * CASE WHEN deptno IN (10, 20) THEN 1.15 ELSE 1.05 END
  RETURNING ename, sal, deptno BULK COLLECT INTO names, sals, depts;
  
  FOR i IN 1 .. names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE (
      names(i) || ' ' || sals(i) || ' ' || depts(i)
   );
  END LOOP;
END;
/

If you really need cursors then:

DECLARE
  CURSOR v_depts IS
    SELECT DEPTNO
    FROM   dept;

  CURSOR V_C1 (V_DEPTNO EMP.DEPTNO%TYPE) IS
    SELECT EMPNO, ENAME, SAL, DEPTNO
    FROM   EMP
    WHERE  DEPTNO = V_DEPTNO
    FOR UPDATE OF SAL NOWAIT;

  V_DEPT V_depts%ROWTYPE;
  V_REC  V_C1%ROWTYPE;
  v_sal  EMP.SAL%TYPE;
BEGIN
  FOR v_dept IN v_depts
  LOOP
    FOR V_REC IN V_C1 (v_dept.deptno)
    LOOP
      UPDATE EMP
      SET   SAL   = SAL * CASE WHEN deptno IN (10, 20) THEN 1.15 ELSE 1.05 END
      WHERE EMPNO = V_REC.EMPNO
      RETURNING sal INTO v_sal;

      DBMS_OUTPUT.PUT_LINE (
         V_REC.ENAME || ' ' || v_sal || ' ' || V_REC.DEPTNO
      );
    END LOOP;
  END LOOP;
END;
/

db<>fiddle here

CodePudding user response：

Remove the single quotes in V_DEPTNO from WHERE clause, so SQL would search for the content of V_DEPNO. Otherwise, SLQ search DEPTNO field against the literal word V_DEPNO.