mysql 循环存储过程
-- ---------------------------------------------------------------------------------- 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)=2then
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;
页:
[1]