Using user-defined function in a select statement

Using user-defined function in a select statement : Introduction : Procedure Function MySQL TUTORIALS

MySQL TUTORIALS » Procedure Function » Introduction »

Using user-defined function in a select statement

A function can be used directly from within a SELECT, INSERT, or UPDATE statement.

The result of that function is either saved in the table or returned with the output.

Stored procedures may not return any results.

A stored function always returns a single value.

A stored procedure is executed with an explicit statement: the CALL command.


mysql> delimiter $$
mysql>
mysql> CREATE FUNCTION myFunction(
    ->         in_title          VARCHAR(4),
    ->         in_gender         CHAR(1),
    ->         in_firstname      VARCHAR(20),
    ->         in_middle_initial CHAR(1),
    ->         in_surname        VARCHAR(20))
    ->
    ->   RETURNS VARCHAR(60)
    -> BEGIN
    ->   DECLARE l_title               VARCHAR(4);
    ->   DECLARE l_name_string         VARCHAR(60);
    ->
    ->   IF ISNULL(in_title)  THEN
    ->      IF in_gender='M' THEN
    ->         SET l_title='Mr';
    ->      ELSE
    ->         SET l_title='Ms';
    ->      END IF;
    ->   END IF;
    ->
    ->   IF ISNULL(in_middle_initial) THEN
    ->      SET l_name_string=l_title||' '||in_firstname||' '||in_surname;
    ->   ELSE
    ->      SET l_name_string=l_title||' '||in_firstname||' '||
    ->                           in_middle_initial||' '||in_surname;
    ->   END IF;
    ->
    ->   RETURN(l_name_string);
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> select myFunction('Mrs','M','First','Middle','Last');
+-----------------------------------------------+
| myFunction('Mrs','M','First','Middle','Last') |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql>
mysql>
mysql> select myFunction(null,'M','First','Middle','Last');
+----------------------------------------------+
| myFunction(null,'M','First','Middle','Last') |
+----------------------------------------------+
| 0                                            |
+----------------------------------------------+
1 row in set, 2 warnings (0.02 sec)

mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>