MySql教程(15)--MySQL存储

1 存储过程的介绍



– 创建一个查询图书的编号、书名、价格和库存的存储过程。
delimiter //
create procedure selectproc1()
begin
select book_id,book_name,price,store from bookinfo;
end//
delimiter ;

– 调用存储过程
call selectproc1();

2 创建和使用存储过程

– 创建查询图书编号、书名、图书类别的存储过程
delimiter //
create procedure proc1()
begin
select book_id,book_name,category from bookinfo t1
join bookcategory t2
on t1.book_category_id = t2.category_id;
end//
delimiter ;

call proc1();

– 设计一个存储过程，删除一个读者，并输出剩余读者的个数。
delimiter //
create procedure proc2(in cid char(18), out num int)
begin
delete from readerinfo where card_id = cid;
select count(card_id) into num from readerinfo;
end//
delimiter ;

select * from readerinfo;
call proc2(‘210210199901011111’, @num);
select @num;

– 设计一个存储过程，实现交换两个数的处理。
delimiter //
create procedure proc3(inout num1 int, inout num2 int)
begin
declare t int default 0;
set t = num1;
set num1 = num2;
set num2 = t;
end//
delimiter ;

set @n1 = 3, @n2 = 5;
call proc3(@n1,@n2);
select @n1,@n2;

– 删除存储过程
drop procedure proc1;
drop procedure if exists proc2;

3 创建复合结构的存储过程
– 设计比较两个数大小的存储过程。
delimiter //
create procedure proc5(in num1 int, in num2 int, out result varchar(30))
begin
if num1 = num2 then
set result = ‘num1等于num2’;
elseif num1>num2 then
set result = ‘num1大于num2’;
else
set result = ‘num1小于num2’;
end if;
end//
delimiter ;

delimiter //
create procedure proc5(in num1 int, in num2 int, out result varchar(30))
begin
case
when num1 = num2 then set result = ‘num1等于num2’;
when num1 > num2 then set result = ‘num1大于num2’;
else set result = ‘num1小于num2’;
end case;
end//
delimiter ;

– 设计一个向图书类别表插入100条测试数据的存储过程
select floor(rand()*5);

delimiter //
create procedure proc6()
begin
declare n int default 7;
while n<=107 do
insert into bookcategory values(n,concat(‘图书类别’,n),floor(rand()*n));
set n = n + 1;
end while;
end//
delimiter ;

call proc6();

4 存储过程与函数的区别