当前位置: 首页 > news >正文

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()
beginselect 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))
beginselect salary from employeeswhere 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)
beginselect min(salary) into msfrom 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))
beginselect salary into empsalaryfrom employeeswhere 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))
beginselect last_name into empnamefrom empswhere employee_id = (select manager_idfrom empswhere 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()
beginset @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_sqlfrom 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 自定义函数

相关文章:

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

文章目录 概述常见操作创建存储过程存储过程局部变量定义和赋值查看存储过程删除存储过程调用存储过程 示例-动态数据透视详细讲解总结参考资料 概述 Mysql 存储过程是一组预先编译的 sql 语句集合&#xff0c;它们被存储在数据库中&#xff0c;并可以被多次调用执行。存储过程…...

VuePress部署到GitHub Pages

一、git push自动部署 1、创建用于工作流的文件 在项目根目录下创建一个用于 GitHub Actions 的工作流 .yml 文件 name: docson:# 每当 push 到 main 分支时触发部署push:branches: [main]# 手动触发部署workflow_dispatch:jobs:docs:runs-on: ubuntu-lateststeps:- uses: a…...

git 本地仓库

本地仓库 start.bat 启动...

Hive实战:分科汇总求月考平均分

文章目录 一、实战概述二、提出任务三、完成任务&#xff08;一&#xff09;准备数据1、在虚拟机上创建文本文件2、上传文件到HDFS指定目录 &#xff08;二&#xff09;实现步骤1、启动Hive Metastore服务2、启动Hive客户端3、创建分区的学生成绩表4、按分区加载数据5、查看分区…...

快速搭建知识付费小程序,3分钟即可开启知识变现之旅

明理信息科技知识付费saas租户平台 在当今数字化时代&#xff0c;知识付费已经成为一种趋势&#xff0c;越来越多的人愿意为有价值的知识付费。然而&#xff0c;公共知识付费平台虽然内容丰富&#xff0c;但难以满足个人或企业个性化的需求和品牌打造。同时&#xff0c;开发和…...

【计算机图形学划重点】第一讲-Pipeline and Introduction

基础知识 Vertex&#xff08;顶点&#xff09; define the location of primitives in space, and consists of vertex stream. 顶点用于定义空间中基本图形&#xff08;primitives&#xff09;的位置。它包含了一个顶点流&#xff08;vertex stream&#xff09;&#xff0c…...

面试题-DAG 有向无环图

有向无环图用于解决前后依赖问题&#xff0c;在Apollo中用于各个组件的依赖管理。 在算法面试中&#xff0c;有很多相关题目 比如排课问题&#xff0c;有先修课比如启动问题&#xff0c;需要先启动1&#xff0c;才能启动2 概念 顶点&#xff1a; 图中的一个点&#xff0c;比…...

vite + vue3引入ant design vue 报错

npm install ant-design-vue --save下载插件并在main.ts 全局引入 报错 解决办法一&#xff1a; main.ts注释掉全局引入 模块按需引入 解决办法二 将package.json中的ant-design-vue的版本^4.0.0-rc.4改为 ^3.2.15版本 同时将将package-lock.json中的ant-design-vue的版本…...

使用EasyPoi导入数据并返回失败xls

添加依赖 <!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-base --> <dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.4.0</version> </dependency> 工…...

机械配件移动商城课程概述

项目介绍 开发准备 任务 开源库介绍 框架搭建 工具类...

prometheus-docker 快速安装

镜像加速 sudo mkdir -p /etc/docker sudo tee /ect/docker/daemon.json << "EOF" {"register-mirros": ["http://hub-mirror.c.163.com"] } EOF安装docker export DOWNLOAD_URL"http://mirrors.163.com/docker-ce" curl -fsSl…...

RabbitMQ 核心概念(交换机、队列、路由键),队列类型等介绍

RabbitMQ 核心概念(交换机、队列、路由键)&#xff0c;队列类型等介绍 RabbitMQ 是一个消息队列系统&#xff0c;它的核心概念包括交换机&#xff08;Exchange&#xff09;、队列&#xff08;Queue&#xff09;和路由键&#xff08;Routing Key&#xff09;&#xff0c;它们一起…...

1001 害死人不偿命的(3n+1)猜想

卡拉兹(Callatz)猜想&#xff1a; 对任何一个正整数 n&#xff0c;如果它是偶数&#xff0c;那么把它砍掉一半&#xff1b;如果它是奇数&#xff0c;那么把 (3n1) 砍掉一半。这样一直反复砍下去&#xff0c;最后一定在某一步得到 n1。卡拉兹在 1950 年的世界数学家大会上公布了…...

七、HTML 文本格式化

一、HTML 文本格式化 加粗文本斜体文本电脑自动输出 这是 下标 和 上标 <!DOCTYPE html> <html><head><meta charset"utf-8"><title>HTML文本格式化</title> </head><body><b>加粗文本</b><br>…...

OSI 模型和 TCP/IP 模型的异同

开放式系统互联模型&#xff08;OSI&#xff09;是一个参考标准&#xff0c;解释协议相互之间应该如何相互作用。TCP/IP协议是美国国防部发明的&#xff0c;是让互联网成为了目前这个样子的标准之一 OSI&#xff1a;物理层&#xff0c;数据链路层&#xff0c;网络层&#xff0…...

创新性文生视频模型,南洋理工开源FreeInit

文本领域的ChatGPT&#xff0c;画图领域的Midjourney都展现出了大模型强大的一面&#xff0c;虽然视频领域有Gen-2这样的领导者&#xff0c;但现有的视频扩散模型在生成的效果中仍然存在时间一致性不足和不自然的动态效果。 南洋理工大学S实验室的研究人员发现&#xff0c;扩散…...

linux的页缓存page cache

目录 如何查看系统的 Page Cache&#xff1f; 为什么 Linux 不把 Page Cache 称为 block cache&#xff1f; Page Cache 的优劣势 Page Cache 的优势 加快数据访问 减少 IO 次数&#xff0c;提高系统磁盘 I/O 吞吐量 Page Cache 的劣势 由于我们开发的程序要运行的话一般…...

数字IC后端实现之Innovus TA-152错误解析(分频generated clock定义错误)

**ERROR: (TA-152): A latency path from the ‘Fall’ edge of the master clock at source pin… Error Code TA-152 在数字IC后端实现innovus中我们经常会看到这类Error&#xff0c;具体信息如下所示。 Error Message **ERROR: (TA-152): A latency path from the ‘Fa…...

虹科方案丨从困境到突破:TigoLeap方案引领数据采集与优化变革

来源&#xff1a;虹科工业智能互联 虹科方案丨从困境到突破&#xff1a;TigoLeap方案引领数据采集与优化变革 原文链接&#xff1a;https://mp.weixin.qq.com/s/H3pd5G8coBvyTwASNS_CFA 欢迎关注虹科&#xff0c;为您提供最新资讯&#xff01; 导读 在数字化工厂和智能制造时…...

自检服务器,无需服务器、不用编程。

自检服务器&#xff0c;无需服务器、不用编程。 大家好&#xff0c;我是JavaPub. 这几年自媒体原来热&#xff0c;很多人都知道了个人 IP 的重要性。连一个搞中医的朋友都要要做一个自己的网站&#xff0c;而且不想学编程、还不想花 RMB 租云服务。 老读者都知道&#xff0c…...

反向工程与模型迁移:打造未来商品详情API的可持续创新体系

在电商行业蓬勃发展的当下&#xff0c;商品详情API作为连接电商平台与开发者、商家及用户的关键纽带&#xff0c;其重要性日益凸显。传统商品详情API主要聚焦于商品基本信息&#xff08;如名称、价格、库存等&#xff09;的获取与展示&#xff0c;已难以满足市场对个性化、智能…...

Debian系统简介

目录 Debian系统介绍 Debian版本介绍 Debian软件源介绍 软件包管理工具dpkg dpkg核心指令详解 安装软件包 卸载软件包 查询软件包状态 验证软件包完整性 手动处理依赖关系 dpkg vs apt Debian系统介绍 Debian 和 Ubuntu 都是基于 Debian内核 的 Linux 发行版&#xff…...

React19源码系列之 事件插件系统

事件类别 事件类型 定义 文档 Event Event 接口表示在 EventTarget 上出现的事件。 Event - Web API | MDN UIEvent UIEvent 接口表示简单的用户界面事件。 UIEvent - Web API | MDN KeyboardEvent KeyboardEvent 对象描述了用户与键盘的交互。 KeyboardEvent - Web…...

GitHub 趋势日报 (2025年06月08日)

&#x1f4ca; 由 TrendForge 系统生成 | &#x1f310; https://trendforge.devlive.org/ &#x1f310; 本日报中的项目描述已自动翻译为中文 &#x1f4c8; 今日获星趋势图 今日获星趋势图 884 cognee 566 dify 414 HumanSystemOptimization 414 omni-tools 321 note-gen …...

【Oracle】分区表

个人主页&#xff1a;Guiat 归属专栏&#xff1a;Oracle 文章目录 1. 分区表基础概述1.1 分区表的概念与优势1.2 分区类型概览1.3 分区表的工作原理 2. 范围分区 (RANGE Partitioning)2.1 基础范围分区2.1.1 按日期范围分区2.1.2 按数值范围分区 2.2 间隔分区 (INTERVAL Partit…...

大数据学习(132)-HIve数据分析

​​​​&#x1f34b;&#x1f34b;大数据学习&#x1f34b;&#x1f34b; &#x1f525;系列专栏&#xff1a; &#x1f451;哲学语录: 用力所能及&#xff0c;改变世界。 &#x1f496;如果觉得博主的文章还不错的话&#xff0c;请点赞&#x1f44d;收藏⭐️留言&#x1f4…...

Python 训练营打卡 Day 47

注意力热力图可视化 在day 46代码的基础上&#xff0c;对比不同卷积层热力图可视化的结果 import torch import torch.nn as nn import torch.optim as optim from torchvision import datasets, transforms from torch.utils.data import DataLoader import matplotlib.pypl…...

ubuntu22.04 安装docker 和docker-compose

首先你要确保没有docker环境或者使用命令删掉docker sudo apt-get remove docker docker-engine docker.io containerd runc安装docker 更新软件环境 sudo apt update sudo apt upgrade下载docker依赖和GPG 密钥 # 依赖 apt-get install ca-certificates curl gnupg lsb-rel…...

React从基础入门到高级实战:React 实战项目 - 项目五:微前端与模块化架构

React 实战项目&#xff1a;微前端与模块化架构 欢迎来到 React 开发教程专栏 的第 30 篇&#xff01;在前 29 篇文章中&#xff0c;我们从 React 的基础概念逐步深入到高级技巧&#xff0c;涵盖了组件设计、状态管理、路由配置、性能优化和企业级应用等核心内容。这一次&…...

海云安高敏捷信创白盒SCAP入选《中国网络安全细分领域产品名录》

近日&#xff0c;嘶吼安全产业研究院发布《中国网络安全细分领域产品名录》&#xff0c;海云安高敏捷信创白盒&#xff08;SCAP&#xff09;成功入选软件供应链安全领域产品名录。 在数字化转型加速的今天&#xff0c;网络安全已成为企业生存与发展的核心基石&#xff0c;为了解…...