The SQL Procedure Body Structure | Intelligent Enterprises of the 21st Century

The SQL procedure body can consist of a single SQL statement or, more typically, a compound SQL statement consisting of a BEGIN/END block with multiple statements within it. The compound statement consists of various declarations followed by SQL procedure statements. All declarations must be specified first followed by SQL procedural statements. The syntax diagram in Figure 2.5 shows the required order of declarations.

Figure 2.5. Compound statement syntax diagram.

                          .-NOT ATOMIC--. >>-+---------+--BEGIN----+-------------+------------------------>    '-label:--'           '-ATOMIC------' >-----+-----------------------------------------------+--------->       | .------------------------------------------.  |       | V                                          |  |       '-----+-| SQL-variable-declaration |-+---;---+--'             +-| condition-declaration |----+             '-| return-codes-declaration |-' >--+----------------------------------+------------------------->    | .------------------------------. |    | V                              | |    '---| statement-declaration |--;-+-' >-----+--------------------------------------+------------------->       |  .--------------------------------.  |       |  V                                |  |       '----DECLARE-CURSOR-statement--;----+--' >-----+-------------------------------------+-------------------->       |  .-------------------------------.  |       |  V                               |  |       '----| handler-declaration |--;----+--'       .-------------------------------.       V                               | >--------SQL-procedure-statement--;---+---END--+--------+------><                                                '-label--'

Variable declarations are discussed later in this chapter. Compound statements are discussed in Chapter 4, "Using Flow of Control Statements." Cursor declarations are discussed in Chapter 5, "Understanding and Using Cursors and Result Sets." Condition handlers are discussed in Chapter 6, "Condition Handling." Statement declarations, available in LUW, are discussed in Chapter 7, "Working with Dynamic SQL."

Comments

It is always good practice to include comments in programs. DB2 allows two styles of comments:

When two dashes are specified, any text following them is treated as a comment. This style is typically used to comment on a specific variable declaration or statement. It can be used on any line of the CREATE PROCEDURE statement and terminates with the newline character.
/* */ are C-style comments. The /* begins a comment and */ ends the comment. There may be multiple lines of comments between the delimiters. This style can be used in the body of an SQL procedure only.

Figure 2.6 are examples of comments used in an SQL procedure.

Figure 2.6. Sample comments.

 CREATE PROCEDURE proc_with_comments ( IN aaa INTEGER                                     , OUT bbb INTEGER )     LANGUAGE SQL     SPECIFIC proc_with_comments                   -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries BEGIN     /*       Variables     */     DECLARE v_total INTEGER DEFAULT 0;     SELECT 1 INTO v_total FROM SYSIBM.SYSDUMMY1; END

Variables

The DECLARE statement is used to define variables within a compound SQL statement (see Figure 2.7). Each variable declaration consists of a name for the variable, a DB2 data type, and optionally a default value.

Figure 2.7. Defining variables.

 CREATE PROCEDURE proc_with_variables (IN p_empno VARCHAR(6))     LANGUAGE SQL     SPECIFIC proc_with_vars                     -- applies to LUW and iSeries     -- WLM ENVIRONMENT <env>                    -- applies to zSeries BEGIN     DECLARE v_empno VARCHAR(6);     DECLARE v_total, v_count INTEGER DEFAULT 0;     SELECT empno INTO v_empno FROM employee WHERE empno = p_empno ; END

Variable declarations must be specified at the beginning of a BEGIN/END block before any SQL procedural statements are defined. The CREATE PROCEDURE statement will fail if a DECLARE is found anywhere else.

You should ensure that the variables defined in your procedure are different from column names of tables that will be referenced in the procedure. Such a declaration is allowed but only causes confusion. It is recommended that you define a naming convention for your procedures that clearly differentiates variables and columns. In this book, the convention is to precede variables with a prefix of v_ and precede procedures parameters with a prefix of p_ to differentiate them from possible column names.

When variables are declared, they are initialized as NULL unless the DEFAULT clause is used to initialize the variable to a value. In Figure 2.7 the value of v_empno is null until it is set within the procedure body. The integer variables v_total and v_count are initialized to zero in the declaration.

Setting Variables

As in any programming language, you can initialize or set variables. There are several ways to set variables in SQL procedures, and they are demonstrated in Figure 2.8.

Figure 2.8. Setting variables.

 CREATE PROCEDURE set_variables ()     LANGUAGE SQL     SPECIFIC set_variables                       -- applies to LUW and iSeries     -- WLM ENVIRONMENT <env>                     -- applies to zSeries BEGIN     DECLARE v_rcount INTEGER;     DECLARE v_max DECIMAL(9,2);     DECLARE v_adate,v_another DATE;     DECLARE v_total INTEGER DEFAULT 0;                -- (1)     SET v_total = v_total + 1;                        -- (2)     SELECT MAX(salary) INTO v_max FROM employee;      -- (3)     VALUES CURRENT DATE INTO v_adate;                 -- (4)     SELECT CURRENT DATE, CURRENT DATE       INTO v_adate, v_another       FROM SYSIBM.SYSDUMMY1;                          -- (5) END

When declaring a variable, you can specify a default value using the DEFAULT clause as in Line (1).

Line (2) shows that a SET can be used to set a single variable.

Variables can also be set by executing a SELECT or FETCH statement in combination with INTO as shown in (3). Details on using FETCH can be found in Chapter 4, "Understanding and Using Cursors and Result Sets."

Lines (4) and (5) show how the VALUES INTO statement can be used to evaluate a function or special register and assign the value to a variable. Special registers are discussed in more detail in the next section.

DB2 Special Registers

Recall that special registers are memory registers that allow DB2 to provide information to an application about its environment. They can be referenced in SQL statements. The most commonly used special registers are

CURRENT DATE. A date based on the time-of-day clock at the database server. If this register is referenced more than once in a single statement, the value returned will be the same for all references. This is a non-updateable register.
CURRENT ISOLATION (LUW only). Identifies the isolation level for any dynamic SQL statements issued within the current session.
CURRENT LOCK TIMEOUT (LUW only). Specifies the number of seconds that an application will wait to obtain a lock. This is an updateable register.
CURRENT PATH. Identifies the SQL path used to resolve procedure, functions, and data type references for dynamically prepared SQL statements. The value of the CURRENT PATH special register is a list of one or more schema names. This is an updateable register.
CURRENT PACKAGE PATH (LUW and zSeries only). Identifies the path to be used when resolving references to packages. This is an updateable register.
CURRENT SCHEMA. Identifies the schema name used to qualify unqualified database objects in dynamic SQL statements. The default value is the authorization ID of the current user or the value of CURRENT SQLID on zSeries. This special register can be modified using the SET CURRENT SCHEMA statement. This is an updateable register.
CURRENT TIME. A time based on the time-of-day clock at the database server. If this register is referenced more than once in a single statement, the value returned will be the same for all references. This is a non-updateable register.
CURRENT TIMESTAMP. A timestamp based on the time-of-day clock at the database server. If this register is referenced more than once in a single statement, the value returned will be the same for all references. This is a non-updateable register.
USER. Specifies the runtime authorization ID used to connect to the database. This is a non-updateable register.
SESSION_USER (LUW only). Specifies the authorization ID to be used for the current session. This is a synonym for the USER special register. This is a non-updateable register.
SYSTEM_USER (LUW only). Specifies the authorization ID of the user who connected to the database. This is a non-updateable register.

Special registers can be categorized as updateable and non-updateable. For example, CURRENT SCHEMA and CURRENT PATH are both updateable registers. CURRENT TIMESTAMP is an example of a non-updateable register. To obtain the value of the register, use the SELECT statement as shown:

 SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1;

Use the SET command to update the updateable registers such as the current schema:

 SET CURRENT SCHEMA = DB2ADMIN

When invoking SQL procedures with unqualified names, the CURRENT PATH special register is used to resolve the requested SQL procedure. The path is a list of schemas which DB2 searches to locate a procedure. To obtain the CURRENT PATH special register, connect to the database and simply issue this command

 SELECT CURRENT PATH FROM SYSIBM.SYSDUMMY1;

The default setting for the PATH register in LUW looks like

 "SYSIBM", "SYSFUN", "SYSPROC", "DB2ADMIN"

The default PATH is made up of system schemas followed by the current user connected to the database. On LUW, SYSIBM, SYSFUN, and SYSPROC are the system schemas. With the previously shown PATH, DB2 first searches for the procedure in schema SYSIBM, then SYSFUN, then SYSPROC, and lastly DB2ADMIN. If there are overloaded procedures in both SYSPROC and DB2ADMIN, the SYSPROC one will be used. Sometimes you might want to use the DB2ADMIN procedure instead. In that case, set the CURRENT PATH to customize the search order of the schemas:

 SET CURRENT PATH = DB2ADMIN,SYSIBM,SYSFUN,SYSPROC

Figure 2.9 shows the use of several special registers.

Figure 2.9. Using special registers.

 CREATE PROCEDURE registersample ( OUT p_start TIMESTAMP                                 , OUT p_end   TIMESTAMP                                 , OUT p_c1    TIMESTAMP                                 , OUT p_c2    TIME                                 , OUT p_user  CHAR(20))     LANGUAGE SQL     SPECIFIC registersample                       -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries BEGIN     CREATE TABLE datetab (c1 TIMESTAMP,c2 TIME,c3 DATE);     VALUES CURRENT TIMESTAMP INTO p_start;               -- (1)     INSERT INTO datetab VALUES( CURRENT TIMESTAMP                                , CURRENT TIME                                , CURRENT DATE + 3 DAYS); -- (2)     SELECT c1,c2 INTO p_c1,p_c2 FROM datetab;     VALUES CURRENT TIMESTAMP INTO p_end;     SET p_user = USER;                                   -- (3)     DROP TABLE datetab; END

The procedure registersample creates a table, datetab, to demonstrate how special registers are used with SQL statements. This table is dropped at the end so that the procedure can be run repeatedly.

On Line (1), the values statement is used to set the variable p_start to the current timestamp.

Line (2) shows several special registers being used within a single SQL statement to retrieve the date or time. Here, the time portion of the CURRENT TIMESTAMP special register will be the same as the value of the CURRENT TIME special register, and date portion of the CURRENT TIMESTAMP special register will be the same as the value of CURRENT DATE. This statement also demonstrates the use of built-in functions. Column c3 will receive a date that is three days from the current date. You can find more details on using built-in functions such as DATE, TIME, and TIMESTAMP in the DB2 SQL Reference.

The p_user variable is set to the authorization ID of the currently connected user on Line (3).

The following list includes other special registers that are available. More information about them can be found in the DB2 SQL Reference for the corresponding platform.

CURRENT APPLICATION ENCODING SCHEME (zSeries only). Specifies which encoding scheme is to be used for dynamic statements. It allows an application to indicate the encoding scheme that is used to process data. This is an updatable register.
CURRENT DBPARTITIONNUM (LUW only). Specifies an INTEGER value that identifies the coordinator node number for the statement. This is a non-updateable register.
CURRENT DEFAULT TRANSFORM GROUP (LUW only). Identifies the name of a transform group used by dynamic SQL statements for exchanging user-defined structured type values with host programs. This is an updateable register.
CURRENT DEGREE (LUW and zSeries only). Specifies the degree of intra-partition parallelism for the execution of dynamic SQL statements. This is an updateable register.
CURRENT EXPLAIN MODE (LUW only). Holds a value that controls the behavior of the Explain facility. This is an updateable register.
CURRENT EXPLAIN SNAPSHOT (LUW only). Holds a value that controls behavior of the Explain Snapshot facility. This is an updateable register.
CURRENT LOCALE LC_CTYPE (zSeries only). Specifies the LC_CTYPE locale that will be used to execute SQL statements which use a built-in function that references a locale. This is an updatable register.
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION (LUW and zSeries only). Specifies the types of tables that can be considered for optimization when dynamic SQL queries are processed. This is an updateable register.
CURRENT MEMBER (zSeries only). Specifies the member name of a current DB2 data sharing member on which a statement is executing. This is a non-updatable register.
CURRENT OPTIMIZATION HINT (zSeries only). Specifies the user-defined optimization hint that DB2 should use to generate the access path for dynamic statements. This is an updateable register.
CURRENT PRECISION (zSeries only). Specifies the rules to be used when both operands in a decimal operation have precisions of 15 or less. This is an updatable register.
CURRENT QUERY OPTIMIZATION (LUW only). Specifies the query optimization level used when binding dynamic SQL statements. This is an updateable register.
CURRENT REFRESH AGE (LUW and zSeries only). Specifies the maximum duration that a cached data object, such as a materialized query table, can be used for dynamic queries before it must be refreshed. This is an updateable register.
CURRENT RULES (zSeries only). Specifies whether certain SQL statements are executed in accordance with DB2 rules or the rules of the SQL standard. This is an updatable register.
CURRENT SERVER. Specifies the name of the database to which the application is connected. This is a non-updateable register.
CURRENT TIMEZONE. Specifies the difference between UTC (Coordinated Universal Time) and local time at the application server. This is a non-updateable register.
CLIENT ACCTNG (LUW and zSeries only). Specifies the accounting string for the client connection. On zSeries, use CURRENT CLIENT_ACCTNG. This is an updateable register.
CLIENT APPLNAME (LUW and zSeries only). Specifies the client application name. On zSeries, use CURRENT CLIENT_APPLNAME. This is an updateable register.
CLIENT USERID (LUW and zSeries only). Specifies the client user ID. On zSeries, use CURRENT CLIENT_USERID. This is an updateable register.
CLIENT WRKSTNNAME (LUW and zSeries only). Specifies the client workstation name. On zSeries, use CURRENT CLIENT_WRKSTNNAME. This is an updateable register.