20210120mysql存储过程

存储过程大家在网上都能收到一大堆资料，各种定义变量、集合循环集合。今天就我在写存储过程的中间遇到的问题跟大家分享一下，遇到的可以顺利解决。

定义存储过程：

CREATE DEFINER=`root`@`root` PROCEDURE `存储过程名称`()
BEGIN
    -- 写执行的业务逻辑
END;

定义变量：

declare t_id varchar(16);
declare t_name varchar(16);
declare t_age int default(0);

定义集合在这里是游标的概念：

declare list cursor for select id,name from adminuser where ...;

循环游标：

declare CONTINUE handler for not found set flag = '1';
  open list;
    fetch list into t_id,t_name;
   while flag <> 1 do
        //执行通过id 和name 的select或者update语句
        update adminuser set age = '5' where id = t_id and name = t_name;
        ...
    fetch logicWarehouseList into id,name;
   end while;
  close logicWarehouseList;

上面是基础的一些语句，然后就是我在编写的时候遇到的问题,下面是当时执行报错的存储过程：mysql报错信息：（> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare logicWarehouseList cursor for  select y.LOGIC_WAREHOUSE_ID as 'to_logic_' at line 14）

CREATE  PROCEDURE `task`()
BEGIN
  declare margeProvinceCode varchar(16);
  declare srcOrgCode varchar(16);
	declare srcOrgNameMark varchar(64);
	declare toOrgCode varchar(16);
	declare toOrgName varchar(64);
	declare to_logic_warehouse_id varchar(32);
	declare to_logic_warehouse_name varchar(32);
	declare flag int DEFAULT(0);
	#查询字段赋值
	select province_code as 'margeProvinceCode',org_code as 'srcOrgCode',org_name as 'srcOrgNameMark',to_org_code as 'toOrgCode',to_org_name as 'toOrgName' into margeProvinceCode,srcOrgCode,srcOrgNameMark,toOrgCode,toOrgName from marge_record where execute_status = '2' and execution_start_time is not NULL limit 1;
	#查询目标逻辑库集合
	declare logicWarehouseList cursor for  select y.LOGIC_WAREHOUSE_ID as 'to_logic_warehouse_id',y.LOGIC_WAREHOUSE_NAME as 'to_logic_warehouse_name' from logic_warehouse y on y.province_code = margeProvinceCode and y.to_org_code = toOrgCode;
  declare CONTINUE handler for not found set flag = '1';
  open logicWarehouseList;
    fetch logicWarehouseList into to_logic_warehouse_id,to_logic_warehouse_name;
   while flag <> 1 do
				-- 执行业务逻辑
    fetch logicWarehouseList into to_logic_warehouse_id,to_logic_warehouse_name;
   end while;
  close logicWarehouseList;
END

但是通过查询别的资料会发现这样写语法没有问题，而且单独执行上半部分没问题下半部分也没问题：

上半部分执行成功：

下半部分执行成功：

但是全部执行直接报错了：

然后各种查资料搞不定尝试的把这上面的查询字段赋值语句调整到下面（这里查询目标逻辑库集合用到了上面定义的值需要调整sql直接查出来了）：我的mysql版本是5.6.49，不知道是不是版本的问题查阅的资料提到版本的也不多。

总之算一个坑了。