Designing SQL procedures requires
an understanding of your requirements, SQL procedure features, how
to use the SQL features, and knowledge of any restrictions that might
impede your design.
The following
topics about SQL procedure design help you learn how to design SQL
procedures that make best use of SQL procedure features.
Parts of SQL procedures
There
are many parts that make up an SQL procedure. Each part is essential
to ensure that the procedure is executed properly.
Structure of SQL procedures
SQL procedures consist of several logic parts and SQL procedure
development requires you to implement these parts according to a structured
format.
Parameters in SQL procedures
Parameters
can be useful in SQL procedures when implementing logic that is conditional
on a particular input or set of input scalar values or when you need
to return one or more output scalar values and you do not want to
return a result set.
Parameter markers
A parameter marker, often denoted by a question mark (?)
or a colon followed by a variable name (:var1), is a place holder
in an SQL statement whose value is obtained during statement execution.
Cursors in SQL procedures
In SQL procedures, a cursor make it possible to define
a result set (a set of data rows) and perform complex logic on a row
by row basis.
SQL PL logic elements in the SQL-procedure body
SQL
Procedural Language (SQL PL) provides support for variables and flow
of control statements, which can be used to control the sequence of
statement execution.
Returning result sets from SQL procedures
In SQL procedures, cursors can be
used to do more than iterate through rows of a result set. They can
also be used to return result sets to the calling program.
DB2 10.5 for Linux, UNIX, and Windows