ABSTRACT: This paper mainly studied the method of using DDL statements to operate custom functions.

Understanding custom functions

What is it?

Custom functions are procedural database objects that are very similar to stored procedures. Like stored procedures, it is a code fragment consisting of SQL statements and procedural statements, and can be invoked by applications and other SQL statements.

The Difference between Custom Functions and Stored Procedures

Custom functions cannot have output parameters, because the custom functions themselves are output parameters; and stored procedures can have output parameters.

Custom functions must contain a return statement, and this particular SQL statement is not allowed to be included in stored procedures.

Calls to custom functions can be made directly without the call statement, while calls to stored procedures need the call statement.

Create custom functions

grammar

1 create function function name ([parameter])
2 returns type
3 Function Subject

Explain

1) Function name

Specify the name of the custom function. Note that custom functions cannot have the same name as stored procedures.

2) parameters

Parameters used to specify custom functions. The parameters here are only names and types, and keywords in, out, and inout cannot be specified.

3) type

The data type used to declare the return value of a custom function.

4) Subject of Function

The body of a custom function, also known as a function body. All SQL statements used in stored procedures are also applicable in custom functions, including local variables, set statements, process control statements, cursors and so on.

In addition, the custom function body must contain a return return return statement to specify the return value of the custom function. When the return return value statement contains the select statement, the return result of the select statement can only be one row and only one column of values.

Example

Create custom functions without parameters:

1 mysql> create function showTopGrade()
2     -> returns int(10)
3     -> return (select max(grade) from score);
4 Query OK, 0 rows affected (0.00 sec)
5 
6 mysql> 

Create custom functions with parameters:

1 mysql> create function getStuGrade(stu varchar(45))
2     -> returns int(10)
3     -> return (select max(grade) from score where student = stu);
4 Query OK, 0 rows affected (0.00 sec)
5 
6 mysql> 

Use custom functions

grammar

1 select custom function name ([parameter]);

Example

1 mysql> select showTopGrade();
2 +----------------+
3 | showTopGrade() |
4 +----------------+
5 |             95 |
6 +----------------+
7 1 row in set (0.00 sec)
8 
9 mysql> 

View custom functions

View all custom functions

 1 mysql> mysql> show function status;
 2 +------+--------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 3 | Db   | Name         | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
 4 +------+--------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 5 | demo | getGrade     | FUNCTION | root@localhost | 2019-09-07 18:40:17 | 2019-09-07 18:40:17 | DEFINER       |         | utf8                 | utf8_general_ci      | gb2312_chinese_ci  |
 6 | demo | getStuGrade  | FUNCTION | root@localhost | 2019-09-10 00:25:54 | 2019-09-10 00:25:54 | DEFINER       |         | utf8                 | utf8_general_ci      | gb2312_chinese_ci  |
 7 | demo | showTopGrade | FUNCTION | root@localhost | 2019-09-10 00:19:40 | 2019-09-10 00:19:40 | DEFINER       |         | utf8                 | utf8_general_ci      | gb2312_chinese_ci  |
 8 +------+--------------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 9 3 rows in set (0.00 sec)
10 
11 mysql> 

View Creation Statements for Custom Functions

1 mysql> mysql> show create function getGrade;
2 +----------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
3 | Function | sql_mode                                   | Create Function                                                                                                        | character_set_client | collation_connection | Database Collation |
4 +----------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
5 | getGrade | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `getGrade`() RETURNS int(10) return (select grade from score where id = 12) | utf8                 | utf8_general_ci      | gb2312_chinese_ci  |
6 +----------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
7 1 row in set (0.00 sec)
8 
9 mysql> 

Modify custom functions

The modification can be achieved by deleting the custom function first and then recreating the custom function.

Delete custom functions

grammar

1 drop function custom function name

Example

1 mysql> drop function getGrade;
2 Query OK, 0 rows affected (0.00 sec)
3 
4 mysql>