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

64次阅读

共计 6564 个字符，预计需要花费 17 分钟才能阅读完成。

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

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

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 ;

例如：输出：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 ;

提醒：show create procedure jsearch1。为不便查看，可在以上命令开端加 \G，应用两列文本模式显示。

jsearch1：

jsearch2：

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

drop procedure jsearch1;

该触发器的性能：当用户向 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)

该触发器的作用是提醒用户“不能删除该表中的数据”并阻止用户删除 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';

该触发器的作用是删除 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';

该触发器的作用是禁止更新 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';

drop trigger if exists update_s;

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 表中数据的需要。

别离示意课号，选该课程的学生人数，参加考试人数，该门课程的均匀问题。利用触发器实现如下的性能：当 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 表数据更新

其中员工 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$$

执行前：

执行后：

进行试验时我因为没有读清题目，误以为须要失去我的项目在北京的 s 表信息，导致节约大量工夫。

进行试验时须要删除字符串最初的逗号，然而上网查阅的办法给出的获取字符串长度的函数是 length（），但后果谬误。通过进一步查问发现 length（）给出的是字节长度，而此处须要的是字符长度，于是我找到了 char_length（）函数，这回后果正确了。

此处我在 workbench 中产生谬误：

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

正文完

mysql

发表至： mysql

2021-11-21

0

关于mysql:轻量级binlog同步工具包binlogportal

关于mysql:MySQL主从同步配置

关于mysql:一文带你剖析MySQL到底都有哪些常用的查询

关于mysql:隐藏了2年的Bug终于连根拔起悲观锁并没有那么简单

关于mysql:实验五触发器与存储过程函数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

Just My Socks（注册教程内含优惠码）

关于mysql:实验五触发器与存储过程函数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

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）