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;
...