mysql基础(七)自定义函数和存储过程

自定义函数语法格式如图：

第一行是函数名和参数，第二行是返回值类型（必须有返回值并且是唯一结果），再之后是函数体。
下面创建一个单条语句函数：

CREATE FUNCTION riqi ()
 RETURNS VARCHAR (10) 
 RETURN (SELECT DATE_FORMAT(CURDATE(),'%Y%m%d'));

调用函数：

SELECT riqi()

如果创建函数失败报错you might want to use the less safe log_bin_trust_function_creators variable ，执行以下语句开启创建函数、存储过程的权限：

SET GLOBAL log_bin_trust_function_creators = 1;

上面的函数里只有一条语句，那么多条语句该怎么弄？下例用declare声明一个变量result，然后把语句的结果传给result，再返回result：

CREATE FUNCTION riqi1()
RETURNS VARCHAR (10)
DECLARE result VARCHAR(10);
SELECT DATE_FORMAT(CURDATE(),’%Y%m%d’) INTO result;
RETURN result;
结果自然报错。
这时候要用到BEGIN … END复合语句，这是官方文档的解释：存储子程序可以使用BEGIN … END复合语句来包含多个语句。statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。
那就加入复合语句：
CREATE FUNCTION riqi1 ()
RETURNS VARCHAR (10)
BEGIN
DECLARE result VARCHAR(10);
SELECT DATE_FORMAT(CURDATE(),’%Y%m%d’) INTO result;
RETURN result;
END;
还是报错，为什么？虽然用复合语句包裹住了多条语句，但是sql中默认分号结尾，当执行到第一个分号的时候就判定结束进行了提交，报错自然难免了。在这种情况下，我们就应该改变结束符，比如以！为结束符，定义结束符的关键字是demiliter，如下例：

 DELIMITER !
 CREATE FUNCTION riqi1 ()
 RETURNS VARCHAR (10) 
 BEGIN
 DECLARE result VARCHAR(10);
 SELECT DATE_FORMAT(CURDATE(),'%Y%m%d') INTO result;
 RETURN result;
 END !

调用函数（别忘了恢复默认结束符）：

DELIMITER ;
SELECT riqi1()

mysql有如下流程控制语句：
if、case、loop、while、repeat。
IF语法格式
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list]
END IF
示例：

DELIMITER !
CREATE FUNCTION if_eg(a INT)
RETURNS VARCHAR(10)
BEGIN
DECLARE s VARCHAR(10);
IF a>=90 THEN
	SET s='优';
ELSEIF a>=80 THEN
	SET s='良';
ELSEIF a>=70 THEN
	SET s='中';
ELSE 
	SET s='差';
END IF;
RETURN s;
END !

调用结果：

case语法格式：
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] …
[ELSE statement_list]
END CASE
或者
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] …
[ELSE statement_list]
END CASE
示例：

DELIMITER !
CREATE FUNCTION case_eg(a INT)
RETURNS VARCHAR(10)
BEGIN
DECLARE s VARCHAR(10);
CASE TRUNCATE(a/10,0)
WHEN 10 THEN
	SET s='优';
WHEN 9 THEN
	SET s='优';
WHEN 8 THEN
	SET s='良';
WHEN 7 THEN 
	SET s='中';
ELSE
	SET s='差';
END CASE;
RETURN s;
END !

或者：

DELIMITER !
CREATE FUNCTION case_eg1(a INT)
RETURNS VARCHAR(10)
BEGIN
DECLARE s VARCHAR(10);
CASE 
WHEN a>=90 THEN
	SET s='优';
WHEN a>=80 THEN
	SET s='良';
WHEN a>=70 THEN 
	SET s='中';
ELSE
	SET s='差';
END CASE;
RETURN s;
END !

loop语句格式：

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

循环用leave退出。
示例：
求a以内的累加和

DELIMITER !
CREATE FUNCTION loop_eg(a INT)
RETURNS INT
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE s INT DEFAULT 0;
t1:LOOP
	SET n=n+1;
	SET s=s+n;
	IF n>=a THEN
		LEAVE t1;
	END IF;
END LOOP t1;
RETURN s;
END !

调用结果：

也可以用iterate在循环内部再次循环：

DELIMITER !
CREATE FUNCTION loop_eg1(a INT)
RETURNS INT
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE s INT DEFAULT 0;
t1:LOOP
	SET n=n+1;
	SET s=s+n;
	IF n<a THEN 
		ITERATE t1;
	END IF;
	LEAVE t1;
END LOOP t1;
RETURN s;
END !

while循环语句格式：

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

示例：

DELIMITER !
CREATE FUNCTION while_eg(a INT)
RETURNS INT
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE s INT DEFAULT 0;
WHILE n<a DO
	SET n=n+1;
	SET s=s+n;
END WHILE;
RETURN s;
END !

repeat循环语句格式：

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

示例：

DELIMITER !
CREATE FUNCTION repeat_eg(a INT)
RETURNS INT
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE s INT DEFAULT 0;
REPEAT
	SET n=n+1;
	SET s=s+n;
UNTIL n>=a
END REPEAT;
RETURN s;
END !

存储过程示例：

CREATE PROCEDURE rq()
SELECT DATE_FORMAT(CURDATE(),'%y年%m月%d日')

存储过程不指定返回类型，无return关键字，用call调用

CALL rq()

调用结果：

以上是单条语句，多条语句和定义函数一样也是使用BEGIN … END,存储过程有三种参数IN、OUT、INOUT，也就是输入、输出、输入和输出。
下例传入参数a进行累加求和,然后返回累加和b.。

DELIMITER //
CREATE PROCEDURE test1(IN a INT,OUT b INT)
BEGIN
DECLARE c INT DEFAULT 0;
SET b=0;
WHILE c<a DO
	SET c=c+1;
	SET b=b+c;
END WHILE;
END //

调用：

DELIMITER ;
CALL test1(10,@b);
SELECT @b;

@b是接收参数，用来接收返回值。
结果：

在刚才存储过程里面增加交换两个数字的功能：

DELIMITER //
CREATE PROCEDURE test2(IN a INT,OUT b INT,INOUT i INT,INOUT j INT)
BEGIN
DECLARE c INT DEFAULT 0;
DECLARE t INT DEFAULT 0;
SET b=0;
SET t=i;
SET i=j;
SET j=t;
WHILE c<a DO
	SET c=c+1;
	SET b=b+c;
END WHILE;
END //

调用：

DELIMITER ;
SET @c=10,@d=20;
CALL test2(10,@b,@c,@d);
SELECT @b,@c,@d;

结果：

函数和存储过程有哪些区别？
1.功能上存储过程更强大，可以对表的结构进行操作，而自定义函数只能对表的内容进行操作。
2.返回值不同，存储过程可返回也可以不返回，函数必须返回一个值且是唯一的值。
3.参数不同，存储过程有in、out、inout类型，而函数只有类似IN的参数。
4.语法结构不同，存储过程不用指定返回类型，无return，自定义函数必须指定返回类型，且必须包含有效的return语句。
5.调用方式不同：存储过程用call调用，是独立的，可以在外部调用。自定义函数用select调用，是sql的一部分，在sql语句中调用。

查看自定义函数和存储过程

SHOW FUNCTION STATUS;
SHOW PROCEDURE STATUS;