drop procedure if exists proc134f; CREATE PROCEDURE proc134f() BEGIN declare kidoo int; declare tid int; declare stroo int; declare str varchar(255); declare strlength int; declare istr varchar(3); declare istr_cnt int; declare done int;-- 游標用 declare cur_kid CURSOR for select kid from spam_keyword;-- 游標用 declare continue handler for not found set done=1;-- 游標用,據說是直達引擎的通道 set tid=0; delete from t; open cur_kid;-- 開啟游標 loop1:loop-- 開啟1層外循環 fetch cur_kid into kidoo; -- 從定義的范圍中讀取下一行並賦予 if done=1 then leave loop1;end if;-- 判斷是否 found 下一行,否則跳出 select word into str from spam_keyword where kid=kidoo; set str=replace(str,' ',''); set strlength=char_length(str); set stroo=0; loop2:loop-- 2層內循環 set stroo=stroo+1; set istr=substr(str,stroo,1); select count(*) into istr_cnt from t where t=istr;-- 計數 if istr_cnt<>0 then update t set cnt=cnt+1 where t=istr;else set tid=tid+1; insert into t set id=tid,t=istr,cnt=1;end if; if stroo>=strlength then leave loop2;end if;end loop loop2; set done=0; end loop loop1; close cur_kid;-- 關閉游標 select * from t order by cnt desc; END;