18. Mysql 存储过程，实现动态数据透视

概述

Mysql 存储过程是一组预先编译的 sql 语句集合，它们被存储在数据库中，并可以被多次调用执行。存储过程可以接受参数、执行复杂的逻辑操作，并返回结果或修改数据库的状态。

存储过程有以下几个优点：

1. 代码复用：存储过程可以将常用的操作逻辑封装起来，以便在多个地方重复使用，避免重复编写相同的 sql 语句。

2. 提高性能：存储过程在编译时进行优化，可以减少通信开销，提高数据库的执行效率。

3. 安全性：存储过程可以控制对数据库的访问权限，只暴露必要的接口，提高数据的安全性。

4. 简化操作：存储过程可以执行复杂的数据库操作，减少客户端与数据库之间的交互次数，简化了客户端的代码。

常见操作

创建存储过程

使用 create procedure 语句来创建存储过程，语法如下：

create procedure procedure_name ([in|out|inout] parameter_name data_type [, ...])
begin
    -- 存储过程的代码逻辑
end;

其中，procedure_name是存储过程的名称，parameter_name是存储过程的参数名，data_type是参数的数据类型。 in、out 或 inout 类型解释如下：，分别表示输入参数、输出参数和输入输出参数。

• in ：表示当前参数为输入参数，存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 in 。
• out ：表示当前参数为输出参数，执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值。
• inout ：表示当前参数既可以为输入参数，也可以为输出参数。

存储过程局部变量定义和赋值

局部变量的定义和赋值，在上一节有提到过，具体可查看：Mysql 变量的使用。

declare <变量名> <变量类型> default <默认值>;
set <变量名>=<值>;

查看存储过程

# 查看存储过程和函数的创建信息
show create procedure 存储过程名称;
# 查看存储过程状态信息
show procedure status like '%max_salary%';

删除存储过程

删除是必修课，在任何一个DDL语句中都必须学习创建和删除，语法如下：

drop procedure [if exists] 存储过程名称;

调用存储过程

使用 call 语句来调用存储过程，procedure_name是存储过程的名称，parameter_value是存储过程的参数值。参数值可以是常量、变量或表达式。

call procedure_name([parameter_value, ...]);

示例

没有任何参数情况

drop procedure if exists employee_avg_salary;
create procedure employee_avg_salary()
begin
	select avg(salary) from emps;
end;
call employee_avg_salary();

存在 in 参数情况

drop procedure if exists show_someone_salary;
create procedure show_someone_salary(in empname varchar(20))
begin
	select salary from employees
	where last_name = empname;
end;
CALL show_someone_salary('Abel');

存在 out 参数情况

drop procedure if exists show_min_salary;
create procedure show_min_salary(out ms double)
begin
	select min(salary) into ms
	from emps;
end;
call show_min_salary(@ms);-- 调用
select @ms;-- 查看输出变量

存在 in 和 out 参数情况

drop procedure if exists show_someone_salary2;
create procedure show_someone_salary2(in empname varchar(20),out empsalary decimal(10,2))
begin
	select salary into empsalary
	from employees
	where last_name = empname;
end;
call show_someone_salary2('abel',@empsalary);-- 调用
select @empsalary;-- 查看输出值

存在 inout 参数情况

drop procedure if exists show_mgr_name;
create procedure show_mgr_name(inout empname varchar(25))
begin
	select last_name into empname
	from emps
	where employee_id = (
				select manager_id
				from emps
				where last_name = empname
				);
end;
set @empname := 'Abel';
call show_mgr_name(@empnam);
select @empnam;

示例-动态数据透视详细讲解

数据准备和需求

准备一张产品销售表，其中产品会随着新品发布会增加，需要是我想要看到区域对应每个产品的金额，并且新增加的产品也要透视。

create table sql_test1.sales
(
    id           int comment '销售id',
    product_name varchar(255) comment '产品名称',
    amount       double comment '金额',
    region       varchar(255) comment '区域',
    create_time  datetime default current_timestamp null comment '创建时间'
);
insert into sql_test1.sales(id,product_name,amount,region) values (1,'Product A',2000,'North');
insert into sql_test1.sales(id,product_name,amount,region) values (2,'Product B',3000,'South');
insert into sql_test1.sales(id,product_name,amount,region) values (3,'Product A',1500,'East');
insert into sql_test1.sales(id,product_name,amount,region) values (4,'Product C',2500,'West');
insert into sql_test1.sales(id,product_name,amount,region) values (5,'Product B',1800,'North');

数据测试和实现

首先，查看现有产品对应区域的金额现状。

select region
     , sum(case when product_name = 'Product A' then amount else 0 end) as `Product A`
     , sum(case when product_name = 'Product B' then amount else 0 end) as `Product B`
     , sum(case when product_name = 'Product C' then amount else 0 end) as `Product C`
from sql_test1.sales
group by region;
+--------+-----------+-----------+-----------+
| region | Product A | Product B | Product C |
+--------+-----------+-----------+-----------+
| North  |      2000 |      1800 |         0 |
| South  |         0 |      3000 |         0 |
| East   |      1500 |         0 |         0 |
| West   |         0 |         0 |      2500 |
+--------+-----------+-----------+-----------+

其次，我们利用上一节 Mysql 动态 sql ，让代码自动生成要执行的动态 sql 。\n\t 表示换行符和制表符。

# 录入新产品数据
insert into sql_test1.sales(id,product_name,amount,region) values (1,'Product D',2000,'North');
# 编辑动态sql进行测试
set @dynamic_column_sql = null; -- 定义动态列的变量
select group_concat(distinct concat('max(case when product_name = \'',product_name,'\' then amount else 0 end) as `',product_name, '`') separator '\n\t, ') into @dynamic_column_sql
from sql_test1.sales;-- 利用 group_concat 函数拼接出需要透视的动态列文本，并更新@dynamic_column_sql变量
set @pivot_sql = concat('select region\n\t, ', @dynamic_column_sql, '\nfrom sql_test1.sales \ngroup by region;');-- 整体拼接
select @pivot_sql;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @pivot_sql                                                                                                                                                                                                                                                                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| select region
	, max(case when product_name = 'Product A' then amount else 0 end) as `Product A`
	, max(case when product_name = 'Product B' then amount else 0 end) as `Product B`
	, max(case when product_name = 'Product C' then amount else 0 end) as `Product C`
	, max(case when product_name = 'Product D' then amount else 0 end) as `Product D`
from sql_test1.sales 
group by region; |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

最后，我们看到上一步拼接的动态 sql 自动添加了新产品 Product D 的汇总情况，这就是我们要执行的，进一步封装成存储过程，简单执行。

drop procedure if exists dynamic_pivot;
create procedure dynamic_pivot()
begin
    set @dynamic_column_sql = null;
    select group_concat(distinct concat('max(case when product_name = \'',product_name,'\' then amount else 0 end) as `',product_name, '`') separator '\n\t, ') into @dynamic_column_sql
    from sql_test1.sales;
    set @pivot_sql = concat('select region\n\t, ', @dynamic_column_sql, '\nfrom sql_test1.sales \ngroup by region;');
    prepare stmt from @pivot_sql;
    execute stmt;
    deallocate prepare stmt;
end;
call dynamic_pivot();

创建存储过程后，不需要改任何代码，每次只需要 call dynamic_pivot();就可以轻松查看区域对应的产品金额了。

总结

存储过程是MySQL中一种非常有用的数据库对象，可以提高数据库的性能、代码的复用性和数据的安全性。除了实现动态数据透视，它的作用远不止这些，多去实践操作，我相信你会发现更多妙用。

参考资料

• Mysql 变量的使用
• Mysql 动态SQL
• MySQL Documentation Stored Procedures
• MySQL Tutorial Stored Procedures
• Mysql 自定义函数