|
1、Oracle9i 新增了按照拼音、部首、笔画排序功能，在使用时一般都是按拼音排序
按照拼音排序：
select * from [表名]order by nlssort([栏位名],'NLS_SORT=SCHINESE_PINYIN_M');
按照笔画排序：
select * from [表名]order by nlssort([栏位名],'NLS_SORT=SCHINESE_STROKE_M');
按照部首排序：
select * from [表名] order by nlssort([栏位名],'NLS_SORT=SCHINESE_RADICAL_M');
其中NLS_SORT 各参数值的含义：
SCHINESE_PINYIN_M ： 按照拼音排序
SCHINESE_STROKE_M： 先按照笔画再按部首排序
SCHINESE_RADICAL_M：先按照部首再按照笔画排序。
2、通过oracle的NLSSORT函数对汉字按照拼音排序，然后根据汉字的区间返回对应的首字母。具体实现效果和代码如下。 
/* 获取拼音简码函数 */
CREATE OR REPLACE FUNCTION GET_PYJM (P_NAME IN VARCHAR2)
RETURN VARCHAR2
AS
V_COMPARE VARCHAR2 (100);
V_RETURN VARCHAR2 (4000);
BEGIN
DECLARE
FUNCTION F_NLSSORT (P_WORD IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN NLSSORT (P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
END;
BEGIN
FOR I IN 1 .. LENGTH (P_NAME)
LOOP
V_COMPARE := F_NLSSORT (SUBSTR (P_NAME, I, 1));
IF V_COMPARE >= F_NLSSORT ('吖')
AND V_COMPARE = F_NLSSORT ('八')
AND V_COMPARE = F_NLSSORT ('嚓')
AND V_COMPARE = F_NLSSORT ('咑')
AND V_COMPARE = F_NLSSORT ('妸')
AND V_COMPARE = F_NLSSORT ('发')
AND V_COMPARE = F_NLSSORT ('旮')
AND V_COMPARE = F_NLSSORT ('妎')
AND V_COMPARE = F_NLSSORT ('丌')
AND V_COMPARE = F_NLSSORT ('咔')
AND V_COMPARE = F_NLSSORT ('垃')
AND V_COMPARE = F_NLSSORT ('嘸')
AND V_COMPARE = F_NLSSORT ('拏')
AND V_COMPARE = F_NLSSORT ('筽')
AND V_COMPARE = F_NLSSORT ('妑')
AND V_COMPARE = F_NLSSORT ('七')
AND V_COMPARE = F_NLSSORT ('亽')
AND V_COMPARE = F_NLSSORT ('仨')
AND V_COMPARE = F_NLSSORT ('侤')
AND V_COMPARE = F_NLSSORT ('屲')
AND V_COMPARE = F_NLSSORT ('夕')
AND V_COMPARE = F_NLSSORT ('丫')
AND V_COMPARE = F_NLSSORT ('帀')
AND V_COMPARE |