Oracle: Benefits of Packages over standalone functions and procedures (Procedures vs packages)
Most us know oracle packages as convenient way of grouping functions and procedures .i.e. having all at one place. There oracle packages offer more than this
Overloading: We have multiple functions/procedures with same name.. during the execution, based on number of parameters and data types will determine the correct version to be invoked.
This way, every time we need additional functionality on top of existing, we do not need to modify existing functions/procedures.. we can simply create new without impacting existing applications...
Scope of Variable : We can declare a variable in package specification .. i.e. global variable... to have the variable accessible during the session in order to pass info between the packages/procedures/functions during a session. or declare with in body to be private to the package to be accessible only by the package, not to be accessible to other packages in the same session.
Global and Private variables are persistent between multiple calls of package during the session.. but if want it to reset for every call then we need have PRAGMA SERIALLY_REUSABLE; in both specification and body.. Note: PRAGMA is compiler directive .
Initialization: Package Body can have BEGIN and END; (not a function or procedure) block. This is executed only once , very first time any one of procedure or function referenced from package and information retrieved/set by this pl/sql block will be available for rest of the session.
Information Hiding: Only the package specification is visible to people who have execute privilege on the package. We can also control only specific procedures or functions are accessible by creating other procedures calling specific package procedures.. Therefore packages provide very good encapsulation technique.
Insulation from dependency crisis: If we are creating standalone procedures and are interlinked (called one from other).. a change in one procedure will invalidate all the parent procedures referencing changed procedure. Though oracle will automatically recompile when the invalid procedure called, it will be costly in terms of resources and performance. This problem is not there with packages as any parent procedure/function calling one of package procedure/function is only dependent on package specification and do not require recompilation when package body change.
Note: All the dependencies are present in the system table user_dependencies
Enabling recursion: In the standalone procedures or functions, the recursion is not allowed example.. have to procedures X, Y... calling Y from X and also calling X from Y is not allowed.. the procedures would not compile at all.. but if same procedures are created in Packages then its allowed.
Packages are not good fit to create functions for function-based index. Because of isolation from dependency chain, the function body changes will not disable or enforce rebuild for function-based index which could yield wrong results. Therefore its always better to use standalone function for function-based index.
Most us know oracle packages as convenient way of grouping functions and procedures .i.e. having all at one place. There oracle packages offer more than this
Overloading: We have multiple functions/procedures with same name.. during the execution, based on number of parameters and data types will determine the correct version to be invoked.
This way, every time we need additional functionality on top of existing, we do not need to modify existing functions/procedures.. we can simply create new without impacting existing applications...
Scope of Variable : We can declare a variable in package specification .. i.e. global variable... to have the variable accessible during the session in order to pass info between the packages/procedures/functions during a session. or declare with in body to be private to the package to be accessible only by the package, not to be accessible to other packages in the same session.
Global and Private variables are persistent between multiple calls of package during the session.. but if want it to reset for every call then we need have PRAGMA SERIALLY_REUSABLE; in both specification and body.. Note: PRAGMA is compiler directive .
Initialization: Package Body can have BEGIN and END; (not a function or procedure) block. This is executed only once , very first time any one of procedure or function referenced from package and information retrieved/set by this pl/sql block will be available for rest of the session.
Information Hiding: Only the package specification is visible to people who have execute privilege on the package. We can also control only specific procedures or functions are accessible by creating other procedures calling specific package procedures.. Therefore packages provide very good encapsulation technique.
Insulation from dependency crisis: If we are creating standalone procedures and are interlinked (called one from other).. a change in one procedure will invalidate all the parent procedures referencing changed procedure. Though oracle will automatically recompile when the invalid procedure called, it will be costly in terms of resources and performance. This problem is not there with packages as any parent procedure/function calling one of package procedure/function is only dependent on package specification and do not require recompilation when package body change.
Note: All the dependencies are present in the system table user_dependencies
Enabling recursion: In the standalone procedures or functions, the recursion is not allowed example.. have to procedures X, Y... calling Y from X and also calling X from Y is not allowed.. the procedures would not compile at all.. but if same procedures are created in Packages then its allowed.
When Not to use packages
Packages are not good fit to create functions for function-based index. Because of isolation from dependency chain, the function body changes will not disable or enforce rebuild for function-based index which could yield wrong results. Therefore its always better to use standalone function for function-based index.
No comments:
Post a Comment