Informix Guide to SQL: Tutorial Chapter 14: Creating and Using SPL Routines

Home

Contents

Index

Master Index

New Book

Returning Values from an SPL Function SPL functions can return one or more values. To have your SPL function return values, you need to include two parts: First, you must write a RETURNING clause in the CREATE PROCEDURE or CREATE FUNCTION statement that specifies the number of values to be returned and their data types. Second, in the body of the function, you enter a RETURN statement that explicitly returns the values. Tip: You can define a routine with CREATE PROCEDURE that returns values, but in that case, the routine is actually a function. Informix recommends that you use CREATE FUNCTION if the routine returns values. Once you define a return clause (with a RETURNING statement), the SPL function can return values that match those specified in number and data type, or no values at all. If you specify a return clause, and the SPL routine returns no actual values, it is still considered a function. In that case, the routine returns a null value for each value defined in the return clause. An SPL function can return variables, expressions, or the result of another function call. If the SPL function returns a variable, the function must first assign the variable a value by one of the following methods: A LET statement A default value A SELECT statement Another function that passes a value into the variable Each value an SPL function returns can be up to 32 kilobytes long. Returning a Single Value Figure 14-55 shows how an SPL function can return a single value. Figure 14-55 CREATE FUNCTION increase_by_pct(amt DECIMAL, pct DECIMAL) RETURNING DECIMAL; DEFINE result DECIMAL; LET result = amt + amt * (pct/100); RETURN result; END FUNCTION; The increase_by_pct function receives two arguments of DECIMAL value, an amount to be increased and a percentage by which to increase it. The return clause specifies that the function will return one DECIMAL value. The RETURN statement returns the DECIMAL value stored in result. Returning Multiple Values Suppose that the table person is a typed table based on the named row type person_t, whose definition Figure 14-56 shows. CREATE ROW TYPE person_t ( emp_no INTEGER NOT NULL, name VARCHAR(30), address VARCHAR(20), city VARCHAR(20), state CHAR(2), zip INTEGER, bdate DATE NOT NULL ); CREATE TABLE person OF TYPE person_t; Figure 14-56 The person_t Row Type and person Typed Table Figure 14-57 shows an example of an SPL function that returns more than one value from a single row of a table. Figure 14-57 CREATE FUNCTION b_date( num INTEGER ) RETURNING VARCHAR(30), DATE; DEFINE n VARCHAR(30); DEFINE b DATE; SELECT name, bdate INTO n, b FROM person WHERE emp_no = num; RETURN n, b; END FUNCTION; The function in Figure 14-57 returns to the calling routine, two values (a name and birthdate) from one row of the person table. In this case, the calling routine must be prepared to handle the VARCHAR and DATE values returned. Suppose you want an SPL function to return more than one value from more than one row, as in Figure 14-58. Figure 14-58 CREATE FUNCTION b_date_2( num INTEGER ) RETURNING VARCHAR(30), DATE; DEFINE n VARCHAR(30); DEFINE b DATE; FOREACH cursor1 FOR SELECT name, bdate INTO n, b FROM person WHERE emp_no > num; RETURN n, b WITH RESUME; END FOREACH END FUNCTION; In Figure 14-58, the SELECT statement fetches two values from the set of rows whose employee number is higher than the number the user enters. The set of rows that satisfy the condition could contain one row, many rows, or 0 rows. Because the SELECT statement can return many rows, it is placed within a cursor. Tip: When a statement within an SPL routine returns no rows, the corresponding SPL variables are assigned null values. The RETURN statement uses the WITH RESUME keywords. When RETURN WITH RESUME is executed, control is returned to the calling routine. But the next time the SPL function is called (by a FETCH or the next iteration of a cursor in the calling routine), all of the variables in the SPL function keep their same values, and execution continues at the statement immediately following the RETURN WITH RESUME statement. If your SPL routine returns multiple values, the calling routine must be able to handle the multiple values through a cursor or loop, as follows: If the calling routine is an SPL routine, it needs a FOREACH loop. If it is an ESQL/C program, it needs a cursor declared with the DECLARE statement. If it is an external routine, it needs a cursor or loop appropriate to the language in which the routine is written.