NLSSORT以指定的排序方式对String执行排序【转】

一:
Oracle9i之前，中文是按照二进制编码进行排序的。在oracle9i中新增了按照拼音、部首、笔画排序功能。
1、设置NLS_SORT参数值
      SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
      SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
      SCHINESE_PINYIN_M 按照拼音排序
2、Session级别的设置，修改ORACLE字段的默认排序方式：
      按拼音：alter session set nls_sort = SCHINESE_PINYIN_M;
      按笔画：alter session set nls_sort = SCHINESE_STROKE_M;
      按偏旁：alter session set nls_sort = NLS_SORT=SCHINESE_RADICAL_M；
3、语句级别设置排序方式：
      按照笔划排序
      select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
      按照部首排序
      select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
      按照拼音排序，此为系统的默认排序方式
      select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
4、修改系统参数（数据库所在操作系统）：
      set NLS_SORT=SCHINESE_RADICAL_M ;export NLS_SORT （sh）
       setenv NLS_SORT SCHINESE_RADICAL_M (csh)
      HKLC/SOFTWARE/ORACLE/home0/NLS_SORT (win注册表)
Oracle 官方说明

[url=]NLS_SORT[/url][url=]NLS_SORT specifies the collating sequence for ORDER BY queries.[/url][url=]NLS_COMP[/url][url=]NLS_COMP specifies the collation behavior of the database session.[/url]

Property
Description
Parameter type
String
Syntax
NLS_SORT = { BINARY | linguistic_definition }
Default value
Derived from NLS_LANGUAGE
Modifiable
ALTER SESSION
Range of values
BINARY or any valid linguistic definition name

• Ifthe value is BINARY, then the collating sequence for ORDER BY queriesis based on the numeric value of characters (a binary sort thatrequires less system overhead).
• If the value is a namedlinguistic sort, sorting is based on the order of the definedlinguistic sort. Most (but not all) languages supported by theNLS_LANGUAGE parameter also support a linguistic sort with the samename.
  Note:
  Setting NLS_SORT toanything other than BINARY causes a sort to use a full table scan,regardless of the path chosen by the optimizer. BINARY is the exceptionbecause indexes are built according to a binary order of keys. Thus theoptimizer can use an index to satisfy the ORDER BY clause when NLS_SORTis set to BINARY. If NLS_SORT is set to any linguistic sort, theoptimizer must include a full table scan and a full sort in theexecution plan.

         You must use the NLS_SORT operator with comparison operations if you want the linguistic sort behavior.  

Property
Description
Parameter type
String
Syntax
NLS_COMP = { BINARY | LINGUISTIC | ANSI }
Default value
BINARY
Modifiable
ALTER SESSION
Basic
No

Values:

• BINARY

Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.  

• LINGUISTIC

Comparisonsfor all SQL operations in the WHERE clause and in PL/SQL blocks shoulduse the linguistic sort specified in the NLS_SORT parameter. To improvethe performance, you can also define a linguistic index on the columnfor which you want linguistic comparisons.

• ANSI

A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC  

二:
alter session set nls_sort='schinese_pinyin_m';
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
Oracle9i之前，中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序，系统的默认排序方式为拼音排序

举例如下：
表名为 dept ，其中name字段是中文，下面分别实现按照单位名称的笔划、部首和拼音排序。
1: //按照笔划排序
2: select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
3: //按照部首排序
4: select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
5: //按照拼音排序，此为系统的默认排序方式
6: select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M'); 注意，该SQL指令并非标准指令，在SQLServer下面的实现方式并不相同。 create or replace procedure P1 is
V1 varchar2(100);
begin
execute immediate 'alter SESSION set NLS_SORT = SCHINESE_PINYIN_M';
select 地区
  into V1
  from (select 地区
      from RPT_1
      where 年度 = '2000'
      order by 地区 desc)
  where ROWNUM = 1;
end P1;