SQL Language Guide : 7. Database Procedures, Sessions, and Events : How Database Procedures Are Created, Invoked, and Executed : Row-Producing Procedures
 
Share this page                  
Row-Producing Procedures
A row-producing procedure is a database procedure that can return zero or more rows to its caller.
Format of a Row-Producing Procedure
The RESULT ROW clause in the procedure definition defines the format of a row-producing procedure. The RETURN ROW statement specifies the value returned in each "column" of a result row. The value can be a local variable or parameter of the procedure, or any expression involving constants, local variables, and parameters. The local variables must contain data retrieved in the procedure by a SELECT statement. Multiple result rows must be returned to the caller using the For-loop that retrieves data from a SELECT statement.
How a Row-Producing Procedure Is Called
Row-producing procedures can only be called directly from an embedded SQL host program (not using dynamic SQL, a terminal monitor, or by nesting a call in another database procedure). The host program, however, must include a Begin/End block to process the rows as they are returned from the procedure. This block functions much the same as the “select block” used with embedded SELECT statements.
A row-producing procedure cannot execute another procedure.
Row-Producing Procedure Example
CREATE PROCEDURE emp_sales_rank
RESULT ROW (INT, INT, MONEY)
AS
DECLARE
sales_tot  MONEY;
empid INT;
sales_rank  INT;
BEGIN
sales_rank = 0;
FOR
        SELECT e.empid,
               sum(s.sales) as sales_sum
        INTO   :empid,
               :sales_tot
        FROM    employee e,
                sales s
        WHERE   e.job   = 'sales'
        AND     e.empid = s.empid
        GROUP BY e.empid
        ORDER BY sales_sum DESC
        DO
                sales_rank = sales_rank + 1;
                RETURN ROW (:sales_rank, :empid, :tot_sales);
   ENDFOR;
END;
 
 
 
EXEC SQL BEGIN DECLARE;
     int       empid;
     int sales_rank;
     float     sales_tot;
     ...
EXEC SQL END DECLARE;
     ...
EXEC SQL EXECUTE PROCEDURE emp_sales_rank
         RESULT ROW (:sales_rank, :empid, :tot_sales);
EXEC SQL BEGIN;
     ...
EXEC SQL END;
     ...