CREATE OR REPLACE FUNCTION F_GET_PYBM (v_name IN VARCHAR2) RETURN VARCHAR2 AS
/*******************************************************************
模块名称：获取汉字首字母拼音
模块编号：
功 能：
*********************************************************************
备注: 此函数根据网络找的一个函数改造而成,此函数的原理是使用oracle
自带的汉字排序规则,根据拼音得到固定值
例如以f开头的拼音那么肯定 是 >=fa and <=jì
那么就有
IF nb_compare >=nlssort( ‘发’, ‘NLS_SORT=SCHINESE_PINYIN_M’) AND nb_compare <= nlssort( ‘猤’, ‘NLS_SORT=SCHINESE_PINYIN_M’) THEN
nb_return := nb_return || ‘F’;
如果测试部分汉字的拼音不正确,可以根据实际情况调整左右边界值
如果涉及到其它字符集的时候,可以将使用下列语句得到每个拼音的最大和最小边界
select pybm,max(nlssort(hzbm, ‘NLS_SORT=SCHINESE_PINYIN_M’)),min(nlssort(hzbm, ‘NLS_SORT=SCHINESE_PINYIN_M’))
from t_pybmk2 a
group by pybm
这里要注意多音
结果集说明：
修改历史: 删除原来的从表中获取的模式 修改后以 f_Get_pybm(‘瞿得光’) 测试耗时为原来0.1倍
********************************************************************/
nb_compare VARCHAR2(100);
nb_return VARCHAR2(100);
BEGIN
FOR i IN 1 .. length(v_name) LOOP
nb_compare := nlssort( substr(v_name, i, 1), ‘NLS_SORT=SCHINESE_PINYIN_M’) ;
IF nb_compare >= ‘3B290000010001’ /*f_nlssort(‘吖’)*/ AND nb_compare <= ‘3C820000010001’ /*f_nlssort(‘驁’)*/ THEN
nb_return := nb_return || ‘A’;
ELSIF nb_compare >= ‘3C840000010001’ /*f_nlssort(‘八’)*/ AND nb_compare <= ‘427C0000010001’ /*f_nlssort(‘簿’)*/ THEN
nb_return := nb_return || ‘B’;
ELSIF nb_compare >= ‘427D0000010001’ /*f_nlssort(‘嚓’)*/ AND nb_compare <= ‘4BA90000010001’ /*f_nlssort(‘錯’)*/ THEN
nb_return := nb_return || ‘C’;
ELSIF nb_compare >= ‘4BAD0000010001’ /*f_nlssort(‘咑’)*/ AND nb_compare <= ‘52000000010001’ /*f_nlssort(‘鵽’)*/ THEN
nb_return := nb_return || ‘D’;
ELSIF nb_compare >= ‘52050000010001’ /*f_nlssort(‘妸’)*/ AND nb_compare <= ‘53320000010001’ /*f_nlssort(‘樲’)*/ THEN
nb_return := nb_return || ‘E’;
ELSIF nb_compare >= ‘53340000010001’ /*f_nlssort(‘发’)*/ AND nb_compare <= ‘573A0000010001’ /*f_nlssort(‘猤’)*/ THEN
nb_return := nb_return || ‘F’;
ELSIF nb_compare >= ‘573C0000010001’ /*f_nlssort(‘旮’)*/ AND nb_compare <= ‘5CB60000010001’ /*f_nlssort(‘腂’)*/ THEN
nb_return := nb_return || ‘G’;
ELSIF nb_compare >= ‘5CB90000010001’ /*f_nlssort(‘妎’)*/ AND nb_compare <= ’63FD0000010001′ /*f_nlssort(‘夻’)*/ THEN
nb_return := nb_return || ‘H’;
ELSIF nb_compare >= ’63FE0000010001′ /*f_nlssort(‘丌’)*/ AND nb_compare <= ‘6E850000010001’ /*f_nlssort(‘攈’)*/ THEN
nb_return := nb_return || ‘J’;
ELSIF nb_compare >= ‘6E860000010001’ /*f_nlssort(‘咔’)*/ AND nb_compare <= ‘72280000010001’ /*f_nlssort(‘穒’)*/ THEN
nb_return := nb_return || ‘K’;
ELSIF nb_compare >= ‘722C0000010001’ /*f_nlssort(‘垃’)*/ AND nb_compare <= ‘7C980000010001’ /*f_nlssort(‘擽’)*/ THEN
nb_return := nb_return || ‘L’;
ELSIF nb_compare >= ‘7C990000010001’ /*f_nlssort(‘嘸’)*/ AND nb_compare <= ‘82220000010001’ /*f_nlssort(‘椧’)*/ THEN
nb_return := nb_return || ‘M’;
ELSIF nb_compare >= ‘82240000010001’ /*f_nlssort(‘拏’)*/ AND nb_compare <= ‘85740000010001’ /*f_nlssort(‘瘧’)*/ THEN
nb_return := nb_return || ‘N’;
ELSIF nb_compare >= ‘85750000010001’ /*f_nlssort(‘筽’)*/ AND nb_compare <= ’85AE0000010001′ /*f_nlssort(‘漚’)*/ THEN
nb_return := nb_return || ‘O’;
ELSIF nb_compare >= ’85B10000010001′ /*f_nlssort(‘妑’)*/ AND nb_compare <= ’89C40000010001′ /*f_nlssort(‘曝’)*/ THEN
nb_return := nb_return || ‘P’;
ELSIF nb_compare >= ’89C50000010001′ /*f_nlssort(‘七’)*/ AND nb_compare <= ’90AA0000010001′ /*f_nlssort(‘裠’)*/ THEN
nb_return := nb_return || ‘Q’;
ELSIF nb_compare >= ’90B00000010001′ /*f_nlssort(‘亽’)*/ AND nb_compare <= ’92E40000010001′ /*f_nlssort(‘鶸’)*/ THEN
nb_return := nb_return || ‘R’;
ELSIF nb_compare >= ’92E50000010001′ /*f_nlssort(‘仨’)*/ AND nb_compare <= ‘9BA90000010001’ /*f_nlssort(‘蜶’)*/ THEN
nb_return := nb_return || ‘S’;
ELSIF nb_compare >= ‘9BAA0000010001’ /*f_nlssort(‘侤’)*/ AND nb_compare <= ‘A1AE0000010001’ /*f_nlssort(‘籜’)*/ THEN
nb_return := nb_return || ‘T’;
ELSIF nb_compare >= ‘A1B00000010001’ /*f_nlssort(‘屲’)*/ AND nb_compare <= ‘A5D40000010001’ /*f_nlssort(‘鶩’)*/ THEN
nb_return := nb_return || ‘W’;
ELSIF nb_compare >= ‘A5D60000010001’ /*f_nlssort(‘夕’)*/ AND nb_compare <= ‘AF1E0000010001’ /*f_nlssort(‘鑂’)*/ THEN
nb_return := nb_return || ‘X’;
ELSIF nb_compare >= ‘AF200000010001’ /*f_nlssort(‘丫’)*/ AND nb_compare <= ‘BC580000010001’ /*f_nlssort(‘韻’)*/ THEN
nb_return := nb_return || ‘Y’;
ELSIF nb_compare >= ‘BC590000010001’ /*f_nlssort(‘帀’)*/ AND nb_compare <= ‘C7B40000010001’ /*f_nlssort(‘咗’)*/ THEN
nb_return := nb_return || ‘Z’;
ELSE
nb_return := nb_return || substr(v_name, i, 1);
END IF;
END LOOP;
RETURN nb_return;
END;