菜单sql生成脚本

-- 开始：清理变量
SET @menu_1 = NULL, @menu_2 = NULL, @menu_3 = NULL, @menu_4 = NULL, @menu_5 = NULL;
SET @menu_level = NULL, @field_parent_id = NULL, @field_value = NULL, @menu_value = NULL, @error_msg = NULL;

-- 菜单层级
-- SET @menu_1 = '采购中心';
SET @menu_2 = '采购折扣管理';
SET @menu_3 = '采购退补价确认单';
-- SET @menu_4 = '测测';
-- SET @menu_5 = '成都成都成都';

SET @menu_level = CASE WHEN @menu_1 IS NULL THEN 0 WHEN @menu_2 IS NULL THEN 1 WHEN @menu_3 IS NULL THEN 2 WHEN @menu_4 IS NULL THEN 3 WHEN @menu_5 IS NULL THEN 4 ELSE 5 END;

SET @field_parent_id = CASE WHEN @menu_1 IS NULL THEN NULL WHEN @menu_2 IS NULL THEN '0' WHEN @menu_3 IS NULL THEN 't1.id' WHEN @menu_4 IS NULL THEN 't2.id' WHEN @menu_5 IS NULL THEN 't3.id' ELSE 't4.id' END;

SET @field_value = 'INSERT INTO `tb_platform_sys_menu` (`id`, `parent_id`, `system_code`, `system_name`, `menu_name`, `menu_type`, `menu_code`, `menu_business_code`, `sort_order`, `is_display`, `menu_status`, `route_path`, `description`, `icon_type`, `icon_url`, `web_url`, `revision`, `button_key`, `button_type`, `operator`, `operator_name`, `delete_flag`, `delete_timestamp`, `create_time`, `modify_time`, `menu_tag`, `system_module_code`, `system_module_name`, `menu_abbr`) \n';


SET @menu_value = IF(@menu_level = 0, '', (SELECT CONCAT_WS(', ', 
                       id,
                       @field_parent_id,
                       IFNULL(QUOTE(system_code), 'NULL'),
                       IFNULL(QUOTE(system_name), 'NULL'),
                       IFNULL(QUOTE(menu_name), 'NULL'),
                       IFNULL(menu_type, 'NULL'),
                       IFNULL(QUOTE(menu_code), 'NULL'),
                       IFNULL(QUOTE(menu_business_code), 'NULL'),
                       IFNULL(sort_order, 'NULL'),
                       IFNULL(is_display, 'NULL'),
                       IFNULL(menu_status, 'NULL'),
                       IFNULL(QUOTE(route_path), 'NULL'),
                       IFNULL(QUOTE(description), 'NULL'),
                       IFNULL(QUOTE(icon_type), 'NULL'),
                       IFNULL(QUOTE(icon_url), 'NULL'),
                       IFNULL(QUOTE(web_url), 'NULL'),
                       IFNULL(revision, 'NULL'),
                       IFNULL(QUOTE(button_key), 'NULL'),
                       IFNULL(button_type, 'NULL'),
                       IFNULL(QUOTE(operator), 'NULL'),
                       IFNULL(QUOTE(operator_name), 'NULL'),
                       IFNULL(delete_flag, 'NULL'),
                       IFNULL(delete_timestamp, 'NULL'),
                       IFNULL(QUOTE(create_time), 'NULL'),
                       IFNULL(QUOTE(modify_time), 'NULL'),
                       IFNULL(QUOTE(menu_tag), 'NULL'),
                       IFNULL(QUOTE(system_module_code), 'NULL'),
                       IFNULL(QUOTE(system_module_name), 'NULL'),
                       IFNULL(QUOTE(menu_abbr), 'NULL')
                      )
                     FROM (
                       SELECT t1.*
                       FROM tb_platform_sys_menu t1 
                       WHERE @menu_level = 1 AND t1.menu_name = @menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
                        UNION ALL
                       SELECT t2.*
                       FROM tb_platform_sys_menu t1 
                       INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = @menu_2 AND t2.delete_flag = 0
                       WHERE @menu_level = 2 AND t1.menu_name = @menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
                        UNION ALL
                       SELECT t3.*
                       FROM tb_platform_sys_menu t1 
                       INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = @menu_2 AND t2.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = @menu_3 AND t3.delete_flag = 0
                       WHERE @menu_level = 3 AND t1.menu_name = @menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
                        UNION ALL
                       SELECT t4.*
                       FROM tb_platform_sys_menu t1 
                       INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = @menu_2 AND t2.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = @menu_3 AND t3.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = @menu_4 AND t4.delete_flag = 0
                       WHERE @menu_level = 4 AND t1.menu_name = @menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
                        UNION ALL
                       SELECT t5.*
                       FROM tb_platform_sys_menu t1 
                       INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = @menu_2 AND t2.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = @menu_3 AND t3.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = @menu_4 AND t4.delete_flag = 0
                       INNER JOIN tb_platform_sys_menu t5 ON t5.parent_id = t4.id AND t5.menu_name = @menu_5 AND t5.delete_flag = 0
                       WHERE @menu_level = 5 AND t1.menu_name = @menu_1 AND t1.parent_id = 0 AND t1.delete_flag = 0
                     ) t));
										 		 
SET @error_msg = '';
SET @error_msg = IF(@menu_level = 0, CONCAT(@error_msg, '一级菜单未填;'), @error_msg);
SET @error_msg = IF((@menu_value IS NULL OR @menu_value = ''), CONCAT(@error_msg, '未能查询到对应菜单;'), @error_msg); 
										 
SET @menu_value = IF(@menu_level = 1, CONCAT('VALUES (', @menu_value, ');'), @menu_value);
SET @menu_value = IF(@menu_level > 1, CONCAT('SELECT ', @menu_value, '\n'), @menu_value);

SELECT IF(@error_msg != '', @error_msg, 
   CONCAT(
      @field_value, 
      @menu_value,
      IF(@menu_level >= 2, 'FROM tb_platform_sys_menu t1 \n', ''),
      IF(@menu_level >= 3, CONCAT('INNER JOIN tb_platform_sys_menu t2 ON t2.parent_id = t1.id AND t2.menu_name = ', QUOTE(@menu_2), ' AND t2.delete_flag = 0 \n'), ''),
      IF(@menu_level >= 4, CONCAT('INNER JOIN tb_platform_sys_menu t3 ON t3.parent_id = t2.id AND t3.menu_name = ', QUOTE(@menu_3), ' AND t3.delete_flag = 0 \n'), ''),
      IF(@menu_level >= 5, CONCAT('INNER JOIN tb_platform_sys_menu t4 ON t4.parent_id = t3.id AND t4.menu_name = ', QUOTE(@menu_4), ' AND t4.delete_flag = 0 \n'), ''),
      IF(@menu_level >= 2, CONCAT('WHERE t1.menu_name = ', QUOTE(@menu_1), ' AND t1.parent_id = 0 AND t1.delete_flag = 0;'), '')
   )) AS generated_sql
FROM DUAL;

-- 结束：清理变量
SET @menu_1 = NULL, @menu_2 = NULL, @menu_3 = NULL, @menu_4 = NULL, @menu_5 = NULL;
SET @menu_level = NULL, @field_parent_id = NULL, @field_value = NULL, @menu_value = NULL, @error_msg = NULL;