mysql>
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=CONCAT(l_title,' ',in_firstname,' ',in_surname);
-> ELSE
-> SET l_name_string=CONCAT(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, 1 warning (0.00 sec)
mysql>
mysql>
mysql> select myFunction(null,'M','First','Middle','Last');
+----------------------------------------------+
| myFunction(null,'M','First','Middle','Last') |
+----------------------------------------------+
| Mr First M Last |
+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
|