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

来源：互联网发布：微店淘宝搬家编辑：程序博客网时间：2024/04/28 20:44

/*取得姓名对应的拼音*/

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;

-- 对应字典表结构

create table qbzhpt_dm.T_DM_SZM( hz VARCHAR2(2), py VARCHAR2(8), ym VARCHAR2(2), szm VARCHAR2(2))tablespace TS_DT_DM pctfree 10 initrans 1 maxtrans 255 storage ( initial 448 minextents 1 maxextents unlimited );-- Add comments to the table comment on table qbzhpt_dm.T_DM_SZM is '汉字首字母';

数据字典请从本处下载：http://download.csdn.net/detail/zhangbo936/3855704