seo网站营销推广公司,商城开发分销系统,建站工具 wordpress,网页设计基础教程题库文章目录 概述常见操作创建存储过程存储过程局部变量定义和赋值查看存储过程删除存储过程调用存储过程 示例-动态数据透视详细讲解总结参考资料 概述
Mysql 存储过程是一组预先编译的 sql 语句集合#xff0c;它们被存储在数据库中#xff0c;并可以被多次调用执行。存储过程… 文章目录 概述常见操作创建存储过程存储过程局部变量定义和赋值查看存储过程删除存储过程调用存储过程 示例-动态数据透视详细讲解总结参考资料 概述
Mysql 存储过程是一组预先编译的 sql 语句集合它们被存储在数据库中并可以被多次调用执行。存储过程可以接受参数、执行复杂的逻辑操作并返回结果或修改数据库的状态。
存储过程有以下几个优点 代码复用存储过程可以将常用的操作逻辑封装起来以便在多个地方重复使用避免重复编写相同的 sql 语句。 提高性能存储过程在编译时进行优化可以减少通信开销提高数据库的执行效率。 安全性存储过程可以控制对数据库的访问权限只暴露必要的接口提高数据的安全性。 简化操作存储过程可以执行复杂的数据库操作减少客户端与数据库之间的交互次数简化了客户端的代码。
常见操作
创建存储过程
使用 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 动态SQLMySQL Documentation Stored ProceduresMySQL Tutorial Stored ProceduresMysql 自定义函数
