清洗数据：原始数据和码表比对中文字个数[mysql]

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_count_20161015`()
begin    
		declare v_sql varchar(1000);
		declare j ,jmax,i,icnt int;
		declare k int;
		declare kword,jdisease_name,idisease_name,idcode varchar(1000);
		-- set sql_safe_updates=0; --需要在外部执行下这句语句 mysql才能允许执行update 操作
		set j=6986;
		select max(rid) into jmax from tmp_disease_tb_b;  -- tmp_disease_tb_b待判定的原始表
		
		repeat
			select disechiname into jdisease_name from tmp_disease_tb_b where rid=j;
            truncate table tmp_count_tb; -- 辅助表
            commit;
			set k=1;
			select substr(jdisease_name,k,1) into kword from dual;
			repeat
				set v_sql=concat('insert into tmp_count_tb select dcode,dname,''', kword,''' from tmp_disease_code5 where dname like ''%',kword,'%''');
                -- tmp_disease_code5 是icd-10码表
                set @v_sql=v_sql;
				prepare stmt from @v_sql;
				EXECUTE stmt;
				deallocate prepare stmt; 
				
				set k=k+1;
				select substr(jdisease_name,k,1) into kword from dual;
			until (kword='')
			end repeat;
			
            select a.dis_name,count(*),a.dis_code into idisease_name ,icnt,idcode
			from tmp_count_tb a 
			group by a.dis_name,a.dis_code order by count(*) desc,length(a.dis_name) asc limit 0,1;
            commit;
			update tmp_disease_tb_b set prd2=idisease_name,icnt=icnt,idcode=idcode where rid=j;            
			commit;			
			select j;
			set j=j+1;
		until j>jmax
		end repeat;	
    
    
end$$
DELIMITER ;