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

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

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

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

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

实验五:触发器与存储过程(函数)-NPU_第1张图片

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

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

实验五:触发器与存储过程(函数)-NPU_第2张图片

(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 ;

实验五:触发器与存储过程(函数)-NPU_第3张图片

(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 ;

实验五:触发器与存储过程(函数)-NPU_第4张图片

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

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

jsearch1:

实验五:触发器与存储过程(函数)-NPU_第5张图片

jsearch2:

实验五:触发器与存储过程(函数)-NPU_第6张图片

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

提示:show procedure status like 'jsearch%';

实验五:触发器与存储过程(函数)-NPU_第7张图片

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

drop procedure jsearch1;

image-20211016161136250

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)

image-20211016170331740

测试cno

insert into sc values(2001, 9, 999)

image-20211016170355148

(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';

实验五:触发器与存储过程(函数)-NPU_第8张图片

(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';

image-20211016171507181

实验五:触发器与存储过程(函数)-NPU_第9张图片

(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';

image-20211016172359164

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

drop trigger if exists update_s;

实验五:触发器与存储过程(函数)-NPU_第10张图片

(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表插入数据

实验五:触发器与存储过程(函数)-NPU_第11张图片

CAvgGrade表数据更新

image-20211017133121418

DELETE
delete from sc where cno = 1;

sc表删除数据

image-20211017133729476

CAvgGrade表数据更新

image-20211017133757739

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

sc表修改数据

image-20211017133952377

CAvgGrade表数据更新

image-20211017134008900

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$$

执行函数,生成数据

实验五:触发器与存储过程(函数)-NPU_第12张图片

(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$$

执行前:

image-20211017141141705

执行后:

image-20211017141407607

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

1

image-20211016145744046

进行实验时我因为没有读清题目,误以为需要得到项目在北京的s表信息,导致浪费大量时间。

2

实验五:触发器与存储过程(函数)-NPU_第13张图片

进行实验时需要删除字符串最后的逗号,但是上网查阅的方法给出的获取字符串长度的函数是length(),但结果错误。经过进一步查询发现length()给出的是字节长度,而此处需要的是字符长度,于是我找到了char_length()函数,这回结果正确了。

3

image-20211016160721350

此处我在workbench中产生错误:

image-20211016160812836

经查阅,第三方工具可能产生此问题,换到命令行中执行成功!

你可能感兴趣的