Task1

1.1

DELIMITER //  
CREATE PROCEDURE password_check_or_change(  
    IN in_username varchar(20),  
    IN in_password varchar(20),  
    IN in_new_password varchar(20),  
    IN in_action int,  
    OUT result int  
)  
BEGIN  
    declare temp_pwd varchar(20);  
    declare temp_uname varchar(20);  
    declare done int default 0;  
    declare cur cursor for select user_name, password from account;  
    declare continue handler for not found set done = true;  
    set result = false;  
  
    open cur;  
    read_loop: LOOP  
        fetch cur into temp_uname, temp_pwd;  
        if done then  
            leave read_loop;  
        end if;  
        if in_action = 1 then  
            if temp_uname = in_username and temp_pwd = in_password then  
                set result = true;  
            end if;  
        end if;  
        if in_action = 2 then  
            if temp_uname = in_username and temp_pwd = in_password and  
               in_new_password regexp '^[A-Za-z0-9]{4,10}$' then  
                    update account set password = in_new_password  
                       where user_name = in_username and password = in_password;  
                    set result = true;  
            end if;  
        end if;  
    end loop;  
    close cur;  
end//  
delimiter ;  
  
-- 测试1：检查正确密码（返回 1）  
CALL password_check_or_change('aaa', '1234', NULL, 1, @result);  
SELECT @result;  
  
-- 测试2：修改密码为非法格式（返回 0）  
CALL password_check_or_change('aaa', '1234', 'ab#', 2, @result);  
SELECT @result;  
  
-- 测试3：修改密码为合法格式（返回 1）  
CALL password_check_or_change('aaa', '1234', 'newPass123', 2, @result);  
SELECT @result;

1.2

DELIMITER //  
CREATE PROCEDURE borrow_book(  
    IN in_username varchar(20),  
    IN in_ISBN varchar(20),  
    OUT result int  
)  
BEGIN  
    declare temp_uid int;  
    declare temp_num int;  
    declare temp_borrowed int;  
    set result = false;  
  
    select user_id into temp_uid from account where user_name = in_username;  
    select num into temp_num from library where ISBN = in_ISBN;  
    select count(*) into temp_borrowed from record  
    where user_name = in_username and ISBN = in_ISBN and return_time is NULL;  
    if temp_uid IS NOT NULL and temp_num >= 1 and temp_borrowed = 0 then  
        update library set num = num-1 where ISBN = in_ISBN;  
        insert into record(user_id, user_name, ISBN, borrow_time, expire_time, return_time) values (temp_uid, in_username, in_ISBN, CURDATE(), CURDATE() + INTERVAL 30 DAY, NULL);  
        set result = true;  
    end if;  
end//  
delimiter ;  
  
-- 测试1：用户不存在（返回 0）  
CALL borrow_book('bbb', '9781234567890', @result);  
SELECT @result;  
  
-- 测试2：库存不足（返回 0）  
UPDATE library SET num = 0 WHERE ISBN = '9781234567890';  
CALL borrow_book('aaa', '9781234567890', @result);  
SELECT @result;  
  
-- 测试3：成功借书（返回 1）  
UPDATE library SET num = 5 WHERE ISBN = '9781234567890';  
CALL borrow_book('aaa', '9781234567890', @result);  
SELECT @result;

1.3

DELIMITER //  
CREATE PROCEDURE return_book(  
    IN in_username varchar(20),  
    IN in_ISBN varchar(20),  
    OUT result int  
)  
BEGIN  
    declare temp_uid int;  
    declare temp_borrowed int;  
    set result = false;  
  
    select user_id into temp_uid from account where user_name = in_username;  
    select count(*) into temp_borrowed from record  
    where user_name = in_username and ISBN = in_ISBN and return_time is NULL;  
    if temp_uid IS NOT NULL and temp_borrowed = 1 then  
        update library set library.num = library.num + 1 where library.ISBN = in_ISBN;  
        update record set record.return_time = CURDATE()  
                      where record.user_name = in_username and record.ISBN = in_ISBN and record.return_time IS NULL;  
        set result = true;  
    end if;  
end//  
delimiter ;  
  
-- 测试1：无借阅记录（返回 0）  
CALL return_book('aaa', 'invalid_isbn', @result);  
SELECT @result;  
  
-- 测试2：成功还书（返回 1）  
CALL return_book('aaa', '9781234567890', @result);  
SELECT @result;  
  
-- 测试3：重复还书（返回 0）  
CALL return_book('aaa', '9781234567890', @result);  
SELECT @result;

1.4

delimiter //  
create procedure view_records(in in_username varchar(50))  
begin  
    declare temp_ISBN varchar(13);  
    declare temp_exdate date;  
    declare done int default 0;  
    -- 声明游标遍历未归还记录  
    declare cur cursor for  
        select ISBN, expire_time from record  
        where user_name = in_username and return_time is NULL;  
    declare continue handler for not found set done = 1;  
  
    -- 创建临时结果表  
    create temporary table if not exists temp_results (ISBN varchar(20), expire_time date);  
  
    open cur;  
    read_loop: loop  
        fetch cur into temp_ISBN, temp_exdate;  
        if done then  
            leave read_loop;  
        end if;  
        insert into temp_results values(temp_ISBN, temp_exdate);  
    end loop;  
    close cur;  
  
    select in_username as username, ISBN, expire_time from temp_results;  
    drop temporary table temp_results;  
end //  
delimiter ;  
  
-- 测试1：无借阅记录（返回空）  
CALL view_records('bbb');  
  
-- 测试2：存在未归还记录（返回记录）  
CALL borrow_book('bbb', '9781234567890', @result);  
CALL view_records('bbb');  
  
-- 测试3：已归还记录不显示  
CALL return_book('bbb', '9781234567890', @result);  
CALL view_records('bbb');

2

// 插入
DELIMITER //
CREATE TRIGGER after_sells_insert
AFTER INSERT ON sells
FOR EACH ROW
BEGIN
    -- 计算总金额并更新客户等级
    UPDATE customer c
    SET c.level = (
        SELECT CASE 
            WHEN SUM(f.price * s.quantity) >= 20000 THEN 'SVIP'
            WHEN SUM(f.price * s.quantity) >= 10000 THEN 'VIP'
            ELSE 'normal'
        END
        FROM sells s
        JOIN fruits f ON s.fid = f.fid
        WHERE s.cid = NEW.cid
    )
    WHERE c.cid = NEW.cid;
END //
DELIMITER ;

// 更新
DELIMITER //
CREATE TRIGGER after_sells_update
AFTER UPDATE ON sells
FOR EACH ROW
BEGIN
    -- 处理旧客户
    UPDATE customer c
    SET c.level = (
        SELECT CASE 
            WHEN SUM(f.price * s.quantity) >= 20000 THEN 'SVIP'
            WHEN SUM(f.price * s.quantity) >= 10000 THEN 'VIP'
            ELSE 'normal'
        END
        FROM sells s
        JOIN fruits f ON s.fid = f.fid
        WHERE s.cid = OLD.cid
    )
    WHERE c.cid = OLD.cid;

    -- 处理新客户（如果 cid 被修改）
    IF NEW.cid != OLD.cid THEN
        UPDATE customer c
        SET c.level = (
            SELECT CASE 
                WHEN SUM(f.price * s.quantity) >= 20000 THEN 'SVIP'
                WHEN SUM(f.price * s.quantity) >= 10000 THEN 'VIP'
                ELSE 'normal'
            END
            FROM sells s
            JOIN fruits f ON s.fid = f.fid
            WHERE s.cid = NEW.cid
        )
        WHERE c.cid = NEW.cid;
    END IF;
END //
DELIMITER ;

// 删除
DELIMITER //
CREATE TRIGGER after_sells_delete
AFTER DELETE ON sells
FOR EACH ROW
BEGIN
    -- 更新旧客户的等级
    UPDATE customer c
    SET c.level = (
        SELECT CASE 
            WHEN SUM(f.price * s.quantity) >= 20000 THEN 'SVIP'
            WHEN SUM(f.price * s.quantity) >= 10000 THEN 'VIP'
            ELSE 'normal'
        END
        FROM sells s
        JOIN fruits f ON s.fid = f.fid
        WHERE s.cid = OLD.cid
    )
    WHERE c.cid = OLD.cid;
END //
DELIMITER ;

// 验证
DELIMITER //
CREATE PROCEDURE test_triggers()
BEGIN
    -- 重置测试数据
    DELETE FROM sells;
    UPDATE customer SET level = 'normal' WHERE cid IN (1, 2);

    -- 测试1: 插入记录使总金额达到 VIP
    INSERT INTO sells (fid, cid, quantity) VALUES (1, 1, 2000); -- 5*2000 = 10000
    SELECT 'Test1 - After Insert VIP' AS Description, cid, level FROM customer WHERE cid = 1;

    -- 测试2: 插入记录使总金额达到 SVIP
    INSERT INTO sells (fid, cid, quantity) VALUES (1, 1, 2000); -- 5*4000 = 20000
    SELECT 'Test2 - After Insert SVIP' AS Description, cid, level FROM customer WHERE cid = 1;

    -- 测试3: 删除记录降级到 VIP
    DELETE FROM sells WHERE cid = 1 AND fid = 1 LIMIT 1;
    SELECT 'Test3 - After Delete VIP' AS Description, cid, level FROM customer WHERE cid = 1;

    -- 测试4: 修改记录升级到 SVIP
    UPDATE sells SET quantity = 3000 WHERE cid = 1 AND fid = 1; -- 5*3000 = 15000 → VIP
    SELECT 'Test4 - After Update VIP' AS Description, cid, level FROM customer WHERE cid = 1;

    -- 测试5: 修改客户 cid 触发跨客户更新
    UPDATE sells SET cid = 2 WHERE cid = 1; -- John 降级为 normal，Alice 升级为 VIP
    SELECT 'Test5 - After Update CID (John)' AS Description, cid, level FROM customer WHERE cid = 1;
    SELECT 'Test5 - After Update CID (Alice)' AS Description, cid, level FROM customer WHERE cid = 2;
END //
DELIMITER ;
-- 调用存储过程
CALL test_triggers();