DB2 UDB Row Returning Database Procedure Example
The following example shows how to create, register and invoke a database procedure that returns rows using the DB2 UDB gateway.
Note: The user creating the DB2 UDB stored procedure must have create procedure permissions. The register procedure requires insert permissions on the gateway procedure catalogs. The execute procedure statement requires execute permissions for the DB2 UDB stored procedure and select permissions on the gateway procedure catalogs.
To create, register and invoke a database procedure that returns rows
1. Create the DB2 UDB stored procedure from the terminal monitor. The trailing spaces after each line are required to successfully create the procedure.
REMOVE PROCEDURE emp_rowproc;
\p\g
DIRECT EXECUTE IMMEDIATE 'DROP FUNCTION emp_rowproc';
\p\g
DIRECT EXECUTE IMMEDIATE '
CREATE FUNCTION emp_rowproc(insalary FLOAT, multiplier INTEGER)
RETURNS TABLE (NAME char(10),
SALARY FLOAT,
DEPT CHAR(8),
DIV CHAR(3),
MGR CHAR(10),
BIRTHDATE CHAR(25),
NUM_DEP INTEGER)
LANGUAGE SQL
RETURN
SELECT NAME, SALARY*multiplier,
DEPT, DIV, MGR, BIRTHDATE, NUM_DEP
FROM example_table WHERE SALARY <= insalary
';
\p\g
2. Register the stored procedure as a gateway database procedure:
REGISTER PROCEDURE emp_rowproc
(sal float,
multiplier integer)
AS IMPORT FROM 'emp_rowproc'
RESULT ROW
(char(10), float, char(8), char(3), char(10), char(25), integer);
\p\g
3. Invoke the gateway database procedure embedded SQL:
printf( "name Salary Dept Div Manager Hire date Department#\n");
EXEC SQL execute procedure emp_rowproc(multiplier = :multi,sal = 30000)
result row (:name, :fsalary, :cdept, :div, :mgr, :bdate,:num_dep);
EXEC SQL begin;
printf( "%s %8.2f %s %s %s %s %d\n",
name, fsalary, cdept, div, mgr, bdate, num_dep);
EXEC SQL end