mysql存储过程集合参数 mysql存储过程查询集合

-- 建立存储过程之前需要判断该存储过程是否存在，若存在则删除
DROP PROCEDURE IF EXISTS insert_project_unit;
-- 创建存储过程，insert_project_unit为存储过程名
CREATE PROCEDURE insert_project_unit()
-- 标识存储过程开始
BEGIN
    -- 定义变量
    DECLARE s int DEFAULT 0;
    DECLARE tenantId varchar(20);
    DECLARE organizationId bigint(20);
    DECLARE userNo varchar(20);
    -- 定义游标，并将sql结果集赋值到游标中，report为游标名
    DECLARE report CURSOR FOR SELECT tenant_id, organization_id, user_no
                              FROM jixiaoguanjia.u_tenant_info;
    -- 声明当游标遍历完后将标志变量置为某个值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 100;
    -- 打开游标
    OPEN report;
    -- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
    FETCH report INTO tenantId, organizationId,userNo;
    -- 当s不等于1时，也就是未遍历完时，会一直循环
    WHILE s <> 1
        DO
            -- 执行业务逻辑
            insert into tenant_project_unit(create_user, gmt_create, modified_user, gmt_modified,
                                                          organization_id,
                                                          tenant_id, type, user_type, status_id, sort, name)
            SELECT userNo,
                   '2022-12-14 14:36:07',
                   userNo,
                   '2022-12-14 14:36:07',
                   organizationId,
                   tenantId,
                   type,
                   0,
                   status_id,
                   sort,
                   name
            from jixiaoguanjia.tenant_project_unit
            where organization_id = 0;
            -- 当s等于1时代表遍历已完成，退出循环
            FETCH report INTO tenantId, organizationId,userNo;
        END WHILE;
    -- 关闭游标
    CLOSE report;
-- 标识存储过程结束
END;
CALL insert_project_unit();
DROP PROCEDURE IF EXISTS insert_project_unit;