Friday, 21 June 2013

PROGRAM TO INCREASE THE SALARY OF EMPLOYEES IN ORACLE?



 SQL> DECLARE
        2   CURSOR SAL_DATA IS
        3   SELECT EMPNO, JOB FROM EMP;
        4   ECODE EMP.EMPNO%TYPE;
        5   POST EMP.JOB%TYPE;
        6   BEGIN
        7   OPEN SAL_DATA;
        8   LOOP
        9   FETCH SAL_DATA INTO ECODE, POST;
       10   EXIT WHEN SAL_DATA%NOTFOUND;
       11   IF POST='MANAGER' THEN
       12   UPDATE EMP SET SAL=SAL*1.10 WHERE       
              EMPNO=ECODE;
       13   ELSIF POST='CLERK' THEN
       14   UPDATE EMP SET SAL =SAL*1.08 WHERE 
              EMPNO=ECODE;
       15   ELSIF POST='ANALYST' THEN
       16   UPDATE EMP SET SAL=SAL*1.09 WHERE
              EMPNO=ECODE;
       17   ELSIF POST='PRESIDENT' THEN
       18   UPDATE EMP SET SAL=SAL*1.50 WHERE  
              EMPNO=ECODE;
       19   END IF;
       20   END LOOP;
       21   CLOSE SAL_DATA;
       22   END;
       23       /

PL/SQL procedure successfully completed.

  SQL> START INCREASESAL.SQL;

PL/SQL procedure successfully completed.
SQL> SELECT SAL FROM EMP;

      SAL
    ---------
   1269.5
  2042.05
  1595.36
   5270.4
  1595.36
  5048.96
  4340.34
   3564.3
    11250
  1914.42
  1745.55
  1507.54
   3564.3
  2062.94

14 rows selected.

No comments:

Post a Comment