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

Comments

Popular posts from this blog

Write a program to add two number using inline function in C++?

Traversing of elements program with algorithm and Flowchart