十、存储过程和函数
目录
存储过程
含义
一组预先编译好SQL语句的集合，理解成批处理语句，单比批处理强大的多。
优点
* 提高语句的重用性
* 简化操作
* 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
创建语法
create procedure 存储过程名(参数列表)
begin
存储过程体（合法有效的SQL语句）
end;
注意
1、参数列表包含三部分
参数模式 参数名 参数类型
举例：in param_name varchar(20)
参数模式：
in （入参）
out （返回值）
inout （入参和返回值）
2、如果存储过程体仅仅只有一句话，begin end关键字可以省略，存储过程中的每条SQL语句的结尾必须加分号。存储过程的结尾可以用 delimiter 重新设置
语法：delimiter 结束标记，举例如：delimiter $
调用语法
call 存储过程名(实参列表);
存储过程演示
1、空参列表
插入到 admin 表中五条记录
select * from admin; # 查询现有数据
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | zhangsan | 123456 |
+----+----------+----------+
# 创建存储过程
delimiter $
create procedure insert_admin()
begin
insert into admin(username, `password`) values
('111', '123456'),
('222', '123456'),
('333', '123456'),
('444', '123456'),
('555', '123456');
end $
# 执行存储过程
call insert_admin();
# 再次查看 admin 表信息
select * from admin;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | zhangsan | 123456 |
| 2 | 111 | 123456 |
| 3 | 222 | 123456 |
| 4 | 333 | 123456 |
| 5 | 444 | 123456 |
| 6 | 555 | 123456 |
+----+----------+----------+
2、有参列表(in)
创建存储过程，根据用户名查询对应数据
# 查看 admin 表信息
select * from admin;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | zhangsan | 123456 |
| 2 | 111 | 123456 |
| 3 | 222 | 123456 |
| 4 | 333 | 123456 |
| 5 | 444 | 123456 |
| 6 | 555 | 123456 |
+----+----------+----------+
# 创建存储过程
create procedure query_admin_by_name(in user_name varchar(255))
begin
select * from admin where username = user_name;
end;
# 执行存储过程
call query_admin_by_name('111');
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 2 | 111 | 123456 |
+----+----------+----------+
创建存储过程，根据用户名、面查询用户登录状态
# 创建存储过程
create procedure `check_login`(in user_name varchar(255), in `password` varchar(255))
begin
# 声明变量并初始化
declare result int default 0;
select
count(*) into result # 赋值
from admin a where a.username = user_name and a.`password` = `password`;
select if(result > 0, '成功', '失败');
end
# 执行存储过程
call check_login('111', '123456');
+----------+
| 登录结果 |
+----------+
| 成功 |
+----------+
# 执行存储过程
call check_login('111', '46513131');
+----------+
| 登录结果 |
+----------+
| 失败 |
+----------+
3、有返回值(out)
根据用户名，返回对应 id 编号
# 创建存储过程
create procedure `query_id_by_username`(in user_name varchar(255), out result_id int(11))
begin
select
id into result_id
from
admin
where username = user_name;
end
# 执行存储过程
call query_id_by_username('111', @result_id);
select @result_id;
+------------+
| @result_id |
+------------+
| 2 |
+------------+
call query_id_by_username('444', @result_id);
select @result_id;
+------------+
| @result_id |
+------------+
| 5 |
+------------+
4、带inout的存储过程
传入a和b两个值，最终a和b都乘以2返回
# 创建存储过程
create procedure test_inout(inout a int(11), inout b int(11))
begin
select (a * 2), (b * 2);
end;
# 调用存储过程
set @a = 2;
set @b = 4;
call test_inout(@a,@b);
+---------+---------+
| (a * 2) | (b * 2) |
+---------+---------+
| 4 | 8 |
+---------+---------+
存储过程的删除
语法
drop procedure 存储过程名;
存储过程的查看
语法
show create procedure 存储过程名;
函数
含义
一组预先编译好SQL语句的集合，理解成批处理语句，单比批处理强大的多。
优点
- 提高语句的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
和存储过程区别
- 存储过程：可以有0个返回，也可以有过个返回值，适合做一些批量插入、批量更新等
- 函 数：有且只有一个返回值，适合做处理数据后返回一个结果
创建语法
语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
注意
- 参数列表包含两部分：参数名、参数类型
- 函数体：肯定会有return语句，如果没有会报错，如果return语句
没有放在函数体最后也不报错，但不建议这样做 - 函数体中仅有一句话，则可以省略 begin end 语句
- 使用 delimiter 语句设置结束标记
调用语法
语法
select 函数名(参数列表);
实际案例
无参有返回
返回用户数
# 创建存储过程
create function count_admin() returns int
begin
declare result int default 0;
select count(*) into result from admin;
return result;
end;
# 调用函数
select count_admin();
+---------------+
| count_admin() |
+---------------+
| 6 |
+---------------+
有参有返回
根据用户名返回密码
# 创建存储过程
create function query_password_by_username(user_name varchar(20)) returns varchar(50)
begin
declare result varchar(50) default '';
select a.`password` into result
from admin a where a.username = user_name;
return result;
end
# 调用函数
select query_password_by_username('111');
+-----------------------------------+
| query_password_by_username('111') |
+-----------------------------------+
| 123456 |
+-----------------------------------+
函数的查看
show create function 函数名;
函数的删除
drop function 函数名;
