第二课 存储过程

• 存储过程和函数：一组预先编译好的SQL语句的集合，理解成批处理语句，类似于Java中的方法
• 好处
  • 提高代码的重用性
  • 简化操作
  • 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

1 存储过程的创建和调用

1.1 语法及注意事项

• 语法

# 创建存储过程
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体(一组合法的SQL语句)
END

# 调用存储过程
CALL 存储过程名（实参列表）;

• 注意
  • 参数列表包含三个部分：参数模式，参数名，参数类型（IN stuname VACHAR(20)）
  • 参数模式：
    • IN：该参数可以作为输入，也就是该参数需要调用传入值
    • OUT：该参数可以作为输出，也就是该参数可以作为返回值
    • INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
  • 如果存储过程体中仅仅只有一句话，BEGIN和END可以省略
  • 存储过程中的每条SQL语句的结尾要求必须加分号
  • 存储过程为结尾可以使用 DELIMITER 任意符号 进行结束表示

1.2 三种参数模式的使用

• 空参列表

# 创建存储过程
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin VALUES
	(NULL,'A','AA'),
	(NULL,'B','BB'),
	(NULL,'C','CC'),
	(NULL,'D','DD'),
	(NULL,'E','EE'),
	(NULL,'F','FF');
END $

# 调用存储过程
CALL myp1();

• 带IN模式的参数的存储过程

# 创建存储过程（IN模式）
# 验证登陆
CREATE PROCEDURE myp2 (IN username VARCHAR (20), IN `password` VARCHAR (20))
BEGIN
	DECLARE result INT DEFAULT 0; 
	SELECT COUNT(*) INTO result FROM admin WHERE admin. PASSWORD = PASSWORD	AND admin.username = username; # 查询结果，并将结果保存在result变量中
  SELECT IF (result > 0, '成功', '失败') AS 结果; # 对结果进行分析
END

CALL myp2 ('A', 'BB'); # 调用函数

• 带OUT模式的参数的存储过程

# 3 创建存储过程（OUT模式，多个返回值）
# 模拟返回密码和ID
CREATE PROCEDURE myp3 (IN username VARCHAR(20),OUT `password` VARCHAR(20),OUT id INT)
BEGIN
	SELECT admin.`password`,admin.id INTO `password`,id FROM admin WHERE admin.username = username;
END

# 调用存储过程
CALL myp3('A',@password,@id);
# 查看返回值
SELECT @id,@password;

• 带INOUT模式的参数的存储过程

# 4 创建存储过程（INOUT模式，多个返回值）
# 模拟验证密码后返回id和用户名
CREATE PROCEDURE myp4 (INOUT username VARCHAR(20),IN `password` VARCHAR(20),OUT id INT)
BEGIN
	SELECT admin.id,admin.username INTO id,username FROM admin WHERE admin.username = username AND admin.`password` = `password`;
END

# 设置传入值，同时设置变量用于接受返回值
SET @username = 'A';
# 调用存储过程
CALL myp4(@username,'AA',@id);
SELECT @id,@username;

2 存储过程的删除和查看

2.1 删除

• 每条语句仅支持一个存储过程的删除操作

DROP PROCEDURE 存储过程名;

2.2 查看

SHOW CREATE PROCEDURE 存储过程名;

3 模拟应用

# 分页查询departments表中的数据
CREATE PROCEDURE myp5 (IN startIndex INT,IN size INT)
BEGIN
	SELECT * FROM departments LIMIT startIndex,size;
END;

CALL myp5(3,5);