MySQL(5)-存储过程

MySQL的存储过程

SQL命令->MySQL引擎->（分析）语法正确->转换成MySQL可识别命令->（执行）执行结果->（返回）客户端

存储过程的概念：

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

优点：

• 增强*SQL语句的功能和灵活性*
• 实现较快执行速度（第一次MySQL引擎会对SQL进行语法分析及编译，将编译结果存储在内存中，以后再次调用次存储过程时直接从内存中执行）
• 减少网络流量（客户端单独发送SQL语句让服务器执行，通过HTTP协议所提交的数据量相对较大，因为我们每次都要输入长度较长的SQL语句，但如果我们用存储过程传参调用，那么会节省很多时间，减少网络流量）

创建和调用存储过程

创建

CREATE [DEFINER = [用户名]] PROCEDURE [存储过程的名字]（参数1，参数2，..） [特征] [过程体]；
//DEFINER这条语句不写就默认当前用户

//参数可以为0到多个，参数前有IN|OUT|INOUT三个关键字：
    1.IN表示该参数的值必须在调用存储过程时指定
    2.OUT表示该参数的值可以被存储过程改变,并且可以返回
    3.INOUT表示该参数的调用时指定,并且可以被改变和返回

//特征:
CONTAINS SQL:包含SQL语句,但不包含读或写数据的语句
NO SQL:不包含SQL语句
READS SQL DATA:包含读数据的语句
MODIFIES SQL DATA:包含写数据的语句
SQL SECURITY {DEFINER | INVOKER}指明谁有权限来执行

//过程体
过程体由合法的SQL语句构成
过程体可以是任意的SQL语句(实际上我们不可能通过存储过程创建数据库和数据表,主要指对于记录的增删改查及多表连接)
过程体如果为复合结构则使用BEGIN...END语句
复合结构可以包含声明,循环,控制结构;(IF语句 WHEN语句 WHILE循环等等)

调用

CALL PROCEDURE [存储过程的名字]（参数1，参数2，...）;//如果该存储过程无参数,则调用时可以不带小括号,否则必须带

查看当前有哪些存储引擎

SHOW PROCEDURE STATUS；

删除存储过程

DROP PROCEDURE IF EXISTS [存储过程的名字];

修改存储过程

ALTER PROCEDURE [存储过程的名字] [特征]; //修改存储过程仅能修改特征 无法修改过程体,只能删除存储过程重新创建

含参存储过程

例1:
DELIMITER //
CREATE PROCEDURE deleteusername(IN p_id SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
END
//
DELIMITER ;
上述语句设计的是将users表中的id删除的存储过程
调用:
CALL deleteusername(15); //删除表中id为15的用户信息

例2:
DELIMITER //
CREATE PROCEDURE removeuserandreturnusernums(IN p_id INT UNSIGNED, OUT usernums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO usernums;
END
//
DELIMITER ;
调用:
CALL removeuserandreturnusernnums(27, @nums); //@[变量名] 创建一个变量
SELECT @nums;//查看变量中存放的值

创建用户变量

SET @i = 8; //用户变量和MySQL的客户端绑定的

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

存储过程实现的功能要复杂一些,而函数的针对性更强一些,实际应用中很少用函数对表进行操作,但存储过程经常对表做操作
存储过程可以返回多个值,函数只能有一个返回值
存储过程一般独立来执行,而函数可以作为其他SQL语句的组成部分来出现