欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQL-mysql储存过程

程序员文章站 2024-03-17 19:47:16
...

一 前言

本篇内容是关于mysql储存过程的知识,学习本篇的基础是知识追寻者之前发过的文章(公众号读者看专辑)

《SQL-你真的了解什么SQL么?》

《SQL-小白最佳入门sql查询一》

《SQL-小白最佳入门sql查询二》

《SQL- 多年开发人员都不懂的插入与更新删除操作注意点》

《SQL-SQL事物操作》

《SQL-Mysql数据类型》

《SQL-mysql视图的前世今生》

公众号:知识追寻者

知识追寻者(Inheriting the spirit of open source, Spreading technology knowledge;)

二 储存过程

2.1 储存过程的概念

我们经常使用的SQL查询语句都是单条语句,如果要使用多条语句达到一个目的就显得力不从心了,储存过程就是使用多条语句完成业务的操作,你可以理解为linux脚本编程类似,window的批处理文件那样;简单的定义储存过程就是多条SQL的集合;

我们使用储存过程能够简化复杂的单条SQL,相比于单条复杂的SQL极大提高了性能;如果表结构发生变化只需要改变储存过程使用到SQL语句的表名,如果业务逻辑发生变化,只需要跳转储存过程即可,具有很强的灵活性;建立一次储存过程,即可使用,不用反复建立,保证开发人员使用到都是相同的储存过程,保证数据可靠性;总之使用储存过程,简单,灵活,安全可靠,性能好;

2.2 存储过程语法

  • 创建存储过程
Create PROCEDURE 储存过程名称 (参数列表)
begin
	过程体
end;

  • 参数列表
IN 表示输入; 示例 IN var1 Decimal(6,2)

OUT 表示输出;示例 IN var2 Decimal(6,2)

INOUT 表示输入输出;示例 IN var3 Decimal(6,2)
  • 变量
declare 变量名称 变量类型 [default value]
  • 执行存储过程
call 储存过程名称
  • 删除储存过程
DROP PROCEDURE 储存过程名称
  • 赋值
使用 setselect into 语句为变量赋值。
set @var := 20
select sum(price) into total from table_name
  • if 语句
f 条件 then
    表达式   
[elseif 条件 then
    表达式]
...
[else
    表达式]
end if;
  • case语句
CASE 值 WHEN 匹配值 THEN 结果
[WHEN 匹配值 THEN 结果]
......
[ELSE 结果]
END
  • while语句
[开始标签:]while 条件 do
    循环体
[结尾标签]    
end while ;
  • loop 语句
[开始标签:] loop  
	语句体
[结尾标签]  
end loop;  
  • iterate/ leave语句
其是通过标签可以实现;iterate 表示迭代, leave表示离开
  • repeat语句
repeat
--循环体
until 循环条件  
end repeat;

tip : 如果你是用命令行学习,在写多行SQL的时候 使用 // 可以实现换行哟!

2.3 储存过程示例

我们准备表如下,一个订单明细表;

CREATE TABLE `oder_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `detail_name` varchar(255) DEFAULT NULL COMMENT '订单明细',
  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  `oid` int(11) DEFAULT NULL COMMENT '订单id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='订单明细表';

准备数据如下

INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (1, '毛巾', 20.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (2, '牙膏', 15.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (3, '杯子', 5.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (4, '毛巾', 15.00, 2);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (5, '杯子', 15.00, 2);

无参储存过程

查看订单明细的所有订单名称,示例如下,跟普通的查询语句没区别;

create procedure slelect_detail()
begin
	select detail_name from oder_detail;
end;	

我们再调用储存过程

call slelect_detail();

此时就会打印内容如下

毛巾
牙膏
杯子
毛巾
杯子

删除储存过程

drop procedure slelect_detail;

带入参储存过程示例

现在我们需要查询oid为动态的所有订单明细名称,由于考虑到oid为动态,就是需要用户自己输入,故将oid作为入参;

create procedure slelect_detail(IN order_id INT)
begin
	select detail_name from oder_detail where oid = order_id;
end;

调用储存过程,只查询oid为1的用户的订单明细名称

call slelect_detail(1);

打印内容

毛巾
牙膏
杯子

删除储存过程

drop procedure slelect_detail;

带入参和出参的存储过程示例

查询任意用户的订单明细的所有金额;定义入参订单id 为 order_id , 输出总金额为 total;

create procedure slelect_toatal_money(IN order_id INT, OUT total DECIMAL(8,2))
begin
	select sum(price) into total from oder_detail where oid = order_id;
end;

调用储存过程示例

call slelect_toatal_money(1,@total);

查询 order_id 为1 总金额示例

SELECT @total;

输出为 40;

删除储存过程

drop procedure slelect_toatal_money;

2.4 if 语句示例

上节的储存过程都是单条SQL,这次我们开始使用控制流程,实现复杂的储存过程;

知识追寻者对输入的 order_id 自动加5 ,然后判断 var 是否 小于7 ,如果是就查询订单明细价格,否则查询订单明细价格总和;

create procedure slelect_toatal_money(IN order_id INT)
begin
	-- 定义变量
	declare var  int;
	-- 赋值
	set var= order_id+5;
	-- if 判断
	if var<7 then
		select price  from oder_detail where oid = order_id;
	else
		select sum(price)  from oder_detail where oid = order_id;
	end if;
end;

调用

call slelect_toatal_money(1);

输出

price
20
15
5

调用

call slelect_toatal_money(2);

输出

sum(price)
30

2.6 while 语句示例

对 变量 var 进行判断,如果 var <7 就执行 查询价格语句,并且var 进行自增;

create procedure slelect_toatal_money(IN order_id INT)
begin
	-- 定义变量
	declare var  int;
	-- 赋值
	set var= order_id+5;
	-- while
	while var<7 do
		select price  from oder_detail where oid = order_id;
		set var = var + 1;
	end while;
end;

调用示例

call slelect_toatal_money(1);

输出

price 
20
15
5

2.7 case语句示例

如下语句实现的效果与上面if语句实现效果一致;

create procedure slelect_toatal_money(IN order_id INT)
begin
	-- 定义变量
	declare var  int;
	-- 赋值
	set var:= order_id;
	-- case 判匹配
	case var  
	when 1 then 
		select price  from oder_detail where oid = order_id;
	when 2	then
		select sum(price)  from oder_detail where oid = order_id;
	end case;
end;

调用示例

call slelect_toatal_money(2);

输出

sum(price)
30

将参数改为1试试结果

2.8 loop语句

如果 var 小于3 就 计算 价格 + var 的值;

create procedure slelect_toatal_money(IN order_id INT)
begin
	-- 定义变量
	declare var  int;
	-- 赋值
	set var:= order_id;
	-- loop
	select_loop :  loop
		select price+var  from oder_detail where oid = order_id;
		set var = var +1;
		-- 跳出循环
		if var > 3 then
			leave select_loop;
		end if;
	end loop;
end;

调用示例

call slelect_toatal_money(1);

会输出三组结果

2.7 repeat

repeat 与 while 的不同之处就是 ,while 在 执行之前检查条件,其实执行之后检查条件;

create procedure slelect_toatal_money(IN order_id INT)
begin
	-- 定义变量
	declare var  int;
	-- 赋值
	set var= order_id+5;
	-- repeat循环
	repeat
		select price  from oder_detail where oid = order_id;
		set var = var + 1;
		until var>7
	end repeat;
end;

调用示例

call slelect_toatal_money(1);

此时会输出2组相同结果;

price
20
15
5

tip: loop, while , repeat , iterate 都是循环,loop,while, repeat 功能几乎相同;iterate可以通过标签的形式调用 循环,与 leave 语句使用方式一样;

关注知识追寻者:
SQL-mysql储存过程

相关标签: SQL