-- 存储过程
DELIMITER $$
CREATE PROCEDURE 存储过程的名字(IN 参数1 参数1类型，OUT 参数2 参数2类型,INOUT 参数3 类型)
过程选项
BEGIN
过程内容
END
$$
DELIMITER ;
-- 计算某个学生选了多少课程
DELIMITER $$
CREATE PROCEDURE get_choose_number_proc(IN tmp_student_no VARCHAR(10),OUT tmp_choose_count INT)
READS SQL DATA
BEGIN
SELECT COUNT(*) INTO tmp_choose_count FROM choose WHERE student_no = tmp_student_no;
END
$$
DELIMITER ;
SET @student_no = '2012002';
SET @choose_count = 0;
CALL get_choose_number_proc(@student_no,@choose_count);
SELECT @choose_count;
DELIMITER $$
CREATE PROCEDURE get_choose_number2_proc(INOUT number INT)
READS SQL DATA
BEGIN
SELECT COUNT(*) INTO number FROM choose WHERE student_no = number;
END
$$
DELIMITER ;
SET @number = '2012002';
CALL get_choose_number2_proc(@number);
SELECT @number;
SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE get_choose_number2_proc;
-- 函数和存储过程
-- 相同点
-- 1. 创建好了之后，都是存储在数据库上的对象，只需提供名称就可以调用
--SELECT get_teacher_name_fn();
--CALL get_teacher_name_proc();
-- 2. 重复调用
-- 3. 增强安全性 可以根据用户权限访问
-- 不同点
-- 1. 函数只有一个返回值，至少一个
-- 存储过程可以多个
-- 2. 函数体内可以使用select .. into 为某变量赋值，不能返回结果集
-- 存储过程可以返回结果集
-- 3. 函数一般嵌入在sql语句中 ，存储过程一般单独调用call
-- 4. 函数体内 事务语句有限制，存储过程没有。
-- 作业:编写存储过程
-- 1. 查询学生的成绩
-- 传入学生学号、课程编号 返回成绩
DELIMITER $$
CREATE PROCEDURE request_student_record(IN stu_no VARCHAR(20),IN c_no VARCHAR(20),OUT re INT)
READS SQL DATA
BEGIN
SELECT score INTO re FROM choose WHERE student_no = stu_no AND course_no = c_no;
END
$$
DELIMITER ;
SET @record = 0;
CALL request_student_record('2012001','1',@record);
SELECT @record;
-- 2. 查询 根据教师编号，返回所教授课程
DROP PROCEDURE request_record_name_proc;
DELIMITER $$
CREATE PROCEDURE request_record_name_proc(IN t_no VARCHAR(30),OUT tmp_name VARCHAR(30))
READS SQL DATA
BEGIN
SELECT course_name INTO tmp_name FROM course WHERE t_no = teacher_no;
END
$$
DELIMITER ;
SET @c_name = '';
CALL request_record_name_proc('001',@c_name);
SELECT @c_name;
-- 3. 选课的存储过程
-- 传入 学号、课程号 传出 状态
-- 状态
-- 0 插入成功
-- -1 选课失败,已选择
-- -2 选课失败,课程已达到人数上限
-- -3 选课失败,课程未审核
-- 插入时要判断的条件:
-- 未选择过该课程，否则返回 -1
-- 课程是审核过的,否则返回 -3
-- 课程人数未满, 否则返回 -2
-- 用以下语句测试
SET @state = 0;
CALL choose_proc('2014010',1,@state);
SELECT @state,@state2;
-- 在存储过程中，如果sql语句运行出错，比如:外键约束这种错误报错
-- 后面的语句就不会运行了
UPDATE course SET available = 0 WHERE course_no = 2; -- 先把2课程可用人数设置为0
SET @state = 0;
CALL choose_proc('2013003',2,@state); -- -2 人数已满
SELECT @state,@state2;
SET @state = 0;
CALL choose_proc('2013003',3,@state); -- -1 已选择过
SELECT @state;
SET @state = 0;
CALL choose_proc('2013003',4,@state); -- -3 未审核
SELECT @state;
SELECT * FROM choose WHERE student_no = '2013003';
DROP PROCEDURE choose_proc;
DELIMITER $$
CREATE PROCEDURE choose_proc(IN stu_no VARCHAR(20),IN c_no VARCHAR(20),OUT state INT)
MODIFIES SQL DATA
BEGIN
DECLARE s1 INT;
DECLARE s2 VARCHAR(6);
DECLARE s3 INT;
-- 1. 查询有没有选择过
SELECT COUNT(*) INTO s1 FROM choose WHERE student_no = stu_no AND course_no = c_no;
IF(s1 >= 1) THEN
SET state = -1;
ELSE
-- 2. 查询课程是不是已审核的
SELECT STATUS INTO s2 FROM course WHERE course_no = c_no;
IF (s2 = '已审核') THEN
-- 3. 查询课程是不是人数未满的
SELECT available INTO s3 FROM course WHERE course_no = c_no;
IF(s3 >0 ) THEN
SET state = 0;
SET @state2 = '这里有没有运行?';
-- 插入
INSERT INTO choose VALUES(NULL,stu_no,c_no,NULL,NOW());
--
ELSE
SET state = -2;
END IF;
ELSE
SET state = -3;
END IF;
END IF;
END
$$
DELIMITER ;
SHOW CREATE TABLE choose;
