MySQL - 存储过程游标循环插入数据

我一直在流浪

已于 2023-03-25 10:57:15 修改

阅读量1.2k

点赞数 1

分类专栏：【数据库-MySQL进阶】文章标签： mysql 数据库 java

于 2023-02-09 11:42:14 首次发布

本文链接：https://blog.csdn.net/qq_42764468/article/details/128950268

版权

【数据库-MySQL进阶】专栏收录该内容

50 篇文章 18 订阅

订阅专栏

1. 数据库表：

CREATE TABLE `t_role_policy` (
  `id` varchar(32) NOT NULL,
  `roleId` varchar(32) NOT NULL COMMENT '外键，角色ID',
  `policyId` varchar(32) NOT NULL COMMENT '外键，策略ID',
  `relationship` tinyint(4) NOT NULL COMMENT '权限关系：拥有关系、可授予关系',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='角色-权限策略关联表';

2. 存储过程游标循环插入数据：

# 如果存在名字为handle_data的procedure则删除
drop procedure if exists handle_data; 
# 定义//为一句sql的结束标志，取消;的所代表的意义
delimiter // 
# 声明存储过程handle_data（）
CREATE PROCEDURE handle_data ( ) 
  # 存储过程开始
  BEGIN
	# 定义一个变量，while循环时使用,存储roleId
	DECLARE u_roleId varchar( 32 );
	# 定义一个变量，while循环时使用,存储policyI
	DECLARE u_policyId varchar( 32 );
	# 为下面while循环建立一个退出标志，
    DECLARE flag INT DEFAULT 0;
	# 定义一个游标来记录sql查询的结果
    DECLARE deviceCodeList CURSOR FOR ( select  `roleId`, `policyId` from t_role_policy where policyId in('fx-policy-fec8332fd0dc12f0000020',
		'fx-policy-fec8332fd0dc12f000000b','fx-policy-fec8332fd0dc12f000002f','fx-policy-fec8332fd0dc12f0000014'));
    # 当游标遍历完后将flag的值设置为1
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
	# 打开游标
    OPEN deviceCodeList;
	# 将游标中的值赋给定义好的变量，实现for循环的要点
    FETCH deviceCodeList INTO u_roleId,u_policyId;
	# 当flag不等于1时，会一直循环
    WHILE flag != 1 DO
		# 执行需要循环处理的SQL
		if(u_policyId='fx-policy-fec8332fd0dc12f0000020') then
			INSERT INTO `t_role_policy` (`id`, `roleId`, `policyId`, `relationship`) VALUES
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66d90000021', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66ad0e415c8', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c30000001', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c50000005', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c50000003', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c9000000b', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c50000009', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c50000007', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66ca000000d', 0);
		elseif(u_policyId='fx-policy-fec8332fd0dc12f000000b') then
			INSERT INTO `t_role_policy` (`id`, `roleId`, `policyId`, `relationship`) VALUES 
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66cb000000e', 0),
	    (concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66d90000022', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c9000000c', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c9000000a', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c50000008', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c50000006', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c50000004', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66c40000002', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66b10000000', 0);
		elseif(u_policyId='fx-policy-fec8332fd0dc12f000002f') then
			INSERT INTO `t_role_policy` (`id`, `roleId`, `policyId`, `relationship`) VALUES 
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66cb000000f', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66d90000017', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66d70000013', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66d90000019', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66d90000015', 0);
		elseif(u_policyId='fx-policy-fec8332fd0dc12f0000014') then
			INSERT INTO `t_role_policy` (`id`, `roleId`, `policyId`, `relationship`) VALUES 
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66d90000020', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66d90000018', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66d80000014', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66cd0000012', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66cc0000010', 0),
			(concat('fx-rolepo-',substring(md5(rand()), 1, 22)), u_roleId, 'fx-policy-fec84451f3b66d90000016', 0);
		end if;
	  # 游标往后移，注：不能漏
      FETCH deviceCodeList INTO u_roleId,u_policyId;
    end while;
	 # 关闭游标
    CLOSE deviceCodeList;
	# 存储过程结束 
  end //
    
delimiter ;
# 调用存储过程
call handle_data();

3. 测试：

# 添加一行数据
INSERT INTO `t_role_policy` (`id`, `roleId`, `policyId`, `relationship`) VALUES ('fx-rolepo-b8c7ef290cc57f1412bdb0', 'fx-role-fec8339100dc0d10000003', 'fx-policy-fec8332fd0dc12f0000020', 0);

# 调用存储过程
call handle_data();