Mysql存储过程实践——用户常用模块表初始化数据

最新推荐文章于 2022-04-18 11:03:13 发布

Andy_Zheng.

最新推荐文章于 2022-04-18 11:03:13 发布

阅读量406

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/xyajia/article/details/104531938

版权

数据库专栏收录该内容

46 篇文章 4 订阅

订阅专栏

需求描述：为所有在职用户添加常用模块初始化数据

需求分析：

1、首先是主键ID可以直接用mysql自带的函数生成UUID，

2、机构ID是固定的，是为某一个机构初始数据

3、用户ID是查询用户表，筛选在职的用户

4、模块指定"考勤","工作总结","工作计划","请假","日志"5个常用模块，赋值为这5个模块各自的ID即可

存储过程实现：

CREATE DEFINER=`root`@`%` PROCEDURE `initUserMostlyUsedModular`()
BEGIN
	DECLARE userId VARCHAR(50);
	DECLARE flag int(1) DEFAULT 0;
	DECLARE w_count int(10) DEFAULT 0;
	DECLARE w_index int(10) DEFAULT 0;
	DECLARE cur CURSOR for SELECT user_id from cms_user where is_del = 0;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET flag=1;
	SELECT count(*) into w_count from cms_user where is_del = 0;
	open cur;
		fetch cur into userId;
		WHILE flag <> 1 DO
			SET w_index = w_index+1;
			INSERT INTO `crm_user_rel_commonly_used_modular` (`id`, `top_org_id`, `user_id`, `modular_id`, `app_id`) VALUES 
				(REPLACE(UUID(),'-',''), '149300c4126dc809ee3f7839f4ec03c1', userId, '4079858a750d41b7bd2598176a9d66f4', '1');
			INSERT INTO `crm_user_rel_commonly_used_modular` (`id`, `top_org_id`, `user_id`, `modular_id`, `app_id`) VALUES 
				(REPLACE(UUID(),'-',''), '149300c4126dc809ee3f7839f4ec03c1', userId, '08eb460fbe43424f9a04fa2b34afbb2a', '1');
			INSERT INTO `crm_user_rel_commonly_used_modular` (`id`, `top_org_id`, `user_id`, `modular_id`, `app_id`) VALUES 
				(REPLACE(UUID(),'-',''), '149300c4126dc809ee3f7839f4ec03c1', userId, 'dd6f0b7853d4461dbaf10a6da9032da3', '1');
			INSERT INTO `crm_user_rel_commonly_used_modular` (`id`, `top_org_id`, `user_id`, `modular_id`, `app_id`) VALUES 
				(REPLACE(UUID(),'-',''), '149300c4126dc809ee3f7839f4ec03c1', userId, 'f378ed626a7c4cf68805133c2e946cee', '1');
			INSERT INTO `crm_user_rel_commonly_used_modular` (`id`, `top_org_id`, `user_id`, `modular_id`, `app_id`) VALUES 
				(REPLACE(UUID(),'-',''), '149300c4126dc809ee3f7839f4ec03c1', userId, 'ac5acd5577a94710ac94df046498f218', '1');
		fetch cur into userId;
		IF w_count = w_index THEN
							set flag=1;
						ELSE
							set flag=0;
						END IF;
		END WHILE;
	close cur; 	

END