oracle获取中文拼音/WB

1.oracle获取中文拼音首字母

CREATE OR REPLACE FUNCTION      FUNC_GET_PY(z varchar2)
RETURN varchar2
IS
  py varchar2(20);  --与源字符对应的拼音码
        curr_no integer;  --当前字符序号
  maxlen  integer;  --z(源字符串)的最大长度
  l_schar varchar2(100);   --截取后的源字符串
  py_char char(1);  --当前字符的拼音码
  l_vzchar varchar2(2);  --当前字符
  py_flag smallint;  --当前字符是否存在拼音码的标志
  curr_len integer;       --当前拼音码长度
BEGIN
  --初始化参数
  curr_no:=1;
  py:='';
  py_char:='';
  l_vzchar:='';
  py_flag:=0;
  l_schar:=trim(z);
  maxlen:=length(l_schar);
  if maxlen = 0 then
    return '';
  end if;
  curr_len := 0;
  --拼音码的最大长度为20且不超过字符的最大长度
<<L>>  while curr_no <= maxlen and curr_len < 20 loop
  --l_vzchar:=substr(z,curr_no,1);
  select substr(l_schar,curr_no,1) into l_vzchar from dual;
  select count(py) into py_flag from trans_twzk where z = l_vzchar;
  if py_flag = 1 then
    select py into py_char from trans_twzk where z = l_vzchar;
    --py_char:=nvl(py_char,' ');
        else
    --'%-+~.'用其本身代替
    if l_vzchar = '%' or l_vzchar = '-' or l_vzchar = '+' or l_vzchar = '~' or l_vzchar = '.' then
      py_char:=l_vzchar;
    --'()（）［］'和' '则跳过
    elsif l_vzchar = '(' or l_vzchar = ')' or l_vzchar = '（' or l_vzchar = '）' or l_vzchar = '［' or l_vzchar = '］' or l_vzchar=' ' or l_vzchar='[' or l_vzchar=']' then
      select  substr(l_schar,1,curr_no-1) || substr(l_schar,curr_no+1,length(l_schar)) into l_schar from dual;  --跳过当前字符串
      maxlen:=maxlen-1;  --跳过则源字符长度减1
      goto l;
    --其他的未找到的字符(如汉字)则用'z'来代替
    else
      py_char:='z';
    end if;
        end if;
        curr_no:=curr_no+1;
  py:=py || py_char;
  curr_len := length(py);
  end loop;
  return trim(py);
END;

2.获取WB

CREATE OR REPLACE FUNCTION      FUNC_GET_WB(z varchar2)
RETURN varchar2
IS
  wb varchar2(20);
        curr_no integer;  --当前字符串序号
  maxlen  integer;  --z的最大长度
  l_schar varchar2(500);
  wb_char char(1);  --当前字符的拼音码
  l_vzchar varchar2(2);  --当前字符
  wb_flag smallint;
BEGIN
  --初始化参数
  --maxlen:=length(z);
  l_schar:=trim(z);
  maxlen:=length(l_schar);
  curr_no:=1;
  wb:='';
  wb_char:='';
  l_vzchar:='';
  wb_flag:=0;
  if maxlen = 0 then
    return '';
  end if;
  --拼音码的最大长度为6且不超过字符的最大长度
<<L>>  while curr_no < 20 and curr_no <= maxlen loop
  --l_vzchar:=substr(z,curr_no,1);
  select substr(l_schar,curr_no,1) into l_vzchar from dual;
  --判断是否有对应的五笔码
  select count(wb) into wb_flag from trans_twzk where z = l_vzchar;
  if wb_flag = 1 then
    select wb into wb_char from trans_twzk where z = l_vzchar;
    --wb_char:=nvl(wb_char,' ');
        --没有对应的五笔码的处理
  else
    --'%-+~.'字符用其本身代替
    if l_vzchar = '%' or l_vzchar = '-' or l_vzchar = '+' or l_vzchar = '~' or l_vzchar = '.' then
      wb_char:=l_vzchar;
    --'()（）［］'和' '则跳过
    elsif l_vzchar = '(' or l_vzchar = ')' or l_vzchar = '（' or l_vzchar = '）' or l_vzchar = '［' or l_vzchar = '］' or l_vzchar=' ' then
      select  substr(l_schar,1,curr_no-1) || substr(l_schar,curr_no+1,length(l_schar)) into l_schar from dual;
      maxlen:=maxlen-1;
      goto l;
    --其他的未找到的字符(如汉字)则用'z'来代替
    else
      wb_char:='z';
    end if;
        end if;
        curr_no:=curr_no+1;
  wb:=wb || wb_char;
  end loop;
  return wb;
END;

3.获取年龄

CREATE OR REPLACE function      fun_age(age in varchar2) return number is
  FunctionResult number;
  f_age varchar2(10);
  h_age varchar2(10);
begin
  if age is null or trim(age) = '' then
    FunctionResult := '';
  else
  select to_number(substr(age,0,length(age)-1)) into f_age from dual;
  select substr(age,-1) into h_age from dual;
 if h_age = 'Y' then
 FunctionResult := f_age;
 else
   FunctionResult :=0;
  end if;
 end if;
  return FunctionResult;
   exception
       when others then
         return 0;
end fun_age;

#4.

CREATE OR REPLACE function      fun_age_md(age in varchar2) return varchar2 is
  FunctionResult varchar2(2);
  f_age varchar2(10);
  h_age varchar2(10);
begin
  if age is null or trim(age) = '' then
    FunctionResult := '';
  else
  select to_number(substr(age,0,length(age)-1)) into f_age from dual;
  select substr(age,-1) into h_age from dual;
 if h_age = 'M' then
 FunctionResult := f_age;
 else if h_age = 'D' then
  FunctionResult := f_age;
   end if ;
  end if;
 end if;
  return FunctionResult;
   exception
       when others then
         return 0;
end fun_age_md;