创建存储过程,对于读者编号,逐条检查借阅表中该读者的借阅情况:换水日期为空记录,借阅天数=系统日期-借阅日期,如借阅日期天数小于15天,则输出“正常”;借阅天数大于等于15天并且小于等于30天,则输出“通知还书”;借阅天数大于30天,则输出“逾期”。调用该存储过程,测试读者编号为“0001”的借阅情况
时间: 2024-01-03 20:02:56 浏览: 135
一个自动产生编号的存储过程
好的,根据您的要求,我已经创建了一个名为 `check_borrow_status` 的存储过程,如下所示:
```sql
CREATE PROCEDURE check_borrow_status(IN reader_id VARCHAR(10))
BEGIN
DECLARE borrow_date DATE;
DECLARE return_date DATE;
DECLARE diff INT;
DECLARE status VARCHAR(20);
SELECT MAX(borrow_date) INTO borrow_date
FROM borrow_table
WHERE reader_id = reader_id AND return_date IS NULL;
IF borrow_date IS NULL THEN
SELECT CONCAT('Reader ', reader_id, ' has no borrowed books') AS 'status';
ELSE
SET diff = DATEDIFF(CURRENT_DATE(), borrow_date);
IF diff < 15 THEN
SET status = 'Normal';
ELSEIF diff >= 15 AND diff <= 30 THEN
SET status = 'Notify to return book';
ELSE
SET status = 'Overdue';
END IF;
SELECT CONCAT('Reader ', reader_id, ' is ', status, ', borrow date is ', borrow_date) AS 'status';
END IF;
END;
```
您可以通过调用该存储过程并传入参数 reader_id = '0001' 来测试该读者的借阅情况,如下所示:
```sql
CALL check_borrow_status('0001');
```
注意:您需要将 `borrow_table` 替换为您实际使用的借阅表名称。
阅读全文