MySQL存储过程

• 存储过程示例：根据用户id和邮件内容content给用户发邮件

drop table if exists user_info;
drop table if exists email_info;
create table user_info(
 id int not null auto_increment primary key,
 name varchar(30),
 email varchar(50)
);
insert into user_info(id, name, email) values(1, '柳峰', 'liufeng@qq.com');
insert into user_info(id, name, email) values(2, '张三', 'zhangsan@qq.com');
create table email_info(
 id int not null auto_increment primary key,
 email varchar(50),
 content text,
 send_time datetime
);

delimiter //
create procedure sendEmail(in user_id int,in content text)
begin
	set @user_email=(select email from user_info where id=user_id);
	
	insert into email_info(email,content,send_time) values (@user_email,content,now());
end;
//
delimiter ;
call sendEmail(2,'delimiter ;要有空格');
select * from email_info;

• 存储过程实现1+2+3+…+n

delimiter //
create procedure t_02(in n int,out res int)
begin
	declare i int default 1;
	declare sum int default 0;
	while i<=n do
		set sum=sum+i;
		set i=i+1;
	end while;
	set res=sum;
end;
//
delimiter ;
call t_02(500,@res);
select @res;