Calling Stored Functions
A function can be called by specifying its name and parameter list wherever an expression of the appropriate data type may be used. To show how stored functions can be called, we'll use the simple stored function shown in Example 10-6.
Example 10-6. Simple stored function
|
CREATE FUNCTION isodd(input_number int) RETURNS int BEGIN DECLARE v_isodd INT; IF MOD(input_number,2)=0 THEN SET v_isodd=FALSE; ELSE SET v_isodd=TRUE; END IF; RETURN(v_isodd); END ; |
From the MySQL command line, we can invoke our simple stored function in a number of ways. Example 10-7 shows how to call the stored function from a SET statement and from a SELECT statement.
Example 10-7. Calling a stored function from the MySQL command line
|
mysql> SET @x=isodd(42); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x; +------+ | @x | +------+ | 0 | +------+ 1 row in set (0.02 sec) mysql> SELECT isodd(42) -> ; +-----------+ | isodd(42) | +-----------+ | 0 | +-----------+ |
From within a stored procedure, we can invoke the function both within a SET clause and within a variety of flow control statements. Example 10-8 shows how to call a stored function from within a SET statement, as well as from an IF statement.
Example 10-8. Calling a stored function from within a stored procedure
|
SET l_isodd=isodd(aNumber); IF (isodd(aNumber)) THEN SELECT CONCAT(aNumber," is odd") as isodd; ELSE SELECT CONCAT(aNumber," is even") AS isodd; END IF; |
Programming languages support a variety of methods for calling a stored function. Java and .NET languages (VB.NET and C#) provide methods to call stored functions directly. However, in many of the dynamic languages (PHP, Perl, Python) there is no API for directly accessing a stored function. (We give guidelines for common programming languages in Chapters 12 through 17.)
If a language does not support a method for directly calling a stored function, you should embed the call in a SELECT statement without a FROM clause and retrieve the function result from the subsequent result set. For instance, in PHP, with the mysqli interface, we can retrieve a stored function result as shown in Example 10-9.
Example 10-9. Calling a stored function from PHP
|
$stmt=$my_db->prepare("SELECT isodd(?)") or die($my_db->error); $stmt->bind_param('i',$aNumber) or die($stmt->error); $stmt->execute( ) or die($stmt->error); $stmt->bind_result($isodd); $stmt->fetch( ); if ($isodd == 1 ) printf("%d is an odd number ",$aNumber); else printf("%d is an even number ",$aNumber); |
Some languages specifically support calling stored functions . For instance, Java JDBC allows a stored function to be called directly, as shown in Example 10-10.
Example 10-10. JDBC support for stored functions
|
CallableStatement PreparedFunc = MyConnect.prepareCall("{ ? = call isodd( ? ) }"); PreparedFunc.registerOutParameter(1, Types.INTEGER); PreparedFunc.setInt(1, aNumber); PreparedFunc.execute( ); if (PreparedFunc.getInt(1) == 1) System.out.println(aNumber + " is odd"); else System.out.println(aNumber + " is even"); |