一、存储过程概述
存储过程的英文是
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;