《数据库原理》实验报告DB5——触发器与存储过程(函数)

最新推荐文章于 2023-12-28 00:38:10 发布

子澍

最新推荐文章于 2023-12-28 00:38:10 发布

阅读量4.3k

点赞数 11

分类专栏：数据库文章标签：数据库 mysql 触发器

本文链接：https://blog.csdn.net/CityBoySHU/article/details/111600703

版权

数据库专栏收录该内容

7 篇文章 10 订阅

订阅专栏

一. 实验内容、步骤以及结果

1. 针对SPJ_MNG数据库，创建并执行如下存储过程。（共计40分）

(1) 创建一个没有参数的存储过程—jsearch1。该存储过程的作用是：当执行该存储过程时，将返回S表中北京供应商的所有信息。调用该存储过程并验证结果。（5分）

创建：

DELIMITER $$
USE `spj_ming`$$
CREATE PROCEDURE `jsearch1` ()
BEGIN
select * from s
    where city = '北京';
END$$

验证：

Call jsearch1;

(2) 创建带输入参数的存储过程—jsearch2。该存储过程的作用是：当输入一个供应商所在城市名时（如北京），将返回该供应商的所有信息。调用存储过程并验证结果。（5分）

创建：

DELIMITER $$
USE `spj_ming`$$
CREATE PROCEDURE `jsearch2` (in incity char(10))
BEGIN
select * from s
where city = incity;
END$$

验证：

call jsearch2('北京')；

(3) 创建带输入参数和输出参数的存储过程(函数)—jsearch3。该存储过程的作用是：当输入一个供应商编号(输入参数SNO)时，将返回该供应商的名称(输出参数SNAME)。调用存储过程并验证结果。（5分）

创建：

DELIMITER $$
USE `spj_ming`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `jsearch3`(in snoo char(5),out snamee char(10))
BEGIN
select sname into snamee from s
    where sno = snoo;
END$$

验证：

call jsearch3('s1',@name);
select @name;

(4) 创建一个使用游标的存储过程jsearch4，创建成功后调用该存储过程并验证结果。该存储过程的功能：当输入一个工程号JNO时，将返回供应该工程零件的所有供应商的名称(SNAME)，这些供应商名拼接成一个字符串，并用逗号’,’分隔。例如：输入：J2，输出： ‘精益, 盛锡, 为民’。（10分）

创建：

DELIMITER $$
USE `spj_ming`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `jsearch4`(in in_jno char(10))
BEGIN
	declare name char(10);
	declare done int;
	declare tmp char(100) default '''';
	declare c_name cursor for 
		select distinct sname from s,spj
		where spj.sno = s.sno and jno = in_jno;
	declare continue handler for sqlstate '02000' set done = 1; 
    open c_name;
    fetch c_name into name;
	repeat 
        set tmp = concat (tmp,name);
		set tmp = concat (tmp,',');
        fetch c_name into name;
	until done
	end repeat;
	set tmp = concat (tmp,'''');
	close c_name;
	select tmp;
END$$

验证：

call jsearch4('J2');

(5) 查看存储过程jsearch1和jsearch2的文本信息。（5分）

提示：show create procedure jsearch1。为方便查看，可在以上命令末尾加\G，使用两列文本形式显示。

show create procedure jsearch1\G;
 
show create procedure jsearch2\G;

(6) 查看存储过程jsearch1基本状态信息。（5分）

提示：show procedure status like 'jsearch1';

show procedure status like 'jsearch1';

(7) 删除jsearch1存储过程。（5分）

drop procedure jsearch1;

2. 针对Student数据库，创建和执行如下的触发器：（共计40分）

(1) 删除SC表上的外键约束，针对SC表创建一个名为insert_s的INSERT触发器。该触发器的功能：当用户向SC表中插入记录时，如果插入的cno值不是C表中Cno的已有值，则提示用户“不能插入C表中没有的数据”，并阻止该数据的插入；如果插入的sno值不是S表中的sno的已有值，则提示用户“不能插入S表中没有的数据”，并阻止该数据的插入。触发器创建成功之后，向SC表插入记录，验证触发器是否正常工作。(5分)

创建：

DELIMITER $$
USE `student`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `insert_s` BEFORE INSERT ON `sc` FOR EACH ROW BEGIN
if new.sno not in (select distinct(sno) from sc) then
		signal sqlstate '45000'
		set message_text = '不能插入S表中没有的数据';
	end if;
	if new.cno not in (select distinct(cno) from sc) then
		signal sqlstate '45000'
		set message_text = '不能插入C表中没有的数据';
        end if;
END$$
DELIMITER ;

验证：

insert into sc values(2004,1,96);//验证s表中没有的数据是否能插入
insert into sc values(2002,4,90);//验证c表中没有的数据是否能插入
insert into sc values(2003,1,90);//验证其他数据插入是否正常

(2) 为S表创建一个名为dele_s1的DELETE触发器，该触发器的作用是提示用户“不能删除该表中的数据”并阻止用户删除S表中的数据。触发器创建成功之后，删除S表中记录，验证触发器是否正常工作。(5分)

创建：

DELIMITER $$
USE `student`$$
CREATE DEFINER = CURRENT_USER TRIGGER `student`.`delete_s1` BEFORE DELETE ON `s` FOR EACH ROW
BEGIN
	signal sqlstate '03100'
	set message_text = '不能删除该表中的数据';
END$$
DELIMITER ;

验证：

delete from s where sno = '2002';

(3) 为S表创建一个名为dele_s2的DELETE触发器，该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。触发器创建成功之后，删除S表中的记录，验证触发器是否正常工作（确认S表和SC表相关数据是否被删除）。(5分)

创建：

DELIMITER $$
USE `student`$$
CREATE DEFINER = CURRENT_USER TRIGGER `student`.`delete_s2` AFTER DELETE ON `s` FOR EACH ROW
BEGIN
	set @sno = old.sno;
	delete from sc where sno = @sno;
END$$
DELIMITER ;

验证：

delete from s where sno = '2002';

(4) 为S表创建一个名为update_s的UPDATE触发器，该触发器的作用是禁止更新S表中“sdept”字段的内容（更新不成功，并且提示“不能更新sdept字段”）。触发器创建成功之后，更新S表中“sdept”字段的内容，验证触发器是否正常工作。(5分)

创建：

DELIMITER $$
USE `student`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `update_s1` BEFORE UPDATE ON `s` FOR EACH ROW BEGIN
	if new.sdept != old.sdept
    then
        signal sqlstate '45000'
		set message_text = '不能更新sdept字段';
        end if;
END$$
DELIMITER ;

验证：

UPDATE s SET sdept = 'ma' WHERE sdept = 'CS';

(5) 删除update_s触发器。(5分)

drop trigger update_s1;

(6) 自己设计一个before update触发器和after update触发器，比较before 和after触发器的区别。(5分)

创建：

Before update触发器

CREATE DEFINER=`root`@`localhost` TRIGGER `sc_BEFORE_UPDATE` BEFORE UPDATE ON `sc` FOR EACH ROW BEGIN
	call update_cavggrade(new.cno);
    call update_cavggrade(old.cno);
END

After update触发器

CREATE DEFINER=`root`@`localhost` TRIGGER `sc_AFTER_UPDATE` AFTER UPDATE ON `sc` FOR EACH ROW BEGIN
	call update_cavggrade(new.cno);
    call update_cavggrade(old.cno);
END

比较：

After update:

UPDATE sc SET cno = 2 WHERE cno = 1 and sno = 2003;

可以看到使用after触发器，数值改变符合要求，cno=1和cno=2，对应的各项数值改变均符合期望。

Before update:

UPDATE sc SET cno = 5 WHERE cno = 2 and sno = 2001;

可以看到使用before触发器，数值改变符合要求，cno=5和cno=2，对应的各项数值均未改变。

before 和after触发器的区别：before触发器在update执行之前就执行了，所以所期望的更新cavggrade表中数据功能无法实现；after触发器在sc表数据更新后才触发，所以可以满足更新cavggrade表中数据的需求。

(7) 创建一个新的课程成绩统计表 CAvgGrade(Cno, Snum, examSNum, avgGrade)，分别表示课号，选该课程的学生人数，参加考试人数，该门课程的平均成绩。利用触发器实现如下的功能：当SC表中插入、删除或者更新某个人的成绩时，自动更新表CAvgGrade。注意SC表中的grade为NULL时表明该学生还未参加考试，计算平均成绩时不需要计算该成绩，但是grade为0即考试成绩为0时，计算平均成绩需要计算该学生成绩。(10分)

提示：在MySQL中需要针对插入、更新、删除动作需分别创建3个触发器。可以先设计并实现一个公共的存储过程，然后在3个触发器中调用该存储过程。

储存过程：

DELIMITER $$
USE `student`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_cavggrade`(in in_cno int)
BEGIN
	declare e_snum int;
	declare e_examsnum int;
	declare e_avggrade int;
	select count(*) from sc where cno = in_cno
	into e_snum;
	select count(*) from sc where cno = in_cno and grade is not null
	into e_examsnum;
	select avg(grade) from sc where cno = in_cno and grade is not null
	into e_avggrade;
       if in_cno in (select distinct cno from cavggrade)
		then UPDATE cavggrade SET snum = e_snum,examsnum = e_examsnum,avggrade = e_avggrade
		WHERE cno = in_cno;
	else 
		insert into cavggrade values(in_cno,e_snum,e_examsnum,e_avggrade);
	end if;
END$$

触发器：

After insert触发器

DELIMITER $$
USE `student`$$
CREATE DEFINER = CURRENT_USER TRIGGER `student`.`sc_AFTER_INSERT` AFTER INSERT ON `sc` FOR EACH ROW
BEGIN
  call update_cavggrade(new.cno);
END$$
DELIMITER ;

After update触发器

DELIMITER $$
USE `student`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `sc_AFTER_UPDATE` AFTER UPDATE ON `sc` FOR EACH ROW BEGIN
	call update_cavggrade(new.cno);
    call update_cavggrade(old.cno);
END$$
DELIMITER ;

After delete触发器

DELIMITER $$
USE `student`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `sc_AFTER_DELETE` AFTER DELETE ON `sc` FOR EACH ROW BEGIN
	call update_cavggrade(old.cno);
END$$
DELIMITER ;

数据测试：
（1）插入一个cavggrade表中已有cno的数据，可看到改变符合要求。

insert into sc values(2005,1,96);

（2）插入一个cavggrade表中没有cno的数据，可看到新增加了一行且改变符合要求。

insert into sc values(2003,5,96);

（3）删除一个数据，可看到对应的cno行改变符合要求。

delete from sc where cno = 5;

（4）改变一个sc表中的grade字段，可看到avggrade列的数值改变符合要求。

UPDATE sc SET grade = 60 WHERE cno = 1 and sno = 2001;

（5）改变一个sc表中的cno字段，可看到改变前改变后对应的cno行数值改变符合要求。

UPDATE sc SET cno = 6 WHERE cno = 1 and sno = 2001;

（6）改变一个sc表中的cno字段，使其变到cavggrade表中无其对应的cno，可看到新加入一行，且改变前改变后对应的cno行数值改变符合要求。

UPDATE sc SET cno = 7 WHERE cno = 6 and sno = 2001;

3. 创建一个员工表employee(eID, eName, salary)，假设该表中有1000条员工数据，完成下列要求。（总计20分，每题10分）

(1) 为了协助本题自动生成1000条员工数据，创建一个自动生成员工ID的用户自定义函数generateEID。其中员工ID要求是一个8位的数字，前四位表示插入员工数据的当前年份，后四位按照从0001到9999的顺序增长。例如2015年插入的第一条数据是20150001，所有1000条员工ID分别是20150001-20151000。调用该函数实现自动插入1000条数据。（注意插入数据的时候员工姓名可以为任意值，工资是2000-5000之间的数字）

DELIMITER $$
USE `shiyan5`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `generateEID`(in n int)
BEGIN
	set @x = 0;
	repeat 
		set @x = @x + 1;
		insert into employee values(20200000 + @x,concat('ename',cast(@x as char(10))),floor(2000 + 3000*rand()));
	until @x >= n
	end repeat;
END$$

(2) 该公司计划为员工按照一定的规则涨工资，请使用游标创建一个存储过程，执行该存储过程完成本次工资调整。工资增长规则如下：

工资在3000元以下，每月涨300元；
工资在3000-4000元之间，每月涨200元；
工资大于或者等于4000元，每月涨50元；

DELIMITER $$
		USE `shiyan5`$$
		CREATE PROCEDURE `update_salary` ()
		BEGIN
			declare e_salary int;
			declare e_eid int;
  		  	declare done int;
			declare c_name cursor for 
				select eid,salary from employee;
			declare continue handler for sqlstate '02000' set done = 1; 
    		open c_name;
			fetch c_name into e_eid,e_salary;  
			repeat 
				case 
        		when e_salary < 3000
					then update employee set salary = salary + 300 where eid = 				e_eid;
				when e_salary >= 3000 and e_salary < 4000
					then update employee set salary = salary + 200 where eid = 				e_eid;
				else	
					update employee set salary = salary + 50 where eid = e_eid;
				end case;	
				fetch c_name into e_eid,e_salary;
			until done
			end repeat;	
			close c_name;		
		END$$

二. 实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)

子澍

关注

11
点赞
踩
38

收藏

觉得还不错? 一键收藏
0
评论
《数据库原理》实验报告DB5——触发器与存储过程(函数)

一. 实验内容、步骤以及结果1. 针对SPJ_MNG数据库，创建并执行如下存储过程。（共计40分）(1) 创建一个没有参数的存储过程—jsearch1。该存储过程的作用是：当执行该存储过程时，将返回S表中北京供应商的所有信息。调用该存储过程并验证结果。（5分）创建：DELIMITER $$USE `spj_ming`$$CREATE PROCEDURE `jsearch1` ()BEGINselect * from s where city = '北京';END$$验证：Ca
复制链接

扫一扫