Execute a noncursor function

A noncursor function returns only one row of return values to the application. Use the EXECUTE...INTO SQL DESCRIPTOR statement to execute the function and save the return value or values in a system-descriptor area.

An external function that is not explicitly defined as an iterator function returns only a single row of data. Therefore, you can use EXECUTE...INTO SQL DESCRIPTOR to execute most external functions dynamically. This single row of data consists of only one value because external function can only return a single value. The system-descriptor area contains only one item descriptor with the single return value.

An SPL function whose RETURN statement does not include the WITH RESUME keywords returns only a single row of data. Therefore, you can use EXECUTE...INTO SQL DESCRIPTOR to execute most SPL functions dynamically. An SPL function can return one or more values at one time so the system-descriptor area contains one or more item descriptors.

Important: Because you usually do not know the number of returned rows that a user-defined function returns, you cannot guarantee that only one row is returned. If you do not use a cursor to execute cursor function, generates a runtime error. Therefore, it is a good practice to always associate a user-defined function with a function cursor.

The following program fragment dynamically executes an SPL function called items_pct. This SPL function calculates the percentage that the items of a given manufacturer represent out of the total price of all items in the items table. It accepts one argument, the manu_code value for the chosen manufacturer, and it returns the percentage as a decimal value. The following figure shows the items_pct SPL function.

Figure 1: Code for items_pct SPL function

create function items_pct(mac char(3)) returning decimal;
   define tp money;
   define mc_tot money;
   define pct decimal;
   let tp = (select sum(total_price) from items);
   let mc_tot = (select sum(total_price) from items
         where manu_code = mac);
   let pct = mc_tot / tp;
   return pct;
end function;