When implementing routine logic you can choose to implement
SQL routines or external routines. There are reasons for choosing
each of these two implementations.
About this task
As long as SQL routines can meet your requirements,
you should choose SQL routines over external routines. Use external
routines when you must implement complex logic, or access files or
scripts on a database server.
Procedure
- Choose to implement SQL routines if:
- SQL PL and SQL statements provide adequate support to implement
the logic that you require.
- The routine logic consists primarily of SQL statements that query
or modify data and performance is a concern. Logic that contains
a relatively small amount of control-flow logic relative to the number
of SQL statements that query or modify database data will generally
perform better with an SQL routine implementation. SQL PL is intended
to be used for implementing procedural logic around database operations
and not primarily for programming complex logic.
- The SQL statements that you need to execute can be executed in
an external routine implementation.
- You want to make the modules highly portable between operating
system environments and minimize the dependency on programming language
code compilers and script interpreters.
- You want to implement the logic quickly and easily using a high
level programming language.
- You are more comfortable working with SQL than with scripting
or programming languages.
- You want to secure the logic within the database management system.
- You want to minimize routine maintenance and routine package maintenance
upon release upgrades or operating system upgrades.
- You want to minimize the amount of code required to implement
the logic.
- You want to maximize the safety of the code that is implemented
by minimizing the risk of memory management, pointer manipulation,
or other common programming pitfalls.
- You want to benefit from special SQL caching support made available
when SQL PL is used.
- Choose to implement an external procedure
if:
- If the routine logic is very complex and consists of few SQL statements
and routine performance is a concern. Logic such as a complex math
algorithm, that involves a large amount of string manipulation, or
that does not access the database will generally perform better with
an external routine implementation.
- If the SQL statements that you need to execute can be executed
in an external routine implementation.
- The routine logic will make operating system calls - this can
only be done with external routines.
- The routine logic must read from or write to files - this can
only be done with external routines.
- Write to the server file system. Do this only with caution.
- Invoke an application or script that resides on the database server.
- Issue particular SQL statements that are not supported in SQL
procedures.
- You are more comfortable programming in a programming language
other than SQL PL.