MYSQL——存储过程

• 存储过程简介
  SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
  ————
  存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式

• 存储过程的优点
  1.增强SQL语句的功能和灵活性，部署方便性
  2.实现较快的执行速度
  3.减少网络流量
• 存储过程的缺点
  1.面对复杂的业务逻辑时，存储过程的过程化的处理会很吃力，不应该用存储过程去处理复杂的业务逻辑。
  2.不便于调试
  3.没有移植性
  4.无法适应数据库的切割
• 创建存储过程
  CREATE
  [DEFINER={user|CURRENT_USER}] //定义时的用户，不写默认为当前用户
  PROCEDURE sp_name([proc_parameter[,…]]) //可以带0到多个参数、sp_name：存储过程的名字
  [characteristic …] routine_body
  ————
  其中参数：
  proc_parameter:
  [ IN | OUT | INOUT ] param_name type
  IN : 表示该参数的值必须在调用存储过程时指定，不能被返回。输入
  OUT : 表示该参数的值可以被存储过程改变，并且可以返回。输出
  INOUT : 表示该参数的值调用时指定，并且可以被改变和返回

• 存储过程的特性
  COMMENT ‘string’
  {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY{ DEFINER | INVOKER}
  COMMENT:注释
  CONTAINS SQL:包含SQL语句, 但不包含读或写数据的语句
  NO SQL:不包含SQL语句
  READS SQL DATA:包含读数据的语句
  MODIFIES SQL DATA:包含写数据的语句
  SQL SECURITY {DEFINER|INVOKER}指明谁有权限来执行

• 过程体 (基本和函数的过程体一样)
  1.过程体由合法的SQL语句构成；
  2.过程体可以是’任意’SQL语句;对表格进行增删，连接，但是不能创建数据表
  3.过程体如果为复合结构则使用BEGIN…END语句
  4.复合结构可以使用条件、循环等控制语句

• 存储过程的调用
  1.CALL sp_name([parameter [,…]])
  2.CALL sp_name[()]
  区别：
  1.如果我们的存储过程没有参数，那么调用的小括号带有没带有都可以。
  2.如果存储过程带有参数，那么小括号就一定要写

• 存储过程的修改(只能修改一些简单的特性，并不能修改过程体，如果想要修改过程体就要先将存储过程DROP然后重新创建)
  ALTER PROCEDURE sp_name[characteristic …]
  COMMENT ‘string’
  | { CONTAINS SQL |ＮＯ　SQL | READS SQL DATA | MODIFIES SQL DATA}
  |SQL SECURITY {DEFINER | INVOKER }

• 创建不带参数的存储过程
  CREATE PROCEDURE p1()
  SELECT NOW();

调用：这里存储过程没有参数，所以用CALL p1;和CALL p1();都可以。

• 创建带IN类型参数的存储过程
  比如删除表user中的一条数据。
  DELIMITER // 修改分隔符
  CREATE PROCEDURE removeUserById(IN user_id INT UNSIGEND)
  BEGIN
  DELETE FROM user WHERE id = user_id; //特别注意此处的id和user_id名字不能一样，一样的话数据库会认为是两个字段，那么全表数据都会被删除，一定要区分开。
  END
  //

调用：CALL removeUserById(4);

• 创建带有IN和OUT类型参数的存储过程
  删除表user中id不固定的数并且返回剩余数量
  CREATE PROCEDURE p3(IN user_id INT UNSIGNED,OUT userNums INT UNSIGNED)
  BEGIN
  DELETE FROM user WHERE id=user_id;
  SELECT COUNT(id) FROM user INTO userNums;
  END
  //
  调用： CALL p3(1,@nums);
  SELECT @nums；

此处用@nums来接收p3返回的变量剩余数userNums，
用DECLARE声明的变量叫局部变量，只能在BEGIN和END中间，而用@声明的叫用户变量。
用户变量：以‘@’开始，形式名为‘@变量名’
用户变量和MySQL客户端是绑定的，设置的变量，只对当前用户使用的客户端生效。如果以set 来给变量赋值给存储过程的out传递一个@变量，也相当于创建用户变量并传入，在存储过程中被赋值。
全局变量：定义时，以以下两种形式出现，set GLOBAL 变量名 或者 set @@global。变量名，对所有客户端生效。只有具有super权限的用户才可以设置全局变量。
会话变量：只对连接的客户端有效
局部变量：作用在BEGIN和END语句块之间，在该语句块之间专门用DECLARE定义局部变量，且必须位于BEGIN和END的第一行来定义。比如：DECLARE 变量名 变量类型 ； set语句是设置不同类型的变量，可以为各种变量赋值，为set 变量名 = 变量值 ； 包括会话变量和全局变量。

上面也可写成：

然后调用时就直接输出剩余数不用再select了。

• 创建带有多个OUT类型参数的存储过程
  删除表user中age不固定的数并且返回剩余用户数和删除的用户数
  ROW_COUNT():返回插入、删除或更新的，即被影响的行数

比如：

CREATE PROCEDURE p0(IN uage INT UNSIGNED,OUT deleteNums INT UNSIGNED,OUT userCounts INT UNSIGNED)
BEGIN
DELETE FROM user WHERE age=uage;
SELECT ROW_COUNT() INTO deleteNums;
SELECT COUNT(id) FROM user INTO userCounts;
END
//
调用：CALL p0(20,@a,@b);
SELECT @a,@b;

• 存储过程和自定义函数的区别
  1.存储过程实现的功能要复杂一些；而函数的针对性更强
  2.存储过程可以返回多个值；函数只能有一个返回值
  3.存储过程一般独立的来执行；而函数可以作为其他sql语句的组成部分来出现，就像内置函数一样。
• 总结
  1.存储过程：是sql语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。
  2.参数：输入类型（IN） 输出类型（OUT） 输入&&输出（INOUT）
  3.创建存储过程：CREATE….PROCEDURE 名称……
  4.注意事项：
  （1）创建存储过程或者自定义函数时需要通过DELIMITER 语句来修改定界符
  （2）如果函数体或过程体有多个语句，需要包含在BEGIN….END语句块中
  （3）存储过程通过CALL 来调用