Can anyone give a clear picture on Procedure, Package and Package Body in Oracle?
asked
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).
Pacakge contains proto type like definiting procedure , function signatures and any variables declaration.
Package contains Package body where all procedures and functions actual definitions will be defined.
Procedure is nothing but a pl/sql block where all required processing code will be written to do a specific task. Its like fucntion but it won't return any value.
Package helps us to group all related functionality of an application under one pack. The concept is same as OOPS concept. So In Pacakage, as I explained above contains all signature/prototypes which are going to be defined in Pacakge body. Let's take an expample of oracle pre-defined pacakges such as DBMS_STATS. This is a package. This package contains all procedures, functions and so on belong to Stats collection functions. In package body we can see the actual defintions of procedures. Calling a procedure in package is DBMS_STATS.MyProc()
Packages also allow you to hide implementation details. A Package Spec allows someone to run a program w/o access to the details.
In other rdbms like DB2 UDB, MySQL, MSSQL - there is no concept as package Body and Packages. So what are the advantages of Packages and Package Body. How do we take the decision when to use a Package and Package Body. I know I can write application logic in a procedure, just like any othe RDBMS.
http://www.oracle.com/technology/oramag/oracle/05-may/o35plsql.html .. May be this would help for detailed explanation.
There's discussion of packages vs procedures on AskTom:
My take on this is that procedures (and functions) are good for almost anything but implementing a system of procedures (and functions) should involve a package.
A package for definitions plus a package body can be used to hide, as another said, implementation details and also to avoid distracting the clients or users with the details that they don't really need to see, like work variables, internally called functions, etc. The one downside to that is that you might be developing something that they'd find useful otherwise but they can't access it (like a function to do a special kind of replace or translation could be generally useful elsewhere).
