作者：佚名 来源：网络收集 发布时间：2008-2-26 7:16:08 发布人：网络
有些时候我们在查询时不想通过lower或者upper等函数而把大小写的内容都查询出来，在Oracle 10g可以这么实现。
| SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SQL> select * from test; ID -------------------- A a SQL> select * from test where id='a'; ID -------------------- a SQL> ALTER SESSION SET NLS_COMP=ANSI; Session altered SQL> ALTER SESSION SET NLS_SORT=binary_ci; Session altered SQL> select * from test where id='A'; ID -------------------- A a SQL> select * from test where id='a'; ID -------------------- A a SQL> |
10gR2中，nls_com新增加了一个值LINGUISTIC ，设置这个值，可以使在nl_sort中设置大小写不敏感。相应的，nl_sort也增加了一个值：BINARY_CI，(CI即Case Insensitive)，也就是大小写不敏感。
但是，实际上设置过这两个值以后，并非真正大小写不敏感了，而是相当于Oracle会自动给语句加上upper函数。例子：
| SQL> set autot on SQL> select * from t2 where f1 = 'a'; F1 AAA ---------- ---------- a 2 Execution Plan ---------------------------------------------------------- Plan hash value: 2238318762 -------------------------------------------------------------------------------- ------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim e | -------------------------------------------------------------------------------- ------- | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00: 00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 5 | 2 (0)| 00: 00:01 | |* 2 | INDEX RANGE SCAN | T2_IDX1 | 1 | | 1 (0)| 00: 00:01 | -------------------------------------------------------------------------------- ------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("F1"='a') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set NLS_SORT=BINARY_CI; Session altered. SQL> alter session set NLS_COMP=LINGUISTIC; Session altered. SQL> select * from t2 where f1 = 'a'; F1 AAA ---------- ---------- A 1 a 2 Execution Plan ---------------------------------------------------------- Plan hash value: 1513984157 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NLSSORT("F1",'nls_sort=''BINARY_CI''')=HEXTORAW('6100') ) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 0 bytes sent via SQL*Net to client 0 bytes received via SQL*Net from client 0 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed |