Example SPL iterator function

To create an SPL iterator function to be used in the FROM clause, your function must use the RETURN WITH RESUME construct, as shown in the following example.

Because an SPL UDR can return more than one value, you can specify multiple column names in the virtual column list in the FROM clause. You can reference any of these virtual column names in the target list of the SELECT query.

create function find_top_earners()
    returning integer,decimal,lvarchar
define ret_empid integer;
define ret_salary decimal;
define ret_empname lvarchar;
    foreach select emp_id,salary into
        ret_empid,ret_salary from salary
        if (ret_salary > 100000.00)
          select emp_name into ret_empname from employee
          where emp_id = ret_empid;
        return ret_empid,ret_salary,ret_empname with 
          resume;
        end if;
    end foreach;
end function;

The following query uses the above iterator UDR, find_top_earners(), to retrieve the top earners sorted by employee name.

select vemp_name,vemp_id,vemp_sal from
    table (function find_top_accounts()) 
    vtab1(vemp_name,vemp_id,vemp_sal)
    order by vemp_name;