取拼音码 oracle,Oracle中生成汉字拼音码的函数（转载）

Oracle中生成汉字拼音码的函数(转载)

2018-11-21

效果：select fgetpy("中国") FROM dual;结果   zgCREATE OR REPLACE FUNCTION fgetpy (v_str VARCHAR2)RETURN VARCHAR2ASv_strlen   INT;v_return   VARCHAR2 (500);v_ii       INT;v_n        INT;v_c        VARCHAR2 (2);v_chn      VARCHAR2 (2);v_rc       VARCHAR2 (500);/*************************************************************************生成汉字拼音码的函数。　wallimn 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_strlenLOOPv_ii := v_ii + 1;v_n := 63;SELECT SUBSTR (v_rc, v_ii, 1)INTO v_chnFROM DUAL;SELECT v_n + MAX (rowsf)INTO v_nFROM (SELECT chn, ROWNUM rowsfFROM (SELECT   chnFROM (SELECT "吖" chnFROM DUALUNIONSELECT "八"FROM DUALUNION ALLSELECT "嚓"FROM DUALUNION ALLSELECT "咑"FROM DUALUNION ALLSELECT "妸"FROM DUALUNION ALLSELECT "发"FROM DUALUNION ALLSELECT "旮"FROM DUALUNION ALLSELECT "铪"FROM DUALUNION ALLSELECT "丌"FROM DUAL              --because have no "i"UNION ALLSELECT "丌"FROM DUALUNION ALLSELECT "咔"FROM DUALUNION ALLSELECT "垃"FROM DUALUNION ALLSELECT "嘸"FROM DUALUNION ALLSELECT "拏"FROM DUALUNION ALLSELECT "噢"FROM DUALUNION ALLSELECT "妑"FROM DUALUNION ALLSELECT "七"FROM DUALUNION ALLSELECT "呥"FROM DUALUNION ALLSELECT "仨"FROM DUALUNION ALLSELECT "他"FROM DUALUNION ALLSELECT "屲"FROM DUALUNION ALLSELECT "屲"FROM DUALUNION ALLSELECT "屲"FROM DUALUNION ALLSELECT "夕"FROM DUALUNION ALLSELECT "丫"FROM DUALUNION ALLSELECT "帀"FROM DUALUNION ALLSELECT v_chnFROM DUAL) aORDER BY NLSSORT (chn, "NLS_SORT=SCHINESE_PINYIN_M")) c) bWHERE 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;此函数有一个BUG，不知道为什么会这样select fgetpy("她们") FROM dual的结果是 jm,"她"字的拼音首字母在函数里被转成了j，应该是t

免责声明：本文仅代表文章作者的个人观点，与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺，请读者仅作参考，并自行核实相关内容。

http://www.pinlue.com/style/images/nopic.gif