mysql自定义函数多参数_MySQL数据库高级（二）——自定义函数

MySQL数据库高级(二)——自定义函数

一、自定义函数简介

自定义函数 (user-defined function UDF)是一种对MySQL扩展的途径，其用法和内置函数相同。

自定义函数的两个必要条件：

A、参数

B、返回值(必须有)。函数可以返回任意类型的值。

二、自定义函数的使用

1、自定义函数语法

CREATE FUNCTION function_name(parameter_nametype,[parameter_name type,...])

RETURNS {STRING|INTEGER|REAL}

runtime_body

在函数体中可以使用更为复杂的语法，比如复合结构/流程控制/任何SQL语句/定义变量等。

带复合结构的函数体的自定义函数的创建语法如下：

DELIMITER //

CREATE FUNCTION function_name(parameter_nametype,[parameter_name type,...])

RETURNS {STRING|INTEGER|REAL}

BEGIN

//body

END

// /* 此处的”//“为告诉系统函数定义结束 */

当函数体内需要执行的是多条语句时，要使用BEGIN...END语句；且当编写函数体内容的时候，需要使用DELIMITER关键字将分隔符先修改为别的，否则编写语句的时候写到’;’的时候会直接执行，导致函数编写失败。

2、创建带复合结构的函数体的自定义函数

在函数体中，如果包含多条语句，需要把多条语句放到BEGIN...END语句块中。

复合结构可以包括声明、循环、控制结构。

3、自定义函数中定义局部变量

变量定义的语法：

DECLARE var_name[,varname]...date_type [DEFAULT VALUE];

为变量赋值的语法：

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

SELECT INTO parameter_name

实例：

DECLARE x int;

SELECT COUNT(id) FROM tdb_name INTO x;

SET @x = 100;

4、流程控制

自定义函数中可以使用流程控制来控制语句的执行。

MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

A、IF语句

IF语句用来进行条件判断。根据是否满足条件，将执行不同的语句。其语法的基本形式如下：

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF

search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。

IF语句都需要使用END IF来结束。

IF语句实例：

IF age>20 THEN SET @count1=@count1+1;

ELSEIF age=20 THEN SET @count2=@count2+1;

ELSE SET @count3=@count3+1;

END IF;

B、CASE语句

CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。CASE语句的基本形式如下：

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list] ...

[ELSE statement_list]

END CASE

case_value参数表示条件判断的变量；when_value参数表示变量的取值；

statement_list参数表示不同when_value值的执行语句。

CASE语句实例：

CASE age

WHEN 20 THEN SET @count1=@count1+1;

ELSE SET @count2=@count2+1;

END CASE ;

CASE语句还有另一种形式，语法如下：

CASE

WHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list] ...

[ELSE statement_list]

END CASE

search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。

CASE语句实例：

CASE

WHEN age=20 THEN SET @count1=@count1+1;

ELSE SET @count2=@count2+1;

END CASE ;

C、LOOP语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。但LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。

LOOP语句的语法的基本形式如下：

[begin_label:] LOOP

statement_list

END LOOP [end_label]

begin_label参数和end_label参数分别表示循环开始和结束的标志，两个标志必须相同，而且都可以省略；statement_list参数表示需要循环执行的语句。

LOOP语句实例：

add_num: LOOP

SET @count=@count+1;

END LOOP add_num ;

D、LEAVE语句

LEAVE语句主要用于跳出循环控制。其语法形式如下：

LEAVE label

label参数表示循环的标志。

LEAVE语句实例：

add_num: LOOP

SET @count=@count+1;

IF @count=100 THEN

LEAVE add_num ;

END LOOP add_num ;

E、ITERATE语句

ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。

ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。

ITERATE语句的基本语法形式如下：

ITERATE label

其中，label参数表示循环的标志。

ITERATE语句实例：

add_num: LOOP

SET @count=@count+1;

IF @count=100 THEN

LEAVE add_num ;

ELSE IF MOD(@count,3)=0 THEN

ITERATE add_num;

SELECT * FROM employee ;

END LOOP add_num ;

LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环