当前位置: 首页 > 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…...

KubeSphere 容器平台高可用:环境搭建与可视化操作指南

Linux_k8s篇 欢迎来到Linux的世界&#xff0c;看笔记好好学多敲多打&#xff0c;每个人都是大神&#xff01; 题目&#xff1a;KubeSphere 容器平台高可用&#xff1a;环境搭建与可视化操作指南 版本号: 1.0,0 作者: 老王要学习 日期: 2025.06.05 适用环境: Ubuntu22 文档说…...

智慧工地云平台源码,基于微服务架构+Java+Spring Cloud +UniApp +MySql

智慧工地管理云平台系统&#xff0c;智慧工地全套源码&#xff0c;java版智慧工地源码&#xff0c;支持PC端、大屏端、移动端。 智慧工地聚焦建筑行业的市场需求&#xff0c;提供“平台网络终端”的整体解决方案&#xff0c;提供劳务管理、视频管理、智能监测、绿色施工、安全管…...

多场景 OkHttpClient 管理器 - Android 网络通信解决方案

下面是一个完整的 Android 实现&#xff0c;展示如何创建和管理多个 OkHttpClient 实例&#xff0c;分别用于长连接、普通 HTTP 请求和文件下载场景。 <?xml version"1.0" encoding"utf-8"?> <LinearLayout xmlns:android"http://schemas…...

数据链路层的主要功能是什么

数据链路层&#xff08;OSI模型第2层&#xff09;的核心功能是在相邻网络节点&#xff08;如交换机、主机&#xff09;间提供可靠的数据帧传输服务&#xff0c;主要职责包括&#xff1a; &#x1f511; 核心功能详解&#xff1a; 帧封装与解封装 封装&#xff1a; 将网络层下发…...

C# 类和继承(抽象类)

抽象类 抽象类是指设计为被继承的类。抽象类只能被用作其他类的基类。 不能创建抽象类的实例。抽象类使用abstract修饰符声明。 抽象类可以包含抽象成员或普通的非抽象成员。抽象类的成员可以是抽象成员和普通带 实现的成员的任意组合。抽象类自己可以派生自另一个抽象类。例…...

从零实现STL哈希容器:unordered_map/unordered_set封装详解

本篇文章是对C学习的STL哈希容器自主实现部分的学习分享 希望也能为你带来些帮助~ 那咱们废话不多说&#xff0c;直接开始吧&#xff01; 一、源码结构分析 1. SGISTL30实现剖析 // hash_set核心结构 template <class Value, class HashFcn, ...> class hash_set {ty…...

多种风格导航菜单 HTML 实现(附源码)

下面我将为您展示 6 种不同风格的导航菜单实现&#xff0c;每种都包含完整 HTML、CSS 和 JavaScript 代码。 1. 简约水平导航栏 <!DOCTYPE html> <html lang"zh-CN"> <head><meta charset"UTF-8"><meta name"viewport&qu…...

稳定币的深度剖析与展望

一、引言 在当今数字化浪潮席卷全球的时代&#xff0c;加密货币作为一种新兴的金融现象&#xff0c;正以前所未有的速度改变着我们对传统货币和金融体系的认知。然而&#xff0c;加密货币市场的高度波动性却成为了其广泛应用和普及的一大障碍。在这样的背景下&#xff0c;稳定…...

微软PowerBI考试 PL300-在 Power BI 中清理、转换和加载数据

微软PowerBI考试 PL300-在 Power BI 中清理、转换和加载数据 Power Query 具有大量专门帮助您清理和准备数据以供分析的功能。 您将了解如何简化复杂模型、更改数据类型、重命名对象和透视数据。 您还将了解如何分析列&#xff0c;以便知晓哪些列包含有价值的数据&#xff0c;…...

JavaScript基础-API 和 Web API

在学习JavaScript的过程中&#xff0c;理解API&#xff08;应用程序接口&#xff09;和Web API的概念及其应用是非常重要的。这些工具极大地扩展了JavaScript的功能&#xff0c;使得开发者能够创建出功能丰富、交互性强的Web应用程序。本文将深入探讨JavaScript中的API与Web AP…...