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

Oracle查询表空间大小

1 查询数据库中所有的表空间以及表空间所占空间的大小 SELECTtablespace_name,sum( bytes ) / 1024 / 1024 FROMdba_data_files GROUP BYtablespace_name; 2 Oracle查询表空间大小及每个表所占空间的大小 SELECTtablespace_name,file_id,file_name,round( bytes / ( 1024 …...

关于iview组件中使用 table , 绑定序号分页后序号从1开始的解决方案

问题描述&#xff1a;iview使用table 中type: "index",分页之后 &#xff0c;索引还是从1开始&#xff0c;试过绑定后台返回数据的id, 这种方法可行&#xff0c;就是后台返回数据的每个页面id都不完全是按照从1开始的升序&#xff0c;因此百度了下&#xff0c;找到了…...

【android bluetooth 框架分析 04】【bt-framework 层详解 1】【BluetoothProperties介绍】

1. BluetoothProperties介绍 libsysprop/srcs/android/sysprop/BluetoothProperties.sysprop BluetoothProperties.sysprop 是 Android AOSP 中的一种 系统属性定义文件&#xff08;System Property Definition File&#xff09;&#xff0c;用于声明和管理 Bluetooth 模块相…...

成都鼎讯硬核科技!雷达目标与干扰模拟器,以卓越性能制胜电磁频谱战

在现代战争中&#xff0c;电磁频谱已成为继陆、海、空、天之后的 “第五维战场”&#xff0c;雷达作为电磁频谱领域的关键装备&#xff0c;其干扰与抗干扰能力的较量&#xff0c;直接影响着战争的胜负走向。由成都鼎讯科技匠心打造的雷达目标与干扰模拟器&#xff0c;凭借数字射…...

(转)什么是DockerCompose?它有什么作用?

一、什么是DockerCompose? DockerCompose可以基于Compose文件帮我们快速的部署分布式应用&#xff0c;而无需手动一个个创建和运行容器。 Compose文件是一个文本文件&#xff0c;通过指令定义集群中的每个容器如何运行。 DockerCompose就是把DockerFile转换成指令去运行。 …...

C++八股 —— 单例模式

文章目录 1. 基本概念2. 设计要点3. 实现方式4. 详解懒汉模式 1. 基本概念 线程安全&#xff08;Thread Safety&#xff09; 线程安全是指在多线程环境下&#xff0c;某个函数、类或代码片段能够被多个线程同时调用时&#xff0c;仍能保证数据的一致性和逻辑的正确性&#xf…...

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

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

GC1808高性能24位立体声音频ADC芯片解析

1. 芯片概述 GC1808是一款24位立体声音频模数转换器&#xff08;ADC&#xff09;&#xff0c;支持8kHz~96kHz采样率&#xff0c;集成Δ-Σ调制器、数字抗混叠滤波器和高通滤波器&#xff0c;适用于高保真音频采集场景。 2. 核心特性 高精度&#xff1a;24位分辨率&#xff0c…...

C# 求圆面积的程序(Program to find area of a circle)

给定半径r&#xff0c;求圆的面积。圆的面积应精确到小数点后5位。 例子&#xff1a; 输入&#xff1a;r 5 输出&#xff1a;78.53982 解释&#xff1a;由于面积 PI * r * r 3.14159265358979323846 * 5 * 5 78.53982&#xff0c;因为我们只保留小数点后 5 位数字。 输…...

Go 语言并发编程基础:无缓冲与有缓冲通道

在上一章节中&#xff0c;我们了解了 Channel 的基本用法。本章将重点分析 Go 中通道的两种类型 —— 无缓冲通道与有缓冲通道&#xff0c;它们在并发编程中各具特点和应用场景。 一、通道的基本分类 类型定义形式特点无缓冲通道make(chan T)发送和接收都必须准备好&#xff0…...