Oracle自定义函数：生成汉字首字母拼音码的函数、MD5

1 生成汉字拼音码的函数

使用方法：

select 用户名.函数名(需要获取首字母拼音码的字段名) from 用户名.表名;
select oracle_user1.fgetpy(t.name) from oracle_user1.student t;

函数定义：

create or replace function fgetpy(v_str varchar2) return varchar2 as
  v_strlen int;
  v_return varchar2(500);
  v_ii     int;
  v_n      int;
  v_c      varchar2(2);
  v_chn    varchar2(2);
  v_rc     varchar2(500);
  /*************************************************************************
  生成汉字拼音码的函数。 2009-06-21
  **************************************************************************/
begin
  --dbms_output.put_line(v_str);
  v_rc     := v_str;
  v_strlen := length(v_rc);
  v_return := '';
  v_ii     := 0;
  while v_ii < v_strlen loop
    v_ii := v_ii + 1;
    v_n  := 63;
    select substr(v_rc, v_ii, 1) into v_chn from dual;
    select v_n + max(rowsf)
      into v_n
      from (select chn, rownum rowsf
              from (select chn
                      from (select '吖' chn
                              from dual
                            union
                            select '八'
                              from dual
                            union all
                            select '嚓'
                              from dual
                            union all
                            select '咑'
                              from dual
                            union all
                            select '妸'
                              from dual
                            union all
                            select '发'
                              from dual
                            union all
                            select '旮'
                              from dual
                            union all
                            select '铪'
                              from dual
                            union all
                            select '丌'
                              from dual --because have no 'i'
                            union all
                            select '丌'
                              from dual
                            union all
                            select '咔'
                              from dual
                            union all
                            select '垃'
                              from dual
                            union all
                            select '嘸'
                              from dual
                            union all
                            select '拏'
                              from dual
                            union all
                            select '噢'
                              from dual
                            union all
                            select '妑'
                              from dual
                            union all
                            select '七'
                              from dual
                            union all
                            select '呥'
                              from dual
                            union all
                            select '仨'
                              from dual
                            union all
                            select '他'
                              from dual
                            union all
                            select '屲'
                              from dual
                            union all
                            select '屲'
                              from dual
                            union all
                            select '屲'
                              from dual
                            union all
                            select '夕'
                              from dual
                            union all
                            select '丫'
                              from dual
                            union all
                            select '帀'
                              from dual
                            union all
                            select v_chn
                              from dual) a
                     order by nlssort(chn, 'NLS_SORT=SCHINESE_PINYIN_M')) c) b
     where chn = v_chn;
    v_c := chr(v_n);
    if chr(v_n) = '@' then
      --英文直接返回
      v_c := v_chn;
    end if;
    v_return := v_return || v_c;
    v_return := lower(v_return);
  end loop;
  return v_return;
end fgetpy;

2 MD5

使用方法：

select 用户名.函数名(需要加密的字段名) from 用户名.表名;
select oracle_user1.md5(t.id) from oracle_user1.student t;

函数定义：

create or replace function md5(passwd in varchar2) return varchar2 is
  retval varchar2(32);
begin
  retval := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5(input_string => passwd));
  return retval;
end;