mysql存储过程

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