（89）课110--111：无返回值的过程函数的综合举例，更新学生的借书记录。

（185）素材准备 ：

++对应的创建代码 ：

create table books(
	book_id int primary key auto_increment,
	book_name varchar(50) not null,
	book_author varchar(20) not null,
	book_price decimal(10,2) not null,
	book_stock int not null,
	book_desc varchar(200)
)
-- 添加图书信息
insert into books(book_name,book_author,book_price,book_stock,book_desc) 
values('Java程序设计','乐乐',38.80,12,'乐乐带你学Java');
insert into books(book_name,book_author,book_price,book_stock,book_desc)
values('Java王者之路','周哥',44.40,9,'周哥，Java王者领路人');
-- 创建学生信息表
create table students(
	stu_num char(4)primary key,
	stu_name varchar(20)not null,
	stu_gender char(2)not null,
	stu_age int not null
)
-- 添加学生信 息
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1001','张三','男',20);
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1002','李芳','女',20);
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1003','王小明','男',20);
-- 借书记录表:
create table records(
	rid int primary key auto_increment,
	snum char(4) not null,
	bid int not null,
	borrow_num int not null,
	is_return  int not null, -- 0表示未归还 1表示已经归还
	borrow_date date not null,
	constraint FK_RECORDS_STUDENTS foreign key(snum) references students(stu_num),
	constraint FK_RECORDS_BOOKS foreign key(bid) REFERENCES books(book_id)
);

（186）

++老师创建的借书过程函数 ：

++精简版 ：

++对应的过程代码版 ：

CREATE PROCEDURE pBorrowBook(in stuID char(4), in bookID int, in num int, out state int)
BEGIN
	DECLARE stuFind   int DEFAULT 0;
	DECLARE bookFind  int DEFAULT 0;
	DECLARE bookStock int DEFAULT 0;
	
	SELECT COUNT(*) INTO stuFind FROM students WHERE students.stu_num = stuID;
	IF stuFind = 0 THEN
		set state := 2;           -- 无此学生
	ELSE
		SELECT COUNT(*) into bookFind FROM books WHERE books.book_id = bookID;
		IF bookFind = 0 THEN 
			SET state := 3;       -- 无此本书
		ELSE 
			SELECT books.book_stock into bookStock FROM books WHERE book_id = bookID;
			IF bookStock < num THEN
				set state := 4;   -- 库存不足
			ELSE 
				INSERT INTO records(snum, bid, borrow_num, is_return, borrow_date)
				VALUES (stuID, bookID, num, 0, now()); -- 或 sysdate()
				
				UPDATE books SET book_stock = book_stock - num where book_id = bookID;
				set state := 1;   -- 顺利借书
			END if;  -- IF bookStock < num THEN
		END if;      -- IF bookFind = 0 THEN 
	END IF;          -- IF stuFind = 0 THEN
end;

-- set @res = 0; 可见，用户自定义变量，不提前声明也没事
call pBorrowBook('1001', 2, 2, @res);
SELECT * FROM records;
SELECT @res;

SELECT * FROM students;
SELECT * FROM books;
SELECT * FROM records;

++测试结果 ：

（187）

谢谢