乐趣区

关于mysql:实验五触发器与存储过程函数NPU

jiezi

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

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 中产生谬误：

经查阅，第三方工具可能产生此问题，换到命令行中执行胜利！

退出移动版