Both Procedures and Functions contain SQL and PL/SQL code - they are identical except that functions always return a value to the caller. Optional parameters can be supplied in order to input (or output) values.
Procedures and functions are created in a user's schema and stored (in compiled form) inside the database.
Packages
Packages provide a method of encapsulating and storing related procedures and
functions together in
the database. Packages help to keep code routines properly organized.
All objects within a package are parsed, compiled, and loaded into memory together - this helps performance.
A package consists of two parts: the specification and the body. The specification
declares public constructs of the package.
This arrangement minimises the need to recompile procedures during development and provides more flexibility in referencing public/private procedures and local/global
variables.
Granting the privilege to use a package makes all constructs of the package accessible to the grantee.
PL/SQL - Procedural Language Extension to SQL
PL/SQL is Oracle's procedural language extension to SQL. As described above, PL/SQL packages can be stored centrally in the database or alternatively,
front end applications can send PL/SQL to the database.
To minimise network traffic and maximise performance use stored procedures, or failing that, send 'blocks' of PL/SQL rather than individual SQL statements.
By applying suitable permissions, access to data can be restricted so that users may access a set of data only via
a specific PL/SQL routine.
Both stored procedures and PL/SQL triggers are stored within the database in a compiled form called P code (pseudocode.)
Advanced Queuing
Oracle Advanced Queuing allows distributed applications to communicate asynchronously using messages. Messages are queued for deferred
processing by the Oracle server. This can take the place of additional software such as transaction processing monitors or messaging
middleware.
"Ugly programs are like ugly suspension bridges:
they're much more
liable to collapse than pretty ones, because the way humans perceive beauty
is intimately related to our ability to process and understand complexity” ~ Eric
S. Raymond, (Why Python?)
Related
Oracle Supplied Packages
PL/SQL commands