Nonstop Database, HiRDB Version 9 UAP Development Guide

[Contents][Index][Back][Next]

4.3.1 Defining a stored procedure

A stored procedure is a facility that registers an SQL-coded database processing procedure to a database as a procedure.

Organization of this subsection
(1) Benefits of using an SQL stored procedure
(2) Defining and executing an SQL stored procedure
(3) Example of an SQL stored procedure
(4) Debugging an SQL stored procedure
(5) Completing a transaction in a stored procedure
(6) Results-set return facility (limited to SQL stored procedures)

(1) Benefits of using an SQL stored procedure

Manipulating a database may involve searching for data with the FETCH statement and then issuing the UPDATE or INSERT statement, depending on whether or not matching data is found. This process may be repeated many times, resulting in high overhead between the client and the server. This type of database access processing can also be defined in a routine that is stored as a procedure and can then be executed by calling it with a CALL statement. Use of a stored procedure reduces the amount of overhead associated with passing and receiving data between the client and the server. Because the SQL statements that are stored in a procedure are stored at the server in a compiled form (as SQL objects), using a stored procedure permits the client and the server to share processing, at the same time reducing the SQL parsing overhead.

The following figure shows the benefits of using an SQL stored procedure.

Figure 4-9 Benefits of using an SQL stored procedure

[Figure]

(2) Defining and executing an SQL stored procedure

CREATE PROCEDURE or CREATE TYPE stores a defined procedure in a database as an SQL stored procedure; DROP PROCEDURE deletes an SQL stored procedure from the database. Once stored in a database, an SQL stored procedure can be executed by calling it with a CALL statement.

If a procedure has an SQL object that has been invalidated, the ALTER PROCEDURE or ALTER ROUTINE statement can be used to re-create that procedure.

If an SQL stored procedure has already been registered, the pddefrev command can be executed to create definition-type SQL statements for that SQL stored procedure. This command is useful for creating a new SQL stored procedure, the processing of which is similar to that of an existing SQL stored procedure. For details about the pddefrev command, see the HiRDB Version 9 Command Reference manual.

The following figure illustrates the definition and execution of an SQL stored procedure.

Figure 4-10 Defining and executing an SQL stored procedure

[Figure]

Public procedure
If you use a stored procedure defined by another user, you must specify the owner's authorization identifier and routine identifier when you call the stored procedure from within a UAP. However, if CREATE PUBLIC PROCEDURE is executed to define the stored procedure as a public procedure, there is no need to specify the owner's authorization identifier when the stored procedure is called from a UAP (only the routine identifier must be specified).

(3) Example of an SQL stored procedure

An example of the definition and execution of an SQL stored procedure that defines SQL statements and statements for controlling the SQL statements (routine control SQL) is shown as follows:

Figure 4-11 Example of an SQL stored procedure

[Figure]

Explanation
  1. Defines the procedure name and the SQL parameters.
  2. Begins compound statements.
  3. Declares SQL variables.
  4. Specifies repetitive execution of statements.
  5. Specifies exiting a statement.
  6. Specifies conditional branching.
  7. Specifies value assignments.
  8. Ends the conditional branch.
  9. Ends repetitive executions of statements.
  10. Ends the compound statements.
  11. Calls the procedure.

Notes
  1. For details about the individual SQL statements, see the HiRDB Version 9 SQL Reference manual.
  2. This example specifies entrydate as a selection item in the SELECT clause for cursor declaration, so that the data can be sorted according to entrydate. However, because entrydate values are not referenced, the FETCH statement omits the embedded variable corresponding to entrydate and does not fetch entrydate values.

(4) Debugging an SQL stored procedure

To debug an SQL stored procedure, use WRITE LINE statements in a routine control SQL and output the SQL variables and SQL parameters to be referenced to a client file. For details about the WRITE LINE statement, see the manual HiRDB Version 9 SQL Reference.

An example of specifying WRITE LINE statements in an SQL stored procedure is shown below.

 
 CREATE PROCEDURE proc_1 (IN fromdate date, IN todate date)
   BEGIN
     ...
     WRITE LINE 'fromdate='||char(fromdate);   ...................1
     WRITE LINE 'todate='||char(todate);   .......................2
     ...
 

Explanation:
  1. Converts the value of the fromdate SQL parameter to a character string and outputs the string to a file.
  2. Converts the value of the todate SQL parameter to a character string and outputs the string to a file.

To output the values of the value expressions in the WRITE LINE statements from the SQL stored procedure in which the WRITE LINE statements were written to a client file, set the PDWRTLNFILSZ client environment definition, and call the SQL stored procedure from the UAP. An example is shown below.

PDWRTLNFILSZ setup example for csh (C shell) (UNIX edition HiRDB client)

setenv PDWRTLNFILSZ 4096

PDWRTLNFILSZ setup example (Windows edition HiRDB client)

PDWRTLNFILSZ=4096

Calling the SQL stored procedure:

strcpy(e_fromdate, "2003-06-01");
strcpy(e_todate, "2003-06-30");
EXEC SQL CALL proc_1(IN :e_fromdate, IN :e_todate);

Contents of output file:

fromdate=2003-06-01
todate=2003-06-30

Note: The output file is set with PDWRTLNPATH in the client environment definition.

Once debugging is completed, if you no longer need to output the values of the value expressions in the WRITE LINE statements from the SQL stored procedure in which the WRITE LINE statements were specified to a file, omit the PDWRTLNFILSZ client environment definition before executing the UAP. When the PDWRTLNFILSZ specification is omitted, the WRITE LINE statements in the SQL stored procedure are not executed.

(5) Completing a transaction in a stored procedure

(a) SQL statements for completing a transaction

To complete a transaction in a stored procedure, execute one of the following SQL statements in that procedure (note that SQL statements cannot be executed within a C stored procedure):

COMMIT is executed automatically when one of the following SQL statements is executed:

ROLLBACK is executed automatically when the following condition applies:

(b) Notes about re-executing stored procedures

If an error occurs during a stored procedure after a transaction has been completed, execution of the procedure is terminated before completion. If you re-execute the stored procedure that resulted in an error, the procedure processes are executed again from the beginning. You must therefore consider whether the operations performed before the transaction was terminated due to error can be executed twice. An example is shown below.

[Figure]

(6) Results-set return facility (limited to SQL stored procedures)

When defining an SQL stored procedure, you can use the results-set return facility by specifying a value of 1 or higher in the DYNAMIC RESULT SETS clause of CREATE PROCEDURE. The results-set return facility cannot be used for SQL stored functions.

(a) What is the results-set return facility?

The results-set return facility allows the calling source of an SQL stored procedure to reference the cursor obtained when the SELECT statement in the SQL stored procedure is executed.

The following figure provides an overview of the results-set return facility.

Figure 4-12 Overview of results-set return facility (for SQL stored procedures)

[Figure]

(b) Languages of calling sources that can use the results-set return facility

Listed below are the languages of calling sources that can use the results-set return facility:

#: COBOL can be used if an RDB file input/output function is not used.
(c) Example of using the results-set return facility

In this example, the SQL stored procedure searches the emps_1 and emps_2 tables and retrieves id, name, and age data for id column values that satisfy the condition id<10. The calling source accepts the two result sets and executes them.

Definitions of the SQL stored procedure
 
CREATE PROCEDURE proc2(IN param1 INTEGER) ............... 1
  DYNAMIC RESULT SETS 2 ................................. 2
  BEGIN
    DECLARE CUR1 CURSOR WITH RETURN ..................... 3
       FOR SELECT id,name,age FROM emps_1
            WHERE id < param1 ORDER BY id;
    DECLARE CUR2 CURSOR WITH RETURN ..................... 4
       FOR SELECT id,name,age FROM emps_2
            WHERE id < param1 ORDER BY id;
    OPEN CUR1; .......................................... 5
    OPRN CUR2; .......................................... 6
  END; .................................................. 7
 

Explanation:
  1. Defines the procedure name and the parameter.
  2. Specifies the number of search result information sets to be returned.
  3. Declares the CUR1 cursor.
  4. Declares the CUR2 cursor.
  5. Opens the CUR1 cursor.
  6. Opens the CUR2 cursor.
  7. Terminates the call and returns the result sets.

Explanation:
  1. Sets the cursor name.
  2. Executes the CALL statement.
  3. Determines whether there is a result set to be returned.
  4. Assigns a cursor (associates the first result set with the cursor).
  5. Outputs information from the first result set.
  6. Closes the cursor (associates the second result set with the cursor).
  7. Determines whether there is another result set.
  8. Outputs information from the second result set.
  9. Closes the cursor.
(d) Notes about using the results-set return facility