华东师范大学软件工程学院实践报告
| 实验课程：数据库系统实践 | 姓名：刘佳奇 | 学号：10225101455 |
|---|---|---|
| 实验名称：Lab2 函数与存储过程 | 实验日期：2024/4/18 | 指导老师：姚俊杰 |
实验目的
熟悉关于navicat函数，触发器，递归查询，高级聚集函数的原理与具体使用方法。
实验环境
- Navicat Premium 15
实验过程与分析
Q1. 创建一个名为dept_count的用户自定义函数,统计指定院系的教师人数
COUNT(*)是一个聚合函数，用于计算选择的行数。
该函数接受一个部门名称d_name作为参数，然后返回该院系在instructor表中的人数。
CREATE DEFINER=`root`@`localhost` FUNCTION `dept_count`(d_name varchar(20)) RETURNS int
BEGIN
DECLARE d_count INTEGER;
SELECT COUNT(*) INTO d_count from instructor WHERE instructor.dept_name=d_name;
RETURN d_count;
ENDSELECT dept_name, budget
FROM department
WHERE dept_count(dept_name) > 4;
Q2. 创建一个名为instructor_of的用户自定义函数，返回指定院系教师的信息
CREATE DEFINER=`root`@`localhost` PROCEDURE `instructor_of`(d_name VARCHAR(20))
BEGIN
SELECT ID, name, dept_name, salary
FROM instructor
WHERE dept_name = d_name;
END调用示例：直接call一下
CALL instructor_of("Psychology");
Q3. 将自定义函数dept_count改写成一个名为dept_count_proc的存储过程
修改过程为RETURN -> OUT，注意去掉重复定义。
CREATE DEFINER=`root`@`localhost` PROCEDURE `dept_count_prop`(IN d_name varchar(20), OUT d_count INT)
BEGIN
SELECT COUNT(*) INTO d_count from instructor WHERE instructor.dept_name=d_name;
END
```
调用示例：CALL完还要SELECT需要的参数 运行结果：
### Q4. 创建一个名为register_student的用户自定义函数，用于 为学生注册课程
首先考虑需求，匹配课程时不光是课程id，还要考虑年份、季节相匹配，据此确定定义变量。输出为课程是否可以选上，这里我加了一个输出current_enrollment帮助我们验证结果是否正确。root@localhost PROCEDURE
register_student( IN s_id VARCHAR(5), IN s_course_id
VARCHAR(8), IN s_sec_id VARCHAR(8), IN s_semester VARCHAR(6), IN s_year
NUMERIC(4,0), OUT msg VARCHAR(100), OUT current_enrollment INT) BEGIN --
DECLARE current_enrollment INTEGER; DECLARE limit_capacity INTEGER;
SELECT COUNT(*) INTO current_enrollment FROM takes
WHERE course_id = s_course_id AND sec_id = s_sec_id AND semester = s_semester AND year = s_year;
SELECT capacity INTO limit_capacity FROM classroom NATURAL JOIN section
WHERE course_id = s_course_id AND sec_id = s_sec_id AND semester = s_semester AND year = s_year;
IF (current_enrollment < limit_capacity) THEN
BEGIN
INSERT INTO takes VALUES(s_id, s_course_id, s_sec_id, s_semester, s_year, NULL);
SET msg = 'Successful!';
END;
ELSE
SET msg = CONCAT('Enrollment limit reached for the course ', s_course_id, ' section ', s_sec_id);
END IF;END
调用示例： SELECT @msg,@current_enrollment;
运行结果：
通过查表得知Colin(id为1018)想选春季的Marine Mammals课程，该课在Gates Building 314教室，该教室可容纳10人，但有300人选了这门课，显示Enrollment limit reached。
### Q5. 编写一个名为test的存储过程，完成下列任务：
输入参数：course_id,为该课程在新学期开设一个新的section；该课程所属院系的学生如果之前没有修过该课程，则为其注册该课程；为相关属性选择合理的值。
分析需求，首先获取course_id与sec_id，输出sec_id + 1，semester,building,room_number,time_slot_id与之前一样，并插入到section表中对应的位置。随后根据输入的s_id与student中id匹配，将符合院系且没有选过这门课的ID提取出来，grade先空着，对应插入。添加课程时教室、楼等均采用上一次的。
同时获取课程的title与学生ID的集合输出msg1与msg2，便于我们进行查阅 root@localhost
PROCEDURE test_add( IN p_course_id VARCHAR(8), OUT msg1
VARCHAR(100), OUT msg2 VARCHAR(1000)) BEGIN DECLARE v_sec_id INT;
DECLARE v_semester VARCHAR(6); DECLARE v_year INT; DECLARE v_building
VARCHAR(20); DECLARE v_room_number INT; DECLARE v_time_slot_id
VARCHAR(1); DECLARE v_title VARCHAR(50); DECLARE s_id VARCHAR(5);
DECLARE v_selected_students VARCHAR(1000); -- 存储已选课学生的 ID
-- 获取当前课程的最大 sec_id
SELECT MAX(sec_id) INTO v_sec_id FROM section WHERE course_id = p_course_id;
-- 如果没有该课程的 section，设置初始 sec_id 为 1，否则在最大 sec_id 上加 1
IF v_sec_id IS NULL THEN
SET v_sec_id = 1;
ELSE
SET v_sec_id = v_sec_id + 1;
END IF;
-- 获取课程的 semester 和 time_slot_id
SELECT semester, building, room_number, time_slot_id INTO v_semester, v_building, v_room_number, v_time_slot_id FROM section WHERE course_id = p_course_id LIMIT 1;
SET v_year = YEAR(CURDATE());
-- 插入新的 section 记录
INSERT INTO section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
VALUES (p_course_id, v_sec_id, v_semester, v_year, v_building, v_room_number, v_time_slot_id);
-- 查询已选课学生的 ID，并将其存储到 takes 表中
SELECT GROUP_CONCAT(ID) INTO v_selected_students
FROM student
WHERE dept_name = (SELECT dept_name FROM course WHERE course_id = p_course_id)
AND ID NOT IN (SELECT ID FROM takes WHERE course_id = p_course_id);
-- 为课程所属院系的学生注册该课程
INSERT INTO takes (ID, course_id, sec_id, semester, year, grade)
SELECT ID, p_course_id, v_sec_id, v_semester, YEAR(CURDATE()), NULL
FROM student
WHERE dept_name = (SELECT dept_name FROM course WHERE course_id = p_course_id)
AND ID NOT IN (SELECT ID FROM takes WHERE course_id = p_course_id);
SELECT title INTO v_title FROM course WHERE course_id = p_course_id;
SET msg1 = CONCAT('New course ', v_title, ' is inserted');
SET msg2 = CONCAT('Registration completed for eligible students. Students already enrolled: ', v_selected_students);
END
调用示例：运行结果：
这里我们选取的课程是The Music of the Ramones，为Physics学院的课程，学生我们选取ID为10727的Allard同学观察，他并未选过这门课可以看到输出结果有10727，再去takes中查找一下。
可以看到这门课已经被添加到Allard同学的选课中。
### Q6. 为触发器创建两个名为tri_insert_section_func 的函数，功能为：
在section表中添加课程前，先检测新插入的time_slot_id在time_slot表中是否存在，如若不存在则函数报错，插入失败。
tri_insert_section_func触发器函数： root@localhost PROCEDURE
tri_insert_section_func( IN p_time_slot_id VARCHAR(8), IN
p_course_id VARCHAR(8), IN p_sec_id VARCHAR(10), IN p_semester
VARCHAR(7), IN p_year VARCHAR(4)) BEGIN DECLARE time_slot_count INT;
-- 检查 time_slot_id 是否存在于 time_slot 表中
SELECT COUNT(*) INTO time_slot_count FROM time_slot WHERE time_slot_id = p_time_slot_id;
IF time_slot_count = 0 THEN
-- 如果不存在，则返回错误信息给用户
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'time_slot表中不存在指定的time_slot_id，INSERT操作失败！';
END IF;END
调用示例： 运行结果：
查看表可知，调用前面的语句时没有插入；调用后面的语句时，在time_slot表中插入新的数据，然后插入成功。
### Q7. 在更新课程的时候同时更新student表中的学分
cred_update函数： root@localhost PROCEDURE
cred_update(IN p_course_id VARCHAR(8), IN p_ID VARCHAR(5))
BEGIN DECLARE v_cred INT; SELECT credits INTO v_cred FROM course WHERE
course_id = p_course_id; UPDATE student SET tot_cred = tot_cred + v_cred
WHERE ID = p_ID; END 其中部署时会遇到死锁的问题，具体见问题辨析2。
## 实验结果总结
### 结果归纳
1. Q1与Q2对比，FUNCTION有RETURN值，返回某个数目；PROCEDURE是一个过程，无返回值。
2. Q3,Q4,Q5总结INSERT的几种插入，可以是VALUES定义值，也可以是SELECT直接将值输入。另外，如果几个VALUES中只有一个需要SELECT，其他都是现成的，可以像Q5的处理方法，同时Q5还展现了许多个ID可以直接对应一一插入。
### 问题辨析
1. Q3中忘记删除DECLARE导致变量被重复定义root@localhost PROCEDURE
dept_count_prop(IN d_name varchar(20), OUT d_count INT)
BEGIN DECLARE d_count INTEGER; SELECT COUNT(*) INTO d_count from
instructor WHERE instructor.dept_name=d_name; END 当PROCEDURE使用与输出参数同名的局部变量时，会出现变量作用域的优先级问题，MySQL会优先使用内部定义的局部变量，导致后续CALL的时候输出为NULL。
2. 在Q7中一直报错如下：
上网搜了一下说是无法在存储过程或触发器中调用更新同一张表的语句，因为这会导致死锁和递归调用的问题，但也不知道怎么改。人美心善的猪脚进行了一个巨大的帮助，INSERT INTO ... SELECT语句，会锁定原表student表。所以加一个临时表存储数据就可以了。将注册课程部分修改为： INSERT INTO takes (ID, course_id, sec_id, semester, year, grade) SELECT * FROM temp_table;
DROP TABLE IF EXISTS temp_table;``` 最后成功更新学分。
3.
在Q6中最开始写的是判断time_slot_id不在time_slot中的话则对应将插入的行删掉，但一直报和Q7相同的错误，推测是insert与delete在同一张表而递归调用死循环。因tri_insert_section_func为before插入型触发器，故改为该函数报出错误信息‘time_slot表中不存在指定的time_slot_id，INSERT操作失败！’，而后续插入就自然不会操作，达成同样效果。
