MySQL自定义函数

最新推荐文章于 2023-04-28 22:29:18 发布

露天窗

最新推荐文章于 2023-04-28 22:29:18 发布

阅读量368

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/lutianchuang/article/details/105858424

版权

Mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

什么是MySQL函数
函数存储着一系列sql语句，调用函数就是一次性执行这些语句,MySQL有很多内置的函数供大家使用,当然我们也可以根据业务需求自定义封装函数,方便我们自己使用.
如何自定义函数
我们可以利用MySQL图形化工具来创建函数

点击创建函数后出现如下图所示sql结构语句

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `sys`.`fun`()
    RETURNS TYPE
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN

    END$$

DELIMITER ;

DELIMITER $$

意思是修改默认的结束符";“为”$$"，以后的SQL语句都要以此结尾

CREATE FUNCTION ‘sys’.‘fun’()
这句意思是在sys数据库中创建函数fun,()内可填入方法参数
例如(id INT,str VARCHAR),先写参数变量名,再写参数类型
RETURNS TYPE
这句意思是声明函数返回值类型为TYPE
例如VARCHAR(200) CHARSET utf8
注意:此处定义返回参数类型的时候一定要定义参数长度
BEGIN和END$$

"BEGIN和END$$"之间写函数的实现过程

先写个小例子
函数实现功能:根据员工id查询员工姓名

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `sys`.`fun`(id INT)
    RETURNS VARCHAR(20) CHARSET utf8
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
    	/*定义变量*/
		DECLARE m VARCHAR(20) DEFAULT '';
		/*变量赋值*/
		SET m = (SELECT employeeName FROM t_employee WHERE employeeId=id);
		/*返回变量*/
		RETURN m;
    END$$

DELIMITER ;

执行以上语句后,函数栏下会多出一个fun方法
在这里插入图片描述
生成函数后在sql语句中直接调用就可以了

函数实现的语法

定义变量
定义局部变量语法：DECLARE var_name[,varname]…date_type [DEFAULT VALUE];
例如:
定义一个变量 m
DECLARE m VARCHAR(200) DEFAULT ‘’;
定义多个变量 a b c
DECLARE a,b,c VARCHAR(200) DEFAULT ‘’;
注意定义变量类型的时候参数长度不能掉
设置变量
方式1
SET m= (SELECT employeeName FROM t_employee WHERE employeeId=id);
注意()不能掉
方式2
SELECT employeeName FROM t_employee WHERE employeeId=id into m
返回函数结果
RETURN m;

流程控制语句

if语句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list]
END IF
例如:

	DECLARE m VARCHAR(200) DEFAULT '';
	DECLARE n INT(10) DEFAULT 0;
	SET n=0;
		
	IF n>0 THEN SET m="变量n大于0";
	ELSEIF n=0 THEN SET m="变量n等于0";
	ELSE SELECT employeeName FROM t_employee WHERE employeeId=id INTO m;
	END IF;

根据n与0的大小关系来执行不同的SET语句。
IF语句都需要使用END IF来结束

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

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] …
[ELSE statement_list]
END CASE

	DECLARE m VARCHAR(200) DEFAULT '';
	DECLARE n INT(10) DEFAULT 0;
	
	SET n=0;
		CASE n
			WHEN 3 THEN SET m="n的值为3";
			WHEN 4 THEN SET m="n的值为4";
			ELSE SET m="n的值不是3也不是4";
		END CASE;

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

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] …
[ELSE statement_list]
END CASE

	DECLARE m VARCHAR(200) DEFAULT '';
	DECLARE n INT(10) DEFAULT 0;
	SET n=0;
	CASE
		WHEN n>0 THEN SET m="n的值大于0";
		WHEN n=0 THEN SET m="n的值等于0";
		ELSE SET m="n的值小于0";
	END CASE;

LOOP语句
LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。
但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。
LOOP语句的语法的基本形式如下
[begin_label:] LOOP
statement_list
END LOOP [end_label]
例如:

add_num: LOOP  
SET @count=@count+1;  
END LOOP add_num ;

该示例循环执行count加1的操作。因为没有跳出循环的语句，这个循环成了一个死循环。
LOOP循环都以END LOOP结束。

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

add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
END LOOP add_num ;

该示例循环执行count加1的操作。当count的值等于100时，则LEAVE语句跳出循环。

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 ;

该示例循环执行count加1的操作，count值为100时结束循环。如果count的值能够整除3，则跳出本次循环，不再执行下面的SELECT语句。
说明：LEAVE语句和ITERATE语句都用来跳出循环语句，但两者的功能是不一样的。
LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。

REPEAT语句
REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label]

statement_list参数表示循环的执行语句；
search_condition参数表示结束循环的条件，满足该条件时循环结束。
下面是一个ITERATE语句的示例。代码如下：

REPEAT 
SET @count=@count+1; 
UNTIL @count=100 
END REPEAT ;

该示例循环执行count加1的操作，count值为100时结束循环。
REPEAT循环都用END REPEAT结束。

WHILE语句
WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。
WHILE语句是当满足条件时，执行循环内的语句。
WHILE语句的基本语法形式如下：

[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label]

search_condition参数表示循环执行的条件，满足该条件时循环执行；
statement_list参数表示循环的执行语句。
下面是一个ITERATE语句的示例。代码如下：

WHILE @count<100 DO 
SET @count=@count+1; 
END WHILE ;

该示例循环执行count加1的操作，count值小于100时执行循环。
如果count值等于100了，则跳出循环。WHILE循环需要使用END WHILE来结束。

函数与存储过程的区别
a. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。
b. 对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。
c. 存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。
d. 存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

露天窗

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL自定义函数

什么是MySQL函数函数存储着一系列sql语句，调用函数就是一次性执行这些语句,MySQL有很多内置的函数供大家使用,当然我们也可以根据业务需求自定义封装函数,方便我们自己使用.如何自定义函数我们可以利用MySQL图形化工具来创建函数点击创建函数后出现如下图所示sql结构语句DELIMITER $$CREATE /*[DEFINER = { user | CUR...
复制链接

扫一扫