mysql 存储过程菜单拷贝

1、根据菜单名称，查找菜单记录，并拷贝生成一条新的菜单

CREATE  PROCEDURE `menu_copybyname`(in params_name varchar(255))
begin
#定义
declare cur_menu_id INT default 0 ;
declare v_maxid bigint(20);
declare v_new_pid bigint(20); #新的子菜单pid
declare done int default false;
declare v_menu_id bigint(20)  ;
declare v_pid bigint(20) ;
declare v_sub_count int(5) ;#'子菜单数目'
declare v_type int(11) ; # '菜单类型';
declare v_title varchar(255) ; # '菜单标题';
declare v_name varchar(255) ; # '组件名称';
declare v_component varchar(255) ; # '组件';
declare v_menu_sort int(5) ; # '排序';
declare v_icon varchar(255) ; # '图标';
declare v_path varchar(255) ; # '链接地址';
declare v_i_frame bit(1) ; # '是否外链';
declare v_cache bit(1) DEFAULT b'0'; # COMMENT '缓存';
declare v_hidden bit(1) DEFAULT b'0' ; #COMMENT '隐藏';
declare v_permission varchar(255) ; # '权限';
declare v_display int(1) DEFAULT '0';
declare v_menu_category int(1) ; # '菜单类型';

#删除name 为 name+ _link	记录
#DELETE from sys_menu where name = CONCAT(params_name,'_link');

#根据菜单名称查询记录
select menu_id,pid,sub_count,type,title,name,component ,menu_sort, icon,path,i_frame,cache,hidden,permission,display,menu_category  into cur_menu_id,v_pid,v_sub_count,v_type,v_title,v_name,v_component ,v_menu_sort ,v_icon,v_path,v_i_frame,v_cache,v_hidden,v_permission,v_display,v_menu_category  from sys_menu where  name = params_name;
SELECT cur_menu_id,v_pid,v_sub_count,v_type,v_title,v_name,v_component ,v_menu_sort ,v_icon,v_path,v_i_frame,v_cache,v_hidden,v_permission,v_display,v_menu_category ;

#插入当前查询的记录，680为临时菜单ID
insert into sys_menu
	(pid,sub_count,type,title,name,component ,menu_sort, icon,path,i_frame,cache,hidden,permission,display,menu_category)
	values(680,v_sub_count,v_type,v_title,CONCAT(v_name,'_link'),v_component ,v_menu_sort ,v_icon,v_path,v_i_frame,v_cache,v_hidden,v_permission,v_display,v_menu_category );
#获取v_new_pid
select max(menu_id) into v_new_pid from sys_menu;
#SELECT v_new_pid ;
#递推copy菜单到新的菜单中
call menu_copybypid(cur_menu_id,v_new_pid);

 
end

2、递归调用拷贝子菜单

CREATE  PROCEDURE `menu_copybypid`(in params_pid bigint(20),in params_new_pid bigint(20))
begin
#定义
declare curmenuid INT default 0 ;
declare counts INT default 0 ;
declare v_maxid bigint(20);
declare v_new_pid bigint(20);
declare done int default false;
declare v_menu_id bigint(20)  ;
declare v_pid bigint(20) ;
declare v_sub_count int(5) ;#'子菜单数目'
declare v_type int(11) ; # '菜单类型';
declare v_title varchar(255) ; # '菜单标题';
declare v_name varchar(255) ; # '组件名称';
declare v_component varchar(255) ; # '组件';
declare v_menu_sort int(5) ; # '排序';
declare v_icon varchar(255) ; # '图标';
declare v_path varchar(255) ; # '链接地址';
declare v_i_frame bit(1) ; # '是否外链';
declare v_cache bit(1) DEFAULT b'0'; # COMMENT '缓存';
declare v_hidden bit(1) DEFAULT b'0' ; #COMMENT '隐藏';
declare v_permission varchar(255) ; # '权限';
declare v_display int(1) DEFAULT '0';
declare v_menu_category int(1) ; # '菜单类型';


#查询pid = curmenuid 的 下级菜单 记录
declare cursor_name cursor for
 select  menu_id,pid,sub_count,type,title,name,component ,menu_sort, icon,path,i_frame,cache,hidden,permission,display,menu_category 
        from sys_menu
        where pid = params_pid
        order by menu_sort asc;
declare continue handler for not found set done = true;

SET @@max_sp_recursion_depth = 10;
#select params_pid,params_new_pid;
open cursor_name;
#获取下级菜单
info_loop:loop

	fetch cursor_name into v_menu_id,v_pid,v_sub_count,v_type,v_title,v_name,v_component ,v_menu_sort ,v_icon,v_path,v_i_frame,v_cache,v_hidden,v_permission,v_display,v_menu_category ;
	if done then
		leave info_loop;
	end if;	

	#生成新的菜单
  insert into sys_menu
	(pid,sub_count,type,title,name,component ,menu_sort, icon,path,i_frame,cache,hidden,permission,display,menu_category)
	values(params_new_pid,v_sub_count,v_type,v_title,CONCAT(v_name,'_link'),v_component ,v_menu_sort ,v_icon,v_path,v_i_frame,v_cache,v_hidden,v_permission,v_display,v_menu_category );
	SELECT params_new_pid,v_menu_id,v_pid,v_sub_count,v_type,v_title,v_name,v_component ,v_menu_sort ,v_icon,v_path,v_i_frame,v_cache,v_hidden,v_permission,v_display,v_menu_category ;
	


	#获取v_new_pid
	select max(menu_id) into v_new_pid from sys_menu;

  #查一下菜单v_menu_id有没有子菜单
  select count(1) into counts from sys_menu where  pid = v_menu_id ;
  
  if counts>0 then 
	select v_menu_id ;
	  #SELECT CONCAT('select  * from sys_menu where  pid =',v_menu_id);
		call menu_copybypid(v_menu_id,v_new_pid) ;
	end if; 
 
end loop info_loop;

end