CREATE FUNCTION func_split
(f_string
text character set utf8 ,f_delimiter
varchar(5),f_order
int)
RETURNS varchar(9999)
BEGIN
DECLARE result VARCHAR(9999) character set utf8 default ‘’;
set result=REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END ;
select func_split(‘a,ab,abc,abcd,abcde,abcdef’,’,’,3);
abc;
select func_split(‘a|ab|abc|abcd|abcde|abcdef’,’|’,4);
abcd;
CREATE DEFINER = zkf
@localhost
FUNCTION func_split_TotalLength
(f_string
text character set utf8 ,f_delimiter
varchar(5))
RETURNS int(11)
BEGIN
RETURN 1+(LENGTH(f_string)-LENGTH(replace(f_string,f_delimiter,’’)));
END ;
select func_split_TotalLength(‘a,ab,abc,abcd,abcde,abcdef’,’,’);
6;
select func_split_TotalLength(‘a|ab|abc|abcd|abcde’,’|’);
5;
通常嵌套再循环中使用对每个结果操作。如下:
CREATE PROCEDURE D_AUTHORITY
(IN d_authority_id
varchar(20000),IN controlParameter
varchar(8888))
BEGIN
DECLARE cnt int DEFAULT 0;
DECLARE i int DEFAULT 0;
set cnt=func_split_TotalLength(d_authority_id,’,’);
while i < cnt
do
set i=i+1;
update db_role r
set r.authority_ids = replace(r.authority_ids,CONCAT(func_split(d_authority_id,’,’,i),’,’),’’);
update db_role r
set r.authority_ids = replace(r.authority_ids,CONCAT(’,’,func_split(d_authority_id,’,’,i)),’’);
update db_authority a
set a.delete_authority = ‘1’ where a.authority_id=func_split(d_authority_id,’,’,i);
end WHILE;
END;
————————————————
from:
版权声明:本文为CSDN博主「大声笑」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_29573217/article/details/77233730