sql-存储过程的基本使用

什么是存储过程？

存储过程（Stored Procedure）是为了完成特定功能的SQL语句集。经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行。类似于编程语言中的方法或函数。

存储过程的优点：

1.存储过程是对SQL语句的封装，增强可复用性
2.存储过程可以隐藏复杂的业务/商业逻辑
3.存储过程支持接收参数，并返回运算结果

存储过程的缺点：

1.可移植性差（如果更换数据库，要重写存储过程）
2.难以调试和扩展
3.无法使用Explain对存储过程进行分析
4.某些互联网公司开发手册中禁止使用存储过程

存储过程示例

1.求两数之和

--“求两数之和”存储过程定义
delimiter //  --声明语句结束符，将语句结束符设为//，当遇到//时，说明语句执行结束，避免与存储过程里的;混淆。（不然会报错）

create procedure my_sum(in a int, in b int, out result int)  --定义存储过程my_sum; 定义（in）入参a,b; 定义（out）出参result
begin  --存储过程开始
	set result= a + b;  --用set给变量赋值
end  --存储过程结束
//  --语句执行结束

delimiter ;  --再将语句结束符改回;

--存储过程调用
call my_sum(10, 20, @result); --调用存储过程 
select @result;  --select语句取结果 result=30

2.计算1+2+…+n的和

--存储过程定义：计算1+2+...+n
delimiter //

create procedure my_n_sum(in n int, out result int) --存储过程定义
begin
	declare i int default 1;  --声明i=1
	declare sum int default 0;  --声明sum=0

	while i <= n do  --计算1+2+...+n
		set sum = sum + i;
		set i = i + 1;
	end while

	set result = sum;  --将结果传给result
end;
//

delimiter ;

--存储过程定义
call my_n_sum(100, @result);
select @result;  --result=5050

存储过程实战

给指定用户发邮件通知

1.数据描述

此案例中用到两张表：用户表（uesr_info）和邮件表（email_info）

drop table if exists user_info;  --表存在时删除
drop table if exists email_info;

create table user_info(  --创建用户表
	id int not null auto_increment primary key,  --定义id为int型、不为空、自增长、主键
	name varchar(30),  --name是变长字符，最长为30个字节
	emailvarchar(50)
);

/*插入两条记录*/
insert into user_info(id, name, email) values(1, '小红', 'xiaohong@qq.com');
insert into user_info(id, name, email) values(2, '小明', 'xiaoming@qq.com');

create table email_info(  --创建邮件表
	id int not null auto_increment primary key,
	email varchar(50),
	content text,  --文件内容
	send_time datetime  --邮件发送时间
);

此时user_info表为：

id	name	email
1	小红	xiaohong@qq.com
2	小明	xiaoming@qq.com

email_info表为空。

2.存储过程定义

我们需要根据传入的用户id和内容发送邮件。

#处理过程
--1.根据传入的id查找用户邮箱
select email from user_info where id=1;  --假设传入id为1
--2.根据查到的email修改email_info表，以发送内容
insert into email_info(email, content, send_time) values('xiaohong@qq.com', '欢迎你！', now());

此时email_info表为：

id	email	content	send_time
1	xiaohong@qq.com	欢迎你!	2021-01-08 23:19:02

下面用存储过程来实现邮件的发送（即上面处理过程的两步）：

--“根据用户id和邮件内容content给用户发邮件”的存储过程实现
delimiter //

create procedure send_email(in user_id int, in content text)
begin
	/*根据用户id查询邮箱email*/
	set @user_email = (select email from user_info where id = user_id);

	/*模拟发送邮件*/
	insert into email_info(email, content, send_time) values(@user_email, content, mow());
end;
//

delimiter;

--存储过程调用
call send_email(1, '欢迎你！');