Package example

 

 Package specification creation  

CREATE OR REPLACE PACKAGE MY_PACK IS

FUNCTION ADD_NUM(X NUMBER, Y NUMBER) RETURN NUMBER  ;

FUNCTION AOR( L NUMBER , B NUMBER) RETURN NUMBER ;

PROCEDURE UPDATE_SAL(P_EMPNO NUMBER, P_SAL NUMBER) ;

END MY_PACK;

Package Body creation

CREATE OR REPLACE PACKAGE BODY MY_PACK IS

FUNCTION ADD_NUM(X NUMBER, Y NUMBER) RETURN NUMBER IS

V_NUM NUMBER;

BEGIN

V_NUM := X+Y;

RETURN V_NUM;

END ADD_NUM;

FUNCTION AOR( L NUMBER , B NUMBER) RETURN NUMBER IS

V_AOR NUMBER;

BEGIN

V_AOR := L*B;

RETURN V_AOR;

END AOR;

PROCEDURE UPDATE_SAL(P_EMPNO NUMBER, P_SAL NUMBER) IS

BEGIN

UPDATE EMP SET SAL = P_SAL WHERE EMPNO =P_EMPNO;

COMMIT;

END UPDATE_SAL;

END MY_PACK;

/

Testing the package

SQL> select my_pack.add_num(100, 200) from dual;

MY_PACK.ADD_NUM(100,200)

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

                     300

SQL> select my_pack.aor(10, 200) from dual;

MY_PACK.AOR(10,200)

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

               2000

SQL> select sal from emp where empno =7369;

       SAL

----------

       800

SQL> exec my_pack.update_sal(7369, 100);

PL/SQL procedure successfully completed.

SQL> select sal from emp where empno =7369;

       SAL

----------

       100