MySQL----存储过程（Stored Procedure）

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。是具有名字的一段代码，用来完成一个特定的功能。创建的存储过程保存在数据库的数据字典中

存储过程优缺点

优点

• 存储过程可封装，并隐藏复杂的商业逻辑
• 存储过程可以回传值，并可以接受参数
• 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同
• 存储过程可以用在数据检验，强制实行商业逻辑等

缺点

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程
• 存储过程的性能调校与撰写，受限于各种数据库系统

创建存储过程

create procedure 存储过程名(int/out/inout 参数名 参数类型,...)
begin
	存储过程体
end

#例子
DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

分隔符

MySQL默认以";“为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个”//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原

参数

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型：

• IN：参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
• OUT：该值可在存储过程内部被改变，并可返回
• INOUT：调用时指定，并且可被改变和返回

过程体

过程体的开始与结束使用BEGIN与END进行标识。

变量

#声明变量
DECLARE 变量名1[,变量名2...] 数据类型 [默认值];

变量类型

数值类型

日期和时间类型
字符串类型

变量赋值

SET 变量名 = 变量值 [,变量名= 变量值 ...]

用户变量

用户变量一般以@开头
**注意：**滥用用户变量会导致程序难以理解及管理

IN参数例子

DELIMITER //
  CREATE PROCEDURE in_param(IN p_in int)
    BEGIN
    SELECT p_in;
    SET p_in=2;
    SELECT p_in;
    END;
    //
DELIMITER ;

#调用
SET @p_in=1;
CALL in_param(@p_in);
SELECT @p_in;

执行结果：

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

OUT参数例子

#存储过程OUT参数
DELIMITER //
  CREATE PROCEDURE out_param(OUT p_out int)
    BEGIN
      SELECT p_out;
      SET p_out=2;
      SELECT p_out;
    END;
    //
DELIMITER ;

#调用
SET @p_out=1;
CALL out_param(@p_out);
SELECT @p_out;

执行结果：

INOUT参数例子

#存储过程INOUT参数
DELIMITER //
  CREATE PROCEDURE inout_param(INOUT p_inout int)
    BEGIN
      SELECT p_inout;
      SET p_inout=2;
      SELECT p_inout;
    END;
    //
DELIMITER ;
#调用
SET @p_inout=1;
CALL inout_param(@p_inout) ;
SELECT @p_inout;

执行结果：

存储过程的调用

用call和过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数

存储过程的查询

#查询存储过程
SELECT name FROM mysql.proc WHERE db='数据库名';
SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
SHOW PROCEDURE STATUS WHERE db='数据库名';

#查看存储过程详细信息
SHOW CREATE PROCEDURE 数据库.存储过程名;

存储过程的修改

ALTER PROCEDURE 更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能

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

• **sp_name：**参数表示存储过程或函数的名称；
• **characteristic：**参数指定存储函数的特性。
• **CONTAINS SQL：**表示子程序包含SQL语句，但不包含读或写数据的语句；
• **NO SQL：**表示子程序中不包含SQL语句；
• **READS SQL DATA：**表示子程序中包含读数据的语句；
• **MODIFIES SQL DATA：**表示子程序中包含写数据的语句。
• **SQL SECURITY { DEFINER | INVOKER }：**指明谁有权限来执行，DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行
• **COMMENT ‘string’：**是注释信息。

存储过程的删除

# 从MySQL的表格中删除一个或多个存储过程。
DROP PROCEDURE [过程1[,过程2…]]