Mysql function creation and application methods

These statements create a storage subroutine. To CREATE a subroutine in MySQL 5.1, you must have the create routine permission and the alter routine and EXECUTE permissions are automatically granted to its creator. If the binary log function is allowed, you may also need SUPER permissions. For details, see section 20.4, "storing subprograms and triggering program binary log functions ".

By default, the subroutine is associated with the current database tutorial. To explicitly associate a subroutine with a given database, you can specify the name db_name.sp_name when creating a subroutine.

If the subprogram name is the same as the built-in SQL function name, you need to insert a space between the subprogram name and the subsequent parentheses when defining the subprogram. Otherwise, a syntax error occurs. When you call a subroutine, insert it. Therefore, even if this problem may occur, we recommend that you avoid giving your own storage subroutines the same name as the existing SQL functions.

Parameter columns enclosed by parentheses must always exist. If no parameter exists, use an empty parameter column (). Each parameter is an IN parameter by default. To specify other parameters, you can use the keyword OUT or INOUT before the parameter name.

Note: specifying the IN, OUT, or INOUT parameter is valid only for PROCEDURE. (FUNCTION parameters are always considered as IN parameters)

The RETURNS clause can only be specified for the FUNCTION, which is mandatory for the FUNCTION. It is used to specify the RETURN type of the function, and the function body must contain a RETURN value statement.

 

Mysql tutorial> 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>

Note: It may be affected by the system.

Affected Systems:
MySQL AB MySQL 4.1.5
MySQL AB MySQL 4.1.4
MySQL AB MySQL 4.1.3-beta
MySQL AB MySQL 4.1.3-0
MySQL AB MySQL 4.1.2-alpha
MySQL AB MySQL 4.1.0-alpha
MySQL AB MySQL 4.1.0-0
MySQL AB MySQL 4.0.9-gamma
MySQL AB MySQL 4.0.9
MySQL AB MySQL 4.0.8-gamma
MySQL AB MySQL 4.0.8
MySQL AB MySQL 4.0.7-gamma
MySQL AB MySQL 4.0.7
MySQL AB MySQL 4.0.6
MySQL AB MySQL 4.0.5a
MySQL AB MySQL 4.0.5
MySQL AB MySQL 4.0.4
MySQL AB MySQL 4.0.3
MySQL AB MySQL 4.0.21
MySQL AB MySQL 4.0.20
MySQL AB MySQL 4.0.2
MySQL AB MySQL 4.0.18
MySQL AB MySQL 4.0.15
MySQL AB MySQL 4.0.14
MySQL AB MySQL 4.0.13
MySQL AB MySQL 4.0.12
MySQL AB MySQL 4.0.11-gamma
MySQL AB MySQL 4.0.11
MySQL AB MySQL 4.0.10
MySQL AB MySQL 4.0.1
MySQL AB MySQL 4.0.0
Unaffected system:
MySQL AB MySQL 4.1.10a
MySQL AB MySQL 4.0.24