Database mysql/mariadb Knowledge Point--function

Function

A function is to encapsulate a piece of code in a struct, and invoke the struct (function) directly when it is necessary to execute the code. This operation enables the reuse of code. In MySQL, there are two kinds of functions: system functions and custom functions.

1. System functions

As the name implies, system functions are well-defined functions, which we call directly when needed.

Any function has a return value (for an empty function, we think its return value is 空 ), and any operation that has a return value in MySQL is done by select operation, so the function call of MySQL is implemented by it select .

Reference Official Document: Https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

2. Custom Function (user-defined function:udf)

The custom function is saved in the Mysql.proc table

• View the UDF list

SHOW Functioin STATUS;

• viewing UDF definitions

SHOW CREATE FUNCTION function_name

• Delete UDF

DROP FUNCTION function_name

• Calling Custom Function syntax

SELECT function_name (Parameter_value,...)

• Assigning a value to a variable

SET parameter_name = Value[,parameter_name = value ...]

• View variables

SELECT into Parameter_name

Example 1: no parameter UDF

MariaDB [testdb]> CREATE FUNCTION simplefun () RETURNS VARCHAR RETURN "Hello world!";

Example 2: Parametric UDF

MariaDB [testdb]> DELIMITER//  #修改结束符为//mariadb [testdb]>create FUNCTION Deletebyid (uid SMALLINT UNSIGNED) RETURNS VARCHAR (a),    BEGIN    ->delete from students WHERE stuid = UID;    --RETURN (SELECT COUNT (UID) from students);    End//query OK, 0 rows affected (0.01 sec) MariaDB [testdb]> DELIMITER;  #定义完函数后再修改回来

Example 3:

Define local variable syntax in a custom function:

DECLARE variable 1[, variable 2,...] Variable type [default defaults]

Description: The scope of the local variable is at begin ... End program, and the definition of a local variable statement must be at begin ... The first line of the end defines

MariaDB [testdb]> DELIMITER//  #修改结束符为//mariadb [testdb]> CREATE FUNCTION addtwonumber (x SMALLINT UNSIGNED, Y S Mallint UNSIGNED)    , RETURNS SMALLINT    , DECLARE A, b SMALLINT UNSIGNED DEFAULT ten;    SET  a = x, b = y;    --RETURN a+b;    End//query OK, 0 rows affected (0.01 sec) MariaDB [testdb]> DELIMITER;  #定义完函数后再修改回来MariaDB [testdb]> SELECT Addtwonumber (8,9);  #调用UDF求和 +-------------------+| Addtwonumber (8,9) |+-------------------+|                |+-------------------+

View functions

To view a function, the basic syntax is:

SHOW FUNCTION STATUS;

View function definitions

SHOW CREATE FUNCTION function_name

　　

Database mysql/mariadb Knowledge Point--function