![[Contents]](FIGURE/CONTENT.GIF)
Scalable Database Server, HiRDB Version 8 UAP Development Guide
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
![[Next]](FIGURE/AFTER.GIF)
A stored procedure is a facility that registers an SQL-coded database processing procedure to a database as a 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.
Figure 4-1 shows the benefits of using an SQL stored procedure.
Figure 4-1 Benefits of using an SQL stored procedure
![[Figure]](FIGURE/ZU040301.GIF)
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 8 Command Reference manual.
Definition and execution of an SQL stored procedure are illustrated in Figure 4-2.
Figure 4-2 Defining and executing an SQL stored procedure
![[Figure]](FIGURE/ZU040302.GIF)
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-3 Example of an SQL stored procedure
![[Figure]](FIGURE/ZU040303.GIF)
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 8 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
...
|
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.
setenv PDWRTLNFILSZ 4096 |
PDWRTLNFILSZ=4096 |
strcpy(e_fromdate, "2003-06-01"); strcpy(e_todate, "2003-06-30"); EXEC SQL CALL proc_1(IN :e_fromdate, IN :e_todate); |
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.
To complete a transaction in a stored procedure, execute one of the following SQL statements in that procedure:
COMMIT is executed automatically when one of the following SQL statements is executed:
ROLLBACK is executed automatically when the following condition applies:
When creating a stored procedure that completes a transaction, you must consider the note described below.
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]](FIGURE/ZU040307.GIF)
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.
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.
Figure 4-4 shows an overview of the results-set return facility.
Figure 4-4 Overview of results-set return facility (for SQL stored procedures)
![[Figure]](FIGURE/ZU040308.GIF)
Listed below are the languages of calling sources that can use 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.
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
|
#include <stdio.h>
#include <string.h>
main()
{
EXEC SQL BEGIN DECLARE SECTION;
struct {
long len;
char str[31];
} cur1;
int emp_id;
char emp_name[13];
int emp_age;
EXEC SQL END DECLARE SECTION;
--------(CONNECT process to HiRDB (omitted))--------
cur1.len = sprintf(cur1.str, "cursor1"); .................... 1
EXEC SQL CALL PROC(10); ..................................... 2
If (SQLCODE == 120) { ....................................... 3
EXEC SQL ALLOCATE GLOBAL :cur1
FOR PROCEDURE PROC2; ........................... 4
printf("*** emps_1 ***\n"); ............................... 5
while (1) { ............................................... 5
EXEC SQL FETCH GLOBAL :cur1 ............................. 5
INTO :emp_id,:emp_name,:emp_age; ............. 5
if (SQLCODE<0 || SQLCODE==100) break; ................... 5
printf("ID=%d NAME=%s AGE=%d\n", ...................... 5
emp_id, emp_name, emp_age); ....................... 5
} ......................................................... 5
CLOSE GLOBAL :cur1; ....................................... 6
if (SQLCODE==121) { ....................................... 7
printf("*** emps_2 ***\n"); ............................. 8
while (1) { ............................................. 8
EXEC SQL FETCH GLOBAL :cur1 ............................... 8
INTO :emp_id,:emp_name,:emp_age; ........... 8
if (SQLCODE<0 || SQLCODE==100) break; ................. 8
printf("ID=%d NAME=%s AGE=%d\n", .................... 8
emp_id, emp_name, emp_age); ..................... 8
} ....................................................... 8
CLOSE GLOBAL :cur1; ..................................... 9
}
}
}
|
All Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.