Reply from DaveKeene on May 11 at 4:44 AM A package should be a grouping of interfaces that are used to perform actions on the tables within the databases. If you choose to hide the actual tables from the users then the package provides the link between the user/application and the stored data.
The package header is the list of interfaces that you provide access to - the signatures actually. There may be other functions and procedures that, for instance, perform partial operations but you might choose only to publish interfaces that provide a complete business service and which manage the transactional integrity of that service. Once defined in a package header, the signature must exist in the package body or Oracle complains at compile time. You;ll only know about individual procs being missed at run-time - never good. Our post-deploy scripts call a getVersion method on every package (it's our standard). If we can get that for each then we know all interfaces are available to the application.
Packages can contain types and constants which are unique to the package, and they can also contain variables which persist beyond the life of a single procedure call (though be wary of package loading/unloading). We use such variables to turn debug monitoring on and off at run time.
Package types and constants provide a much clearer way to write the interface definitions as they can be used as enumerated names in signatures, rather than passing strings or arbitrary values.
Functions and procedures in Oracle are very similar. In MS SQL functions are not allowed to have side-effects. That's annoying but it's quite good practice in most languages that procedures can update, but functions never do. That said, we have been known to write all procedures as functions with the function result being a soft status code where an exception is not valid.
Packages are also useful from a security level inasmuch as you grant access to the package not to each interface or the underlying table. The permissions to actually access/update tables stem from the authority of the user compiling the package, not the user calling it. This is true of individual procedures/functions as well but it's a pain allocating access rights to each one.
The other advantages of packages I can think of are:
Naming: You don't have to worry about someone else writing a procedure of the same name - they're local to the package and should be called as <packagename>.<procname>
Stubbing: When you're using packages with applications they often need to do continuous integration builds with robot tests. We have two package bodies always - the first actually does stuff with the database, the second has pre-programmed behaviour so it always returns known results regardless of what's actually in the tables. We usually write the first so the application programmers can get working before we've ironed out all the db interface issues.
System testing: Just as we provide a dummy interface to the applications we also treat packages as the interface to the database. We have test harnesses that exercise the database through the package interfaces. If they yield the correct results we can say the database is not broken.
| | | ---------------Original Message---------------
From: Nathan Shilling
Sent: Thursday, May 10, 2012 12:43 PM
Subject: Packages, Procedures, Functions...When and Why?
Fairly high level question I know, but can someone clearly explain (or give me link that does) what the diff is between a package, function and procedure? And why in SQL Developer do I see the Package, but can't get to the code unless I go into the Package Body - what is the purpose (and not really relevant to SQL Developer tool) of having the package AND the body structure?
If I need to have the database perform some logic and/or operation - why would I choose a function versus a procedure? Should I put it in a package? From what I gather, a package is just a way of grouping procs and functions logically? I see functions and procedures that fall outside of packages - what is the diff? Are there some sort of scoping considerations with each?
TIA |
| Reply to this email to post your response. __.____._ |   _.____.__ |
No comments:
Post a Comment