Oracle函数取得姓名对应的拼音

Oracle函数取得姓名对应的拼音

/*Oracle函数取得姓名对应的拼音*/

CREATE OR REPLACE FUNCTION F_GET_PY (V_XM varchar)  RETURN VARCHAR2 IS
  s_py  varchar2(100);
  z_hz  varchar2(2);
  z_py  varchar2(10);
  i     number(3);
  i_jls number(5);
BEGIN
    s_py:='';
    if v_xm is not null then
      for i in 1..length(v_xm) loop
        z_hz := substr(v_xm,i,1);
        select count(*) into i_jls from t_dm_szm where hz=z_hz;
        if i_jls >0 then
          select py into z_py from t_dm_szm where hz=z_hz;
          if i = 1 then
             s_py:=s_py||z_py;
          else
             s_py:=s_py||z_py;
          end if;
        end if;
        if z_hz='·' then
          exit;
        end if;
      end loop;
    end if;

  RETURN s_py;
END;