-
1、按首字母排序
- 拼音
- SELECT*FROMTEAMORDERBYNLSSORT(排序字段,'NLS_SORT=SCHINESE_PINYIN_M')
- 笔划
- 'NLS_SORT=SCHINESE_STROKE_M')
- 部首
- 'NLS_SORT=SCHINESE_RADICAL_M')
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序
oracle9i中新增了按照拼音、部首、笔画排序功能
用法示例:
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;
