CREATE DEFINER=`root`@`%` PROCEDURE `p_phone_comments`()BEGIN#################################自动计算手机样本内容属于哪一类型的评价因子的过程##############################################@author Ivan 2862099249@qq.com#@create 2014年12月17日 下午12:20:44 #@update xxxx年xx月xx日  xxxxxxx by xxx#################################自动计算手机样本内容属于哪一类型的评价因子的过程#############################################DECLARE p_typ_id int default 0 ;DECLARE p_keyword varchar(5000) default ‘‘ ;DECLARE done INT DEFAULT 0; DECLARE p_contents varchar(5000) default ‘‘ ;DECLARE p_id char(36) default ‘‘ ;DECLARE p_count int default 0;DECLARE p_i int;DECLARE p_split_key varchar(30) default ‘‘;DECLARE p_iscontain int default 0;DECLARE cur_keys cursor for select t.typ_id,group_concat(t.keyword) from keyword_dic t group by t.typ_id;DECLARE cur_sample cursor for select tt.id, tt.contents from t_comments_sample2 tt;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;#清空上次运行的结果,默认将评价因子置为0UPDATE t_comments_sample2 SET     price = 0,    experience = 0,    function = 0,    brandrelate = 0,    surface = 0,    advert = 0,    servicefeedback = 0,    quality = 0;open cur_keys;    keys_loop: LOOP  #第一层循环start：评价因子         set done=0;          FETCH cur_keys INTO p_typ_id, p_keyword;           IF done=1 THEN              LEAVE keys_loop;         else            open cur_sample;                     sample_loop: LOOP #第二层循环start:样本                      FETCH cur_sample INTO p_id, p_contents;                IF done=1 THEN                              LEAVE sample_loop;                else                            set p_iscontain = 0;                    set p_count = (CHAR_LENGTH(p_keyword) - CHAR_LENGTH(REPLACE(p_keyword, ‘,‘, ‘‘))) + 1;                    split_key_while: while p_count > 0 #第三层循环start:判断样本内容是否包含评价因子的关键字                        do                                  set p_split_key = SUBSTRING_INDEX(SUBSTRING_INDEX(p_keyword, ‘,‘, -(p_count)),‘,‘,1);                            IF (LOCATE(p_split_key,p_contents) > 0) THEN                                #跳出循环                                set p_iscontain = 1;                                            LEAVE split_key_while;                                      end if;                            set p_count = p_count - 1;                            end while split_key_while;#第三层循环end                                    if p_iscontain = 1 then                                  #更新id对应的记录                                  if p_typ_id = 5 then                                       update t_comments_sample2 set price = 1 where id = p_id and price = 0;                                  elseif p_typ_id = 6 then                                       update t_comments_sample2 set experience = 1 where id = p_id and experience = 0;                                  elseif p_typ_id = 4 then                                       update t_comments_sample2 set function = 1 where id = p_id and function = 0;                                  elseif p_typ_id = 1 then                                       update t_comments_sample2 set brandrelate = 1 where id = p_id and brandrelate = 0;                                  elseif p_typ_id = 2 then                                       update t_comments_sample2 set surface = 1 where id = p_id and surface = 0;                                  elseif p_typ_id = 8 then                                       update t_comments_sample2 set advert = 1 where id = p_id and advert = 0;                                  elseif p_typ_id = 7 then                                       update t_comments_sample2 set servicefeedback = 1 where id = p_id and servicefeedback = 0;                                  elseif p_typ_id = 3 then                                       update t_comments_sample2 set quality = 1 where id = p_id and quality = 0;                                  end if;                    end if;                      end if;            end LOOP sample_loop;#第二层循环end                     close cur_sample;         end if;      END LOOP keys_loop;  #第一层循环endclose cur_keys;END