Duplicate handler declared in the same block

下面代码会报错：

DROP PROCEDURE IF EXISTS P3;

CREATE PROCEDURE P3 () BEGIN

	DECLARE flag INT DEFAULT 0;  -- 标记是否有该字段
	DECLARE columName VARCHAR(100);  -- 标记是否有该字段
	DECLARE v_done_column int DEFAULT FALSE; /*创建游标结束标志变量*/ 

	DECLARE tableName VARCHAR(100);
  DECLARE v_done_table int DEFAULT FALSE; /*创建游标结束标志变量*/ 

	/*设置游标结束时v_done的值为true，可以v_done来判断游标是否结束了*/ 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done_table=TRUE; 
	/*设置游标结束时v_done的值为true，可以v_done来判断游标是否结束了*/ 
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done_column=TRUE;
	
	DECLARE all_table CURSOR FOR SELECT TABLE_NAME FROM ams_table  WHERE VALID_FLAG = 1;
	DECLARE all_column CURSOR FOR SELECT COLUMN_NAME FROM information_schema.COLUMNS  WHERE table_name = tableName;
	
	OPEN all_table;
	
	loop_table:LOOP
		FETCH all_table INTO tableName;
		IF v_done_table THEN 
			LEAVE loop_table; 
		ELSE 
			
				OPEN all_column;
				
				loop_colum:LOOP
					FETCH all_column INTO columName;
					IF v_done_column THEN 
						LEAVE loop_colum; 
					ELSE 
						IF flag = 1 THEN
							LEAVE loop_colum;
						ELSE
							IF columName = 'SYS_FILE_SIZE' THEN
								SET flag = 1;
							END IF;
						END IF;
					END IF;
				END LOOP;
				IF flag = 1 THEN 
					SELECT 'get' FROM DUAL;
				ELSE
					SELECT 'no_get' FROM DUAL;
				END IF;			

		END IF;
	END LOOP;
END;

修改后：

DROP PROCEDURE IF EXISTS P2;

CREATE PROCEDURE P2 () BEGIN
	DECLARE tableName VARCHAR(100);
  DECLARE v_done_table int DEFAULT FALSE; /*创建游标结束标志变量*/ 
	DECLARE all_table CURSOR FOR SELECT TABLE_NAME FROM ams_table  WHERE VALID_FLAG = 1;
	/*设置游标结束时v_done的值为true，可以v_done来判断游标是否结束了*/ 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done_table=TRUE; 
	
	OPEN all_table;
	
	loop_table:LOOP
		FETCH all_table INTO tableName;
		IF v_done_table THEN 
			LEAVE loop_table; 
		ELSE 
			
			BEGIN
				DECLARE flag INT DEFAULT 0;  -- 标记是否有该字段
				DECLARE columName VARCHAR(100);  -- 标记是否有该字段
				DECLARE v_done_column int DEFAULT FALSE; /*创建游标结束标志变量*/ 
				DECLARE all_column CURSOR FOR SELECT COLUMN_NAME FROM information_schema.COLUMNS  WHERE table_name = tableName;
				/*设置游标结束时v_done的值为true，可以v_done来判断游标是否结束了*/ 
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done_column=TRUE; 
				
				OPEN all_column;
				
				loop_colum:LOOP
					FETCH all_column INTO columName;
					IF v_done_column THEN 
						LEAVE loop_colum; 
					ELSE 
						IF flag = 1 THEN
							LEAVE loop_colum;
						ELSE
							IF columName = 'SYS_FILE_SIZE' THEN
								SET flag = 1;
							END IF;
						END IF;
					END IF;
				END LOOP;
				IF flag = 1 THEN 
					SELECT 'get' FROM DUAL;
				ELSE
					SELECT 'no_get' FROM DUAL;
				END IF;			
			END;
		END IF;
	END LOOP;
END;

主要是这句话：

游标嵌套

在mysql中同一个error事件只能定义一次，如果多定义的话在编译时会提示 duplicate handler declared in the same block.

每个begin end 块都是一个独立的scope 区域，嵌套的游标可用begin end 包裹。

解决方案参考下文：https://www.cnblogs.com/Ting-light/p/9548356.html