MySQL基础（六）- 存储过程

存储过程

介绍

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

• 增强SQL语句的功能和灵活性
• 实现较快的执行速度（只需要第一次运行编译解析，后面就不用了）
• 减少网络流量（可以不用客户端多次发送SQL语句）

创建存储过程

模板：

CREATE [DEFINER = {user|CURRENT_USER}]
PROCEDURE sp_name([proc_paramter[,...]])
[characteristic ...]routine_body
proc_parameter:[IN|OUT|INOUT] param_name type

proc_paramter（参数）：

• IN: 表示该参数的值必须在调用存储过程时指定
• OUT: 表示该参数的值可以被存储过程改变，并且可以返回
• INOUT: 表示该参数的调用时指定，并且可以被改变和返回

characteristic（特性）:

• COMMIT: 注释
• CONTAINS SQL: 包含SQL语句，但不包含读或写数据的语句
• NO SQL:不包含读数据的语句
• READS SQL DATA: 包含读数据的语句
• MODIFIES SQL DATA: 包含写数据的语句
• SQL SECURITY{ DEFINER|INVOKER} 指明谁有权限来执行

过程体：

• 过程体有合法的SQL语句构成
• 过程体可以是任意的SQL语句（对于记录的操作，不对表结构进行操作）
• 过程体如果为复合结构则使用BGEIN…END语句
• 复合结构可以包含生命，循环，控制结构

演示：

# 数据准备
CREATE TABLE users(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(40) NOT NULL,
password VARCHAR(200) NOT NULL,
age SMALLINT UNSIGNED NOT NULL,
sex BOOLEAN NOT NULL);

insert users(username,password,age,sex) values('A',md5('A'),20,0);
insert users(username,password,age,sex) values('B',md5('B'),23,1);
insert users(username,password,age,sex) values('C',md5('C'),23,1);
insert users(username,password,age,sex) values('D',md5('D'),24,1);
insert users(username,password,age,sex) values('E',md5('E'),24,0);
insert users(username,password,age,sex) values('F',md5('F'),23,0);
insert users(username,password,age,sex) values('G',md5('G'),22,0);
insert users(username,password,age,sex) values('H',md5('H'),23,0);
insert users(username,password,age,sex) values('I',md5('I'),23,0);
insert users(username,password,age,sex) values('J',md5('J'),22,1);
insert users(username,password,age,sex) values('K',md5('K'),22,1);
insert users(username,password,age,sex) values('L',md5('L'),22,0);
insert users(username,password,age,sex) values('M',md5('M'),24,1);
insert users(username,password,age,sex) values('N',md5('N'),21,0);
insert users(username,password,age,sex) values('O',md5('O'),20,0);
insert users(username,password,age,sex) values('P',md5('P'),20,1);
insert users(username,password,age,sex) values('Q',md5('Q'),24,1);
insert users(username,password,age,sex) values('R',md5('R'),24,1);
SELECT * FROM users;

# 创建没有接受参数的存储过程
CREATE PROCEDURE sp1() SELECT VERSION();

CALL sp1;

# 创建带有IN类型参数的存储过程 参数最好不要与列名相同
CREATE PROCEDURE removeUserById(IN id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=id;
END

# 会删除所有的行
CALL removeUserById(18);

SELECT * FROM users;
# 删除这个存储过程
DROP PROCEDURE removeUserById;

# 创建带有IN类型参数的存储过程 
CREATE PROCEDURE removeUserById(IN users_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=users_id;
END

# 删除一行
CALL removeUserById(20);

# 创建带有IN和OUT类型的存储过程
CREATE PROCEDURE removeUserAndReturnUserNum(IN p_id INT UNSIGNED, OUT userNum INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=p_id;
SELECT COUNT(id) FROM users INTO userNum;
END

# 调用带out参数的方法
CALL removeUserAndReturnUserNum(24,@nums);
SELECT @nums;

# 设置用户变量
SET @i=7

# 获取操作表的时候影响的行数
SELECT ROW_COUNT();

CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,
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

CALL removeUserByAgeAndReturnInfos(24,@a,@b);

SELECT count(1) FROM users;
SELECT @a,@b;

调用存储过程

• CALL sp_name([parameter[,…]])
• CALL sp_name[()]

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name