|
有些时候我们在查询时不想通过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
|
|
