oracle汉字转换成拼音、五笔等

select ypmc,ch_code(ypmc,'pyCode',1,8) from yk_typk;
第二个参数：码类型（见下图）
第三个参数:从第几位开始截取
第四个参数:截取最大长度

 

ch_code函数脚本：

CREATE OR REPLACE FUNCTION "CH_CODE" (chStr    varchar2,
                                   codeType varchar2,
                                   len      Integer,
                                   maxLen   Integer) RETURN VARCHAR2 IS
  ch     VARCHAR2(4);
  code   varchar2(50);
  retStr VARCHAR2(100);
BEGIN
  for i in 1 .. length(chStr) loop
    ch := substr(chStr, i, 1);
    if REGEXP_INSTR(ch, '[a-zA-Z0-9]') > 0 or
       (REGEXP_INSTR(asciistr(ch), '[\u4E00-\u9FA5]') > 0 and
        substr(asciistr(ch), 1, 1) = '\') then
      if REGEXP_INSTR(ch, '[a-zA-Z0-9]') > 0 then
        retStr := retStr || ch;
      else
        begin
          execute immediate 'select ' || codeType ||
                            ' from pub_characterencoding t where t.hzcode = :1'
            into code
            using ch;
          retStr := retStr || substr(code, 0, len);
        exception
          when others then
            retStr := '';
        end;
      end if;
    end if;
  end loop;
  return substr(retStr, 0, maxLen);
exception
  when others then
    return '';
END;

pub_characterencoding 表中的数据：https://download.csdn.net/download/dhf984721977/10268879 进行下载