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;
#清空上次运行的结果,默认将评价因子置为0
UPDATE 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; #第一层循环end
close cur_keys;
END