MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。

1、创建存储函数

在MySQL中，创建存储函数使用CREATE FUNCTION关键字，其基本形式如下：

CREATE FUNCTION func_name ([param_name type[,...]])
RETURNS type
[characteristic ...] 
BEGIN
	routine_body
END;

参数说明：

（1）func_name ：存储函数的名称。

（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。

（3）RETURNS type：指定返回值的类型。

（4）characteristic：可选项，指定存储函数的特性。

（5）routine_body：SQL代码内容。

2、调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法基本相同。用户自定义的存储函数与MySQL内部函数性质相同。区别在于，存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下：

SELECT func_name([parameter[,…]]);

示例：创建存储函数，实现根据用户编号，获取用户姓名功能。

（1）先创建tb_user（用户信息表），并添加数据。

-- 创建用户信息表
CREATE TABLE IF NOT EXISTS tb_user
(
	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
	name VARCHAR(50) NOT NULL COMMENT '用户姓名'
) COMMENT = '用户信息表';
 
-- 添加数据
INSERT INTO tb_user(name) VALUES('jenrey`s csdn');
INSERT INTO tb_user(name) VALUES('jenrey');
INSERT INTO tb_user(name) VALUES('zhangsan');
INSERT INTO tb_user(name) VALUES('lisi');
INSERT INTO tb_user(name) VALUES('wangwu');
INSERT INTO tb_user(name) VALUES('maliu');
INSERT INTO tb_user(name) VALUES('https://blog.csdn.net/jenrey');

查询数据结果：

（2）创建存储函数

-- 创建存储函数
DROP FUNCTION IF EXISTS func_user;
CREATE FUNCTION func_user(in_id INT)
RETURNS VARCHAR(50)
BEGIN
	DECLARE out_name VARCHAR(50);
 
	SELECT name INTO out_name FROM tb_user
	WHERE id = in_id;
 
	RETURN out_name;
END;

（3）调用存储函数

-- 调用存储函数
SELECT func_user(1);
SELECT func_user(2);
SELECT func_user(3);
SELECT func_user(4);
SELECT func_user(5);
SELECT func_user(6);
SELECT func_user(7);

执行结果：

3、修改存储函数

MySQL中，通过ALTER FUNCTION 语句来修改存储函数，其语法格式如下：

ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

上面这个语法结构是MySQL官方给出的，修改的内容可以包含SQL语句也可以不包含，既可以是读数据的SQL也可以是修改数据的SQL还有权限。此外在修改function的时候还需要注意你不能使用这个语句来修改函数的参数以及函数体，如果你想改变这些的话你就需要删除掉这个函数然后重新创建。

4、删除存储函数

MySQL中使用DROP FUNCTION语句来删除存储函数。

示例：删除存储函数。

DROP FUNCTION IF EXISTS func_user;

其它补充：

如果你在创建存储函数时提示以下错误：

[Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is
enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

解决方法：

解决办法也有两种， 第一种是在创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个， 例如: CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`()     DETERMINISTIC BEGIN #Routine body goes here... END;;

第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。

设置方法有三种：

（1）在客户端上执行 SET GLOBAL log_bin_trust_function_creators = 1。

（2）MySQL启动时，加上--log-bin-trust-function-creators选贤，参数设置为1。

（3）在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加

vi /etc/my.cnf

log-bin-trust-function-creators=1。

再执行查看binlog参数的命令：show global variables like "%log_bin%"

关于上面的配置解释可以参考：

MySQL参数log_bin_trust_function_creators介绍

1、创建存储过程和函数

    存储过程是一条或者多条SQL语句的集合，相当于批处理文件，但是作用不仅仅限于批处理。使用存储过程将简化操作，减少冗余的步骤，同时还可减少操作过程的失误，提高效率。

（1）创建存储过程

         创建存储过程是通过”CREATE PROCEDURE”语句来创建，语法格式为：

CREATE PROCEDURE sp_name ([proc_parameter])
[characteristics…] routine_body

各选项说明：

         CREATE PROCEDURE：创件存储过程的关键字

         sp_name：存储过程名称

         proc_parameter：指定存储过程的参数列表，列表形式为：[ IN | OUT | INOUT ] param_name type

         characteristics：用于指定存储过程的特性

         routine_body：mysql sql语句内容，使用BEGINE…END来表示SQL代码的开始和结束。

# 创建存储过程前先将sql语句结束符改为//，以防止和默认的结束符冲突， 
mysql> delimiter //
mysql> create procedure proc()
    -> begin
    -> select * from course;
    -> end //
Query OK, 0 rows affected (0.08 sec)
# 存储过程创建完成后将结束符改为默认的结束符
mysql> delimiter ;
# 调用创建的存储过程
mysql> call proc();
+----+-------------+------------+
| id | course_name | teacher_id |
+----+-------------+------------+
|  1 | math        |          3 |
|  2 | english     |          2 |

（2）创建存储函数

         函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用call语句，而调用函数就直接引用函数名+参数即可，创建存储函数使用”CREATE FUNCTION”语句来创建，语法格式为：

CREATE FUNCTION func_name ( param_name type )
RESTURNS type
[characteristic……] routine_body

主要参数说明：

         Param_name：参数名称

         Type：参数类型

         RETURNS type：函数返回数据的类型

         Characteristic：指定存储函数的特性

mysql> delimiter //
# 创建一个函数
mysql> create function stubak_update(param1 int)
    -> returns int
    -> begin
    -> update student_bak set gender=1 where sid=param1;
    -> select count(*) into @a from student_bak where sid>param1;
    -> return @a;
    -> end;
    -> //
mysql> delimiter ;
# 调用函数，并传入一个参数
mysql> select stubak_update(1); 
+------------------+
| stubak_update(1) |
+------------------+
|                7 |
+------------------+

2、存储过程中使用变量

（1）定义变量

         在存储过程中通过DECLARE语句定义变量，定义变量的格式如下：

         DECLARE var_name[,varname]… date_type [DEFAULT value];

         选项说明：

                   var_name：局部变量名称

                   DEFAULT value：用于给变量提供一个默认值

                   Type：用于指定变量的默认类型

（2）为变量赋值

         声明后的变量可以通过select … into var_list进行赋值，或者通过set语句赋值，或者通过定义游标并使用fetch … into var_list赋值,使用set赋值的方式如下：

  SET var_name = expr [, var_name = expr] ……;

# mysql存储过程中使用变量
mysql> delimiter //
mysql> create procedure sp1(v_sid int)
    -> begin
    -> declare xname varchar(10) default 'dayi123';
    -> declare xsex int;
    -> select sname,gender into xname,xsex from student_bak where sid=v_sid;
    -> select xname,xsex; 
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call sp1(1);
+--------+------+
| xname  | xsex |
+--------+------+
| Andrew |    1 |
+--------+------+    

3、定义条件和处理程序

         定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题是应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。

（1）定义条件

         定义条件使用DECLARE语句，语法格式如下

  DECLARE condition_name CONDITION FOR [condition_type]
  [condition_type]:
  SQLSTATE [VALUE] sqlstate_value | mysql_error_code

各选项说明：

         Condition_name：表示条件名称

         Condition_type：表示条件类型

         sqlstate_value | mysql_error_code：mysql中的错误，sqlstate_value为长度为5的字符串类型错误代码，mysql_error_code为数值类型的错误代码

（2）定义处理程序

         定义处理程序时，使用DECLARE语句实现：

 DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement handler_type:
          CONTINUT | EXIT | UNDO
  Condition_value：
          SQLSTATE [VALUE] sqlstate_value | condition_name
  | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

各选项说明：

         Handler_type：错误处理方式，continue表示遇到错误不处理，exit为退出，undo为撤回之前操作。

         Condition_value表示错误类型，有以下的取值

                   SQLSTATE [VALUE] sqlstate_value：包含5个字符的字符串错误值

                   condition_name：declare condition定义错误的错误条件名称

                   SQLWARNING：匹配所有以01开头SQLSTATE错误代码

                   NOT FOUND：匹配所有以02开头的SQLSTATE错误代码

                   SQLEXCEPTION：匹配所有没有被SQLWARENING或NOT FOUNT捕获的SQLSTATE的错误代码

                   mysql_error_code：匹配数值类型的错误代码

         sp_statement：在遇到定义的错误时，需要执行的存储过程或函数。

当condition发生但没有声明handler时，则存储过程和函数依照如下规则处理：

         发生SQLEXCEPTION错误，则执行exit退出

         发生SQLWARNING警告，则执行contine继续执行

         发生NOT FOUND情况，则执行continue继续执行

# 创建一个存储过程并定义处理程序当错误吗为23000时跳过继续执行
# 创建一张用于测试的表
mysql> delimiter //
mysql> create procedure handlerdemo()
    -> begin
    -> declare continue handler for sqlstate '23000' set @x2=1;
    -> set @x=1;
    -> insert into t values(1);
    -> set @x=2;
    -> insert into t values(1);
    -> set @x=3;
    -> end;
    -> //
mysql> delimiter ;
# 调用存储过程
mysql> CALL handlerdemo();
# 插看变量的值
mysql> select @x;
+------+
| @x   |
+------+
|    3 |
+------+

4、存储过程中的流程控制语句

    MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的流程控制语句，另外return语句也是函数中的特定流程控制语句。

（1）case语句

         Case语句有两种语句格式分别如下：

         格式一：

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

         格式二：

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

         创建存储过程时使用case语句

mysql> delimiter //
mysql> CREATE PROCEDURE exp_case(v_sid int)
    ->   BEGIN
    ->     DECLARE v INT DEFAULT 1;
    ->     select gender into v from student_bak where sid=v_sid;
    ->     CASE 
    ->       WHEN v=0 THEN update student_bak set gender=1 where sid=v_sid;
    ->       WHEN v=1 THEN update student_bak set gender=0 where sid=v_sid;
    ->       ELSE
    ->           update student_bak set gender=-1 where sid=v_sid;
    ->     END CASE;
    ->   END;
    -> //
mysql> delimiter ;

（2）if语句

         If语句包含多个条件判断，根据条件判断的结果为TRUE或FALSE执行相应的语句，语法格式为：

IF search_condition THEN statement_List
  [ELSEIF search_condition THEN statement_list] …
  [ELSE statement_list]
END IF

         创建一个函数，使用if语句判断两个数的大小

mysql> CREATE FUNCTION exp_if(n INT, m INT)
    ->   RETURNS VARCHAR(20)
    ->   BEGIN
    ->     DECLARE s VARCHAR(20);
    ->     IF n > m THEN SET s = '>';
    ->     ELSEIF n = m THEN SET s = '=';
    ->     ELSE SET s = '<';
    ->     END IF;
    ->     SET s = CONCAT(n, ' ', s, ' ', m);
    ->     RETURN s;
->   END //
mysql> DELIMITER ;
mysql> select exp_if(1,2);
+-------------+
| exp_if(1,2) |
+-------------+
| 1 < 2       |
+-------------+

         If语句也可在创建存储过程及函数时嵌套使用，

mysql> CREATE FUNCTION exp_if02 (n INT, m INT)
    ->   RETURNS VARCHAR(50)
    ->   BEGIN
    ->     DECLARE s VARCHAR(50);
    ->     IF n = m THEN SET s = 'equals';
    ->     ELSE
    ->       IF n > m THEN SET s = 'greater';
    ->       ELSE SET s = 'less';
    ->       END IF;
    ->       SET s = CONCAT('is ', s, ' than');
    ->     END IF;
    ->     SET s = CONCAT(n, ' ', s, ' ', m, '.');
    ->     RETURN s;
    ->   END //
mysql> delimiter ;

（3）iterate语句

         Iterate语句仅出现在loop,repeat,while循环语句中，其含义表示重新开始此循环，格式如下：

ITERATE label

         Label表示自定义的标签名

（4）leave语句

         Leave语句表明退出指定标签的流程控制语句块，通常会用在begin…end，以及loop,repeat,while的循环语句中，格式如下：

LEAVE label

         Label表示要退出的标签名

（5）LOOP语句

         Loop语句是存储过程或函数中表达循环执行的一种方式，LOOP内的语句一直重复执行直到循环被退出，跳出循环时使用LEVAVE子句，LOOP语句的基本格式如下：

[begin_label:] LOOP
  Statement_list
END LOOP [ END_LABEL ]

         创建存储过程使用loop循环语句实现变量的自增

mysql> DELIMITER //
# 创建存储过程
mysql> CREATE PROCEDURE exp_loop(p1 INT)
    -> BEGIN
    ->   label1: LOOP
    ->     SET p1 = p1 + 1;
    ->     IF p1 < 10 THEN
    ->       ITERATE label1;
    ->     END IF;
    ->     LEAVE label1;
    ->   END LOOP label1;
    ->   SET @x = p1;
    -> END //
mysql> DELIMITER ;
# 调用存储过程
mysql> call exp_loop(1);
# 查看执行后变量的值
mysql> select @x;
+------+
| @x   |
+------+
|   10 |
+------+

（6）repeat语句

         Repeat语句用于创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。

[repeat_label:] REPEAT
  Statement_List
UNTIL search_condition
END REPEAT [repeat_label]

         Repeat_label为REPEAT语句的标注名称，该参数可省略

mysql> delimiter //
# 创建基于repeat循环的存储过程
mysql> CREATE PROCEDURE exp_rep(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT
    ->     SET @x = @x + 1;
    ->   UNTIL @x > p1 END REPEAT;
    -> END
    -> //
mysql> delimiter ;
# 调用存储过程，并查看变量@x的最终值
mysql> call exp_rep(100);
mysql> select @x;
+------+
| @x   |
+------+
|  101 |
+------+

（7）while语句

         While语句也是用于创建一个带条件判断的存储过程，与REPEAT不同的是while在执行语句时先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出，语句格式如下：

[while_label:] WHILE expr_condition DO
  Statement_list
END WHILE [while_label]

         创建基于while循环的存储过程，相对于repeat循环是先判断在执行。

mysql> DELIMITER //
mysql> CREATE PROCEDURE exp_whi(p1 INT)
    -> BEGIN
    ->   SET @b = 0;
    ->   WHILE @b < p1 DO
    ->     SET @b = @b + 1;
    ->   END WHILE;
    -> END;
    -> //
mysql> DELIMITER ;
mysql> call exp_whi(100);
mysql> select @b;
+------+
| @b   |
+------+
|  100 |
+------+

5、游标

         Mysql查询语句可能返回多条记录，如果数据量大则需要在存储过程中和储存函数中使用游标来逐条读取查询结果中的记录。应用程序可以根据需要滚动或浏览其中的数据。

（1）声明游标

         游标必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。声明游标的语句如下：

DECLARE cursor_name CURSOR FOR select_statement

（2）打开游标

         打开游标的语句如下：

OPEN cursor_name

（3）使用游标

         使用游标语句如下

FETCH cursor_name INTO var_name [,var_name] ……

         var_name：表示将光标中的SELECT 语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。

         使用游标时，数据集中的字段需要和INTO语句中定义的变量一一对应，数据集中的数据都fetch完之后，则返回NOT FOUND。

（4）关闭游标

         关闭游标语句如下：

CLOSE cursor_name 

（5）游标的使用

mysql> DELIMITER //
mysql> CREATE PROCEDURE exp_cur()
    -> BEGIN
    ->   DECLARE done INT DEFAULT FALSE;
    ->   DECLARE a CHAR(16);
    ->   DECLARE b, c INT;
    ->   DECLARE cur1 CURSOR FOR SELECT sname,dept_id FROM student;
    ->   DECLARE cur2 CURSOR FOR SELECT id FROM course;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    ->   OPEN cur1;
    ->   OPEN cur2;
    ->   read_loop: LOOP
    ->     FETCH cur1 INTO a, b;
    ->     FETCH cur2 INTO c;
    ->     IF done THEN
    ->       LEAVE read_loop;
    ->     END IF;
    ->     IF b = c THEN
    ->       INSERT INTO test VALUES (a,b);
    ->     ELSE
    ->       INSERT INTO test VALUES (a,c);
    ->     END IF;
    ->   END LOOP;
    ->   CLOSE cur1;
    ->   CLOSE cur2;
-> END //
mysql> DELIMITER ;

6、查看创建的存储过程和函数

         在创建和存储过程和函数后可以通过”show status”语句或”show create”语句来查看创建的存储过程和函数，也可直接从系统的”information_schema”数据库中查询。

         使用”show status”语句查看存储过程和函数状态语句结构如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]

# 查看创建的所有以”exp”开头的存储过程

mysql> show procedure status like 'exp%' \G
*************************** 1. row ***************************
                  Db: course
                Name: exp_case
                Type: PROCEDURE
             Definer: root@localhost
……

         使用”show create”查看存储和函数状态语句如下：

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

         从”information_schema.routines”表中查看存储过程和函数信息的语句结构如下：

Select * from information_schema.routines where routing_name=’sp_name’;

7、修改创建的存储过程和函数

         存储过程及函数穿件完成后可以通过”alter”语句来修改存储过程或函数的特性，修改的语句如下：

ALTER {PROCEDURE|FUNCTION} sp_name [characteristic …]

Characteristic参数用于指定存储函数的特性，取值有：

         CONTAINS SQL：表示子程序包含SQL语句，但不包含读或写数据的语句。

         NO SQL:表示子程序中不包含SQL语句

         READS SQL DATA：表示子程序中包含读数据的语句

         MODIFIES SQL DATA：表示子程序中包含写数据的语句

         SQL SECURITY {DEFINER|INVOKER}：指明谁有权限来执行

         DEFINER：表示只有定义者自己才能够执行

         INVOKER：表名调用者可以执行

         COMMENT ‘string’：表示注释信息

8、删除存储过程及存储函数

         删除存储过程及存储函数使用”drop”语句来删除，语法格式如下：

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name

         删除一个创建的存储过程：

mysql> drop procedure exp_whi;
Query OK, 0 rows affected (0.00 sec)

9、mysql触发器

         触发器是一个特殊的存储过程，触发器的作用是当表上有对应SQL语句发生时，则触发执行。

（1）创建触发器

         创建触发器的语句如下：

CREATE
  [DEFINER = {user | CURRENT_USER }]
  TRIGGER trigger_name
  Trigger_time trigger_event
  ON tbl_name FOR EACH ROW
  [trigger_order]
  trigger_body

各选项说明：

         Definer：用来指定trigger的安全环境

         trigger_name：标识触发器的名称

         Trigger_time：指定触发器的执行时间，BEFORE代表在数据修改前执行，AFTER代表在修改后执行。

         Trigger_event：指定触发该触发器的具体事件，INSERT当新的一行数据插入表中时触发，UPDATE当表的一行数据被修改时触发，DELETE当表的一行数据被删除时触发，当执行insert into … on duplicate key update语句时，当碰到重复行执行update时，则触发update下的触发器

         tbl_name：标识建立触发器的表名

         Trigger_body：表示触发器触发之后要执行的一个或多个语句，在内部可以引用涉及表的字段，OLD.col_name表示行数据被修改或删除之前的字段数据，NEW.col_name表示行数据被插入或修改之后的字段数据

# 先创建一张表用于在触发触发器时备份要修改的数据
mysql> create table student_back(sid int,old_sname varchar(12),sname varchar(12),old_gender int,gender int,update_time time)//
mysql> delimiter //
# 创建触发器
mysql> create trigger update_trigger
    -> after update
    -> on student for each row
    -> begin
    -> insert into student_back values(old.sid,old.sname,new.sname,old.gender,new.gender,now());
    -> end;
    -> //
mysql> delimiter ;
mysql> update student set gender=1 where sid=1;
# 更新数据后查看备份的数据
mysql> select * from student_back;
+------+-----------+--------+------------+--------+-------------+
| sid  | old_sname | sname  | old_gender | gender | update_time |       
+------+-----------+--------+------------+--------+-------------+
|    1 | Andrew    | Andrew |          0 |      1 | 19:12:29    |
+------+-----------+--------+------------+--------+-------------+

（2）查看触发器

         触发器创建好后可以通过”show triggers”命令查看，也可在”triggers”表中查看触发器信息。

         通过命令查看：show triggers;

         通过表查看语句：select * from information_schema.triggers where trigger_name=' trigger_name ' \G

（3）删除触发器

         触发器可以通过”drop trigger”语句来删除，删除触发器的语句格式为：

DROP TRIGGER [schema_name.] trigger_name

         schema_name表示数据库的名称为可选参数

# 删除触发器
mysql> drop trigger update_trigger;                                                             
Query OK, 0 rows affected (0.00 sec)

开始之前

首先创建如下两张表,并初始化一些数据。

创建存储过程(CREATE PROCEDURE)

基本语法格式如下：

CREATE PROCEDURE sp_name (parameters)
[characteristics ...] routine_body

其中：CREATE PROCEDURE为创建存储过程的关键字；sp_name为存储过程的名称(唯一性，没有所谓的重载方法概念)；parameters为参数列表；characteristics指定存储过程的特性(该部分可以省略,即使用默认声明)；routine_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。

parameters 表现形式 --> [IN(OUT/INOUT)] param_name type。IN代表入参、OUT代表出参、INOUT代表既可以是入参也可以是出参， param_name参数名称，type是类型(类型是mysql数据库中支持的任意类型,VARCHAR需要指定长度,如VARCHAR(255))。

characteristics 表现形式 --> 

--简单的示例：

DELIMITER $$  -- 声明结束符
CREATE PROCEDURE list_emp() -- 创建无参数存储过程
	COMMENT '查询所有员工及其所在部门' -- characteristics 部分(可以省略)
	BEGIN
		SELECT * FROM tb_emp e
		INNER JOIN tb_dept d
		ON e.dept_id = d.dept_id;
	END $$
DELIMITER ; -- 恢复默认结束符

CALL list_emp(); -- 调用存储过程

SHOW PROCEDURE STATUS LIKE '%list_emp%'; -- 查看存储过程

DROP PROCEDURE list_emp;  -- 删除存储过程,目前编写错误的话直接删除重写

存储过程定义详情(SHOW PROCEDURE STATUS LIKE '%list_emp%'; -- 查看存储过程)：

执行结果(CALL list_emp(); -- 调用存储过程)：

--有参数的存储过程：

DELIMITER $$
CREATE PROCEDURE count_emp(OUT total_emp INT)
	COMMENT '统计员工数量'
	BEGIN
		SELECT COUNT(*) INTO total_emp FROM tb_emp;
	END $$
DELIMITER ;

SET @total_emp = 10; -- 声明变量

CALL count_emp(@total_emp); -- 调用存储过程

SELECT @total_emp; -- 获取结果

DROP PROCEDURE count_emp; -- 删除存储过程

执行结果：

总结说明：

1、这里的'DELIMITER $$'语句的作用是将mysql的结束符号设置为'$$'，因为mysql默认的结束符号是';'，为了避免与存储过程中的SQL语句结束符号冲突,需要使用'DELIMITER'改变存储过程的结束符,并以'END $$'结束存储过程。存储过程定义完毕再以'DELIMITER ;'恢复默认结束符号。亦可以指定其它符号为结束符，但是不能用反斜杠'\',它是mysql中的转义符。当然,简单的存储过程不更改结束符大多数情况下也是不会出现错误的。

2、有参数的时候,VARCHAR类型需要指定长度，比如VARCHAR(255)。

创建存储函数(CREATE FUNCTION)

基本语法格式如下：

CREATE FUNCTION func_name(params)
RETURNS type
[characteristics ...] routine_body

其中：CREATE FUNCTION为创建存储函数的关键字；func_name为存储函数的名称(唯一性，没有所谓的重载方法概念)；params为参数列表；RETURNS type语句表示函数返回数据的类型，可以是mysql中的任意数据类型；characteristics指定存储函数的特性(和存储过程一样)；routine_body是存储函数主体。

参数列表：IN、OUT、或INOUT只对PROCEDURE是合法的(FUNCTION中总是默认IN参数，所以声明存储函数入参的时候不不能声明IN，会报错，因为默认是IN)。RETURNS子句只能对FUNCTION作指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

-- 简单的示例：

DELIMITER $$ -- 更改结束符
CREATE FUNCTION count_dept()
	RETURNS INT
	COMMENT '统计部门数量'
	BEGIN
		RETURN (SELECT COUNT(*) FROM tb_dept);
	END $$
DELIMITER ; -- 还原结束符

DROP FUNCTION count_dept; -- 删除存储函数

SHOW FUNCTION STATUS LIKE '%count_dept%';  -- 查看存储函数定义

SELECT count_dept();  -- 调用存储函数

执行结果(SELECT count_dept();  -- 调用存储函数)：

变量的使用(关键字：DECLARE)

变量可以在子程序中声明并使用,这些变量的作用域范围是在BEGIN...END程序中,不能单独在存储过程外部声明变量。

1、定义变量

基本语法：

DECLARE var_name[,var_name1]... date_type [DEFAULT value];

var_name是局部变量的名称，可以同时声明多个变量，但是类型只能声明一次，就是说声明多个变量只能是同类型；date_type是变量类型，可以是mysql中任意的数据类型(VARCHAR需要指明长度，例如VARCHAR(255))；DEFAULT 为变量设置默认值，如果是多参数，不能使用，默认值可以被声明为常量，也可以指定一个表达式。如果没有指定默认值，则为null。

简单的示例：

DECLARE param1 INT DEFAULT 10; -- 声明单参数

DECLARE param1, param2, param3 INT; -- 声明多参数

2、为变量赋值

定义变量后，可以为变量赋值以改变其值。

通过SET...为变量赋值，基本语法：

SET var_name = expr[,var_name=wxpr]....; -- 可以同时为单个或多个变量赋值,expr可以是具体的值，也可以是表达式。

--简单的示例

DECLARE param1, param2, param3 INT; -- 声明3个变量
SET param1=10, param2=20; -- 为param1和param2赋具体的值
SET param3 = param1 + param2; -- 将param1和param2的运算结果赋值给param3

通过SELECT...INTO...为一个或多个变量赋值，基本语法如下：

SELECT col_name[,...] INTO var_name[,...] table_expr;

这个SELECT语法把选定的列直接存储到对应位置的变量(所以要求col_name和var_name一一对应)。col_name表示字段名称，var_name表示定义的变量名称；table_expr表示查询条件表达式，包括名称和WHERE子句。

--简单的示例：

SELECT dept_name, dept_addr INTO v_dept_name, v_dept_addr FROM tb_dept WHERE dept_id = 1;

事务的使用

事务并不会影响存储过程或存储函数的执行顺序，也不会中断执行(存储过程或存储函数始终会执行到END)。

START TRANSACTION; -- 开始事务

COMMIT; -- 提交事务

ROLLBACK; -- 回滚事务

流程控制的使用

流程控制语句用来根据条件，控制语句的执行。mysql中用来构造控制流程的语句有：IF、CASE、LOOP、LEAVE、ITERATE、REPEAT、WHILE。

IF语句（说明：mysql中还有一个IF()函数，注意区别）：

注意：IF语句必须配合THEN、END IF使用。除了IF还有ELSEIF(没有分开)和ELSE。

基本语法：

IF (expr_condition) THEN
	....do something.....
ELSEIF (expr_condition) THEN
	....do something.....
ELSE
	....do something.....
END IF;

说明：如果表达式expr_condition计算结果为true,则执行THEN后面的逻辑，如果都不匹配则执行ELSE。条件判断表达式建议用括号包起来，增加可读性。

CASE语句

CASE语句也是一个条件判断语句。需要配合WHEN、THEN和END CASE使用。

CASE语句有两种语法格式：

CASE
	WHEN expr_condition THEN ...do something...
	WHEN expr_condition THEN ...do something...
	....多个 WHEN THEN 语句.....
        [ELSE ...do something...]
END CASE;

其中expr_condition为条件表达式,计算结果为true,则执行THEN后面的语句。多个WHEN...THEN依次执行，ELSE为可选条件。

另外一种语法格式：

CASE case_expr
	WHEN expr THEN .....do something....
	WHEN expr THEN .....do something...
	....多个表达式...
	[ELSE ...dosomething..]
END CASE;

其中，case_expr表示条件判断的表达式,WHEN后的表达式结果如果和case_expr匹配,则执行相应的THEN后面的语句。没有则执行ELSE，ELSE为可选。这种语法类似switch...case(建议使用第一种,逻辑清晰一点)。

LOOP、REPEAT、WHILE、LEAVE和ITERATE

其中LOOP、REPEAT和WHILE都是创建循环语句的关键词。

LEAVE用来退出任何被标注的循环语句(类似break，只能用于循环语句)。

ITERATE语句将执行顺序转到语句段开头处(类似continue，只能用于循环语句)。

LOOP基本语法（需要配合 END LOOP）：

[loop_label]:LOOP
	...do something....
END LOOP [loop_label];

其中，loop_label为可选,表示LOOP体的标签,do something为循环体。

LOOP简单示例(配合LEAVE和ITERATE)：

DECLARE id INT DEFAULT 0;
add_loop:LOOP
	SET id = id + 1;
	IF(id>10) THEN
		LEAVE add_loop;
	ELSE
		ITERATE add_loop;
	END IF;
END LOOP add_loop;

REPEAT基本语法(需要配合 UNTIL和END REPEAT)：

[repeat_label：] REPEAT
	....do something....
UNTIL expr_condition;
END REPEAT [repeat_label];

其中,repeat_label为可选，表示REPEAT循环语句的标签,UNTIL指定循环条件。REPEAT执行过程是，每次循环体执行完毕需要去判断一下条件表达式expr_condition，如果为true继续执行，否则结束循环。

WHILE基本语法（需要配合DO和END WHILE使用）：

[while_lebel:] WHILE expr_condition DO
	.....do something....
END WHILE [while_label];

其中，while_label为可选，表示WHILE循环语句的标签。WHILE的执行过程是，先判断条件表达式expr_condition，如果为true执行循环，否则结束循环(与REPEAT的区别是：WHILE先判断条件，REPEAT是后判断条件)。

调用存储过程

基本语法：

CALL sp_name([params,...]);

说明：如果存储过程没有参数，不能省略括号；参数个数要与存储过程定义的入参和出参个数匹配，用逗号隔开；出参类型的参数需要加上@符号。

调用存储函数

基本语法：

SELECT func_name([params...]);

说明：如果存储函数没有参数，不能省略括号；因为存储函数都是入参，所以只需注意匹配个数即可。

查看存储过程和存储函数

基本语法：

SHOW [PROCEDURE | FUNCTION] STATUS [LIKE 'pattern'];

说明：查看存储过程即PROCEDURE、存储函数即FUNCTION。LIKE为过滤条件(按存储过程、函数名字匹配)，如果没有则是查看所有定义。

SHOW [CREATE | PROCEDURE] FUNCTION sp_name;

此语句查看定义存储函数、存储过程的脚本。

删除存储过程、存储函数

基本语法：

DROP [PROCEDURE | FUNCTION] [IF EXISTS] sp_name;

说明：sp_name为存储过程或存储函数的全称。[IF EXISTS]是mysql的一个扩展，建议使用。如果存储过程或函数不存在，它可以防止错误的产生，但是会产生一个用SHOW WARNINGS查看的警告。

修改存储过程、存储函数

注意：只能修改存储过程或函数的定义，不能修改执行的逻辑代码或参数。

基本语法：

ALTER [PROCEDURE | FUNCTION] sp_name [characteristic.....];

其中，sp_name为存储过程或函数的名称，characteristic指定存储过程的特性。可以修改的特性和定义存储过程的时候的可选特性是一样的，不重复累赘。

总结

1、存储过程和存储函数的区别(除了关键字：PROCEDURE、FUNCTION)

本质上都是存储程序。

参数类型不同：存储函数不允许声明出参类型，只能通过return关键字返回；

调用方式不同：存储过程用CALL，存储函数用SELECT；

存储函数限制比较多，多以建议使用存储过程，慎用存储函数。

2、修改存储过程、存储函数

存储过程或存储函数中的代码是不提供修改的，只能通过drop删除后，重新编写，或者直接编写一个新的程序。只能修改存储过程或存储函数的特性。

3、存储过程和存储函数可以相互调用

存储过程和存储函数包含自定义的SQL语句集合，所以，可以使用CALL或SELECT调用其它存储过程和存储函数。但是不能使用DROP删除其它存储过程和存储函数。

4、注意区别参数名字和表中的字段名

在定义存储过程和存储函数的时候，参数名称一定要与表中的字段名区别开来，否则可能出现无法预期的结果。