数据库原理及应用mysql版陈业斌实验五_数据库原理及应用mysql版陈业斌 存储过程

🏝️专栏：Mysql_猫咪-9527的博客-CSDN博客
🌅主页：猫咪-9527-CSDN博客 

“欲穷千里目，更上一层楼。会当凌绝顶，一览众山小。”

目录

实验五存储程序

1.实验数据如下

2. 插入数据

3.实验内容

5-1 创建存储过程 pro_fndname

5-2 设计函数 count_credit

5-3 创建存储过程p_count_credit

5-4 创建触发器 sum_credit

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

实验五存储程序

【实验目的】

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

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 ;

删除前：

 删除后：