SQL(四) 游标实验、存储过程、函数实验

1.将视图实验中score表中的数据通过以下命令复制到新建的表score_copy中，然后通过带有游标的存储过程对成绩按下面的规则进行更新：80~100，更改为5；60~80，更改为3；低于60分更新为0。

Create table score_copy as select * from score;
DELIMITER //
CREATE PROCEDURE update_scores()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE s_id INT;
    DECLARE s_score INT;
    DECLARE cur CURSOR FOR SELECT score_id, grade FROM score_copy;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO s_id,s_score;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF s_score >= 80 AND s_score <= 100 THEN
            UPDATE score_copy SET grade = 5 WHERE score_id = s_id;
        ELSEIF s_score >= 60 AND s_score < 80 THEN
            UPDATE score_copy SET grade = 3 WHERE score_id = s_id;
        ELSE
            UPDATE score_copy SET grade = 0 WHERE score_id = s_id;
        END IF;
    END LOOP;

    CLOSE cur;
END//

DELIMITER ;
CALL update_scores();
SELECT * FROM score_copy;

2.

将视图实验中score表中每个学生得最高分的元组加入score_max（结构score）表中。score_max建表命令如下：

Create table score_max as select * from score where 1=2;

特别提示：要求编写存储过程后，用call命令调用过程，并用select命令显示scoremax表中的内容。

Create table score_max as select * from score where 1=2;
DELIMITER //

CREATE PROCEDURE insert_max_scores()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE s_id INT;
    DECLARE s_sno VARCHAR(10);
    DECLARE s_cno VARCHAR(10);
    DECLARE s_score VARCHAR(10);
    DECLARE s_max_score VARCHAR(10);
    DECLARE cur CURSOR FOR SELECT score_id,sno,cno,grade FROM score;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO s_id,s_sno,s_cno,s_score;
        IF done THEN
            LEAVE read_loop;
        END IF;

        select max(grade) into s_max_score from score where sno=s_sno;

        IF s_score = s_max_score THEN
            INSERT INTO score_max(score_id,sno,cno,grade) VALUES (s_id,s_sno,s_cno,s_max_score);
        END IF;
    END LOOP;

    CLOSE cur;
END//

DELIMITER ;

CALL insert_max_scores();
SELECT * FROM score_max;

3.

库中有表fibonacci，用来储存斐波拉契数列的前n项（Fibonacci表结构说明）：

斐波拉契数列的前5项为:0,1,1,2,3推导公式为：fib(n) = fib(n-1) + fib(n-2)。

请创建存储过程sp_fibonacci(in m int)，向表fibonacci插入斐波拉契数列的前m项，及其对应的斐波拉契数。fibonacci表初始值为一张空表。保证你的存储过程可以多次运行而不出错。

（斐波拉契数列的第一项索引号设为0，第二项为1，以此类推。测试时设置参数m为9）。

delimiter //
drop procedure sp_fibonacci;
create procedure sp_fibonacci(in m int)
begin
    DECLARE i INT DEFAULT 0;
    DECLARE n1 BIGINT DEFAULT 0;
    DECLARE n2 BIGINT DEFAULT 1;
    DECLARE temp BIGINT;
    truncate table fibonacci;
    INSERT INTO fibonacci (n, fibn) VALUES (0, 0);
    INSERT INTO fibonacci (n, fibn) VALUES (1, 1);
    SET i = 2;
    WHILE i < m DO
        SET temp = n1 + n2;
        SET n1 = n2;
        SET n2 = temp;
        INSERT INTO fibonacci (n, fibn) VALUES (i, temp);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;
CALL sp_fibonacci(9);
select *from fibonacci;

4.

设有基于图书馆数据库的4个基本表：

图书（书号，书名，作者，出版社，单价）

读者（读者号，姓名，性别，办公电话，部门，读者类别编号）

借阅（读者号，书号，借出日期，归还日期）

读者类别（读者类别编号，读者类别，允许借阅册数，允许借阅天数）

编写存储过程实现：根据读者号查询该读者的图书借阅的册数。（采用读者号“1205021”测试）

DELIMITER //
CREATE PROCEDURE checkBorrowLimit(IN in_reader_id VARCHAR(50), OUT out_result VARCHAR(20))
BEGIN
    DECLARE borrowed_count INT;
    DECLARE allowed_count INT;
    SELECT COUNT(*) INTO borrowed_count FROM 借阅 WHERE 读者号 = in_reader_id;
    SELECT 允许借阅册数 INTO allowed_count FROM 读者类别 WHERE 读者类别编号 = (SELECT 读者类别编号 FROM 读者 WHERE 读者号 = in_reader_id);
    IF borrowed_count < allowed_count THEN
        SET out_result = '可以继续借阅';
    ELSE
        SET out_result = '不可以继续借阅';
    END IF;
END//
DELIMITER ;
SET @result ='0';
CALL checkBorrowLimit('1205021', @result);
SELECT @result;

5.

设有基于图书馆数据库的4个基本表：

图书（书号，书名，作者，出版社，单价）

读者（读者号，姓名，性别，办公电话，部门，读者类别编号）

借阅（读者号，书号，借出日期，归还日期）

读者类别（读者类别编号，读者类别，允许借阅册数，允许借阅天数）

编写存储过程实现：

读者借阅图书时，需要查询是否超出允许借阅册数，如果超出则不能继续借阅图书，

试创建存储过程完成此查询功能。（提示：输入参数：读者号；输出："可以继续借阅"或"不可以继续借阅"，

使用读者号“1205021”测试）

DELIMITER //
CREATE PROCEDURE checkBorrowLimit(IN in_reader_id VARCHAR(50), OUT out_result VARCHAR(20))
BEGIN
    DECLARE borrowed_count INT;
    DECLARE allowed_count INT;
    SELECT COUNT(*) INTO borrowed_count FROM 借阅 WHERE 读者号 = in_reader_id;
    SELECT 允许借阅册数 INTO allowed_count FROM 读者类别 WHERE 读者类别编号 = (SELECT 读者类别编号 FROM 读者 WHERE 读者号 = in_reader_id);
    IF borrowed_count < allowed_count THEN
        SET out_result = '可以继续借阅';
    ELSE
        SET out_result = '不可以继续借阅';
    END IF;
END//
DELIMITER ;
SET @result ='0';
CALL checkBorrowLimit('1205021', @result);
SELECT @result;

6.

用create function语句创建符合以下要求的函数：依据读者号计算其所借图书数量。

函数名为：get_borrow_num。使用读者号“1205021”测试结果。

set global log_bin_trust_function_creators=1;
drop function IF EXISTS get_borrow_num;
delimiter $$
create function get_borrow_num(读者号 varchar(50))
returns int
begin
    declare borrow_num int;
    select count(*) into borrow_num from 借阅 where 借阅.读者号 = 读者号;
    return borrow_num;
end$$
delimiter ;
select get_borrow_num('1205021');