Application Development Guide

SQL Procedure Samples

Table 47. SQL Procedure Sample Programs

Sample Program Name Program Description
basecase.db2 The UPDATE_SALARY procedure raises the salary of an employee identified by the "empno" IN parameter in the "staff" table of the "sample" database. The procedure determines the raise according to a CASE statement that uses the "rating" IN parameter.
basecase.sqc Calls the UPDATE_SALARY procedure.
baseif.db2 The UPDATE_SALARY_IF procedure raises the salary of an employee identified by the "empno" IN parameter in the "staff" table of the "sample" database. The procedure determines the raise according to an IF statement that uses the "rating" IN parameter.
baseif.sqc Calls the UPDATE_SALARY_IF procedure.
dynamic.db2 The CREATE_DEPT_TABLE procedure uses dynamic DDL to create a new table. The name of the table is based on the value of the IN parameter to the procedure.
dynamic.sqc Calls the CREATE_DEPT_TABLE procedure.
iterate.db2 The ITERATOR procedure uses a FETCH loop to retrieve data from the "department" table. If the value of the "deptno" column is not 'D11', modified data is inserted into the "department" table. If the value of the "deptno" column is 'D11', an ITERATE statement passes the flow of control back to the beginning of the LOOP statement.
iterate.sqc Calls the ITERATOR procedure.
leave.db2 The LEAVE_LOOP procedure counts the number of FETCH operations performed in a LOOP statement before the "not_found" condition handler invokes a LEAVE statement. The LEAVE statement causes the flow of control to exit the loop and complete the stored procedure.
leave.sqc Calls the LEAVE_LOOP procedure.
loop.db2 The LOOP_UNTIL_SPACE procedure counts the number of FETCH operations performed in a LOOP statement until the cursor retrieves a row with a space (' ') value for column "midinit". The loop statement causes the flow of control to exit the loop and complete the stored procedure.
loop.sqc Calls the LOOP_UNTIL_SPACE procedure.
nestcase.db2 The BUMP_SALARY procedure uses nested CASE statements to raise the salaries of employees in a department identified by the dept IN parameter from the "staff" table of the "sample" database.
nestcase.sqc Calls the BUMP_SALARY procedure.
nestif.db2 The BUMP_SALARY_IF procedure uses nested IF statements to raise the salaries of employees in a department identified by the dept IN parameter from the "staff" table of the "sample" database.
nestif.sqc Calls the BUMP_SALARY_IF procedure.
repeat.db2 The REPEAT_STMT procedure counts the number of FETCH operations performed in a repeat statement until the cursor can retrieve no more rows. The condition handler causes the flow of control to exit the repeat loop and complete the stored procedure.
repeat.sqc Calls the REPEAT_STMT procedure.
rsultset.c Calls the MEDIAN_RESULT_SET procedure, displays the median salary, then displays the result set generated by the SQL procedure. This client is written using the CLI API, which can accept result sets.
rsultset.db2 The MEDIAN_RESULT_SET procedure obtains the median salary of employees in a department identified by the "dept" IN parameter from the "staff" table of the "sample" database. The median value is assigned to the salary OUT parameter and returned to the "rsultset" client. The procedure then opens a WITH RETURN cursor to return a result set of the employees with a salary greater than the median. The procedure returns the result set to the client.
spserver.db2 The SQL procedures in this CLP script demonstrate basic error-handling, nested stored procedure calls, and returning result sets to the client application or the calling application. You can call the procedures using the "spcall" application, in the CLI samples directory. You can also use the "spclient" application, in the C and CPP samples directories, to call the procedures that do not return result sets.
whiles.db2 The DEPT_MEDIAN procedure obtains the median salary of employees in a department identified by the "dept" IN parameter from the "staff" table of the "sample" database. The median value is assigned to the salary OUT parameter and returned to the "whiles" client. The whiles client then prints the median salary.
whiles.sqc Calls the DEPT_MEDIAN procedure.

Sample Program Name	Program Description
`basecase.db2`	The UPDATE_SALARY procedure raises the salary of an employee identified by the "empno" IN parameter in the "staff" table of the "sample" database. The procedure determines the raise according to a CASE statement that uses the "rating" IN parameter.
`basecase.sqc`	Calls the UPDATE_SALARY procedure.
`baseif.db2`	The UPDATE_SALARY_IF procedure raises the salary of an employee identified by the "empno" IN parameter in the "staff" table of the "sample" database. The procedure determines the raise according to an IF statement that uses the "rating" IN parameter.
`baseif.sqc`	Calls the UPDATE_SALARY_IF procedure.
`dynamic.db2`	The CREATE_DEPT_TABLE procedure uses dynamic DDL to create a new table. The name of the table is based on the value of the IN parameter to the procedure.
`dynamic.sqc`	Calls the CREATE_DEPT_TABLE procedure.
`iterate.db2`	The ITERATOR procedure uses a FETCH loop to retrieve data from the "department" table. If the value of the "deptno" column is not 'D11', modified data is inserted into the "department" table. If the value of the "deptno" column is 'D11', an ITERATE statement passes the flow of control back to the beginning of the LOOP statement.
`iterate.sqc`	Calls the ITERATOR procedure.
`leave.db2`	The LEAVE_LOOP procedure counts the number of FETCH operations performed in a LOOP statement before the "not_found" condition handler invokes a LEAVE statement. The LEAVE statement causes the flow of control to exit the loop and complete the stored procedure.
`leave.sqc`	Calls the LEAVE_LOOP procedure.
`loop.db2`	The LOOP_UNTIL_SPACE procedure counts the number of FETCH operations performed in a LOOP statement until the cursor retrieves a row with a space (' ') value for column "midinit". The loop statement causes the flow of control to exit the loop and complete the stored procedure.
`loop.sqc`	Calls the LOOP_UNTIL_SPACE procedure.
`nestcase.db2`	The BUMP_SALARY procedure uses nested CASE statements to raise the salaries of employees in a department identified by the dept IN parameter from the "staff" table of the "sample" database.
`nestcase.sqc`	Calls the BUMP_SALARY procedure.
`nestif.db2`	The BUMP_SALARY_IF procedure uses nested IF statements to raise the salaries of employees in a department identified by the dept IN parameter from the "staff" table of the "sample" database.
`nestif.sqc`	Calls the BUMP_SALARY_IF procedure.
`repeat.db2`	The REPEAT_STMT procedure counts the number of FETCH operations performed in a repeat statement until the cursor can retrieve no more rows. The condition handler causes the flow of control to exit the repeat loop and complete the stored procedure.
`repeat.sqc`	Calls the REPEAT_STMT procedure.
`rsultset.c`	Calls the MEDIAN_RESULT_SET procedure, displays the median salary, then displays the result set generated by the SQL procedure. This client is written using the CLI API, which can accept result sets.
`rsultset.db2`	The MEDIAN_RESULT_SET procedure obtains the median salary of employees in a department identified by the "dept" IN parameter from the "staff" table of the "sample" database. The median value is assigned to the salary OUT parameter and returned to the "rsultset" client. The procedure then opens a WITH RETURN cursor to return a result set of the employees with a salary greater than the median. The procedure returns the result set to the client.
`spserver.db2`	The SQL procedures in this CLP script demonstrate basic error-handling, nested stored procedure calls, and returning result sets to the client application or the calling application. You can call the procedures using the "spcall" application, in the CLI samples directory. You can also use the "spclient" application, in the C and CPP samples directories, to call the procedures that do not return result sets.
`whiles.db2`	The DEPT_MEDIAN procedure obtains the median salary of employees in a department identified by the "dept" IN parameter from the "staff" table of the "sample" database. The median value is assigned to the salary OUT parameter and returned to the "whiles" client. The whiles client then prints the median salary.
`whiles.sqc`	Calls the DEPT_MEDIAN procedure.

[ Top of Page | Previous Page | Next Page ]