< Previous | Next >

Creating an SQL stored procedure

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:

  1. Create a template from an existing template.
    1. Click Window > Preferences. The Preferences window opens.
    2. Expand Data Management > SQL Development > Routines, and then click Templates.
    3. 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."
    4. Click the template, and then click Edit. The Edit Template window opens.
    5. Replace the existing template name with the following text: GSDB4tutorial: Basic employee SEARCH statement.
    6. Replace the existing template description with the following text: Return ID, name, and organization of all employees in EMP_EMPLOYEE_DIM table.
    7. In the pattern, delete all the comment lines that are bounded by the two comment lines with number signs (-- #################).
    8. 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:
      Screen capture showing the Edit Template window with the finished template.
    9. 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.
    10. Click Yes to create the new template. Your template is added to the list on the Templates page.
    11. Click OK in the Preferences window
  2. In the Data Project Explorer view, expand the SPDevelopment project to find the Stored Procedures folder.
  3. Right-click the Stored Procedures folder, and then select New > Stored Procedure. The New Stored Procedure wizard opens.
  4. In the Name field, type SPEmployee.
  5. In the Language field, select SQL.
  6. 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.

  7. In the editor, do the following actions:
    1. Add the org input parameter to the procedure declaration so that the declaration is as follows:
      CREATE PROCEDURE SPEmployee ( IN org CHAR(6) )
    2. Delete the semicolon that follows EMP_EMPLOYEE_DIM.
    3. 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
  8. 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.

< Previous | Next >

Feedback