The choice of using or creating a routine with a built-in,
sourced, SQL, or external routine implementation can influence what
functionality the routine can provide, the performance of the routine,
and the likelihood of runtime problems that might require debugging.
About this task
Whenever possible, if there is an existing built-in routine
that provides the support that you require, use it. Use existing
built-in routines whenever possible. If the functionality you require
is very similar to that of an existing built-in function, consider
creating a sourced function that extends it.
If you must create
a routine, use the following procedure. It is important to determine
what routine implementation to use before proceeding too far with
routine design.
Procedure
To determine whether to use a sourced, SQL, or external
routine implementation when creating a routine:
- Determine whether you want to create a procedure, function,
or method. This should always be your first step when developing
a routine. Also determine what are the support implementations for
that routine type. See:
- Determine what SQL statements you want to include in the
routine. The set of SQL statements that you want to execute in a routine
can limit your choice of routine implementation. See:
- Determine if now or in the future the routine logic must
access data, files, or applications that reside external to the database.
The data, files, or applications might reside in the file system
of the database server or in the available network.
- If the routine logic must access entities outside of the database,
you must use an external routine implementation.
- Determine the number of queries to be included in the routine
relative to the quantity of procedural flow logic.
- If the routine logic contains primarily procedural flow logic
and very few queries, create an external routine.
- If the routine logic contains many queries and a minimal amount
of procedural flow logic, create an SQL routine.