1.定义变量，赋值，判断，输出
DROP PROCEDURE IF EXISTS proc_first; -- 如果proc_first存在就删除
create procedure proc_first()-- 创建存储过程
begin
-- 定义变量
DECLARE age INT;
-- 赋值
set age = 23;
select age;
end;
-- 执行
call proc_first;
1.定义变量，赋值，判断，循环，输出
begin
-- 定义变量
DECLARE modeId INT;
DECLARE depotCode VARCHAR(20);
DECLARE factoryId BIGINT;
DECLARE count INT;
-- 遍历数据结束标志
DECLARE Adone INT DEFAULT FALSE;
-- 查询数据查代发商下的仓库模式对应的仓库数量
DECLARE cursor_factory_modelid_depot CURSOR FOR select d.factoryId,d.modeId,d.depotCode,count(*) as count from scm_factory_depot d GROUP BY d.factoryId,d.modeId;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Adone = TRUE;
-- 打开游标
OPEN cursor_factory_modelid_depot;
read_loop: LOOP
-- 取值 取多个字段
FETCH cursor_factory_modelid_depot INTO factoryId,modeId,depotCode,count;
IF Adone THEN
LEAVE read_loop;
END IF;
-- 如果仓库数量等于1，就把仓库编码同步到UPC表
-- if count=1 THEN
-- update scm_factory_upc u1 set u1.depotCode=depotCode where u1.factoryId=factoryId and u1.modeId=modeId;
-- select '执行update语句';
-- end if;
-- 如果仓库数量大于1，就需要在UPC表新增三条对应的数据，仓库编码不一样
if count>1 THEN
-- 查询数据查代发商下的仓库模式对应的仓库明细
-- select d2.factoryId,d2.modeId,d2.depotCode from scm_factory_depot d2 where d2.factoryId=factoryId and d2.modeId=modeId;
-- 163 5 testdpot
-- 163 5 0001
begin
DECLARE MfactoryId BIGINT;
DECLARE MmodeId INT;
DECLARE MdepotCode VARCHAR(20);
DECLARE Bdone INT DEFAULT FALSE;
DECLARE flag INT;
-- cursor_factory_upc
DECLARE cursor_factory_upc CURSOR FOR select d2.factoryId,d2.modeId,d2.depotCode from scm_factory_depot d2 where d2.factoryId=factoryId and d2.modeId=modeId;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Bdone = TRUE;
-- 打开游标
OPEN cursor_factory_upc;
set flag=1;
read_upc_loop: LOOP
FETCH cursor_factory_upc INTO MfactoryId,MmodeId,MdepotCode;
IF Bdone THEN
LEAVE read_upc_loop;
END IF;
-- SELECT MfactoryId,MmodeId,MdepotCode;
-- 查询出每个仓库的数据
-- select * from scm_factory_upc c1 where c1.factoryId=MfactoryId and c1.modeId=MmodeId;
-- update scm_factory_upc u3 set u3.depotCode=MdepotCode where u3.factoryId=MfactoryId and u3.modeId=MmodeId;
if flag=1 THEN
SELECT '第一个仓库的数据设置了仓库编码',MdepotCode;
-- update scm_factory_upc u3 set u3.depotCode=MdepotCode where u3.factoryId=MfactoryId and u3.modeId=MmodeId;
END if;
if flag>1 THEN
select '插入了数据，仓库是',MdepotCode;
end if;
set flag = flag+1;
END LOOP;
CLOSE cursor_factory_upc;
end;
end if;
END LOOP;
CLOSE cursor_factory_modelid_depot;
end
