什么是存储过程?
存储过程(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 | |
---|---|---|
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 | 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, '欢迎你!');