oracle 拼音首字母查询,ORACLE 提取汉字拼音首字母及汉字排序

CREATEORREPLACEFUNCTIONF_PINYIN(P_NAMEINVARCHAR2)RETURNVARCHAR2AS V_COMPAREVARCHAR2(100); V_RETURNVARCHAR2(4000); FUNCTIONF_NLSSORT(P_WORDINVARCHAR2)RETURNVARCHAR2AS BEGIN returnNLSSORT(P_WORD,'NLS_SORT=SCHINESE_PINYIN_M'); END; BEGIN FORIIN1..LENGTH(P_NAME)LOOP V_COMPARE:=F_NLSSORT(SUBSTR(P_NAME,I,1)); IFV_COMPARE>=F_NLSSORT('吖')ANDV_COMPARE<=F_NLSSORT('驁')THEN V_RETURN:=V_RETURN||'A'; ELSIFV_COMPARE>=F_NLSSORT('八')ANDV_COMPARE<=F_NLSSORT('簿')THEN V_RETURN:=V_RETURN||'B'; ELSIFV_COMPARE>=F_NLSSORT('嚓')ANDV_COMPARE<=F_NLSSORT('錯')THEN V_RETURN:=V_RETURN||'C'; ELSIFV_COMPARE>=F_NLSSORT('咑')ANDV_COMPARE<=F_NLSSORT('鵽')THEN V_RETURN:=V_RETURN||'D'; ELSIFV_COMPARE>=F_NLSSORT('妸')ANDV_COMPARE<=F_NLSSORT('樲')THEN V_RETURN:=V_RETURN||'E'; ELSIFV_COMPARE>=F_NLSSORT('发')ANDV_COMPARE<=F_NLSSORT('猤')THEN V_RETURN:=V_RETURN||'F'; ELSIFV_COMPARE>=F_NLSSORT('旮')ANDV_COMPARE<=F_NLSSORT('腂')THEN V_RETURN:=V_RETURN||'G'; ELSIFV_COMPARE>=F_NLSSORT('妎')ANDV_COMPARE<=F_NLSSORT('夻')THEN V_RETURN:=V_RETURN||'H'; ELSIFV_COMPARE>=F_NLSSORT('丌')ANDV_COMPARE<=F_NLSSORT('攈')THEN V_RETURN:=V_RETURN||'J'; ELSIFV_COMPARE>=F_NLSSORT('咔')ANDV_COMPARE<=F_NLSSORT('穒')THEN V_RETURN:=V_RETURN||'K'; ELSIFV_COMPARE>=F_NLSSORT('垃')ANDV_COMPARE<=F_NLSSORT('擽')THEN V_RETURN:=V_RETURN||'L'; ELSIFV_COMPARE>=F_NLSSORT('嘸')ANDV_COMPARE<=F_NLSSORT('椧')THEN V_RETURN:=V_RETURN||'M'; ELSIFV_COMPARE>=F_NLSSORT('拏')ANDV_COMPARE<=F_NLSSORT('瘧')THEN V_RETURN:=V_RETURN||'N'; ELSIFV_COMPARE>=F_NLSSORT('筽')ANDV_COMPARE<=F_NLSSORT('漚')THEN V_RETURN:=V_RETURN||'O'; ELSIFV_COMPARE>=F_NLSSORT('妑')ANDV_COMPARE<=F_NLSSORT('曝')THEN V_RETURN:=V_RETURN||'P'; ELSIFV_COMPARE>=F_NLSSORT('七')ANDV_COMPARE<=F_NLSSORT('裠')THEN V_RETURN:=V_RETURN||'Q'; ELSIFV_COMPARE>=F_NLSSORT('亽')ANDV_COMPARE<=F_NLSSORT('鶸')THEN V_RETURN:=V_RETURN||'R'; ELSIFV_COMPARE>=F_NLSSORT('仨')ANDV_COMPARE<=F_NLSSORT('蜶')THEN V_RETURN:=V_RETURN||'S'; ELSIFV_COMPARE>=F_NLSSORT('侤')ANDV_COMPARE<=F_NLSSORT('籜')THEN V_RETURN:=V_RETURN||'T'; ELSIFV_COMPARE>=F_NLSSORT('屲')ANDV_COMPARE<=F_NLSSORT('鶩')THEN V_RETURN:=V_RETURN||'W'; ELSIFV_COMPARE>=F_NLSSORT('夕')ANDV_COMPARE<=F_NLSSORT('鑂')THEN V_RETURN:=V_RETURN||'X'; ELSIFV_COMPARE>=F_NLSSORT('丫')ANDV_COMPARE<=F_NLSSORT('韻')THEN V_RETURN:=V_RETURN||'Y'; ELSIFV_COMPARE>=F_NLSSORT('帀')ANDV_COMPARE<=F_NLSSORT('咗')THEN V_RETURN:=V_RETURN||'Z'; ENDIF; ENDLOOP; RETURNV_RETURN; END;