1. 一般输入指令时的执行过程如下所示:
SQL指令-->MySQL引擎-->(分析)语法正确-->(编译)可识别命令-->(执行)执行结果-->(返回)客户端
2. 存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。效率高于简单的SQL语句,因为只在第一次进行检查和编译,后续直接查看编译结果即可,省略了中间的两个环节。
3. 存储过程的特点
(1) 增强SQL语句的功能和灵活性
(2) 实现较快的执行速度
(3) 减少网络流量
4. 创建存储过程
CREATE [DEFINER = {user | CURRENT_USER}] PROCEDURE sp_name ([parameter [,...]]) [characteristic ...] routine_body
(1) 其中过程参数pro_parameter:[IN | OUT |INOUT] parameter_name types
IN:表示该参数的值必须在调用存储过程时指定
OUT:表示该参数的值可以被存储过程改变,并且可以返回
INOUT:表示该参数在调用时指定,并且可以被改变和返回
(2) 特性:
COMMENT 'string' | {CONSTRAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER | INVOKER}
5. 过程体
(1) 过程体由合法的SQL语句构成
(2) 过程体可以是“任意”SQL语句,“任意”针对数据表的增、删、改、查及多表连接语句
(3) 过程体如果为复合结构,则使用BEGIN ... END语句
(4) 复合结构可以包含声明、循环、控制结构
6. 调用存储过程的方法有两种:
(1) CALL sp_name([parameter [,...]]),对应当存储过程带有参数
(2) CALL sp_name[()],对应创建没有参数的存储过程
7. 创建不带参数的存储过程
//该存储过程欲实现获取MySQL版本的信息
CREATE PROCEDURE sp1()
BEGIN
SELECT VERSION();
END
//调用存储过程
CALL sp1();//等价于CALL sp1;
8. 创建带有IN参数的存储过程
//切换结束标识
DELIMITER //
//创建存储过程,注意IN对应的参数名不能和数据表中的记录字段名相同
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
END
DELIMITER ;
//调用存储过程
CALL removeUserById(3);
9. 修改存储过程,注意不能修改过程体
ALTER PROCEDURE sp_name [characteristic ...]
10. 删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name;
11. 创建带有IN和OUT类型参数的存储过程
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 removeUserAndReturnUserNums(27,@nums);
SELECT @nums;
注意:
(1) 在BEGIN与END之间DECLARE声明的变量都是局部变量,而且声明语句必须位于第一行,作用范围也在BEGIN与END之间
(2) SEt @i = 7;其中i指的是用户变量,与MySQL客户端绑定,因此只对用户所使用的客户端有效。
12. 创建带有多个OUT类型参数的存储过程
ROW_COUNT();语句的作用是获取插入、删除、更新被影响到的记录总数
//创建存储过程,根据年龄删除用户,并返回删除用户数及剩余的用户数
DELIMITER //
CREATE PROCEDURE removeUserByIdAndReturnInfos(IN p_age SMALLINT UNSIGNED NOT NULL, OUT deleteUsers SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCounts;
END
DELIMITER ;
//访问该存储过程
CALL removeUserByIdAndReturnInfos(20,@a,@b);
SELECT @a,@b;
13. 存储过程与自定义函数的区别
(1) 存储过程的功能要复杂一些,自定义函数针对性更强
(2) 存储过程可返回多个值,自定义函数只能返回一个值
(3) 存储过程一般独立运行,自定义函数可做为其他SQL语句的组成部分来实现。