declare v_pinyin VARCHAR(200);
#替换各种特殊符号
select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(v_hanzi,
'Ａ','A'),'Ｂ','B'),'Ｃ','C'),'Ｄ','D'),'Ｅ','E'),'Ｆ','F'),'Ｇ','G'),'Ｈ','H'),
'Ｉ','I'),'Ｊ','J'),'Ｋ','K'),'Ｌ','L'),'Ｍ','M'),'Ｎ','N'),'Ｏ','O'),'Ｐ','P'),
'Ｑ','Q'),'Ｒ','R'),'Ｓ','S'),'Ｔ','T'),'Ｕ','U'),'Ｖ','V'),'Ｗ','W'),'Ｘ','X'),
'Ｙ','Y'),'Ｚ','Z'),'＋',''),'－',''),'＊',''),'／',''),
'[',''),']',''),'{',''),'}',''),'（',''),'）',''),
'<',''),'>',''),'《',''),'》',''),'(',''),')',''),'"',''),
'“',''),'”',''),'.',''),' 。',''),'-',''),'－',''),'/',''),
'／',''),'',''),' ',''),'１','一'),'２','二'),'３','三'),
'４','四'),'５','五'),'６','六'),'７','七'),'８','八'),'９','九'),'０','零') R3
into V_NAME_TEMP
from dual;
if v_type = '1' then #简拼
set @V_NAME_TEMP=V_NAME_TEMP;
set @strResult=null;
call Prc_Get_Pym(@V_NAME_TEMP, @strResult);
elseif v_type = '2' then #尾全拼
#判断结尾字符是否是中文
select ltrim(rtrim(substr(V_NAME_TEMP, char_length(V_NAME_TEMP), char_length(V_NAME_TEMP))))
into v_subb
from dual;
if v_subb is null then #如果不是中文则直接生成开口码
set @V_NAME_TEMP=V_NAME_TEMP;
set @strResult=null;
call Prc_Get_Pym(@V_NAME_TEMP, @strResult);
else
select substr(V_NAME_TEMP, 1, char_length(V_NAME_TEMP)-1) into strHanzi from dual;
set @strHanzi=strHanzi;
set @strTemp1=null;
call Prc_Get_Pym(@strHanzi, @strTemp1);
select substr(V_NAME_TEMP, char_length(V_NAME_TEMP), char_length(V_NAME_TEMP)) into strHanzi from dual;
set @strHanzi=strHanzi;
set @strTemp=null;
call Sp_Pinyin(@strHanzi, @strTemp);
select substr(@strTemp, 1, char_length(@strTemp) - 1) into @strResult from dual;
select concat_ws('',@strTemp1,@strResult) into @strResult from dual;
end if;
elseif v_type = '3' then #首全拼
#判断开头字符是否是中文
select ltrim(rtrim(substr(V_NAME_TEMP, 1, 1))) into v_subb from dual;
if v_subb is null then #如果不是中文则直接生成开口码
set @V_NAME_TEMP=V_NAME_TEMP;
set @strResult=null;
call Prc_Get_Pym(@V_NAME_TEMP, @strResult);
else
select substr(V_NAME_TEMP, 2, char_length(V_NAME_TEMP)) into strHanzi from dual;
set @strHanzi=strHanzi;
set @strResult=null;
call Prc_Get_Pym(@strHanzi, @strResult);
select substr(V_NAME_TEMP, 1, 1) into strHanzi from dual;
set @strHanzi=strHanzi;
set @strTemp=null;
call Sp_Pinyin(@strHanzi, @strTemp);
select concat_ws('',substr(@strTemp, 1, char_length(@strTemp) - 1),@strResult) into @strResult from dual;
end if;
end if;
set v_pinyin=UPPER(@strResult);
return v_pinyin;
end;
$$
delimiter ;
4. 使用方法/案例