投稿
  1. 极客之音首页
  2. 后端笔记

【Mysql专题】存储过程介绍及其基本操作

飞熊 • • 后端笔记 • 阅读 150

不管现实多么惨不忍睹，都要持之以恒地相信，这只是黎明前短暂的黑暗而已。不要惶恐眼前的难关迈不过去，不要担心此刻的付出没有回报，别再花时间等待天降好运。真诚做人，努力做事！你想要的，岁月都会给你。【Mysql专题】存储过程介绍及其基本操作，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

目录

一、存储过程的介绍

基本定义
存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。（对应的就是Java里面的函数）

我们通常使用的SQL 语句都是针对一个表或几个表的单条 SQL 语句，但是在数据库的实际操作中，并非所有操作都那么简单，有时候一个完整的操作需要多条 SQL 语句处理多个表才能完成。例如：为了确认学生能否毕业，需要同时查询学生档案表、成绩表和综合表。此时就需要使用多条 SQL 语句来针对几个数据表完成这个处理要求。存储过程可以有效地完成这个数据库操作。

常用操作数据库的 SQL 语句在执行的时候需要先编译，然后执行。存储过程则采用另一种方式来执行 SQL 语句。一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。
存储过程通常有如下优点：

  1. 封装性。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码
  2. 可增强 SQL 语句的功能和灵活性。存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算
  3. 可减少网络流量。这个不难理解，以往我们使用单条sql，每次调用都需要发起一次IO请求将完整的IO请求发送过去，但是现在只需要【CALL 存储过程】就可以了。确切的说：由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载
  4. 高性能。存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能
  5. 提高数据库的安全性和数据的完整性。使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限

特别声明
存储过程跟自定义函数很像。它们的区别是：

  • 存储过程实现的功能要复杂一些；而函数的针对性更强。
  • 存储过程可以返回多个值；函数只能有一个返回值。
  • 存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分实现出来。

二、存储过程的操作

2.1 创建存储过程

语法格式如下：

CREATE PROCEDURE <过程名>(参数列表 参数类型)
BEGIN
<过程体 sql语句>
END

语法说明如下：
1）上面的CREATE PROCEDURE，BEGIN和END是固定的
2）过程名：存储过程的名字。我们在调用的时候，就是使用CALL + 过程名调用。另外，尽量避免过程名跟Mysql已存在的函数名相同导致冲突
3）参数列表-参数类型：存储过程可以没有参数，也可以有多个参数，参数的声明是【参数名+参数类型】声明的。

使用示例：

CREATE PROCEDURE get_canteen_dish_sales ()
BEGIN
	SELECT food_name, sales_weights FROM unify_dish_sales ORDER BY sales_weights DESC LIMIT 20;
END

2.2 调用存储过程

语法格式如下：

CALL 过程名(); # 调用的时候需要加上括号，因为可能存在参数

使用示例：

call get_canteen_dish_sales();

一个很简单的使用，查询的是我们生产库里面一个菜品分析表，获取菜品名称和销售量。

2.3 查看存储过程

语法格式如下：

# 查询指定数据库中的所有的存储过程
select name from mysql.proc where db='数据库名';
​
# 查询存储过程的状态信息
show procedure status;

使用示例：

select name from mysql.proc where db='unify-dev';
show PROCEDURE status；

2.4 删除存储过程

语法格式如下：

DROP PROCEDURE 过程名; # 删除的时候不要加小括号，直接给定存储过程的名字即可。
DROP PROCEDURE IF EXISTS 过程名; # 如果存储删除，不存在不删除并且不会报错

使用示例：

DROP PROCEDURE get_canteen_dish_sales;
DROP PROCEDURE IF EXISTS get_canteen_dish_sales;

三、存储过程中的语法构造

我们在最开始的时候介绍过，存储过程是可以编程的，意味着可以使用变量、表达式、控制语句来完成比较复杂的功能。接下来我们学习一下如何使用更丰富的语法来完成复杂的存储过程。

3.1 变量的声明以及赋值

变量的声明
变量声明的语法格式如下：

DECLARE 变量名[,...] type [DEFAULT value]

注意：声明变量的时候可以一次性声明多个，使用逗号隔开。
示例如下：

CREATE PROCEDURE calculate ()
BEGIN
	DECLARE num1 INT;
	DECLARE num2 INT DEFAULT 1;
	SELECT num1 + num2;
END

PS：注意上面示例num1是没有默认值的，所以SELECT num1 + num2;是一个null值。

变量的赋值
变量的赋值有2种方式，分别为：SET赋值，以及SELECT INTO赋值。先来介绍下SET的赋值方式：

  • SET : 直接赋值使用SET关键字，可以赋常量或者是表达式，具体语法如下：
-- 注意：一次可以给多个变量赋值，中间使用逗号隔开。
SET 变量名 = 变量值 [,变量名 = 变量值] ...
  • 使用示例如下：
CREATE PROCEDURE calculate ()
BEGIN
	DECLARE num1 INT;
	DECLARE num2 INT DEFAULT 1;
	SET num1=2,num2=3;
	SELECT num1 + num2;
END

再来看看SELECT INTO的赋值方式：

  • SELECT INTO：语法格式如下
SELECT <column | 聚合函数> INTO 变量名;
  • 使用示例：
CREATE PROCEDURE calculate ()
BEGIN
	DECLARE num1 INT;
	DECLARE num2 INT DEFAULT 1;
	
	SELECT COUNT(1) INTO num1 FROM unify_dish_sales;
	SELECT num1+num2;
END

3.2 条件判断

if条件判断
语法格式如下：

# 只有满足差选条件才会执行 then 后面的SQL语句
if search_condition(查询条件) then statement_list(SQL语句)
    [else if search_condition(查询条件) then statement_list(SQL语句)]...
    [else statement_list(SQL语句)]
end if;

使用示例：

CREATE PROCEDURE calculate ()
BEGIN
	DECLARE openStatus VARCHAR(20);
	DECLARE storeTurnover DECIMAL(10, 2);

	SELECT turnover INTO storeTurnover FROM unify_report_store_line LIMIT 1;
	IF storeTurnover > 0
	THEN
		SET openStatus='开业了';
	ELSE
		SET openStatus='歇业中';
	END IF;
	SELECT openStatus;
END

上面的源码敢的事情如下：

  1. 声明了一个openStatus 变量，用来描述今天的营业装填
  2. 声明了storeTurnover 用来接收营业额
  3. 去数据库表查询营业额，由于我现在生产库营业额有多条记录，所以我用了个LIMIT 1限定只获取一条
  4. 判断storeTurnover ，如果大于0说明今天营业了；反之则没有

3.3 传递参数

传递参数需要在创建存储过程的时候，就要做好。语法格式如下：

CREATE PROCEDURE <过程名>([in/out/inout] 参数列表 参数类型)
BEGIN
<过程体 sql语句>
END

大家注意这个语法跟们最开始创建存储过程的时候，不同的是，参数列表前面的[in/out/inout]，他们的作用分别如下：

  • in : 该参数可以作为输入，调用该存储过程需要传入的值，默认的选项
  • out : 该参数作为输出，调用该存储过程之后返回的值。
  • inout : 既可以作为输入参数也可以作为输出参数

使用示例1：in参数

CREATE PROCEDURE calculate (IN storeTurnover DECIMAL(10, 2))
BEGIN
	DECLARE openStatus VARCHAR(20);
	IF storeTurnover > 0
	THEN
		SET openStatus='开业了';
	ELSE
		SET openStatus='歇业中';
	END IF;
	SELECT openStatus;
END

很简单的一个示例，改自前一个示例。之前是从数据库获取，现在是由用户自己定义。如果传入的数大于0，则返回开业了，反之返回歇业中。然后调用的时候，跟调用函数一样就可以了。

CALL calculate(0);

使用示例2：out参数

CREATE PROCEDURE calculate (IN storeTurnover DECIMAL(10, 2), OUT openStatus VARCHAR(20))
BEGIN
	IF storeTurnover > 0
	THEN
		SET openStatus='开业了';
	ELSE
		SET openStatus='歇业中';
	END IF;
	SELECT openStatus;
END

调用的话，需要用一个Mysql的会话变量来接收，然后select：

CALL calculate(0, @openStatus);
select @openStatus;

@标识符的作用
@openStatus：这种在变量名前面加上”@“符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。当前会话就是代表的，比如我们在命令提示窗口中给好多带有 @ 符号变量进行赋值，此时这些变量的值只作用于当前的会话，当我们把这个窗口关闭的时候，此时这些变量的值就会释放掉。
@@global : 这种在变量名前加上 “@@” 符号，叫做系统变量。

3.4 case结构

语法格式如下：

# 方式一
case case_value(判断的值)
    when when_value(比较的值) then statement_list(SQL语句)
    [when when_value(比较的值) then statement_list(SQL语句)]...
    [else statement_list(SQL语句)]
end case;
​
# 方式二
case 
    when search_condition(查询条件) then statement_list(SQL语句)
    [when search_condition(查询条件) then statement_list(SQL语句)]...
    [else statement_list(SQL语句)]
end case;

使用示例：

CREATE PROCEDURE calculate (IN storeTurnover DECIMAL(10, 2), OUT openStatus VARCHAR(20))
BEGIN
	CASE 
	WHEN storeTurnover > 1000 THEN
		SET openStatus='赚麻了';
	WHEN storeTurnover > 500 THEN
		SET openStatus='小赚';
	ELSE
		SET openStatus='亏了亏了';
	END CASE;
END

3.5 while循环

有条件的循环控制语句，当满足条件的时候进入循环，不满足条件的时候退出循环。语法结构如下：

# 只要查询条件一直成立就会一直指定do后面的SQL语句，当查询条件不成立的时候直接跳出while循环
while search_condition(查询条件) do
    statement_list(SQL语句)
end while;

使用示例：（计算从1加到n的值）

CREATE PROCEDURE pro_sum (IN num INT(11))
BEGIN
	DECLARE number INT DEFAULT 0;
	DECLARE total INT DEFAULT 0;
	WHILE number <= num DO
	SET total = total + number;
	SET number = number + 1;
	END WHILE;
	SELECT total;
END

3.6 repeat循环

有条件的循环控制语句，当不满足条件的时候进入循环，满足条件的时候跳出循环。他和while循环是反着的。语法结构如下：

repeat 
    statement_list(SQL语句)
    until search_condition(查询添加)
end repeat;

使用示例：（计算从1加到n的值）

CREATE PROCEDURE pro_sum (IN num INT(11))
BEGIN
	DECLARE total INT DEFAULT 0;
	REPEAT 
        set total = total + num;
        set num = num - 1;
        
				# 注意：这个 unti 后的查询条件不要加分号，加分号会报错。
        until num = 0
  END REPEAT;
	SELECT total;
END

3.7 loop循环与leave语句

loop：实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用leave语句实现，具体语法如下：

[begin_label:] loop
    statement_list
end loop [end_label]

（PS：如果不在statement_list中增加退出循环的语句，那么loop语句可以永安里实现简单的死循环。）

leave：用来从标注的流程构造中退出，通常和 begin…end 或循环一起使用。下面是一个使用loop和leave的简单例子，退出循环。

上面两个结合起来的示例如下：

CREATE PROCEDURE pro_sum (IN num INT(11))
BEGIN
	DECLARE total INT DEFAULT 0;
	mySum:loop
        set total = total + num;
        set num = num - 1;

        # 借助leave组织退出条件
        if num <= 0 then
            leave mySum;
        end if; 
    end loop mySum;
	SELECT total;
END

3.8 游标使用（循环遍历）

什么是游标？在 SQL 中，游标（英文：Cursor）是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。
举个例子：你知道如何循环遍历一张表的数据吗？没啥办法吧？游标就是干这个事情的。
如何使用游标呢？通常来说有5步：

  1. 声明/定义游标。语法如下：
DECLARE cursor_name CURSOR FOR select_statement
  1. 打开游标。当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区。语法如下：
OPEN cursor_name
  1. 从游标中获取数据。这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。语法如下：
FETCH cursor_name INTO var_name ...
  1. 关闭游标。有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。语法如下：
CLOSE cursor_name
  1. 释放游标。这一步很奇怪，我百度看了不少博客，有人说需要有人说不需要，而且版本千奇百怪，会有各种问题，所以我没使用。语法如下：
DEALLOCATE PREPARE cursor_name

接下来给大家一个，我线上使用游标的案例。首先先说我遇到的问题：

问题背景：数据库中有多个报表，用来记录门店经营数据。每一个报表中都存在2个字段（line_id，store_id）来记录数据归属门店，它们的关系是一对多关系，并且可以通过line_id找到store_id。
问题现象：由于疏忽，我们线上存量有分属于160+个门店，总计5000+条数据的store_jd字段值缺失但是line_id有值，导致无法通过store_id查找到属于门店的报表数据。
解决办法：于是我想到，使用存储过程，修改线上数据。通过line_id找到对应的store_id，再修改线上数据。

我的解决思路是：

  1. 新增两个存储过程，第一个存储过程接收外部传过来的line_id跟store_id，然后修改数据：
CREATE PROCEDURE `updateReportStoreId`(IN lineId VARCHAR(20), IN storeId VARCHAR(20))
BEGIN
	UPDATE report_line SET store_id=storeId WHERE line_id=lineId;
	UPDATE report_order SET store_id=storeId WHERE line_id=lineId;
END
  1. 第二个存储过程，就是使用游标循环获取line_id跟store_id，并且调用第一个存储过程
CREATE PROCEDURE `cursorLoopCallUpdateReport`()
BEGIN
	#Routine body goes here...
	DECLARE lineId VARCHAR(20);
	DECLARE storeId VARCHAR(20);
	DECLARE done INT DEFAULT FALSE;

  # 定义游标
	DECLARE curr CURSOR FOR SELECT id, store_id FROM m_store_line;

	# 声明异常处理
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

  # 打开游标
	OPEN curr;
	
	# 循环读取
	read_loop : LOOP
		
		# 获取游标数据
		FETCH NEXT FROM curr INTO lineId, storeId;
		
		# 判断游标结束
		IF done THEN
			LEAVE read_loop;
		END IF;

		# 调用更新线上数据脚本
		IF lineId is not null AND storeId is not null THEN
			CALL updateReportStoreId(lineId, storeId);
		END IF;
	END LOOP;
	
	# 关闭游标
	CLOSE curr;

	# 释放游标，我的版本下，下面这句编译通过但是使用报错，我就干脆不用了
	# DEALLOCATE PREPARE curr;
END

感谢

感谢【小白教程】的系列教程《Mysql教程》
感谢知乎大佬【作者：程云博】的文章《MySQL高级篇——存储过程以及语法》
感谢简书大佬【作者：顾子豪】的文章《MySQL中的游标》

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/180498.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！