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");