mysql：存储过程-游标遍历

前情提要

因工作需要要写一些sql脚本，进行一些数据的修改。

直接在数据库ide中写sql涉及就到一些逻辑的判断。

比如判断根据参数判断这条数据存不存在，不存在插入，存在则删除。

最开始查搜索引擎Mysql是支持if判断的，根据格式写完sql也不能执行会报错。经过更细致的学习，发现如果要在mysql中进行一些逻辑判断，需要定义存储过程，相当于一些函数，就可以实现一些逻辑判断或者循环。

下面进入正式学习：

1.存储过程书写格式

DELIMITER $$CREATE PROCEDURE my_procedure()BEGIN    -- Statement 1    -- Statement 2END $$DELIMITER ;

根据格式就可以定义存储过程，写一些需要的逻辑，增删改查

存储过程还可以传递参数并且也可以定义变量，如下例

DELIMITER $$CREATE PROCEDURE my_procedure(IN param1 INT, OUT param2 INT)BEGIN    -- Declare an internal variable    DECLARE var1 INT;    -- PerfORM some calculations using the input parameter    SET var1 = param1 * 2;    -- Update the output parameter with the result    SET param2 = var1;    -- You can perform more statements here, if needed    -- ...    -- The stored procedure will return automatically after this blockEND $$DELIMITER ;

2.存储过程的使用

执行存储过程：

Call my_procedure(1,2) ;

 删除执行过程：

DROP PROCEDURE IF EXISTS my_precedure;

3.存储过程执行业务逻辑

（1）逻辑判断：

IF NOT EXISTS (SELECT 1FROM my_table mtWHERE-- 可以使用存储过程传入的参数param1 mt.id = param1) THEN-- 不存在，插入新数据 INSERT INTO my_table (id,code,name)VALUES (null,code,name);END IF;

（2）循环判断：

存储过程可以使用LOOP语句来实现循环。以下是一个简单的MySQL存储过程例子，演示如何使用LOOP循环来计算某个数的阶乘：

DELIMITER $$CREATE PROCEDURE calculate_factorial(IN number INT, OUT result INT)BEGIN    DECLARE counter INT DEFAULT 1;    DECLARE factorial INT DEFAULT 1;    -- Check if the input number is negative or zero    IF number <= 0 THEN        SET result = 1;        LEAVE calculate_factorial;    END IF;    -- Loop to calculate the factorial    factorial_loop: LOOP        SET factorial = factorial * counter;        SET counter = counter + 1;        -- Exit the loop when counter reaches the input number + 1        IF counter > number + 1 THEN            LEAVE factorial_loop;        END IF;    END LOOP factorial_loop;    SET result = factorial;END $$DELIMITER ;

（3）查询数据并遍历

-- 创建通用的存储过程：将指定表的自定义列分配给特定用户CREATE PROCEDURE assign_columns_to_user(IN param1 INT, IN param2 INT)BEGIN    DECLARE colId INT;    DECLARE colName VARCHAR(255);    DECLARE colDescription VARCHAR(255);    -- 声明游标，用于遍历数据集    DECLARE cur CURSOR FOR        SELECT            column_id AS colId,            column_name AS colName,            column_description AS colDescription        FROM            your_table        WHERE            domain_id = param1;    -- 声明异常处理程序    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @finished = 1;    -- 打开游标    OPEN cur;    -- 初始化完成标志    SET @finished = 0;    loop_start: LOOP        -- 读取游标数据        FETCH cur INTO colId, colName, colDescription;        -- 判断是否读取到数据或已完成        IF @finished = 1 THEN            LEAVE loop_start;        END IF;        -- 检查数据是否已分配给用户        IF NOT EXISTS (            SELECT 1            FROM your_table_assign            WHERE column_id = colId AND user_id = param2        ) THEN            -- 不存在，插入新数据            INSERT INTO your_table_assign (assign_id, user_id, column_id, column_name, column_description, assigned_flag, domain_id)            VALUES (NULL, param2, colId, colName, colDescription, 1, param1);        END IF;    END LOOP loop_start;    -- 关闭游标    CLOSE cur;END;

上述代码从your_table表中查询数据使用‘游标’进行循环遍历，利用‘游标’拿到遍历参数后，判断分配表是否存在，不存在则利用参数创建新的数据插入

注：

使用游标过程中可会遇到 No data - zero rows fetched, selected, or processed sql错误

在上述里例子中声明异常处理程序可以规避这个问题

-- 声明异常处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @finished = 1;

 在游标遍历完数据后，能捕获到这个异常，跳出循环。

来源地址：https://blog.csdn.net/weixin_63656510/article/details/131959842