《Mysql数据库基础之存储过程》

在平时的开发过程中，经常会对数据表进行插入、删除、更新、查找等操作。
Mysql命令执行流程：

如果我们可以将这个过程简化一下，省略语法分析和编译环节，那么Mysql 的执行效率就会提高，实现这个就需要用到存储过程。

存储过程

• 存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理
• 存储过程存储在数据库内，可以由应用程序调用执行，而且允许用户声明变量，以及进行流程控制
• 存储过程可以接收参数，可以接收输入和输出类型的参数，并且可以存在多个返回值

从存储引擎的介绍里边就可以看出来，存储引擎的执行效率要比一般到的SQL语句执行效率要高。比如我们要写两个SQL语句，mysql的引擎会对这两个SQL语句逐一进行语法分析、编译、执行。使用存储过程以后，只在第一次进行语法分析和编译，之后客户端再调用，直接就调用编译结果，也就是省略了其中的语法分析和编译两个环节，效率要高。

存储过程优点：

1. 增强了SQL语句的功能和灵活性。因为在存储过程内可以写控制语句，所以有很强的灵活性，可以完成复杂的判断以及较复杂的运算
2. 实现了较快的执行速度。如果某一个操作包含大量的SQL语句，那么这些语句都会被mysql的引擎进行语法分析及编译等操作，所以效率相对较低，而存储过程是预编译的，当客户端第一次调用某个存储过程的时候，mysql的引擎将对它进行语法分析和编译等操作，然后将编译结果存储到内存中，所以，第一次是和SQL相同的，但是以后，客户端再次调用该存储过程时，就直接从内存当中来执行，所以效率高、速度快。
3. 减少网络流量。如果通过客户端，每个单独发送SQL语句让服务器来执行的话， 那么通过http协议所提交的数据量相对较大，假设现在需要删除users表中id为3的记录，那么我们要执行的就是 delete from
   users where id=3
   这里边最少30个字符，如果我们将其存储为存储过程delete_user，我们只需要调用这存储过程，将id传过去就可以完成，所以这样提交给服务器的数据量相对较少

创建存储过程：

CREATE
[DEFINER = {user | CURRENT_USER}]  //值得是创建者，如果省略则默认是当前登录的用户
PROCEDURE sp_name([proc_parameter[,.....]])//sp_name是存储过程的名字，可以有0个或多个参数  参数的前边可以有三个选项，分别是IN OUT INOUT
[characteristic ...] routine_body

proc_parameter:
[IN | OUT | INOUT] param_name type

IN：表示该参数的值必须在调用存储过程时指定，在存储过程中这个值是不能被返回的
OUT：表示该参数的值可以被存储过程改变，并且可以返回
INOUT：表示该参数在调用时指定，并且可以被改变和返回

过程体：
1，由合法的SQL语句构成
2，可以是“任意”的SQL语句//不能通过存储过程来创建数据表和数据库
3，过程体如果为复合结构则用BEGIN.....END语句
4，符合语句内可以包含声明，循环，控制结构

创建一个没有参数的存储过程：(获取当前mysql版本功能)
create procedure sp1()
select version();

存储过程的调用：
CALL sp_name([parameter,[....]])
CALL sp_name[()]//表示没有参数时，可以不带小括号

带有IN类型参数的存储过程
DELIMITER //        修改定界符
CREATE PROCEDURE delUserById(IN id INT UNSIGNED)
BEGIN
delete from shulv_user where id = id;
END
//
DELIMITER ;

会发现全部被删除了，因为需要注意，参数的名和字段的名字不能写一样，因为数据库会把他们两个都当成字段

删除存储过程：

DROP PROCEDURE [IF EXISTS] sp_name

创建带有IN和OUT类型参数的存储过程

下边是通过存储过程来实现删除shulv_user表中id不固定的记录并返回剩余的记录数
DELIMITER //        修改定界符
CREATE PROCEDURE delUserAndReturn(IN p_id INT UNSIGNED, OUT userNumbers INT UNSIGNED)
BEGIN
delete from shulv_user where id = p_id;
select count(id) from shulv_user into userNumbers;
END
//
DELIMITER ;

执行该存储过程
call delUserAndReturn(4,@nums);   
@nums  表示接收返回值的变量
select @nums;

在mysql的函数中，我们会通过declare来声明变量，这个是局部变量，它的作用只在begin和and之间有效，而且declare语句必须位于第一行。通过这种加@符的变量，我们称为用户变量，用户变量是和mysql的客户端绑定的，它是对当前用户登录的mysql客户端生效

创建带有多个OUT类型参数的存储过程：

根据id删除用户，要返回剩余的用户数和删除的用户用户数
这里说一个函数：ROW_COUNT()得到被影响的记录数(比如插入、删除、更新的记录数)

DELIMITER //
create procedure del(IN p_id INT UNSIGNED, OUT del_num INT UNSIGNED, OUT count_num INT UNSIGNED)
BEGIN
DELETE FROM shulv_user where id = p_id;
SELECT ROW_COUNT() INTO del_num;
SELECT COUNT(id) from shulv_user INTO count_num;
END
//

DELIMITER ;

存储过程与自定义函数的区别：

• 存储过程实现的功能要复杂一些，而函数针对性较强
• 存储过程可以返回多个值；而函数只能有一个返回值
• 存储过程一般独立的来执行；而函数可以作为其它SQL语句的组成部分来出现