Question

Oracle 如何控制查询不区分大小写？

如题。 我原来的数据库用的是 MySql， 后来因为某种原因换成 Oracle 11g， 但原来MySql 中对select时使用的文本条件（如 select ... where name='abc'）默认是不区分大小写的，因此我的程序中也没有对其进行控制。
但Oracle 11g却是默认区分大小写的，如果我要对程序进行修改的话，将会大动土木， 因此不知道 Ocacle 中有没有机制或参数可以控制查询的文本不区分大小写呢？ 谢谢～

------------
注意：请不要回答类如“用SQL函数”之类的。
再重申一下，我就是因为不想改代码所以才想直接改ORACLE配置的。因为如果“用SQL函数”，那么我所有的DAO方法都要重写，那是非常不符合我本意的。 再次谢谢大家。

Answer 1

我是没试过 不过10g以后可以针对某一个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):
---------------------------------------------------

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

Answer 2

select * from table1 where upper(name) = upper("abc")