MySQL - 存储过程与事务

目录

第1关：使用流程控制语句的存储过程

第2关：使用游标的存储过程

第3关：使用事务的存储过程

第1关：使用流程控制语句的存储过程

任务描述

本关任务：创建一个存储过程，向表fibonacci插入斐波拉契数列的前n项。

 use fib;
 
-- 创建存储过程`sp_fibonacci(in m int)`，向表fibonacci插入斐波拉契数列的前m项，及其对应的斐波拉契数。fibonacci表初始值为一张空表。请保证你的存储过程可以多次运行而不出错。
 
drop procedure if exists sp_fibonacci;
delimiter $$
 
create procedure sp_fibonacci(in m int)
begin
######## 请补充代码完成存储过程体 ########
declare num1 int;
declare num2 int;
declare num3 int;
declare i int;
set num1 = 0;
set num2 = 1;
set i = 0;
while i < m DO
    if i = 0 then
        insert into fibonacci(n,fibn) values(i,num1);
    end if;
    if i = 1 then
        insert into fibonacci(n,fibn) values(i,num2);
    end if;
    if i >= 2 then
        set num3 = num1 + num2;
        set num1 = num2;
        set num2 = num3;
        insert into fibonacci(n,fibn) values(i,num3);
    end if;
 
    set i = i+1;
END WHILE;
end $$
 
delimiter ;
 
 

第2关：使用游标的存储过程

任务描述

本关任务：使用游标编程存储过程为医院的某科室排夜班值班表。

-- 编写一存储过程，自动安排某个连续期间的大夜班的值班表:
 
delimiter $$
create procedure sp_night_shift_arrange(in start_date date, in end_date date)
begin
    declare no_doctor boolean default false;
    declare no_nurse boolean default false;
    declare no_data boolean default false;
    declare d date;
    declare doctor_name char(30);
    declare doctor_type int;
    declare nurse1_name char(30);
    declare nurse2_name char(30);
    declare director_found boolean default false;
    declare director_name char(30);
    declare cur_doctor cursor for select e_name,e_type from employee where (e_type) = 1 or (e_type = 2) order by e_id;
    declare cur_nurse cursor for select e_name from employee where e_type = 3 order by e_id;
    declare continue HANDLER for NOT FOUND set no_data = true;
    open cur_doctor;
    open cur_nurse;
    
set d = start_date;
while (d <= end_date) do
if director_found and (dayname(d) = 'Monday') then
set doctor_name = director_name;
set director_found = false;
else
fetch cur_doctor into doctor_name,doctor_type;
set no_doctor = no_data;
if no_doctor then
set no_data = false;
close cur_doctor;
open cur_doctor;
fetch cur_doctor into doctor_name,doctor_type;
set no_doctor=no_data;
end if;
 
if dayname(d) in ('Saturday','Sunday') and (doctor_type = 1) then
    set director_found = true;
    set director_name = doctor_name;
    if no_doctor then
    set no_data = false;
    close cur_doctor;
    open cur_doctor;
    fetch cur_doctor into doctor_name,doctor_type;
    set no_doctor = no_data;
    else
    fetch cur_doctor into doctor_name,doctor_type;
    set no_doctor = no_data;
        if no_doctor then
        set no_data = false;
        close cur_doctor;
        open cur_doctor;
        fetch cur_doctor into doctor_name,doctor_type;
        set no_doctor = no_data;
        end if;
    end if;
end if;
end if;
 
fetch cur_nurse into nurse1_name;
set no_nurse = no_data;
if no_nurse then
    set no_data = false;
    close cur_nurse;
    open cur_nurse;
    fetch cur_nurse into nurse1_name;
end if;
 
fetch cur_nurse into nurse2_name;
set no_nurse = no_data;
if no_nurse then
    set no_data = false;
    close cur_nurse;
    open cur_nurse;
    fetch cur_nurse into nurse2_name;
end if;
 
insert into night_shift_schedule values(d,doctor_name,nurse1_name,nurse2_name);
 
set d = adddate(d,interval 1 day);
 
end while;
 
end$$
 
delimiter ;
 
/*  end  of  your code  */ 

第3关：使用事务的存储过程

任务描述

本关任务：编写实现转账功能的存储过程。

use finance1;
 
-- 在金融应用场景数据库中，编程实现一个转账操作的存储过程sp_transfer_balance，实现从一个帐户向另一个帐户转账。
-- 请补充代码完成该过程：
delimiter $$
create procedure sp_transfer(
	                 IN applicant_id int,      
                     IN source_card_id char(30),
					 IN receiver_id int, 
                     IN dest_card_id char(30),
					 IN	amount numeric(10,2),
					 OUT return_code int)
BEGIN
    start transaction;
set return_code = 0;
if exists (select * from bank_card
where b_number = source_card_id and
b_c_id = applicant_id and
b_type = '储蓄卡' and
b_balance >= amount) and
exists (select * from bank_card
where b_number = dest_card_id and
b_c_id = receiver_id)
then
update bank_card set b_balance =
if(b_type = '信用卡', b_balance - amount, b_balance + amount)
where b_number = dest_card_id;
update bank_card
set b_balance = b_balance - amount where b_number = source_card_id;
set return_code = 1;
end if;
commit;
 
END$$
 
delimiter ;
 
 
 
 
 
 
 
 
/*  end  of  your code  */