PACKAGE WHICH CONTAIN FUNCTION TO RETURN SALARY OF A GIVEN EMPLOYEE CODE, A PROCEDURE WHICH DISPLAYS EMPLOYEE’S OF GIVEN DEPARTMENT NUMBER AND ANOTHER PROCEDURE THAT INCREMENT THE SALARY OF GIVEN EMPLOYEE IN ORACLE?

SQL> CREATE PACKAGE EMP_PACK AS

  2  FUNCTION SAL_CHECK (ECODE NUMBER) RETURN

      NUMBER;

  3  PROCEDURE EMP_INFO (DNO NUMBER);

  4  PROCEDURE SAL_INC(ECODE NUMBER);

  5  END EMP_PACK;

  6  /

Package created.

SQL> @ PACK;

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY EMP_PACK

      AS

  2  FUNCTION SAL_CHECK(ECODE NUMBER) RETURN

      NUMBER AS

  3  SALARY EMP.SAL%TYPE;

  4  BEGIN

  5  SELECT SAL INTO SALARY FROM EMP WHERE

      EMPNO=ECODE;

  6  RETURN SALARY;

  7  EXCEPTION

  8  WHEN NO_DATA_FOUND THEN

  9  RETURN 0;

 10  END SAL_CHECK;

 11  PROCEDURE EMP_INFO(DNO NUMBER) AS

 12  CURSOR EMP_CUR IS

 13  SELECT * FROM EMP WHERE DEPTNO=DNO;

 14  BEGIN

 15  FOR I IN EMP_CUR LOOP

 16  DBMS_OUTPUT.PUT_LINE(I.ENAME||' '||I.SAL);

 17  END LOOP;

 18  END EMP_INFO;

 19  PROCEDURE SAL_INC(ECODE NUMBER) AS

 20  BEGIN

 21  UPDATE EMP SET SAL =SAL+500 WHERE

       EMPNO=ECODE;

 22  IF(SQL%FOUND) THEN

 23  DBMS_OUTPUT.PUT_LINE('DATA UPDATED');

 24  ELSE

 25  DBMS_OUTPUT.PUT_LINE('EMPNO DOESNOT EXIST');

 26  END IF;

 27  END SAL_INC;

 28  END EMP_PACK;

 29 /

Package body created.

  

SQL> @ PACKAGE.SQL;

Package body created.

SQL> SELECT EMP_PACK.SAL_CHECK(7369) FROM DUAL;

EMP_PACK.SAL_CHECK(7369)

  ---------------------------------------

               1469.5

SQL> EXEC EMP_PACK.EMP_INFO(10);

CLARK 4540.34

KING 11450

MILLER 2262.94

PL/SQL procedure successfully completed.

SQL> EXEC EMP_PACK.SAL_INC(7369);

DATA UPDATED