数据库原理及应用.实验6.存储过程、触发器的创建和使用

2024-07-17 07:46| 来源: 网络整理| 查看: 265

实验报告

课程名称：数据库原理及应用

实验项目名称：存储过程、触发器的创建和使用

实验时间：2021 年 6 月 23 日

实验目的了解触发器的概念掌握创建触发器的方法掌握查看、删除触发器信息的方法了解存储过程的概念掌握创建、执行存储过程的方法了解查看、修改和删除存储过程的方法实验环境

MySQL、SQLyog

实验内容及过程

在课本 P79 页的学生-课程数据库基础上，完成以下实验内容

1.触发器定义 BEFORE 行级触发器，为 Stduent 表定义完整性规则“学生的年龄的取值范围为 14~50 的整数”，若年龄的值不在 14~50 之间，则拒绝修改或插入，并抛出提示信息，以便于操作者查找问题。（需定义两个触发器，分别为 insert 事件和 update 事件类型），需要设计测试例子验证触发器是否工作。 DELIMITER // CREATE TRIGGER trig_insert_student BEFORE INSERT ON student FOR EACH ROW BEGIN DECLARE msg VARCHAR(200); IF new.sage NOT BETWEEN 14 AND 50 THEN SET msg = CONCAT('插入时出错，您输入的年龄：', new.sage, '为无效值，请输入 14 到 50 以内的有效值。'); SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg; END IF; END// DELIMITER ; INSERT Student(Sno, Sname, Ssex, Sage, Sdept) VALUES (201215129,'小明','男',13,'CS'); DROP TRIGGER IF EXISTS trig_update_student; DELIMITER // CREATE TRIGGER trig_update_student BEFORE UPDATE ON student FOR EACH ROW BEGIN DECLARE msg VARCHAR(200); IF new.sage NOT BETWEEN 14 AND 50 THEN SET msg = CONCAT('更新时出错，您输入的年龄：', new.sage, '为无效值，请输入 14 到 50 以内的有效值。'); SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg; END IF; END// DELIMITER ; UPDATE student SET Sage=13 WHERE Sno=201215121; 定义一个触发器，当一个学生的选课记录被删除时，把该学生学号、课程号、成绩添加到 deletesc 表中，需要设计测试例子验证触发器是否工作。 USE st; CREATE TABLE DELETESC (Sno CHAR(9), Cno CHAR(4), Grade SMALLINT ); DROP TRIGGER IF EXISTS trig_del_student; DELIMITER// CREATE TRIGGER trig_del_student AFTER DELETE ON SC FOR EACH ROW BEGIN INSERT INTO DELETESC VALUES(old.Sno,old.Cno,old.Grade); END// DELIMITER ; DELETE FROM SC WHERE Sc.Sno = '201215122'; SELECT * FROM DELETESC; 限制数据结构课程最多 5 名学生选修，需要设计测试例子验证触发器是否工作。 DELIMITER // CREATE TRIGGER trig_insert_sc BEFORE INSERT ON SC FOR EACH ROW BEGIN DECLARE num INT; DECLARE msg VARCHAR(200); DECLARE cname VARCHAR(200); SET cname = '数据结构'; SELECT COUNT(*) INTO num FROM course, sc WHERE course.cno = sc.cno AND course.cname = cname; IF num >= 5 THEN SET msg = CONCAT('当前',cname,'选修人数：', num,' 最大选修人数为 5' ); SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg; END IF; END// DELIMITER ; INSERT Student(Sno, Sname, Ssex, Sage, Sdept) VALUES (201215124,'小明','男',20,'CS'), (201215126,'小红','男',19,'IS'); INSERT INTO sc VALUES('201215121', 5, 90); INSERT INTO sc VALUES('201215122', 5, 90); INSERT INTO sc VALUES('201215123', 5, 90); INSERT INTO sc VALUES('201215124', 5, 90); INSERT INTO sc VALUES('201215125', 5, 90); 查看 (1) 触发器的创建信息 SHOW CREATE TRIGGER trig_insert_student; 删除 (2) 所创建的触发器 DROP TRIGGER IF EXISTS trig_del_student; （选做题）定义一个触发器，当插入一条新生记录时，关系 studentcount(dept CHAR(20), stucount SMALLINT)中对应系的学生总人数需跟着改变，若关系 studentcount 中对应系已存在，只需要更新总人数，若不存在，需插入系名及总人数。需要设计测试例子验证触发器是否工作。 USE st; CREATE TABLE StudentCount (dept CHAR(20), stucount SMALLINT ); DROP TRIGGER IF EXISTS trig_update_studentcount; DELIMITER // CREATE TRIGGER trig_update_studentcount AFTER INSERT ON Student FOR EACH ROW BEGIN DECLARE exist INT; SET exist = EXISTS (SELECT * FROM studentcount WHERE StudentCount.dept=new.Sdept); IF exist = 0 THEN INSERT INTO StudentCount SELECT Sdept,COUNT(Sno) FROM student WHERE student.Sdept=new.Sdept GROUP BY Sdept; ELSE UPDATE studentcount SET stucount=stucount+1 WHERE StudentCount.dept=new.Sdept; END IF; END// DELIMITER ; UPDATE studentcount SET stucount=stucount+1 WHERE StudentCount.dept='IS'; SELECT EXISTS (SELECT * FROM studentcount WHERE StudentCount.dept='IS') SELECT EXISTS (SELECT * FROM studentcount WHERE StudentCount.dept='de') INSERT Student(Sno, Sname, Ssex, Sage, Sdept) VALUES (201215127,'小张','男',20,'CS'); 2.存储过程创建一个存储过程，完成的功能是在表 student,course 和 sc 中查询以下字段：学号、姓名、课程名称、考试分数，需调用该存储过程验证结果。 USE st; DELIMITER // CREATE PROCEDURE get_basic_info() BEGIN SELECT Student.Sno,Student.Sname,Course.Cname,Sc.Grade FROM Student,Course,Sc WHERE Student.Sno=Sc.Sno AND Sc.Cno=Course.Cno; END // DELIMITER ; CALL get_basic_info(); 创建一个带有参数的存储过程，该存储过程根据传入的学生编号，在 student 表中查询此学生的信息，需调用该存储过程验证结果。 DELIMITER // CREATE PROCEDURE get_stu_by_sno(IN Sno CHAR(9)) BEGIN SELECT * FROM Student WHERE Student.Sno=Sno; END // DELIMITER ; CALL get_stu_by_sno('201215121'); 创建存储过程，根据指定的课程名（输入参数）返回该课程的最高分、最低分、平均分（输出参数）。要求在创建存储过程前要先判断该存储过程是否已存在，如果存在，则将其删除，需调用该存储过程验证结果。 DROP PROCEDURE IF EXISTS get_course_score; DELIMITER // CREATE PROCEDURE get_course_score(IN Cname CHAR(4), OUT max_grade SMALLINT, OUT min_grade SMALLINT, OUT avg_grade SMALLINT) BEGIN SELECT MAX(Grade),MIN(Grade),AVG(Grade) INTO max_grade,min_grade,avg_grade FROM SC,course WHERE Course.Cname=Cname AND Course.Cno=SC.Cno; END // DELIMITER ; SET @max_grade = 0,@min_grade=0,@avg_grade=0; CALL get_course_score('数据结构',@max_grade,@min_grade,@avg_grade); SELECT @max_grade,@min_grade,@avg_grade; 使用 SHOW CREATE 查看(1)中存储过程信息，SHOW STATUS 查看 (2) 中存储过程信息，从 information_schema.routine 表中查看 (3) 中存储过程信息。 SHOW CREATE PROCEDURE get_basic_info; SHOW PROCEDURE STATUS LIKE 'get_stu_by_sno'; USE information_schema; SELECT * FROM routines WHERE routine_name = 'get_course_score'; 修改 (1) 中的存储过程定义，将读写权限改为 MODIFIES SQL DATA，并指明调用者可以执行，添加注释信息。 ALTER PROCEDURE get_basic_info MODIFIES SQL DATA SQL SECURITY INVOKER; 删除 (1) 中的存储过程 DROP PROCEDURE IF EXISTS get_basic_info; （选做题）统计离散数学的成绩分部情况，即按照分数段统计人数。 DROP PROCEDURE IF EXISTS get_course_score_status; DELIMITER // CREATE PROCEDURE get_course_score_status(IN Cno CHAR(4)) BEGIN SELECT MAX(Grade),MIN(Grade),AVG(Grade) INTO max_grade,min_grade,avg_grade FROM SC WHERE SC.Cno=Cno; END // DELIMITER ; （选做题）统计任意一门课的平均成绩 DROP PROCEDURE IF EXISTS get_course_avg_score; DELIMITER // CREATE PROCEDURE get_course_avg_score(IN Cno CHAR(4)) BEGIN SELECT AVG(Grade) INTO max_grade,min_grade,avg_grade FROM SC WHERE SC.Cno=Cno; END // DELIMITER ; （选做题）将学生选课成绩从百分制改为等级制（即 A B C D E） ALTER TABLE SC ADD Lev CHAR(4); DELIMITER // CREATE PROCEDURE SClev() BEGIN UPDATE SC SET Lev='A' WHERE Grade>=90 AND Grade =80 AND Grade=70 AND Grade=60 AND Grade

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章