MySQL笔记： 存储过程

-- 存储过程

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;