In this exercise, you use a wizard to create a DB2 SQL
stored procedure. This simple stored procedure returns employee information
from the EMP_EMPLOYEE_DIM table when the employee's organization ID
matches a value that the user enters.
In the wizard you select a template to use as the basis
for the code in the stored procedure. In this exercise you create
the template to select in the wizard. For a simple stored procedure
such as the one you that create in this exercise, you ordinarily would
use one of the basic templates. However, you create a template in
this exercise so that you become familiar with the process of creating
templates.
When you create a routine in the workbench, you save
the source code in your project workspace.
To create an SQL
stored procedure:
- Create a template from an existing template.
- Click . The
Preferences window opens.
- Expand , and then click Templates.
- On the Templates page, scroll down until you see the
first template with the context db2luw_sp_sql. The description for this template is "Custom: You supply
the SQL, return a result set."
- Click the template, and then click Edit. The Edit Template window opens.
- Replace the existing template name with the following
text: GSDB4tutorial: Basic employee SEARCH statement.
- Replace the existing template description with the following
text: Return ID, name, and organization of all employees
in EMP_EMPLOYEE_DIM table.
- In the pattern, delete all the comment lines that are
bounded by the two comment lines with number signs (-- #################).
- Replace [Your SQL to execute goes here] with
the following SQL statement:
SELECT EMPLOYEE_CODE, FIRST_NAME,
LAST_NAME, ORGANIZATION_CODE
FROM EMP_EMPLOYEE_DIM;
The finished
template contains the following information:
- Click OK in the Edit
Template window. A message box prompts
you to indicate whether you want to create an additional template
with the new name or rename the existing template.
- Click Yes to create the new template. Your template is added to the list on the Templates page.
- Click OK in the Preferences window
- In the Data Project Explorer view, expand the SPDevelopment
project to find the Stored Procedures folder.
- Right-click the Stored Procedures folder,
and then select . The New Stored
Procedure wizard opens.
- In the Name field, type SPEmployee.
- In the Language field, select SQL.
- Select the GSDB4tutorial: Basic employee SEARCH statement template,
and then click Finish. The
wizard closes and the stored procedure opens in the routine editor,
where you can view and edit the source code
At this point, if you
saved, deployed, and ran the stored procedure, the results would show
all the employees in the table. However, we want to limit the results
to only those employees who are in the 018 organization.
- In the editor, do the following actions:
- Add the org input parameter to the
procedure declaration so that the declaration is as follows:
CREATE PROCEDURE SPEmployee ( IN org CHAR(6) )
- Delete the semicolon that follows EMP_EMPLOYEE_DIM.
- Insert the following condition clause:
WHERE ORGANIZATION_CODE = org;
The finished stored procedure contains the following code:CREATE PROCEDURE SPEmployee ( IN org CHAR(6) )
DYNAMIC RESULT SETS 1
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN for
SELECT EMPLOYEE_CODE, FIRST_NAME,
LAST_NAME, ORGANIZATION_CODE
FROM EMP_EMPLOYEE_DIM
WHERE ORGANIZATION_CODE = org;
-- Cursor left open for client application
OPEN cursor1;
END P1
- Save the stored procedure and then close the routine editor.
The stored procedure, SPEmployee, is displayed in the Data
Project Explorer view in the Stored Procedures folder
under the project in which you created it. The stored procedure is
ready for you to deploy.