 |
|
Oracle Tips by Burleson |
Bulk
Operations, Packages and Triggers
This chapter will wrap up our discussion of with
PL/SQL advanced capabilities, including collections, bulk SQL
operations, PL/SQL packagesand database triggers. These are some of
PL/SQL’s most powerful capabilities.
PL/SQL collections provide the developer with the
ability to load sets of objects into RAM memory where the data can be
manipulated with great speed. Processing data in a PL/SQL collection
does not have the overhead of constantly retrieving and manipulating
data in the database, and collections also form the basis for allowing
PL/SQL to execute bulk operations.
Bulk operations include retrieving large amounts
of data from the database and manipulating large amounts of data in
the database, via operations such as bulk inserts, bulk update and
bulk deletes. Using bulk operations instead of single line fetches
can provide a significant performance boost when dealing with large
sets of data.
Encapsulating code into PL/SQL packagesis one of the most important capabilities in PL/SQL, but sadly, many
developers do not implement them. Packages allow PL/SQL blocks to
persist and share data and code, and packages also allow for procedure
and function overloading. The database also handles code in PL/SQL
packages more efficiently by bringing the entire package into memory.
All PL/SQL development (other than trivial programs) should be
designed and implemented within PL/SQL packages.
Our final PL/SQL subject will be triggers.
Triggers allow the developer to execute PL/SQL code based on “events”
that happen within the database. A triggeris tied to a
database event and when that event happens, the trigger will execute
the defined code. Triggers can be used to implement database
auditing, log database changes or capture and store server errors.
Triggers can also archive data by saving deleted data into history
tables. In sum, triggers are a very powerful database feature.
So why did all these important subjects get lumped
into the final Chapter? It is because you needed to understand PL/SQL
basics before any of these functions can be implemented. You can not
bulk collectdata if you can’t processes the data. You can’t share
data in a package until you can create procedures and functions to
process data. All of these advanced capabilities continue to build on
the PL/SQL basics that we covered in earlier chapters.
Before a developer can bulk collectdata
into memory, he/she must be able to create a structure to store it.
This memory structure is called a collection. Let’s take a closer
look.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
