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;

...