试验五:触发器与存储进程(函数)

1. 针对SPJ_MNG数据库，创立并履行如下存储进程。（合计40分）

(1) 创立一个没有参数的存储进程—jsearch1。该存储进程的作用是：当履行该存储进程时，将回来S表中北京供货商的一切信息。调用该存储进程并验证成果。（5分）

delimiter $$
create procedure jsearch1()
begin
	select * from s
    where city = '北京';
end$$
delimiter ;

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

delimiter $$
create procedure jsearch2(in in_city varchar(45))
begin
	select * from s
    where city = in_city;
end$$
delimiter ;

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

delimiter $$
create procedure jsearch3
	(in in_sno varchar(45), out out_sname varchar(45))
begin
	select sname from s
    where sno = in_sno;
end$$
delimiter ;

(4) 创立一个使用游标的存储进程jsearch4，创立成功后调用该存储进程并验证成果。该存储进程的功用：当输入一个工程号JNO时，将回来供应该工程零件的一切供货商的称号(SNAME)，这些供货商名拼接成一个字符串，并用逗号’,’分隔。

例如：输入：J2，输出： ‘精益, 盛锡, 为民’。（10分）

delimiter $$
create procedure jsearch4(in in_jno varchar(45))
begin
	#定义变量
	declare result varchar(100) default '';
    declare tmp_name varchar(100);
    declare done int default 0;
    #创立游标
	declare cur_sname cursor for
	select distinct sname from s,spj
    where in_jno = spj.jno
		and spj.sno = s.sno;
    #生成成果
    declare continue handler for sqlstate '02000' set done = 1;
	open cur_sname;
    fetch cur_sname into tmp_name;
    repeat
		set result = concat(result, tmp_name);
        set result = concat(result, ', ');
        fetch cur_sname into tmp_name;
	until done
    end repeat;
    close cur_sname;
    select left(result, char_length(result)-2);
end$$
delimiter ;

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

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

jsearch1：

jsearch2：

(6) 检查存储进程jsearch1基本状况信息。（5分）

提示：show procedure status like ‘jsearch%’;

(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 $$
create trigger insert_s before insert on sc
for each row
begin
	if new.sno not in (select sno from s) then
		begin
			#delete from sc where sc.sno = new.sno;
            signal sqlstate 'HY000' set message_text='不能刺进S表中没有的数据';
        end;
	end if;
	if new.cno not in (select cno from c) then
		begin
			#delete from sc where sc.cno = new.cno;
            signal sqlstate 'HY000' set message_text='不能刺进C表中没有的数据';
        end;
	end if;
end $$
DELIMITER ;

测验sno

insert into sc values(2009, 1, 999)

测验cno

insert into sc values(2001, 9, 999)

(2) 为S表创立一个名为dele_s1的DELETE触发器

该触发器的作用是提示用户“不能删去该表中的数据”并阻挠用户删去S表中的数据。触发器创立成功之后，删去S表中记载，验证触发器是否正常作业。(5分)

DELIMITER $$
create trigger dele_s1 before delete on s
for each row
begin
	signal sqlstate 'HY000'
    set message_text='不能删去该表中的数据';
end $$
DELIMITER ;

测验

delete from s where sno = '2001';

(3) 为S表创立一个名为dele_s2的DELETE触发器

该触发器的作用是删去S表中的记载时删去SC表中该学生的选课纪录。触发器创立成功之后，删去S表中的记载，验证触发器是否正常作业（确认S表和SC表相关数据是否被删去）。(5分)

DELIMITER $$
create trigger dele_s2 before delete on s
for each row
begin
	delete from sc where sc.sno = old.sno;
end $$
DELIMITER ;

测验

delete from s where sno = '2001';

(4) 为S表创立一个名为update_s的UPDATE触发器

该触发器的作用是制止更新S表中“sdept”字段的内容（更新不成功，并且提示“不能更新sdept字段”）。触发器创立成功之后，更新S表中“sdept”字段的内容，验证触发器是否正常作业。(5分)

DELIMITER $$
create trigger update_s before update on s
for each row
begin
	if new.sdept != old.sdept then
		signal sqlstate 'HY000'
		set message_text='不能更新sdept字段';
	end if;
end $$
DELIMITER ;

测验

update s set sdept = '1' where sno = '2002';

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

drop trigger if exists update_s;

(6) 自己规划一个before update触发器和after update触发器，比较before 和after触发器的差异。(5分)

before update触发器

drop trigger if exists b_update;
DELIMITER $$
create trigger b_update before update on s
for each row
begin
	if new.sdept != old.sdept then
		signal sqlstate 'HY000'
		set message_text='不能更新sdept字段';
	end if;
end $$
DELIMITER ;

after update触发器

drop trigger if exists b_update;
DELIMITER $$
create trigger b_update before update on s
for each row
begin
	if new.sdept != old.sdept then
		signal sqlstate 'HY000'
		set message_text='不能更新sdept字段';
	end if;
end $$
DELIMITER ;

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 $$
CREATE PROCEDURE update_cavggrade(in in_cno int)
BEGIN
	declare tmp_snum int;
	declare tmp_examsnum int;
	declare tmp_avggrade int;
	select count(*) from sc where cno = in_cno
	into tmp_snum;
	select count(*) from sc where cno = in_cno and grade is not null
	into tmp_examsnum;
	select avg(grade) from sc where cno = in_cno and grade is not null
	into tmp_avggrade;
	#增加进入avggrade
	if in_cno in (select distinct cno from cavggrade) then
		UPDATE cavggrade SET snum = tmp_snum, examsnum = tmp_examsnum, avggrade = tmp_avggrade
		WHERE cno = in_cno;
	else 
		insert into cavggrade values(in_cno,tmp_snum,tmp_examsnum,tmp_avggrade);
	end if;
END$$
DELIMITER ;

After insert触发器：

DELIMITER $$
CREATE TRIGGER sc_AFTER_INSERT AFTER INSERT ON sc FOR EACH ROW
BEGIN
  call update_cavggrade(new.cno);
END$$
DELIMITER ;

After update触发器：

DELIMITER $$
CREATE 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 $$
CREATE TRIGGER sc_AFTER_DELETE AFTER DELETE ON sc FOR EACH ROW
BEGIN
  call update_cavggrade(old.cno);
END$$
DELIMITER ;

数据测验

INSERT

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

sc表刺进数据

CAvgGrade表数据更新

DELETE

delete from sc where cno = 1;

sc表删去数据

CAvgGrade表数据更新

UPDATE

UPDATE sc SET grade = 6 WHERE cno = 1 and sno = 2003;

sc表修正数据

CAvgGrade表数据更新

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 $$
CREATE PROCEDURE gnrt_eID(in n int)
BEGIN
	set @x = 0;
	repeat 
		set @x = @x + 1;
		insert into employee values(20150000 + @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 $$
CREATE PROCEDURE update_salary()
BEGIN
	declare tmp_eID int;
    declare tmp_salary int;
    declare done int default 0;
    declare cur_employee cursor for
		select eID, salary from employee;
	declare continue handler for sqlstate '02000' set done = 1;
    open cur_employee;
    fetch cur_employee into tmp_eID, tmp_salary;
    repeat
		case
			when tmp_salary < 3000
				then update employee 
                set salary = salary + 300 where eID = tmp_eID;
            when tmp_salary >= 3000 and tmp_salary < 4000
				then update employee 
                set salary = salary + 200 where eID = tmp_eID;
            else
				update employee 
                set salary = salary + 50 where eID = tmp_eID;
		end case;
		fetch cur_employee into tmp_eID, tmp_salary;
	until done
    end repeat;
    close cur_employee;
END$$

履行前：

履行后：

试验中出现的问题及解决方案

1

进行试验时我因为没有读清题目，误认为需求得到项目在北京的s表信息，导致糟蹋很多时刻。

2

进行试验时需求删去字符串最后的逗号，可是上网查阅的办法给出的获取字符串长度的函数是length（），但成果过错。通过进一步查询发现length（）给出的是字节长度，而此处需求的是字符长度，所以我找到了char_length（）函数，这回成果正确了。

3

此处我在workbench中发生过错：

经查阅，第三方东西或许发生此问题，换到指令行中履行成功！

实验五:触发器与存储过程(函数)-NPU

试验五:触发器与存储进程(函数)

1. 针对SPJ_MNG数据库，创立并履行如下存储进程。（合计40分）

(1) 创立一个没有参数的存储进程—jsearch1。该存储进程的作用是：当履行该存储进程时，将回来S表中北京供货商的一切信息。调用该存储进程并验证成果。（5分）

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

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

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

(6) 检查存储进程jsearch1基本状况信息。（5分）

(7) 删去jsearch1存储进程。（5分）

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

(1) 删去SC表上的外键束缚，针对SC表创立一个名为insert_s的INSERT触发器。

(2) 为S表创立一个名为dele_s1的DELETE触发器

(3) 为S表创立一个名为dele_s2的DELETE触发器

(4) 为S表创立一个名为update_s的UPDATE触发器

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

(6) 自己规划一个before update触发器和after update触发器，比较before 和after触发器的差异。(5分)

(7) 创立一个新的课程成果统计表 CAvgGrade(Cno, Snum, examSNum, avgGrade)

数据测验

INSERT

DELETE

UPDATE

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

(1) 为了帮忙本题主动生成1000条职工数据，创立一个主动生成职工ID的用户自定义函数generateEID。

试验中出现的问题及解决方案

1

2

3

作者信息

推广

实验五:触发器与存储过程(函数)-NPU

试验五:触发器与存储进程(函数)

1. 针对SPJ_MNG数据库，创立并履行如下存储进程。（合计40分）

(1) 创立一个没有参数的存储进程—jsearch1。该存储进程的作用是：当履行该存储进程时，将回来S表中北京供货商的一切信息。调用该存储进程并验证成果。（5分）

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

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

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

(6) 检查存储进程jsearch1基本状况信息。（5分）

(7) 删去jsearch1存储进程。（5分）

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

(1) 删去SC表上的外键束缚，针对SC表创立一个名为insert_s的INSERT触发器。

(2) 为S表创立一个名为dele_s1的DELETE触发器

(3) 为S表创立一个名为dele_s2的DELETE触发器

(4) 为S表创立一个名为update_s的UPDATE触发器

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

(6) 自己规划一个before update触发器和after update触发器，比较before 和after触发器的差异。(5分)

(7) 创立一个新的课程成果统计表 CAvgGrade(Cno, Snum, examSNum, avgGrade)

数据测验

INSERT

DELETE

UPDATE

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

(1) 为了帮忙本题主动生成1000条职工数据，创立一个主动生成职工ID的用户自定义函数generateEID。

试验中出现的问题及解决方案

1

2

3

相关文章

容器系列: 1 docker的底层技术和快速实践

JS+CSS实现一些简单轮播图

04-复习C语言核心知识|结构体、共用体、位域、输入&输出、文件读写

搞个脚本—自动赚猿分（脱壳篇）

作者信息

推广