存储过程游标循环内提交db2 db2存储过程游标while遍历

转载

ganmaobuhaowan 2024-05-31 20:43:17

文章标签 存储过程游标循环内提交db2 java 后端 mysql sql 文章分类 数据仓库大数据

前情提要

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

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

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

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

下面进入正式学习：

1.存储过程书写格式

DELIMITER $$

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

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 block
END $$

DELIMITER ;

2.存储过程的使用

执行存储过程：

Call my_procedure(1,2) ;

删除执行过程：

DROP PROCEDURE IF EXISTS my_precedure;

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

（1）逻辑判断：

IF NOT EXISTS (
SELECT
 1
FROM
 my_table mt
WHERE
-- 可以使用存储过程传入的参数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错误

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