1 mysql存储过程,游标,以及借还图书简单案例分析

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.30,12,'要凉凉了么');
insert into books(book_name,book_author,book_price,book_stock,book_desc)
VALUES('python','不凉', 38.30,12,'怎么会啊么');

create table students(
 stu_num char(8) 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('1111','李四','男',22);
insert into students(stu_num,stu_name,stu_gender,stu_age) 
values('1021','厨刀','男',23);
insert into students(stu_num,stu_name,stu_gender,stu_age) 
values('1031','张麻子','男',21);

-- 使用存储过程实现借书
-- 哪个学生借了那些书,借了多少本?
-- 1 保存借书记录
-- 2 修改图书过程
-- 3 创建借书记录表
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 DATETIME not null,
 constraint FK_RECORDS_STUDENTS FOREIGN KEY(SNUM) REFERENCES students(stu_num),
 constraint fk_records_books foreign key(bid) references books(book_id)
);

-- 创建存储过程
-- 1 学号 2 图书编号 3 借书数量 4 借书状态
create procedure proc_borrow_book(in num char(4),in id int ,in m int ,out state int)
BEGIN
 -- 判断学号存在
 declare stu_count int DEFAULT(0);
 declare book_count int DEFAULT 0;
 declare book_stock_count int default 0;
 select count(stu_num) into stu_count from students where stu_num = num;
 -- 判断图书存在么
 select count(book_id) into book_count from books where book_id = id;
 if(stu_count > 0) THEN
   -- 学号存在 看库存
	 if book_count > 0 THEN
   	 SELECT book_stock into book_stock_count from books where book_id = id;
	   if book_stock_count >= m THEN
	     -- 有库存 就可以开始借书了
		   insert into records(snum,bid,borrow_num,is_return,borrow_date)  values (num,id,m,0,SYSDATE());
	  	 update books set book_stock = book_stock - m where book_id = id;
	     set state = 1;
	   ELSE
	     set state = 4;
	   end if;
	 else 
	   set state = 3;
	 end if;
 else	 
   set state = 2;
 end if;		
END;


-- 开始使用
set @borrow_state = 0;
call proc_borrow_book('1001',2,1,@state);
call proc_borrow_book('1001',1,1,@state);
select @state from dual;
 
-- 创建一个还书的存储过程
-- 1 书号 学生名字 数量 返回状态
-- state 1 成功 2 失败没找到书 3 失败没有这个学生
create procedure proc_return_book(in id INT,in num char(8),in m int, out state int)
BEGIN
  declare snum_count int default 0;
  declare id_count int default 0;
	select count(stu_num) into snum_count from students where stu_num = num;
	select count(book_id) into id_count from books where book_id = id;
	if snum_count > 0 THEN
	  if id_count > 0 THEN 
		  -- 有这本书和有这个人
			insert into records(snum,bid,borrow_num,is_return,borrow_date) values(num,id,m,1,SYSDATE());
			UPDATE books set book_stock = book_stock+m where book_id = id;
			set state = 1;
	  else 
		  set state = 2;
	  end if;
	else 
	 set state = 3;
  end if;
END;

set @return_state = 0;
call proc_return_book(1,'1001',1,@return_state);
select @return_state ;

-- 游标
-- 要是要创建一个存储过程,返回一个查询到
create procedure proc_test1(in id int,out result varchar(100))
BEGIN
  declare bname varchar(20);
  declare bauthor varchar(20);
  declare bprice decimal(10,2);
	select book_name,book_author,book_price into bname,bauthor,bprice from books where book_id = id;
	set result = concat_ws('~',bname,bauthor,bprice);
end;
set @r = '';
call proc_test1(2,@r);
select @r;


create procedure proc_test2(out result varchar(100))
BEGIN
  declare bname varchar(20);
  declare bauthor varchar(20);
  declare bprice decimal(10,2);
	declare cursor_name cursor for SELECT book_name,book_author,book_price from books;
	-- 查询以后返回的是一个结果集
	-- 游标就是一个迭代器一样的东西,以此取出结果集
	select book_name,book_author,book_price   from books where book_id = id;
	set result = concat_ws('~',bname,bauthor,bprice);
end;








create procedure proc_test2(out result varchar(100))
BEGIN
  declare bname varchar(20);
  declare bauthor varchar(20);
  declare bprice decimal(10,2);
	declare num int;
	declare i int;
	declare str varchar(50);
	declare cursor_name cursor for SELECT book_name,book_author,book_price from books;
	select count(1) into num from books;
	-- 查询以后返回的是一个结果集
	-- 游标就是一个迭代器一样的东西,以此取出结果集
  open cursor_name;
	set i = 0;
	
	while i < num do
	  FETCH cursor_name into bname,bauthor,bprice;-- 提取游标数据,提取之后自动往后遍历
		set i = i + 1;
		select concat_ws('~',bname,bauthor,bprice) into str;
		set result = concat_ws(',',result,str);
	end while;	
	
	-- close you CURSOR
 close cursor_name;
end;

set @a = '';
call proc_test2(@a);
select @a;