NLSSORT与COLLATION

12.2之前一直用的是NLSSORT，从12.2开始建议使用COLLATION替代，它是SQL标准用法。当然你仍可继续使用NLSSORT

The NLSSORT function enables you to force a specific collation (sort order) for ORDER BY, GROUP BY, comparison conditions, and a number of other collation-sensitive operations.

However, starting with Oracle Database 12c Release 2 (12.2), the recommended way to force a specific collation for such operations is to use the COLLATE operator.

The COLLATE operator works for all the collation-sensitive operations, including those for which NLSSORT cannot be used, for example MAX, MIN, and INSTR.

Collation can be specified for:

Table columns

Cluster columns

Tables

Schemas through the owning user

Views and materialized views

PL/SQL units, such as procedures, functions, packages, types, and triggers

SQL expressions

Note: A collation cannot be specified for a cluster, but it can be specified for key columns in a cluster. A collation cannot be specified for a whole database.

1. NLSSORT使用

NLSSORT语法（不指定后面参数即使用默认）

NLSSORT(column1, 'NLS_SORT=xxxx')

NLSSORT(column1, 'NLS_LANG=xxxx')

NLSSORT(column1, 'NLS_LANGUAGE=xxxx')

示例

ALTER SESSION SET NLS_SORT = GERMAN;

SELECT * FROM table1 ORDER BY NLSSORT(column1);

SELECT * FROM table1 ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH');

NLS_COMP表示默认不指定NLSSORT时是否按NLS_SORT排序，如

ALTER SESSION SET NLS_COMP = BINARY;

ALTER SESSION SET NLS_SORT = GERMAN;

SELECT * FROM table1 ORDER BY column1;   --按binary排序

SELECT * FROM table1 ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH');  ---按FRENCH排序

ALTER SESSION SET NLS_COMP = LINGUISTIC;

ALTER SESSION SET NLS_SORT = GERMAN;

SELECT * FROM table1 ORDER BY column1;   --按GERMAN排序

SELECT * FROM table1 ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH');  ---按FRENCH排序

1. COLLATION使用

详细使用见《Linguistic Sorting》, 示例

CREATE TABLE employees (

  emp_code   VARCHAR2(10) PRIMARY KEY,

  first_name VARCHAR2(100),

  last_name  VARCHAR2(200),

  job_code   VARCHAR2(5) COLLATE BINARY,

  dep_code   NUMBER)

DEFAULT COLLATION BINARY_CI;

ALTER TABLE employees DEFAULT COLLATION USING_NLS_COMP;

CREATE USER hrsys IDENTIFIED BY password DEFAULT COLLATION BINARY;

SELECT SYS_CONTEXT('USERENV', 'SESSION_DEFAULT_COLLATION') FROM DUAL;

ALTER SESSION SET DEFAULT_COLLATION=collation_name;

SELECT column1 FROM table1 WHERE column1 COLLATE USING_NLS_SORT > 'B';

SELECT column1 FROM table1 WHERE column1 COLLATE GERMAN > 'B';