Informix Guide to SQL: Tutorial Creating and Using SPL Routines

You can execute an SPL routine or external routine in any of the following ways:

• Using a stand-alone 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

An external routine is a routine written in C or some other external language.

You can use EXECUTE PROCEDURE or EXECUTE FUNCTION to execute an SPL routine or external routine. In general, it is best to use EXECUTE PROCEDURE with procedures and EXECUTE FUNCTION with functions.

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 stand-alone statements from DB-Access or the Relational Object Manager from within an SPL routine or external routine.

If the routine name is unique within the database, and if it does not require arguments, you can execute it by entering just its name and parentheses after EXECUTE PROCEDURE, as Figure 10-96 shows.

EXECUTE PROCEDURE update_orders();

The INTO clause is never present when you invoke a procedure with the EXECUTE statement because a procedure does not return a value.

If the routine expects arguments, you must enter the argument values within parentheses, as Figure 10-97 shows.

EXECUTE FUNCTION scale_rectangles(107, 1.9)
	INTO new;

The statement in Figure 10-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.

If the database has more than one procedure or function of the same name, Dynamic Server locates the right function based on the data types of the arguments. For example, the statement in Figure 10-97 supplies INTEGER and REAL values as arguments, so if your database contains multiple routines named scale_rectangles(), the database server executes only the scale_rectangles() function that accepts INTEGER and REAL data types.

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 10-98, Dynamic Server resolves the routine-by-routine name and arguments only, a process known as partial routine resolution.

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

You can also execute an SPL routine stored on another database server by adding a qualified routine name to the statement, that is, a name in the form database@dbserver:owner_name.routine_name, as in Figure 10-99.

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

When you execute a routine remotely, the owner_name in the qualified routine name is optional.

You can call an SPL routine or an external routine from an SPL routine using the CALL statement. CALL can execute both procedures and functions. If you use CALL to execute a function, add a RETURNING clause and the name of an SPL variable (or variables) that will receive the value (or values) the function returns.

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 with the rectangle description, as in Figure 10-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;

The SPL function in Figure 10-100 uses a CALL statement that executes the external function area(). The value area() returns is stored in a and returned to the calling routine by the RETURN statement.

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

Just as with built-in functions, you can execute SPL routines (and external routines from SPL routines) by using them in expressions in SQL and SPL statements. A routine used in an expression is usually a function because it returns a value to the rest of the statement.

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

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


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

You can also execute an SPL routine from an SQL statement, as Figure 10-102 shows. Suppose you write an SPL function, increase_by_pct, which increases a given price by a given percentage. After you write an SPL routine, it is available for use in any other SPL routine.

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;

The example in Figure 10-102 selects the price column of a specified row of inventory and uses the value as an argument to the SPL function increase_by_pct. The function then returns the new value of price, increased by 20 percent, in the variable p.

You can use a RETURN statement to execute any external function from within an SPL routine. Figure 10-103 shows an external function that is used in the RETURN statement of an SPL program.

CREATE FUNCTION c_func() RETURNS int
LANGUAGE C;


CREATE FUNCTION spl_func() RETURNS INT;
	RETURN(c_func());
END FUNCTION;


EXECUTE FUNCTION spl_func();

When you execute the spl_func() function, the c_func() function is invoked, and the SPL function returns the value that the external function returns.

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 ;