Calling Stored Procedures
A stored procedure is a program that resides on the server. The program can be written by an application developer to accept SQL data via input parameters and to provide SQL data via output parameters. A stored procedure also can return one or more result sets.
Most times, a stored procedure is called from an application program, however, beginning with DB2 v7 for Workstation, stored procedures can be called from the Command Processor. For example:
CALL proc()
This command simply calls a stored procedure named proc which takes no arguments and provides no parameter data back to the caller. The stored procedure can, however, return one or more result sets.
Let's look at a more sophisticated CALL statement.
CALL empinfo(20000, 'Country', ?, ?, 0, 20000000) Value of output parameters -------------------------- Parameter Name : PARM3 Parameter Value : - Parameter Name : PARM4 Parameter Value : 123456789 Parameter Name : PARM5 Parameter Value : 12345.67 Parameter Name : PARM6 Parameter Value : 20000000 Result set 1 -------------- NAME JOB SEL0003 --------- --- ------------------------ Edwards - +1.78440000000000E+004 Koonitz - +1.80017500000000E+004 O'Brien - +1.80060000000000E+004 Pernal - +1.81712500000000E+004 Plotz - +1.83528000000000E+004 Sanders - +1.83575000000000E+004 Lea - +1.85555000000000E+004 Wilson - +1.86745000000000E+004 Daniels - +1.92602500000000E+004 Williams - +1.94565000000000E+004 Quill - +1.98180000000000E+004 Lu - +2.00100000000000E+004 Hanes - +2.06598000000000E+004 Graham - +2.10000000000000E+004 Fraye - +2.11500000000000E+004 Jones - +2.12340000000000E+004 Molinare - +2.29592000000000E+004 17 record(s) selected. Result set 2 -------------- NAME JOB SEL0003 --------- --- ------------------------ Marenghi - +1.75067500000000E+004 Gonzales - +1.68582000000000E+004 Quigley - +1.68083000000000E+004 Rothman - +1.65028300000000E+004 Davis - +1.54545000000000E+004 Wheeler - +1.44600000000000E+004 Sneider - +1.42527500000000E+004 James - +1.35046000000000E+004 Lundquist - +1.33698000000000E+004 Gafney - +1.30305000000000E+004 Naughton - +1.29547500000000E+004 Ngan - +1.25082000000000E+004 Kermisch - +1.22585000000000E+004 Abrahams - +1.20097500000000E+004 Scoutten - +1.15086000000000E+004 Burke - +1.09880000000000E+004 Yamaguchi - +1.05059000000000E+004 17 record(s) selected. Return Status = 0
This procedure has six parameters. The first two were defined as input parameters, the second two as output parameters and the last two as both input and output parameters. The stored procedure also returns two result sets and a return value known as the status.
All input parameters must have a compatible parameter passed. Output parameters are denoted by using the parameter marker '?'. Result sets are returned and displayed in the order that the stored procedure gives them.