《Oracle SQL疑难解析》——1.14 排序

本节书摘来自异步社区出版社《Oracle SQL疑难解析》一书中的第1章，第1.14节，作者： 【美】Grant Allen , Bob Bryla , Darl Kuhn，更多章节内容可以访问云栖社区“异步社区”公众号查看。

1.14 排序

Oracle SQL疑难解析

1.14.1 要解决的问题

我们的文本数据混合了大写、小写字母和句子（英文句子，指第一个字母大写，后续字母小写）。我们希望把这些数据按字母顺序、大小写顺序进行排列。

1.14.2 解决方法

我们先在HR.EMPLOYEES表中创建一些混合大小写的数据。运行下面的UPDATE语句，把William Smith's的姓（last_name）改为大写：

update hr.employees
set last_name = 'SMITH'
where employee_id = 171;

下面的select语句要求按Oracle默认的排序方式显示根据last_name排序的结果：

select last_name
from hr.employees
order by last_name;
LAST_NAME
---------
…
Rogers
Russell
SMITH
Sarchand
Sciarra
Seo
Sewall
Smith
Stiles
Sullivan
…

聪明的读者可能已经猜到了排序结果。Oracle默认用二进制的排序方法。在上面这个简单的例子中，文本是按它们在代码页上对应数值的大小来排列的。编码可以是US7ASCII，WEIISO8859P1或其他编码方式。在这些代码页上，大、小写字母有不同的值，大写的值排在前面。这就是为什么上面查询结果中SMITH排在所有以“S”开头的姓的最前面。

人们通常希望看见两个“Smith”排在一起，忽略大小写。用这个NLS环境变量可以达到目的：

alter session set NLS_SORT='BINARY_CI';

select last_name
from hr.employees
order by last_name;
LAST_NAME
---------
…
Rogers
Russell
Sarchand
Sciarra
Seo
Sewall
Smith
SMITH
Stiles
Sullivan
…

1.14.3 数据库如何工作

Oracle支持区分大小写和不区分大小写两种排序方式。有个环境变量NLS_SORT进行区分大小写和不区分大小写的控制。默认的排序操作区分大小写，即NLS_SORT=BINARY。如果我们希望排序按照不区分大小写的方式，则需把环境变量NLS_SORT设置为BINARY_CI。

顾名思义，NLS_SORT仅仅影响排序结果，并不对其他大小写操作构成影响。即使把NLS_SORT设置为BINARY_CI，以不区分大小写的方式比较数据的操作仍然延续了Oracle默认的方式：

select first_name, last_name
from hr.employees
where last_name like 's%';
no rows selected

对以上结果别灰心。Oracle想到了你的需求，也提供了不区分大小写的比较方式。

比较传统的方式不是使用NLS环境变量，而是用UPPER和LOWER函数把要比较的字段名、文字都转成大写或小写后再比较。这种方法的不足之处在于，使用函数后，标准的索引就不能使用了，优化器无法工作，应对的方式是使用基于功能的索引（function-based index）。