mysql存储过程实例

读者信息表

创建读者信息表

create table reader(
card_id char(18) primary key,
name varchar(20) not null,
sex enum('男','女','保密') default'保密',
age tinyint not null,
phone_number char(11) not null,
balance decimal(6,2) default 200
);

插入读者记录

insert into reader values('330100199201231104','小明','男',29,'15787223423',300);
insert into reader values('330100199201231105','小红','女',21,'15787223424',220);
insert into reader values('330100199201231106','王迷','男',19,'15787223425',310);
insert into reader values('330100199201231107','李思','女',24,'15787223426',230);
insert into reader values('330100199201231108','小王','女',32,'15787223427',240);
insert into reader values('330100199201231109','王霞','女',26,'15787223428',300);

查看下读者信息表

实例1：查询语句

创建一个查询读者的姓名、手机号、余额的存储过程

create procedure selectproc()
select name,phone_number,balance from reader;

call selectproc();

运行结果：

实例2：myql操作及查询

删除一个读者，并显示剩余读者的个数

delimiter//
create procedure deletepro(in myid char(18),out num int)
begin
delete from reader where card_id=myid;
select count(card_id) into num from reader;
end//
delimiter;

call deletepro('330100199201231108',@num);
select @num;

运行结果：

实例3：参数-数字交换

创建一个存储过程，实现数字交换

delimiter//
create procedure switchnum(inout num1 int,inout num2 int)
begin
declare temp int;
set temp=num1;
set num1=num2;
set num2=temp;
end//
delimiter;

set @num1=4,@num2=7;
call switchnum(@num1,@num2);
select @num1,@num2;

运行结果：

实例4：if判断-比较数字大小

delimiter//
create procedure comparenum(in num1 int,in num2 int,out result varchar(20))
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;

set @num1=3,@num2=5;
call comparenum(@num1,@num2,@result);
select @result;

运行结果：

实例5：case…when判断-比较数字大小

delimiter//
create procedure comparenum(in num1 int,in num2 int,out result varchar(20))
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;

call comparenum(3,1,@result);
select @result;

运行结果：

实例6：while…do循环-向表中插入数据

设计一个存储过程，向图书类别中插入n条数据。

创建图书类别表：

create table category(
id int(10) primary key,
name varchar(20) not null,
parent_id int(10) not null
);

存储过程，插入数据

delimiter//
create procedure insertpro(in num int)
begin
declare i int default 1;
while i<=num do
	insert into category values(i,concat('图书类别',i),floor(rand()*i));
	set i=i+1;
end while;
end//
delimiter;

call insertpro(10);
select*from category;

运行结果：