Was this helpful?
FOR - ENDFOR Example
The following database procedure, avgsal_by_dept, returns rows containing the department name, average salary in the department, and count of employees in the department. Any unexpected error from the SELECT statement terminates the loop:
CREATE PROCEDURE avgsal_by_dept
       result row (CHAR(15), FLOAT, INT) AS
DECLARE
       deptname CHAR(15);
       avgsal   FLOAT;
       empcount INT;
       err      INT;
BEGIN
       err = 0;
       FOR SELECT d.dept, AVG(e.salary), COUNT(*) INTO :deptname, :avgsal, :empcount
       FROM department d, employee e
       WHERE e.deptid = d.deptid
       GROUP BY d.deptid do
       IF iierrornumber > 0 THEN
       err = 1;
       ENDLOOP;
       ENDIF;
       return row(:deptname, :avgsal, :empcount);
       ENDFOR;
RETURN :err;
END
Last modified date: 02/03/2024