此例是:传入中文,返回中文首字母的函数,用pl/sql测试时,传入中文beforeword 这个变量接收到的是乱码
create or replace function getPY
(
str in varchar2
)
return varchar2
as
beforeword varchar2(2000);
word varchar2(2);
py varchar2(2000);
num number(6);
begin
beforeword := str;
py:='';
while Length(beforeword) > 0 loop
word := Substr(beforeword,1,1);
num := Ascii(word);
if num between 33088 and 65185 then
if(num>=45217 and num<=45252) then
py:=py || 'A';
elsif(num>=45253 and num<=45760) then
py:=py || 'B';
elsif(num>=45761 and num<=46317) then
py:=py || 'C';
elsif(num>=46318 and num<=46825) then
py:=py || 'D';
elsif(num>=46826 and num<=47009) then
py:=py || 'E';
elsif(num>=47010 and num<=47296) then
py:=py || 'F';
elsif(num>=47297 and num<=47613) then
py:=py || 'G';
elsif(num>=47614 and num<=48118) then
py:=py || 'H';
elsif(num>=48119 and num<=49061) then
py:=py || 'I';
elsif(num>=49062 and num<=49323) then
py:=py || 'J';
elsif(num>=49324 and num<=49895) then
py:=py || 'K';
elsif(num>=49896 and num<=50370) then
py:=py || 'L';
elsif(num>=50371 and num<=50613) then
py:=py || 'M';
elsif(num>=50614 and num<=50621) then
py:=py || 'N';
elsif(num>=50622 and num<=50905) then
py:=py || 'O';
elsif(num>=50906 and num<=51386) then
py:=py || 'P';
elsif(num>=51387 and num<=51445) then
py:=py || 'Q';
elsif(num>=51446 and num<=52217) then
py:=py || 'R';
elsif(num>=52218 and num<=52697) then
py:=py || 'S';
elsif(num>=52698 and num<=52979) then
py:=py || 'T';
elsif(num>=52980 and num<=53688) then
py:=py || 'X';
elsif(num>=53689 and num<=54480) then
py:=py || 'Y';
elsif(num>=54481 and num<=62289) then
py:=py || 'Z';
else
py:=py || word;
end if;
else
py:=py || word;
end if;
beforeword := Substr(beforeword,2,Length(beforeword)-1);
end loop;
return py;
end;
create or replace function getPY
(
str in varchar2
)
return varchar2
as
beforeword varchar2(2000);
word varchar2(2);
py varchar2(2000);
num number(6);
begin
beforeword := str;
py:='';
while Length(beforeword) > 0 loop
word := Substr(beforeword,1,1);
num := Ascii(word);
if num between 33088 and 65185 then
if(num>=45217 and num<=45252) then
py:=py || 'A';
elsif(num>=45253 and num<=45760) then
py:=py || 'B';
elsif(num>=45761 and num<=46317) then
py:=py || 'C';
elsif(num>=46318 and num<=46825) then
py:=py || 'D';
elsif(num>=46826 and num<=47009) then
py:=py || 'E';
elsif(num>=47010 and num<=47296) then
py:=py || 'F';
elsif(num>=47297 and num<=47613) then
py:=py || 'G';
elsif(num>=47614 and num<=48118) then
py:=py || 'H';
elsif(num>=48119 and num<=49061) then
py:=py || 'I';
elsif(num>=49062 and num<=49323) then
py:=py || 'J';
elsif(num>=49324 and num<=49895) then
py:=py || 'K';
elsif(num>=49896 and num<=50370) then
py:=py || 'L';
elsif(num>=50371 and num<=50613) then
py:=py || 'M';
elsif(num>=50614 and num<=50621) then
py:=py || 'N';
elsif(num>=50622 and num<=50905) then
py:=py || 'O';
elsif(num>=50906 and num<=51386) then
py:=py || 'P';
elsif(num>=51387 and num<=51445) then
py:=py || 'Q';
elsif(num>=51446 and num<=52217) then
py:=py || 'R';
elsif(num>=52218 and num<=52697) then
py:=py || 'S';
elsif(num>=52698 and num<=52979) then
py:=py || 'T';
elsif(num>=52980 and num<=53688) then
py:=py || 'X';
elsif(num>=53689 and num<=54480) then
py:=py || 'Y';
elsif(num>=54481 and num<=62289) then
py:=py || 'Z';
else
py:=py || word;
end if;
else
py:=py || word;
end if;
beforeword := Substr(beforeword,2,Length(beforeword)-1);
end loop;
return py;
end;
而服务器端和客户端为同一字符集,那么汉字可以显示,但存储方式不太一样。
用SUBSTR取出的东西可能会是乱码。
而且,在我用的数据库上,测试,“呵”这个汉字的ASCII码为15045045
似乎你的逻辑不能处理呢。
那就光测试这个
create or replace function getZH
(
str in varchar2
)
return varchar2
as
zh varchar2(200);
begin
zh := str;--就是这里赋值的时候就变化了
return zh;
end;