mysql存储过程

来源：互联网发布：网络平台合作方案编辑：程序博客网时间：2024/05/16 18:45

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

阅读全文

1 0