mysql 循环存储过程

最新推荐文章于 2024-10-13 21:08:42 发布

xxxx1243

最新推荐文章于 2024-10-13 21:08:42 发布

阅读量88

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/xxxx1243/article/details/84296090

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

[u][/u]-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `splitStringTotalFunction`(
f_string varchar(1000),f_delimiter varchar(5)
) RETURNS int(11)
BEGIN
declare returnInt int(11);
if length(f_delimiter)=2 then
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')))/2;
else
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
end if;
END

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `splitStringFunction`(
f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END

set i = 1;
if(i_inventoryID is not null and length(i_inventoryID) <> 0) then
while i <= splitStringTotalFunction(i_inventoryID,',')
do
set dictCode = splitStringFunction(i_inventoryID,',',i);
/*终端料仓库盘盈rfid字符串*/
call stockTerminalGainRfidsProcedure(dictCode,null,null,stockTerminalGainRfids);
if(stockTerminalGainRfids <> '' and stockTerminalGainRfids is not null) then
set stockGainRfids = concat('',stockTerminalGainRfids,'' ,',') ;
end if;
set i = i + 1;
end while;
end if;

set i = 1;
if(i_inventoryID is not null and length(i_inventoryID) <> 0) then
while i <= splitStringTotalFunction(i_inventoryID,',')
do
set dictCode = splitStringFunction(i_inventoryID,',',i);
/*实体库盘盈rfid字符串*/
call stockStoreGainRfidsProcedure(dictCode,null,null,stockStoreGainRfids);
if(stockStoreGainRfids <> '' and stockStoreGainRfids is not null) then
set rfid_temp = concat('',stockStoreGainRfids,'' ,',') ;
end if;
set i = i + 1;
end while;
end if;

if(stockGainRfids <> '' and stockGainRfids is not null) then
set stockGainRfids = concat(stockGainRfids,rfid_temp) ;
else
set stockGainRfids = rfid_temp ;
end if;

set @ch = substring(stockGainRfids, -1, 1);
if(@ch = ',') then
set stockGainRfids = left(stockGainRfids, CHAR_LENGTH(stockGainRfids) - 1);
end if;