How to Create a Package in Oracle?

PL/SQL package is like a library that once written stored in the Oracle database and can be used by many applications. For E.g:- 

Create Table EMP_Package

(Id Number(5),

Name Varchar2(100));

O/P:- table EMP_PACKAGE created.

Create or Replace Package PKG_Test as

Procedure Insert_Record (v_id in number,v_name in varchar2);

Procedure Update_Record (v_id in number,v_name in varchar2);

Procedure Delete_Record (v_id in number);

end;

/

O/P:-  PACKAGE PKG_TEST compiled

Note:- v_id and v_name are the input variables

Now we need to create the body of the package for the procedures that we have defined in the package.

Create or Replace Package Body PKG_Test as

--Procedure to Insert Record

Procedure Insert_Record (v_id in number,v_name in varchar2) as

begin

Insert into EMP_Package (id,name)

values (v_id,v_name);

commit;

end;

--Procedure to Update Record

Procedure Update_Record (v_id in number,v_name in varchar2) as

begin

Update EMP_Package

Set Name=v_name Where Id=v_id;

commit;

end;

--Procedure to Delete Record

Procedure Delete_Record (v_id in number) as

begin

Delete from EMP_Package Where Id=v_id;

commit;

end;

end;

/

O/P:- PACKAGE BODY PKG_TEST compiled

To execute the package follow the below commands:-

*Insert the record into the table i.e EMP_Package through package

  Exec PKG_Test.Insert_Record(1,'Database Dynasty');

  select * From EMP_Package;

  O/P:-

Id	Name
1	Database Dynasty

*Update the record into the table i.e EMP_Package through package

  Exec PKG_Test.Update_Record(1,'Database Dynasty Update');

  select * From EMP_Package;

  O/P:-

Id	Name
1	Database Dynasty Update

*Delete the record from the table i.e EMP_Package through package

  Exec PKG_Test.Delete_Record(1);

  select * From EMP_Package;

  O/P:-

Id	Name