【实验7 存储子程序】

-- （一）函数
--    1、创建一个函数，有一个参数用来接收出版社名称，输出统计给定出版社图书的平均价格。
create function avg_book_price (publisher_name varchar(20))
returns DOUBLE
begin
     declare d_price DOUBLE;
     select avg(book.price) into d_price
     from book
     where book.publisher=publisher_name;
     return d_price;
end
select avg_book_price("机械工业出版社")

--    2、创建一个函数，输入图书类别，输出该类别中价格最高的图书名称及价格。
create function max_price_book(categoty_name varchar(20))
returns VARCHAR(30)
begin
     declare d_book VARCHAR(30);
     select CONCAT('书名:',book.book_name,',价格：',max(book.price)) into d_book
     from book
     where book.category=categoty_name;
     return d_book;
end

select max_price_book("计算机")

-- （二）存储过程
-- 3、创建一个存储过程，该存储过程有两个输入参数用来接收读者编号和书号，
-- 一个输出参数用于获取相应读者编号和书号对应的借书时间。
create procedure book_borrow_pro(in reader_num char(10), book_num char(10),
                                 out book_reader_num_time varchar(100))
begin
select
CONCAT('读者编号：',reader_number,'，书籍编号：',book_number,'，借书时间：',borrow_time) into book_reader_num_time
from borrow_book
where borrow_book.reader_number=reader_num and borrow_book.book_number=book_num;
end

CREATE PROCEDURE test_book_proc( ) 
BEGIN
declare d_str VARCHAR(100);
call book_borrow_pro('1002','1001',d_str);
select d_str;
END 

call test_book_proc()

-- 4、创建一个存储过程，统计每位读者的已借书数量，用游标实现。
ALTER table reader add book_borrowed int;
CREATE PROCEDURE count_book()
BEGIN
     DECLARE done INT DEFAULT 0;
     DECLARE v_rno,v_name CHAR(10);
     DECLARE v_number INT;
     # 创建游标，并存储数据 
     DECLARE cur1 CURSOR FOR SELECT  reader_number FROM reader;
     # 游标中的内容执行完后将done设置为1  
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
     # 打开游标
     OPEN cur1;
       REPEAT
          # 取游标中的值 
          FETCH cur1 INTO v_rno;
          SELECT COUNT(book_number) INTO v_number FROM borrow_book WHERE reader_number=v_rno;
          IF not done THEN
               UPDATE reader SET book_borrowed=v_number WHERE reader_number=v_rno;
          END IF;
     UNTIL done END REPEAT;
     # 释放游标 
     CLOSE cur1;    
END
call count_book()
desc reader

-- （三）触发器
-- 5、为读者表创建一个insert触发器，要求“插入的读者记录中，性别只能为男或女；若为其它值，则将其设置为男”。

create trigger init
before insert on reader
for each row
IF new.gender != '男' and new.gender != '女'THEN
       set new.gender='男';
END IF;
INSERT INTO reader VALUES('1012','月亮下的对白','计算机学院','女','2323',0);

DROP trigger init




-- 6、设有以下两张表
-- 创建两张表，请写一个触发器，在订单表生成一条订单记录以后，商品表中的商品个数(num)自动减少相应的数量。
create table g
(
id int primary key auto_increment,
name varchar(20),
num int           
);
create table o
(
oid int primary key auto_increment,
gid int,
number int,
foreign key(gid) references g(id)
);

create trigger num
AFTER insert on o
for each row
BEGIN
UPDATE g SET num = num-new.number WHERE new.gid=id;
END

INSERT INTO g VALUES (1,'月亮下的对白',100);
INSERT INTO o VALUES (1,'月亮下的对白',100);

DROP trigger num