• Using a standalone EXECUTE PROCEDURE or EXECUTE FUNCTION statement that you execute from DB-Access
• Calling the routine explicitly from another SPL routine or an external routine
• Using the routine name with an expression in an SQL statement

The EXECUTE Statements

Tip: For backward compatibility, the EXECUTE PROCEDURE statement allows you to use an SPL function name and an INTO clause to return values. However, Informix recommends that you use EXECUTE PROCEDURE only with procedures and EXECUTE FUNCTION only with functions.

You can issue EXECUTE PROCEDURE and EXECUTE FUNCTION statements as standalone statements from DB-Access or from within an SPL routine or external routine.

How to Use the Statements

Figure 14-96

EXECUTE PROCEDURE update_orders();

If the routine expects arguments, or if the routine name is not unique in the database, you must enter the argument values within parentheses, as Figure 14-97 shows.

Figure 14-97

EXECUTE FUNCTION scale_rectangles( 107, 1.9 )
	INTO new;

Notice that the statement in Figure 14-97 executes a function. Because a function returns a value, EXECUTE FUNCTION uses an INTO clause that specifies a variable where the return value is stored. The INTO clause must always be present when you use an EXECUTE statement to execute a function.

Remember that the parameter list of an SPL routine always has parameter names, as well as data types. When you execute the routine, the parameter names are optional. However, if you pass arguments by name (instead of just by value) to EXECUTE PROCEDURE or EXECUTE FUNCTION, as in Figure 14-98, Universal Server resolves the routine-by-routine name and arguments only, a process known as partial routine resolution.

Figure 14-98

EXECUTE FUNCTION scale_rectangles( rectid = 107,
	scale = 1.9 ) INTO new_rectangle;

Figure 14-99

EXECUTE PROCEDURE informix@davinci:bsmith.update_orders();

Using the CALL Statement

Suppose, for example, that you want the scale_rectangles function to call an external function that calculates the area of the rectangle and then returns the area, along with the rectangle description.

Figure 14-100

CREATE FUNCTION scale_rectangles( rectid INTEGER,
		scale REAL )
	RETURNING rectangle_t, REAL;


	DEFINE rectv rectangle_t;
	DEFINE a REAL;
	SELECT rect INTO rectv
		FROM rectangles WHERE id = rectid;
	IF ( rectv IS NULL ) THEN
		LET rectv.start = (0.0,0.0);
		LET rectv.length = 1.0;
		LET rectv.width = 1.0;
		LET a = 1.0;
		RETURN rectv, a;
	ELSE
		LET rectv.length = scale * rectv.length;
		LET rectv.width = scale * rectv.width;
		CALL area(rectv.length, rectv.width) RETURNING a;
		RETURN rectv, a;
	END IF;


END FUNCTION;

In this example, area() is an external function, but you can use CALL in the same manner with an SPL function.

Executing Routines in Expressions

For example, you might execute a function by a LET statement that assigns the return value to a variable. The statements in Figure 14-101 perform the same task. They execute an external function within an SPL routine and assign the return value to the variable a.

Figure 14-101

LET a = area( rectv.length, rectv.width );


CALL area( rectv.length, rectv.width ) RETURNING a;
		-- these statements are equivalent

Figure 14-102

CREATE FUNCTION raise_price ( num INT )
	RETURNING DECIMAL;


	DEFINE p DECIMAL;


	SELECT increase_by_pct(price, 20) INTO p
		FROM inventory WHERE prod_num = num;


	RETURN p;


END FUNCTION;

Executing Cursor Functions from an SPL Routine

FOREACH SELECT cur_func1(col_name) INTO spl_var FROM tab1
	INSERT INTO tab2 VALUES (spl_var);

END FOREACH



FOREACH EXECUTE FUNCTION cur_func2() INTO spl_var
	INSERT INTO tab2 VALUES (spl_var);
END FOREACH

CREATE PROCEDURE company_proc ( no_of_items INT,
	itm_quantity SMALLINT, sale_amount MONEY, 
	customer VARCHAR(50), sales_person VARCHAR(30) )


DEFINE salesperson_proc VARCHAR(60);


-- Update the company table


INSERT INTO company_tbl VALUES (no_of_items, itm_quantity,
	sale_amount, customer, sales_person); 


-- Generate the procedure name for the variable
-- salesperson_proc


LET salesperson_proc = sales_person || "." || "tbl" ||

	current_month || "_" || current_year || "_proc" ;


-- Execute the SPL procedure that the salesperson_proc 
-- variable names


EXECUTE PROCEDURE salesperson_proc (no_of_items,
	itm_quantity, sale_amount, customer) 



END PROCEDURE;

EXECUTE PROCEDURE owner_variable.proc_variable

	-- this is not allowed ;


LET proc1 = bill.proc1;
EXECUTE PROCEDURE proc1 -- this is allowed ;