在oracle10g中可使得排序不区分大小写

最新推荐文章于 2026-01-04 00:36:12 发布

转载最新推荐文章于 2026-01-04 00:36:12 发布 · 950 阅读

文章标签：

#oracle10g #sql #session #access #table #disk

Oracle 专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何在Oracle 10g中实现查询时不区分大小写的排序功能。通过调整会话参数NLS_SORT或直接在SQL语句中使用nlssort函数，可以轻松实现这一目标。

===========================================================
在oracle10g中可使得排序不区分大小写
===========================================================
作者: zhouwf0726(http://zhouwf0726.itpub.net)
发表于:2006.12.25 14:02
分类: oracle开发
出处：http://zhouwf0726.itpub.net/post/9689/244520
---------------------------------------------------------------

SQL> select * from test order by id;

ID MC
-------------------- ------------------------------------------------------------
A AAAAA
B BBBBB
a aaaaa
b bbbbb

执行计划
----------------------------------------------------------
Plan hash value: 2007178810

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 176 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 176 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST | 4 | 176 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
48 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> ALTER SESSION SET NLS_SORT=generic_m_ci;

会话已更改。

SQL> select * from test order by id;

ID MC
-------------------- ------------------------------------------------------------
A AAAAA
a aaaaa
B BBBBB
b bbbbb

执行计划
----------------------------------------------------------
Plan hash value: 2007178810

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed

如果设置session的环境不方便，以可以设置语句级的排序

代码:SQL> select * from test order by id;
ID                   MC
-------------------- ------------------------------------------------------------
A                    AAAAAB                    BBBBBa                    aaaaab
bbbbbSQL> select * from test order by nlssort(id, 'nls_sort=generic_m_ci');
ID                   MC
-------------------- ------------------------------------------------------------
A                    AAAAAa                    aaaaaB                    BBBBBb                    bbbbb