nls_comp和nls_sort

Oracle官方文档Globalization Support的Linguistic Sorting and String Searching一章有详细解释

nls_sort: 决定了Oracle 排序的方法，影响了ORDER BY, GROUP BY, comparison =, <>, <=, >=, IN, BETWEEN, LIKE, MIN/MAX, GREATEST/LEAST, INSTR等函数/操作符。

语法：NLS_SORT = { BINARY | sort_name }

Binary是默认值。

Linguistic sort（非Binary）时，常规的B-tree索引将不被采用。这时需要建一个基于NLSSORT函数的索引。官方文档里有sort_name的列表。

假如需要做不区分大小写的sort/比较，在sort_name后加_CI。如：

alter session set nls_sort=binary_ci;

nls_comp: 决定比较的方法。

语法：NLS_COMP = { BINARY | LINGUISTIC | ANSI }

Binary: 默认值。 Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.

LINGUISTIC: Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

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

客户端的JDBC驱动，OCI驱动，NLS_LANG环境变量设置将会覆盖服务器端这两个参数的设置。

例子1：大小写不区分, NLS_COMP=ANSI仅作用于少数操作符/函数，不适用于like及min(), max()等大多数函数。

truncate table t1;
alter session set nls_sort=binary_ci;
alter session set nls_comp=ansi;
insert into t1 values(99, 'Wu');
insert into t1 values(1,'wu');
commit;
select * from t1 where name='wu';
        ID NAME                                                                                               
---------- ----------------------------------------------------------------------------------------------------
        99 Wu                                                                                                   
         1 wu  
select * from t1 where name like 'w%';
        ID NAME                                                                                               
---------- ----------------------------------------------------------------------------------------------------
         1 wu 
 

例子2： 大小写不区分， NLS_COMP='linguistic'，适用于所有操作符/函数

alter session set nls_comp='linguistic';
select * from t1 where name like 'w%';
session SET altered.

        ID NAME                                                                                               
---------- ----------------------------------------------------------------------------------------------------
        99 Wu                                                                                                   
         1 wu                                                                                                  
 

NLSSort()函数

select name, nlssort(name, 'nls_sort=binary_ci') from t1;
NAME                                                                                                 NLSSORT(NAME,'NLS_SORT=BINARY_CI')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Wu                                                                                                   0077007500                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
wu                                                                                                   0077007500       

该函数返回Raw数据类型，Raw最大长度2000字节。