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

实验报告

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

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

实验时间：2021 年 6 月 23 日

实验目的

  1. 了解触发器的概念
  2. 掌握创建触发器的方法
  3. 掌握查看、删除触发器信息的方法
  4. 了解存储过程的概念
  5. 掌握创建、执行存储过程的方法
  6. 了解查看、修改和删除存储过程的方法

实验环境

MySQL、SQLyog

实验内容及过程

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

1.触发器

  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;
  1. 定义一个触发器，当一个学生的选课记录被删除时，把该学生学号、课程号、成绩添加到 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;
  1. 限制数据结构课程最多 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. 查看 (1) 触发器的创建信息
SHOW CREATE TRIGGER trig_insert_student;
  1. 删除 (2) 所创建的触发器
DROP TRIGGER IF EXISTS trig_del_student;
  1. （选做题）定义一个触发器，当插入一条新生记录时，关系 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.存储过程

  1. 创建一个存储过程，完成的功能是在表 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();
  1. 创建一个带有参数的存储过程，该存储过程根据传入的学生编号，在 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');
  1. 创建存储过程，根据指定的课程名（输入参数）返回该课程的最高分、最低分、平均分（输出参数）。要求在创建存储过程前要先判断该存储过程是否已存在，如果存在，则将其删除，需调用该存储过程验证结果。
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;
  1. 使用 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. 修改 (1) 中的存储过程定义，将读写权限改为 MODIFIES SQL DATA，并指明调用者可以执行，添加注释信息。
ALTER  PROCEDURE  get_basic_info
MODIFIES SQL DATA  
SQL SECURITY INVOKER;
  1. 删除 (1) 中的存储过程
DROP PROCEDURE IF EXISTS get_basic_info;
  1. （选做题）统计离散数学的成绩分部情况，即按照分数段统计人数。
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 ;
  1. （选做题）统计任意一门课的平均成绩
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 ;
  1. （选做题）将学生选课成绩从百分制改为等级制（即 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 <=100;
    UPDATE SC SET Lev='B' WHERE Grade>=80 AND Grade<90;
    UPDATE SC SET Lev='C' WHERE Grade>=70 AND Grade<80;
    UPDATE SC SET Lev='D' WHERE Grade>=60 AND Grade<70;
    UPDATE SC SET Lev='E' WHERE Grade<60;
END //
DELIMITER ;

EXEC SClev;
SELECT * FROM SC;

实验心得

　　通过本次实验，我了解触发器的概念，掌握创建触发器的方法，掌握查看、删除触发器信息的方法，了解存储过程的概念，掌握创建、执行存储过程的方法，同时了解查看、修改和删除存储过程的方法，收获颇丰。

posted @ 2021-11-18 22:07  海边星  阅读(5151)  评论(0)  编辑  收藏  举报