Actian X 11.1 | FOR - ENDFOR Example

SQL Reference Guide > SQL Reference Guide > SQL Statements > FOR - ENDFOR > FOR - ENDFOR Example

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: 11/09/2022