mysql存储过程

合集 - mysql(1)

1.mysql存储过程2023-05-12

收起

1、前言
在项目开发过程中，经常会遇到一种场景，当修改A表的数据时，会关联修改B表、C表甚至更多表的数据。例如：电商项目中，用户下单后需要减库存，用户余额扣除，商家余额增加等数据表的操作。所有这些操作，相对于服务端来说，最终体现就是与数据库的交互，可想而知，如果业务足够复杂，关联的表足够多，需要与数据库进行IO次数就越多，本篇所要分享的存储过程就能很好的解决以上问题（服务器的性能、数据库的IO交互次数）。

2、什么是存储过程
存储过程是事先经过编译并存储在数据库中的sql语句的集合。

3、存储过程的特点
（1）封装、复用，涉及同一个业务的数据交互经存储过程封装；

（2）可以接收参数，也可以返回数据；

（3）减少网络交互，提升效率

4、存储过程语法
创建存储过程
create procedure 存储过程名称（[参数列表]） begin sql语句…… end;
eg：

CREATE PROCEDURE p1()
BEGIN
SELECT count(1) FROM user;
END;

 

调用存储过程：call 名称（[参数列表]）
eg：CALL p1();

删除存储过程：drop procedure [if exists] 存储过程名称;
eg：

 DROP PROCEDURE IF EXISTS p1;

存储过程中使用局部变量

局部变量定义语法：DECLARE 变量名 变量类型 [DEFAULT 初始值];

变量类型就是数据库字段类型，可选值包括：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

eg：

CREATE PROCEDURE p2()
BEGIN
DECLARE mycount int DEFAULT 0;
SELECT count(1) into mycount FROM user;
SELECT mycount;
END;

存储过程中if…else…的使用

eg：

CREATE PROCEDURE p3()
BEGIN
    DECLARE score INT DEFAULT 59;
    DECLARE result VARCHAR (12);
IF score >= 85 THEN
　　SET result = '优秀';
ELSEIF score >= 60 THEN
　　SET result = '及格';
ELSE
　　SET result = '不及格';
END IF;
SELECT result;
END;

与if…else…作用相似的还有case…when 语句

eg:

创建一个带入参的存储过程：

CREATE PROCEDURE p4(IN score INT)
BEGIN
    DECLARE result VARCHAR (12);
CASE
WHEN score >= 90 THEN
    SET result = '优秀';
WHEN score >= 60 THEN
    SET result = '及格';
ELSE
    SET result = '不及格';
END CASE;
SELECT result;
END;

存储过程中循环语句 while…do…的使用

语法：while 判断条件 do 循环体

eg：计算1+2+3+4+……+n的和

CREATE PROCEDURE p5(in n int)
BEGIN
DECLARE total int DEFAULT 0;
WHILE n>0 DO
    SET total = total+n;
    SET n = n-1;
END WHILE;
SELECT total;
END;

与while…do…相似的还有repeat…until…

语法：repeat 循环体 until 判断条件

当满足判断条件时跳出repeat

eg：

CREATE PROCEDURE p6(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
REPEAT
    SET total = total + n;
    SET n = n - 1;
UNTIL n <= 0
END REPEAT;
SELECT total;
END;

存储过程中游标的使用：

游标定义：

游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理；
游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力；在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标 充当了 指针的作用 ，我们可以通过操作游标来对数据行进行操作。

游标的使用，主要包括游标的声明（declare）、打开（open）、使用（fetch）和关闭（close）

游标的声明：

 DECLARE 游标名 CURSOR FOR sql语句; 

游标打开：

 OPEN 游标名; 

游标使用：

 FETCH 游标名 into var_name [,var_name]... 

游标关闭：

 CLOSE 游标名; 

下面举个简单的例子来说明游标在存储过程中的应用

准备员工工资表及数据：

CREATE  PROCEDURE p7(IN limit_total_salary DECIMAL,OUT total_count INT)
BEGIN
    DECLARE sum_salary DECIMAL DEFAULT 0; #总薪资
    DECLARE cursor_salary DECIMAL DEFAULT 0; #游标指向
    DECLARE cursor_count INT DEFAULT 0; #游标记录次数
    DECLARE total INT DEFAULT (select count(1) from employee); #总记录输
    #定义游标
    DECLARE emp_cursor CURSOR FOR SELECT salary FROM employee ORDER BY salary DESC;
    #打开游标
    OPEN emp_cursor;
    REPEAT
        #使用游标
        FETCH emp_cursor INTO cursor_salary;
        set sum_salary = sum_salary+cursor_salary;
        SET cursor_count = cursor_count +1;
    UNTIL (sum_salary>=limit_total_salary OR cursor_count=total)
    END REPEAT;
    SET total_count = cursor_count;
    #关闭游标
    CLOSE emp_cursor;
END;

以上过程的作用说明：计算输入的工资和需要查询几条员工信息

执行结果如下：

 如果输入的工资和是8000，则需要查询一条员工信息；如果输入的工资和是12000，则需要查询两条员工信息；如果输入的工资和是18000，则需要查询三条员工信息。