函数一:func_get_split_string
CREATE FUNCTION `func_get_split_string`(
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
函数二:func_get_split_string_total
CREATE FUNCTION `func_get_split_string_total`(
f_string varchar(1000),f_delimiter varchar(5)
) RETURNS int(11)
BEGIN
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END
存储过程一:
CREATE PROCEDURE `useCursor`(in articleId int)
BEGIN
declare tmpId int;
declare tmpArticles varchar(255) default '' ;
declare allId varchar(255) default '' ;
declare allArticles varchar(255) default '' ;
declare i INT DEFAULT 0;
declare j INT DEFAULT 0;
declare i_count int;
declare j_count int;
declare beginNum int;
declare endNum int;
declare sSql varchar(4000);
declare cur1 CURSOR FOR SELECT id,articles FROM a1;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpId = null;
set sSql = 'select * from a1 where';
OPEN cur1;
FETCH cur1 INTO tmpId,allArticles;
WHILE (tmpId is not null) DO
SELECT func_get_split_string_total(allArticles,',') into i_count;
set i = 0;
while i < i_count do
set i = i + 1;
select func_get_split_string(allArticles,',',i) into tmpArticles;
SELECT func_get_split_string_total(tmpArticles,'-') into j_count;
if (j_count =1) then
select func_get_split_string(tmpArticles,'-',1) into beginNum;
if (beginNum = articleId) then
set allId = CONCAT(allId ,tmpId,',') ;
set sSql = CONCAT(sSql,' id = ',tmpId,' or');
end if;
end if;
if (j_count = 2) then
select func_get_split_string(tmpArticles,'-',1) into beginNum;
select func_get_split_string(tmpArticles,'-',2) into endNum;
if (articleId >= beginNum and articleId <= endNum) then
set allId = CONCAT(allId ,tmpId,',') ;
set sSql = CONCAT(sSql,' id = ',tmpId,' or');
end if;
end if;
end while;
FETCH cur1 INTO tmpId,allArticles;
END WHILE;
CLOSE cur1;
if (length(allId) > 0) then
set sSql = substring(sSql,1,length(sSql)-2);
end if;
set @sQuery = sSql;
prepare stmt from @sQuery;
execute stmt;
END