Mega Code Archive
Using user-defined function in a select statement
<![CDATA[
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> 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>
]]>