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)BEGINDECLARE 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)BEGINDECLARE s VARCHAR(10);IF a>=90 THENSET s=\'优\';ELSEIF a>=80 THENSET s=\'良\';ELSEIF a>=70 THENSET s=\'中\';ELSESET 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)BEGINDECLARE s VARCHAR(10);CASE TRUNCATE(a/10,0)WHEN 10 THENSET s=\'优\';WHEN 9 THENSET s=\'优\';WHEN 8 THENSET s=\'良\';WHEN 7 THENSET s=\'中\';ELSESET s=\'差\';END CASE;RETURN s;END !

或者：

DELIMITER !CREATE FUNCTION case_eg1(a INT)RETURNS VARCHAR(10)BEGINDECLARE s VARCHAR(10);CASEWHEN a>=90 THENSET s=\'优\';WHEN a>=80 THENSET s=\'良\';WHEN a>=70 THENSET s=\'中\';ELSESET s=\'差\';END CASE;RETURN s;END !

loop语句格式：

[begin_label:] LOOPstatement_listEND LOOP [end_label]

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

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

调用结果：

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

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

while循环语句格式：

[begin_label:] WHILE search_condition DOstatement_listEND WHILE [end_label]

示例：

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

repeat循环语句格式：

[begin_label:] REPEATstatement_listUNTIL search_conditionEND REPEAT [end_label]

示例：

DELIMITER !CREATE FUNCTION repeat_eg(a INT)RETURNS INTBEGINDECLARE n INT DEFAULT 0;DECLARE s INT DEFAULT 0;REPEATSET n=n+1;SET s=s+n;UNTIL n>=aEND 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)BEGINDECLARE c INT DEFAULT 0;SET b=0;WHILE c<a DOSET 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)BEGINDECLARE c INT DEFAULT 0;DECLARE t INT DEFAULT 0;SET b=0;SET t=i;SET i=j;SET j=t;WHILE c<a DOSET 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;