MySQL实现汉字转拼音，赶快一起学起来( 二 ) _MySQL

'Ｑ','Q'),'Ｒ','R'),'Ｓ','S'),'Ｔ','T'),'Ｕ','U'),'Ｖ','V'),'Ｗ','W'),'Ｘ','X'),
'Ｙ','Y'),'Ｚ','Z'),'＋',''),'－',''),'＊',''),'／',''),
'[',''),']',''),'{',''),'}',''),'（',''),'）',''),
'<',''),'>',''),'《',''),'》',''),'(',''),')',''),'"',''),
'“',''),'”',''),'.',''),' 。',''),'-',''),'－',''),'/',''),
'／',''),'',''),' ',''),'１','一'),'２','二'),'３','三'),
'４','四'),'５','五'),'６','六'),'７','七'),'８','八'),'９','九'),'０','零') R3
into V_NAME_TEMP
from dual;

#循环获得字符串拼音码
myloop:loop
if V_NAME is null then
leave myloop;
end if;

select substr(V_NAME_TEMP, i, 1) into V_NAME_SIN from dual;
set i=i+1;
if V_NAME_SIN <> ' ' then
select count(*)
into v_counter1
from bst_wbjq
where bst_wbjq.charactor=v_name_sin;
if v_counter1 > 0 then
select WORD
into V_PINYIN_SIN
from bst_wbjq
where bst_wbjq.CHARACTOR=V_NAME_SIN
limit 1;
select concat_ws('',V_PINYIN_TEMP,V_PINYIN_SIN)
into V_PINYIN_TEMP
from dual;
end if;
end if;
select char_length(V_NAME) into j from dual;
if i > j then
leave myloop;
end if;
end loop;
#截取32位长度字符
if char_length(V_PINYIN_TEMP) > 32 then
select substr(V_PINYIN_TEMP, 1, 32) into V_PYM from dual;
else
select V_PINYIN_TEMP into V_PYM from dual;
end if;
end;
$$
delimiter ;
3.2. 创建存储过程：SP_PINYIN -- ----------------------------
-- procedure structure for SP_PINYIN
-- ----------------------------
delimiter $$
drop procedure if exists SP_PINYIN;
$$
create procedure SP_PINYIN(IN hanzi varchar(256),OUT pinyin varchar(256))
begin
declare aword varchar(200);
declare aresult varchar(200);
declare temp1 varchar(20);
declare len int default 0;
declare point int default 1;
declare charword varchar(20);
declare charlen int default 1;
#定义游标标志变量
declare done int default false;
#定义游标
declare cur_pinyin cursor for
select PY from TBL_PINYIN
where word=substr(aword, point, charlen);
#指定游标循环结束时的返回值
declare continue HANDLER for not found set done=true;
select ltrim(rtrim(hanzi)) into aword from dual;
select char_length(aword) into len from dual;
#<<LABEL1>>
while point <= len do
select '' into temp1 from dual;
select substr(aword, point, 1) into charword from dual;
if (charword is not null and charword != ' ') then
select concat_ws(' ',aresult,charword) into aresult from dual;
else
select 2 into charlen from dual;
end if;
#打开游标
open cur_pinyin;
#开始循环处理游标里的数据
read_loop:loop
#获得游标当前指向的一条数据
fetch cur_pinyin into temp1;
#判断游标的循环是否结束
if done then
leave read_loop;
end if;
end loop; #结束游标循环
#关闭游标
close cur_pinyin;
if (point = 1) then
set aresult = temp1;
else
select concat_ws(' ',aresult,temp1) into aresult from dual;
end if;
select point+charlen into point from dual;
end while;
#输出结果
select aresult into pinyin from dual;
end;
$$
delimiter ;
3.3. 创建函数：to_pinyin -- ----------------------------
-- function structure for to_pinyin
-- ----------------------------
delimiter $$
drop function if exists to_pinyin;
$$
create function to_pinyin(v_hanzi varchar(256),v_type int)
returns varchar(256)
begin
declare strTemp VARCHAR(200);
declare strResult VARCHAR(200);
declare strHanzi VARCHAR(200);
declare strTemp1 VARCHAR(200);
declare v_subb VARCHAR(100);
declare V_NAME_TEMP VARCHAR(200);

MySQL实现汉字转拼音，赶快一起学起来( 二 )

推荐阅读

秦女士|办了卡买了课，它却再也没营业……你常去的健身房还好吗？

【勇哥科技】Pro+长焦体验：双结构OIS防抖，手持夜拍也清晰，荣耀30

【火爆新闻】李健、李思思不算啥，看到她：惊掉下巴，清华北大毕业的艺人

资本实力|哈尔滨银行连续两年位居 “全球银行1000强”第199位

前任房主欠热费,现任房主可说“不”！

女生叫你“哥”意味着什么？

『吉祥说历史』影响中国的历史，最后还能全身而退归隐山林，唐朝最重要的宰相

世界超模中国有哪几位中国第一个国际超模是谁

小米|小米12岁生日！雷军发长文感慨：千万米粉造就了最年轻500强企业

美国|同样是吃生肉：美国是牛肉，日本是鱼肉，印度是蛇肉，中国呢？

殷文富ti|“生死状”？，中国很长的火车线，绕8省11座城市，坐个车还得签

淮安名菜软兜的做法,淮安软兜的制作方法-

可爱多不多|北京卫视《旗袍美探》祝全天下的老师们节日快乐

修复|征信记录日趋严格，万一逾期了怎么办？

明日之后庄园怎么升级?

王者荣耀|王者低星局看见队友选出这几个英雄，心就凉了半截!

诗人|白居易在大雪纷的夜里想喝酒，写下一首趣诗，这样的诗魔当真可爱

央视网|告急！鄱阳湖水位还在涨！

蛋托|当妈妈“被迫”学会各种手工后，她就连蛋托都不放过了

混在唐朝当驸马唐朝的驸马有实权吗