Collections
& Records in Application Design
API generator: DML APIs use cursors and records as the interface to encapsulate select, insert and delete operations.
Data Access
layer in PL/SQL
Least Common Denominator utilities and business logic in PL/SQL
Use records as much as possible for PL/SQL to PL/SQL interfaces (except UPDATE APIs)
Non-PL/SQL
interfaces| Of course, as
with any advice, your particular environment may mean my advice is worthless.
Your mileage may vary. |
|
| You should be
using records all the time and asking yourself, “Now where can I use simple
variables?”, not the other way around.
Collections of records are essential if you want to build PL/SQL
isolation layers (presentation, rule, lookup, data access API, etc.) It’s not a question of when should you use
records, it’s a question of how much pain you can endure as you stick with
individual fields for everything. |
|
| As long as you
are writing lower-level PL/SQL routines that will be called only by other
PL/SQL routines, you should generally use records as the primary
parameters. This is especially true
and beneficial if you are working on the data access layer. Some of you don’t have to imagine a table
with 50, 80, even 100 columns or more.
You live with such modeling horrors every day. Now imagine an INSERT procedure that uses
50 individual parameters. Consider
the very real possibility that Marketing gets an awful promotional approved,
and you’re stuck figuring out how to model and codify that which defies
logic. There’s no budget to refactor
your legacy model, so you end up tacking 5 more columns onto the end of that
monster table. Now you’ve got to hunt
down all dependents on that table and paste in a bunch more parameters and
code. |
|
| Using a record
reduces that entire headache to nothing.
Since the records are tied to the table using %ROWTYPE, the code
automatically adjusts. No parameters
to add, delete or change type length.
If you’ve followed best practices, you’ll only have the insert and
update statements to modify. If you’re on 9i, you can even avoid that! |
|
| If the callers
of your PL/SQL routine are written in other languages, avoid records. In some, like PERL, you can write
anonymous blocks that can take advantage of PL/SQL-only datatypes, but that
is the exception. Java, for example,
cannot handle records, period. Your
only alternative that comes close is to use object types instead, then
convert the object’s attributes to a record once the call has reached the
PL/SQL level. Then there’s the issue
of driver support. Even if your
driver supports protocol Z, version X, it doesn’t mean they did it right. We ended up having to abandon the idea of
passing objects or arrays to PL/SQL thanks to our application server’s JDBC
driver |
