(以下内容只有在"="号的情况下生效,LIKE 不行。)
有时候我们查询的时候不想通过lower或者upper等函数而把大小写的内容都查询出来,如从sqlserver、mysql等转移到 oracle时。在oracle10g可以通过修改session来实现:
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=binary_ci;
解析:
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):
----------