MySQL存储过程入门使用

一、存储过程概述

存储过程的英文是 Stored Procedure。它的思想很简单，就是一组经过预先编译的 SQL 语句的封装。
执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起，应用程序无须关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可。存储过程与函数不同是没有返回值的。

• 存储过程优点：
  1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力
  2、减少操作过程中的失误，提高效率
  3、减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
  4、减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

二、创建存储过程

1、存储过程语法

• 语法：

DELIMITER $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
[characteristics ...]
BEGIN
	sql语句1;
	sql语句2;

END $

DELIMITER ;

2、语法分析

1、参数前面的符号的意思

• IN：当前参数为输入参数，也就是表示入参；

  存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。

• OUT：当前参数为输出参数，也就是表示出参；

  执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。

• INOUT：当前参数既可以为输入参数，也可以为输出参数。

2、形参类型可以是 MySQL数据库中的任意类型。

3、characteristics 表示创建存储过程时指定的对存储过程的约束条件，一般无需指定。

4、存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

5、需要设置新的结束标记
DELIMITER 新的结束标记,因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。

3、代码举例

DELIMITER $$

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
	BEGIN
		DECLARE i INT DEFAULT 1; -- 定义变量
		SELECT salary INTO empsalary FROM emps WHERE ename = empname; -- 查询并赋值
	END $$

DELIMITER ;

编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。
BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
SET：赋值语句，用于对变量进行赋值。
SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

三、调用存储过程

存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。

1、调用语法

• 基本语法

CALL 存储过程名(实参列表)

• 调用in类型参数

CALL sp1('值');

• 调用out类型参数

SET @name;
CALL sp1(@name);
SELECT @name;

四、存储过程查询、修改、删除

MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可直接从系统的information_schema数据库中查询。也可以直接通过navicat这种可视化工具查看。

1、查询

• 1）使用 show create方式
  语法：

SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

-- 例
show create PROCEDURE separatematerial_insert

• 2）使用 show status方式

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

-- 例
SHOW PROCEDURE STATUS LIKE 'separ%'

• 3）从information_schema.Routines表中查看存储过程和函数的信息
  MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

-- 例
SELECT * FROM information_schema.Routines 
WHERE ROUTINE_NAME='separatematerial_insert' AND ROUTINE_TYPE = 'PROCEDURE'

2、修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

3、修改

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

-- 例
DROP PROCEDURE IF EXISTS separatematerial_insert;

五、定义变量

在MySQL中，变量一般分为三种类型: 系统变量、用户定义变量、局部变量。

1、系统变量

系统变量是MySQL服务器系统自身提供的，分为全局变量（GLOBAL）、会话变量（SESSION）。
全局变量在MYSQL启动的时候由服务器自动将他的初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量

SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

SET @@[SESSION | GLOBAL]系统变量名 = 值 ; -- 设置指定变量的值

• mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置；

• 全局变量(GLOBAL): 全局变量针对于所有的会话；

• 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了；

2、用户定义变量

用户定义变量，是用户根据需要自己定义的变量，用户自定义变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以。其作用域为当前session的连接。

SET @变量名 = 值;

3、局部变量（常用）

根据需要定义的在局部生效的变量，访问前，需通过DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。

DECLARE 变量名 变量类型 [DEFAULT ... ] ;

-- 可以通过查询语句给变量赋值
select count(*) into my_count from account;

变量类型就是数据库支持的字段类型。

六、流程控制

1、条件判断

1.1、if

语法：

IF 条件1 THEN
	.....
ELSEIF 条件2 THEN -- 可选
	.....
ELSE -- 可选
	.....
END IF;

2.1、case

语法：

CASE case_value
	WHEN when_value1 THEN statement_list1

	[ WHEN when_value2 THEN statement_list2] ...

	[ ELSE statement_list ]

END CASE;

2、循环语句

循环语句在编程中经常被用到，常用于对批量的数据进行循环处理，在mysql的存储过程中，也提供了几种常用的循环语句，包括：while循环，repeat循环，和loop循环。

可以使用 leave 和 iterate 关键字来结束当前循环。
leave 标签 就是离开退出循环 而 iterate 标签 就是结束本次循环进行下一次

3.1 while循环

while循环时通过循环条件来判断是否进行循环，也可以通过leave 和 iterate 关键字来结束当前循环。

[标签:]while 循环条件 do
	循环体;
	if 条件表达式 then
		iterate 标签； -- 退出本次循环,进入下一次循环
	end if;
end while [标签];

3.2 loop循环

loop是一个简单的循环控制语句，通过leave 和 iterate 关键字来结束当前循环。

[标签:] LOOP
	循环体;
	if 条件表达式 then
		leave 标签； -- 退出循环
	end if;
END LOOP;

3.3 repeat循环

repeat是有条件的循环控制语句, 当满足 until 声明的条件的时候，则退出循环。

[标签:]repeat
	循环体;
	until 条件表达式  --退出循环条件
end repeat [标签];