Explained as follows are the types of jobs for which a stored procedure may be useful. For example, a product management job might involve the processing described as follows for analyzing the sales status of a product.

• For each product, the total number of units ordered each month is computed and the result is incorporated into a table that shows the cumulative number of units ordered since the product was introduced.

This is accomplished through the multiple database access processes described as follows.

1. Use a cursor to retrieve from the product orders table the product numbers, order quantities, and order recording dates for products for which orders were received during the month (SELECT statement).
2. Use a cursor to retrieve from the master order table the cumulative total number of units ordered for each product whose product number is contained in both the master orders table and the product orders table (SELECT statement).
3. For each applicable product, compute the sum of the total number of units ordered during the month and the cumulative total number of units ordered from the time the product was introduced through the end of the previous month, and update the master orders table with the result (INSERT and UPDATE statements).

When a series of database accessing processes such as the job shown here is registered at the database side rather than at the UAP side, the processes can be called for use from multiple UAPs. Figure 5-1 shows jobs to which a stored procedure can be applied.

Figure 5-1 Jobs to which a stored procedure can be applied

In the database access process shown in Figure 5-1, the total number of units ordered during June 2000 is determined for each product from the PRODUCT ORDERS TABLE, which has PRODUCT NO, ORDER QUANTITY, and RECORDING DATE as its columns. The results are incorporated into the MASTER ORDERS TABLE, which has PRODUCT NO and CUMULATIVE ORDER QUANTITY as its columns. In this case, the database access process described as follows can be defined as a stored procedure and registered in the database.

Processing contents of stored procedure

The number of units ordered for a specified period is determined, and the result is incorporated into the master orders table.

A UAP can perform the product management job shown in Figure 5-1 by simply calling the stored procedure with the argument described as follows specified.

Argument specified by UAP

Period for which number of units ordered is to be computed: (begin-date 20000601, end-date 20000630)

Figure 5-2 shows how a stored procedure is used.

Figure 5-2 Stored procedure usage

This illustrates how, when a stored procedure is used, it is possible to register a database access process at the database side, thus turning the database access process into a component.

Even when the database access process is changed, all that has to be done is to change the stored procedure; there is no need to change the UAP, thus reducing the number of steps required for UAP development.

In order to execute multiple SQL statements, an application typically has to access the database as many times as there are SQL statements to be executed. By contrast, storing multiple SQL statements to be executed in the database as a stored procedure means that the multiple SQL statements can be executed with one call of the stored procedure and only one access to the database. This greatly reduces communication processing associated with the passing of data between the HiRDB server and HiRDB client applications, as well as the overhead associated with the parsing of SQL statements by the front-end server. Figure 5-3 shows the communications processing for an SQL stored procedure.

Figure 5-3 Communications processing for an SQL stored procedure

With a stored function, the user can define data processing in a database as an arbitrary function, using conditional branching (IF statement) and a routine control SQL such as SQL repetition (WHILE statement). Consequently, the user can turn data processing into a component. When a plug-in is used, the function provided by the plug-in is registered as a stored function in the database.

To use stored procedures or stored functions, the following types of RDAREAs must be created:

• Data dictionary LOB RDAREAs
• Data dictionary RDAREAs

For details about creating RDAREAs for storing stored procedures and stored functions, see the HiRDB Version 8 System Operation Guide.

Explained as follows are the methods of calling a stored procedure or a stored function. For details about creating a UAP for calling stored procedures and stored functions, see the HiRDB Version 8 UAP Development Guide.

Calling a stored procedure

You call a stored procedure by specifying, in the UAP, a CALL statement written in SQL.

Invoking a stored function

You call a stored function by specifying a function call as a value in an SQL statement. You can specify arguments when the function is called. The value will be returned in the RETURN statement of the SQL.

Multiple stored functions with the same name can be defined, as long as they have different numbers of parameters and their data types are different. Stored functions with the same name are mutually overloaded. Because the overload function can be used to assign the same name to multiple functions with different parameter data types, it is possible to standardize the names of functions that have the same function. When a stored function is invoked, those facilities described as follows become the candidates for execution from among the facilities with the specified name and the same number of parameters as the number of specified arguments:

• Facilities in which the data type of each argument exactly matches the data type of the corresponding parameter
• If the data type of each argument does not exactly match the data type of the corresponding parameter, arguments and parameter data types are compared sequentially from left to right, and the facility with a parameter with the highest priority among the lower-priority parameters becomes the candidate.

For details about the rules for selecting the function to be invoked, see the HiRDB Version 8 SQL Reference.

Explained as follows are the SQL procedures and the definition of user-defined functions. For details about SQL procedures and the definition of user-defined functions, see the HiRDB Version 8 UAP Development Guide.

Defining a procedure for creating a stored procedure

To create a stored procedure, one of the following SQLs must be used first to define a procedure:

• CREATE PROCEDURE
• Procedure body specified by CREATE TYPE (for specifying a procedure for an abstract data type)

Defining a user-defined function for creating a stored function

To create a stored function, one of the following SQLs must be used first to define a user-defined function:

• CREATE FUNCTION
• Function body specified by CREATE TYPE (for specifying a function for an abstract data type)

Procedures, user-defined functions, and system-defined functions are referred to collectively as routines.

When an SQL that defines a procedure or a user-defined function is executed by database definition (pddef), the procedure or user-defined function is compiled automatically, thus creating an SQL object. Moreover, the definition source of the SQL and the SQL object are stored in a data dictionary LOB RDAREA. This registers the stored procedure or stored function into the database. For details about registration of stored procedures and stored functions into the database, see the HiRDB Version 8 System Operation Guide.

If the definition of a table or index is changed or a stored procedure is deleted by the DROP PROCEDURE statement, you need to re-create the voided stored procedure by executing the ALTER PROCEDURE statement. Similarly, if a stored function is deleted by the DROP FUNCTION statement, you need to re-create the voided stored function by executing the ALTER ROUTINE statement. For details about re-creating a stored procedure or a stored function, see the HiRDB Version 8 System Operation Guide.