社区首页 >专栏 >数据库原理及应用mysql版陈业斌实验五

数据库原理及应用mysql版陈业斌实验五

猫咪-9527

发布于 2025-05-03 10:17:20

9400

代码可运行

文章被收录于专栏：猫咪-9527猫咪-9527

运行总次数：0

代码可运行

实验五存储程序

【实验目的】

掌握存储函数、存储过程与触发器等存储程序的相关操作方法，理解存储函数、存储过程与触发器的作用。

1.实验数据如下

student 表（学生表）

CREATE TABLE student (
    sno CHAR(5) PRIMARY KEY,
    snme VARCHAR(20) NOT NULL,        
    sdept VARCHAR(20) NOT NULL,       
    sclass CHAR(2) NOT NULL,          
    ssex CHAR(1),                     
    birthday DATE,                  
    totalcredit DECIMAL(4,1)          
);

course 表（课程表）

CREATE TABLE course (
    cno CHAR(3) PRIMARY KEY,
    cname VARCHAR(50),       
    ctime DECIMAL(3,0),          
    credit DECIMAL(3,1) 
);

teacher 表（教师表）

CREATE TABLE teacher (
    tno CHAR(6) PRIMARY KEY,          
    tname VARCHAR(20),               
    tsex CHAR(1),                    
    tdept VARCHAR(20)                 
);

score 表（成绩表）

CREATE TABLE score (
    sno CHAR(5),                      
    cno CHAR(3),                      
    tno CHAR(6),                     
    grade DECIMAL(5,1),              
    PRIMARY KEY (sno, cno, tno),      
    CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno),
    CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno),
    CONSTRAINT fk_tno FOREIGN KEY(tno) REFERENCES teacher(tno)
);

2. 插入数据

student 表中的数据

INSERT INTO student VALUES('96001', '马小燕', '计算机', '01', '女', '2000/01/02', 0);
INSERT INTO student VALUES('96002', '黎明', '计算机', '01', '男', '2000/03/05', 0);
INSERT INTO student VALUES('96003', '刘东明', '数学', '01', '男', '2000/10/05', 0);
INSERT INTO student VALUES('96004', '赵志勇', '信息', '02', '男', '2000/08/08', 0);
INSERT INTO student VALUES('97001', '马蓉', '数学', '02', '女', '2001/03/04', 0);
INSERT INTO student VALUES('97002', '李成功', '计算机', '01', '男', '2001/09/10', 0);
INSERT INTO student VALUES('97003', '黎明', '信息', '03', '女', '2002/02/08', 0);
INSERT INTO student VALUES('97004', '李丽', '计算机', '02', '女', '2002/01/05', 0);
INSERT INTO student VALUES('96005', '司马志明', '计算机', '02', '男', '2001/11/23', 0);

course 表中的数据

INSERT INTO course VALUES('001', '数学分析', 64, 4);
INSERT INTO course VALUES('002', '普通物理', 64, 4);
INSERT INTO course VALUES('003', '微机原理', 56, 3.5);
INSERT INTO course VALUES('004', '数据结构', 64, 4);
INSERT INTO course VALUES('005', '操作系统', 56, 3.5);
INSERT INTO course VALUES('006', '数据库原理', 56, 3.5);
INSERT INTO course VALUES('007', '编译原理', 48, 3);
INSERT INTO course VALUES('008', '程序设计', 32, 2);

teacher 表中的数据

INSERT INTO teacher VALUES('052501', '王成刚', '男', '计算机');
INSERT INTO teacher VALUES('052502', '李正科', '男', '计算机');
INSERT INTO teacher VALUES('052503', '严敏', '女', '数学');
INSERT INTO teacher VALUES('052504', '赵高', '男', '数学');
INSERT INTO teacher VALUES('052505', '刘玉兰', '女', '计算机');
INSERT INTO teacher VALUES('052506', '王成刚', '男', '信息');
INSERT INTO teacher VALUES('052507', '马悦', '女', '计算机');

score 表中的数据

INSERT INTO score VALUES('96001', '001', '052503', 77.5);
INSERT INTO score VALUES('96001', '003', '052501', 89);
INSERT INTO score VALUES('96001', '004', '052502', 86);
INSERT INTO score VALUES('96001', '005', '052505', 82);
INSERT INTO score VALUES('96002', '001', '052504', 88);
INSERT INTO score VALUES('96002', '003', '052502', 92.5);
INSERT INTO score VALUES('96002', '006', '052507', 90);
INSERT INTO score VALUES('96005', '004', '052502', 92);
INSERT INTO score VALUES('96005', '005', '052505', 90);
INSERT INTO score VALUES('96005', '006', '052505', 89);
INSERT INTO score VALUES('96005', '007', '052507', 78);
INSERT INTO score VALUES('96003', '001', '052504', 69);
INSERT INTO score VALUES('97001', '001', '052504', 96);
INSERT INTO score VALUES('97001', '008', '052505', 95);
INSERT INTO score VALUES('96004', '001', '052503', 87);
INSERT INTO score VALUES('96003', '003', '052501', 91);
INSERT INTO score VALUES('97002', '003', '052502', 91);
INSERT INTO score VALUES('97002', '004', '052505', NULL);
INSERT INTO score VALUES('97002', '006', '052507', 92);
INSERT INTO score VALUES('97004', '005', '052502', 90);
INSERT INTO score VALUES('97004', '006', '052501', 85);

注：把上面的实验数据添加上再开始实验。

3.实验内容

5-1 创建存储过程 `pro_fndname`

存储过程 profndname 通过模糊查询学生姓名，输入一个汉字，返回所有包含该汉字的学生记录。

delimiter ##
create procedure pro_fndname(in seach_char char(1))
begin
    select *from student where snme like concat('%',seach_char,'%');
end ##
delimiter ;

5-2 设计函数 `count_credit`

函数 countcredit 根据学号计算学生的总学分，前提是成绩大于或等于60分时才能获得课程学分。

delimiter ##
  create Function count_credit(v_sno char(6))
  returns int reads sql data
  Begin
  declare sums float ;
  select ifnull(sum(credit),0) into sums from course natural join score where grade >=60 and v_sno=sno;
    return sums;
  end ##
  delimiter ;

5-3 创建存储过程`p_count_credit`

存储过程 p_count_credit 使用 countcredit 函数更新 student 表中的总学分。

delimiter ##
  create procedure  p_count_credit()
  begin
    declare v_sno varchar(20);
    declare v_credit int default 0;
    declare stucur cursor for select sno from student;
    declare exit handler for not found close stucur ;
    open stucur;
    while true do
    fetch stucur into v_sno;
    update student set totalcredit =count_credit(v_sno) where sno=v_sno;
    end while;
    close stucur;
    end ##
  delimiter ;

5-4 创建触发器 `sum_credit`

触发器 sum_credit 在向 score 表插入记录时，自动更新 student 表中的总学分。只有成绩大于或等于60分时，才会增加学分。

delimiter ##
  create trigger sum_credit
  after insert on score for each row
  Begin
  if(new.grade>=60) then
        update student set totalcredit =totalcredit+(select credit from course where cno=new.cno)
        where student.sno=new.sno;
    end if;
    end ##
    delimiter ;

5-5 创建级联删除触发器 `delstudent_score`

触发器 delstudent_score 实现级联删除，当删除 student 表中的学生记录时，会同时删除 score 表中对应学号的成绩记录。

delimiter ##
  create trigger del_studnet_score
  before delete on student for each row
  begin
    delete from score where sno=old.sno;
    end ##
  delimiter ;