存储过程
存储过程没有返回值,只有返回参数
1、最简单的存储过程
delimiter XX
CREATE PROCEDURE pro01()
BEGIN
SELECT * FROM student;
SELECT * FROM class;
SELECT * FROM course;
END XX
delimiter ;
CALL pro01();# 调用存储过程
2、存储过程的参数类型:IN、OUT、INOUT
# 需求:输入一个学生的id,查询该学生的信息
delimiter XX
CREATE PROCEDURE pro02(IN s_id INT(3))
BEGIN
SELECT * FROM student WHERE id = s_id;
END XX
delimiter ;
CALL pro02(10);
案例1:输入一个学生的id,返回该id对应的学生姓名
delimiter XX
CREATE PROCEDURE pro03(IN s_id INT(3),OUT s_name VARCHAR(32))
BEGIN
SELECT name INTO s_name FROM student WHERE id = s_id;
END XX
delimiter ;
CALL pro03(10,@s_name);
SELECT @s_name;
案例2:输入一个学生的id,返回该id对应的学生年龄
delimiter XX
CREATE PROCEDURE pro04(INOUT param INT(3))
BEGIN
SELECT age INTO param FROM student WHERE id = param;
END XX
delimiter ;
SET @param = 10;#设置全局变量
CALL pro04(@param);
SELECT @param;