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

Home

Contents

Index

Master Index

New Book

CREATE FUNCTION read_many  (lastname CHAR(15))

	RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15),CHAR(2),

		CHAR(5);



	DEFINE p_lname,p_fname, p_city CHAR(15);

	DEFINE p_add CHAR(20);

	DEFINE p_state CHAR(2);

	DEFINE p_zip CHAR(5);

	DEFINE lcount, i INT;



	LET lcount = 1;



	TRACE ON; -- Every expression will be traced from here on

	TRACE 'Foreach starts';										
		-- A trace statement with a literal


	FOREACH

	SELECT fname, lname, address1, city, state, zipcode

        INTO p_fname, p_lname, p_add, p_city, p_state, p_zip

        FROM customer

        WHERE lname = lastname

	RETURN p_fname, p_lname, p_add, p_city, p_state, p_zip

		WITH RESUME;

	LET lcount = lcount + 1;		 	 -- count of returned addresses

	END FOREACH



	TRACE 'Loop starts'; -- Another literal 

	FOR i IN (1 TO 5)

        BEGIN

          RETURN i , i+1, i*i, i/i, i-1,i with resume; 

        END

	END FOR;



END FUNCTION;