试验五:触发器与存储过程(函数)
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 scfor each rowbegin 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 sfor each rowbegin 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 sfor each rowbegin 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 sfor each rowbegin 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 sfor each rowbegin 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 sfor each rowbegin 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 ROWBEGIN call update_cavggrade(new.cno);END$$DELIMITER ;- After update触发器：
DELIMITER $$CREATE TRIGGER sc_AFTER_UPDATE AFTER UPDATE ON sc FOR EACH ROWBEGIN 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 ROWBEGIN 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中产生谬误：
经查阅，第三方工具可能产生此问题，换到命令行中执行胜利！