Using user-defined function in a select statement : Introduction « Procedure Function « SQL / MySQL

1. SQL / MySQL
2. Procedure Function
3. Introduction

Using user-defined function in a select statement

  
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>

          
  

Related examples in the same category