Package and Package Body

Packages make the database a much better programming environment by introducing the concept of an interface, which allows encapsulation and abstraction. They allow you to effectively develop and maintain complex applications written in PL/SQL

In MS SQL Server and similar, which just have stored procedures, there is no concept of an external and internal API; all sprocs are at the same level, available to be called by any code. There's no way for a developer to present a limited interface to the outside worlds, all the internal methods are open. Without rigorous controls on development procedures, this can (and does) lead to "spaghetti code", where there's a complex and poorly understood interactions between various stored procedures. It makes ongoing maintenance much more complex as there's no limit to what side effects might occur when a stored procedure is changed.

Packages solve this problem by encapsulating related stored procedures into a single package, and allowing a limited external interface to be defined. This means that the majority of the stored procedures are only ever called within the package and are not exposed to the outside. The impact of changing these is limited to the package. (If you end up with a lot of public stored procedures, you might want to reconsider your interface design and application boundaries to reduce that).

Packages also allow you to limit the namespace, and the scope of variables and exceptions to within that package, meaning there are no side effects. You can develop packages independently of each other without worrying that two packages both have a sproc called "GetCustomer" or a global variable called updateFlag. The reduction in the number of places the code can possibly interact to just the public interfaces of each package greatly simplifies new development and maintenance of the system.

Essentially packages fulfill the role of Class Libraries in .NET or Java, and make the database a useful development environment for large scale software systems. Don't let the application developers tell you they need to do all the work in Java and database stored procedures are just for SQL. PL/SQL is a powerful enough language to usefully manage all the logic for a complex system. (There are pros and cons of taking that approach, but it's a valid direction to consider).

http://ask.sqlservercentral.com/questions/38084/package-and-package-body.html