更新book表的数据记录,把可用数量(available字段)的值更改为库存总量(number字段)减去该书的在借数量。(2)使用存储过程,思路如下: 2.1 查询在借图书的在借数量 2.2 逐行读取查询结果中的书号和在借量到变量sh和zjl, 更新book表中书号等于sh的数据记录,设置 available=number-zjl; 2.3 更新book表中available字段为空的数据记录,设置 available=number;book表字段有bno,bname,number,available borrow表字段有bno,rdate 在借图书的在借数量(rdate为null表示未归还,即处于在借状态)给出实现的sql语句,要求分号改为$结尾
时间: 2023-12-03 19:42:25 浏览: 26
更新book表的数据记录:
```
UPDATE book
SET available = number - (
SELECT COUNT(*)
FROM borrow
WHERE borrow.bno = book.bno AND borrow.rdate IS NULL
);
UPDATE book
SET available = number
WHERE available IS NULL;
```
使用存储过程:
```
CREATE PROCEDURE update_available()
BEGIN
DECLARE sh CHAR(10);
DECLARE zjl INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR
SELECT bno, COUNT(*) AS zjl
FROM borrow
WHERE rdate IS NULL
GROUP BY bno;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO sh, zjl;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE book
SET available = number - zjl
WHERE bno = sh;
END LOOP;
CLOSE cur;
UPDATE book
SET available = number
WHERE available IS NULL;
END$
```
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![7z](https://img-home.csdnimg.cn/images/20210720083312.png)