- 浏览: 1040592 次
- 性别:
- 来自: 南京
文章分类
- 全部博客 (501)
- dwr (6)
- javascript (84)
- oracle (66)
- jsp/servlet (18)
- ant (3)
- 基础知识 (12)
- EXT (10)
- My SQL (10)
- java (71)
- spring (37)
- 学习的对象 (2)
- Linux (24)
- 面试 (1)
- HTML/CSS (11)
- tomcat (11)
- 收藏夹 (2)
- Power Designer (2)
- struts.xml配置文件 (1)
- sturts2 (3)
- myeclipse (8)
- eclipse (7)
- Maven (34)
- SVN (3)
- SAP JCO (2)
- JBOSS (11)
- webservice (8)
- word (1)
- 敏捷开发 (1)
- sybase (4)
- Nexus (3)
- EhCache (3)
- log4j (3)
- Cookie (4)
- Session (4)
- CXF (7)
- AXIS (2)
- SSO (1)
- LDAP (1)
- velocity (2)
- Jquery (5)
- redis (2)
- http (4)
- dojo (1)
- Linux资源监控软件mnon的安装与运用 (1)
- notepad++ (1)
- EA (1)
- UML (1)
- JasperReports (1)
- 权限 (0)
- freemarker (4)
- Spring MVC (1)
- JMS (1)
- activeMQ (1)
- hession (3)
- 安全 (1)
- ibatis (2)
- log (1)
- nginx (1)
最新评论
-
winhbb:
我刚好遇到了一个问题(在可以依赖注入的场合有效):有两个模块A ...
解决Maven项目相互依赖/循环依赖/双向依赖的问题 -
nanjiwubing123:
long3ok 写道你好 XmlOutputFormatter ...
用XStream转换复杂XML -
zhoujianboy:
另外一个方法实现eclipse tomcat 热部署:http ...
eclipse下实现maven项目在tomcat容器热部署方法 -
long3ok:
你好 XmlOutputFormatter 请问这个类是在什么 ...
用XStream转换复杂XML -
ganbo:
总结的好,文章给力。
解决Maven项目相互依赖/循环依赖/双向依赖的问题
ALTER SESSION SET NLS_SORT=''; 排序影响整个会话
Oracle9i之前,中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序
oracle9i中新增了按照拼音、部首、笔画排序功能
拼音 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_PINYIN_M')
笔划 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_STROKE_M')
部首 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_RADICAL_M')
Oracle9i之前,中文是按照二进制编码进行排序的。在oracle9i中新增了按照拼音、部首、笔画排序功能。
1、设置NLS_SORT参数值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序
2、Session级别的设置,修改ORACLE字段的默认排序方式:
按拼音:alter session set nls_sort = SCHINESE_PINYIN_M;
按笔画:alter session set nls_sort = SCHINESE_STROKE_M;
按偏旁:alter session set nls_sort = NLS_SORT=SCHINESE_RADICAL_M;
3、语句级别设置排序方式:
按照笔划排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
按照部首排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
按照拼音排序,此为系统的默认排序方式
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
4、修改系统参数(数据库所在操作系统):
set NLS_SORT=SCHINESE_RADICAL_M ;
export NLS_SORT (sh)
setenv NLS_SORT SCHINESE_RADICAL_M (csh)
HKLC\SOFTWARE\ORACLE\home0\NLS_SORT (win注册表)
Oracle 官方说明
NLS_SORT NLS_SORT specifies the collating sequence for ORDER BY queries. NLS_COMPNLS_COMP specifies the collation behavior of the database session.
Property
Description
Parameter type
String
Syntax
NLS_SORT = { BINARY | linguistic_definition }
Default value
Derived from NLS_LANGUAGE
Modifiable
ALTER SESSION
Range of values
BINARY or any valid linguistic definition name
If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).
If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.
Note:
Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.
You must use the NLS_SORT operator with comparison operations if you want the linguistic sort behavior.
Property
Description
Parameter type
String
Syntax
NLS_COMP = { BINARY | LINGUISTIC | ANSI }
Default value
BINARY
Modifiable
ALTER SESSION
Basic
No
Values:
BINARY
Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.
LINGUISTIC
Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.
ANSI
A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC
注意:当使用了NLS_SORT或NLS_COMP,应该指定函数索引来提高数据库性能。如
create index [schema.]indexName on tableName(function(propertyName))
注意:虽然能在oracle查询的时候能用得上,但不知道为什么,当映射到hibernate上时却无效,依然按NLS_SORT=binary排序,不过可以使用@org.hibernate.annotation.OrderBy("nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M')")指定排序方法
Oracle9i之前,中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序
oracle9i中新增了按照拼音、部首、笔画排序功能
拼音 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_PINYIN_M')
笔划 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_STROKE_M')
部首 SELECT * FROM TEAM ORDER BY NLSSORT(列名,'NLS_SORT = SCHINESE_RADICAL_M')
Oracle9i之前,中文是按照二进制编码进行排序的。在oracle9i中新增了按照拼音、部首、笔画排序功能。
1、设置NLS_SORT参数值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序
2、Session级别的设置,修改ORACLE字段的默认排序方式:
按拼音:alter session set nls_sort = SCHINESE_PINYIN_M;
按笔画:alter session set nls_sort = SCHINESE_STROKE_M;
按偏旁:alter session set nls_sort = NLS_SORT=SCHINESE_RADICAL_M;
3、语句级别设置排序方式:
按照笔划排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
按照部首排序
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
按照拼音排序,此为系统的默认排序方式
select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
4、修改系统参数(数据库所在操作系统):
set NLS_SORT=SCHINESE_RADICAL_M ;
export NLS_SORT (sh)
setenv NLS_SORT SCHINESE_RADICAL_M (csh)
HKLC\SOFTWARE\ORACLE\home0\NLS_SORT (win注册表)
Oracle 官方说明
NLS_SORT NLS_SORT specifies the collating sequence for ORDER BY queries. NLS_COMPNLS_COMP specifies the collation behavior of the database session.
Property
Description
Parameter type
String
Syntax
NLS_SORT = { BINARY | linguistic_definition }
Default value
Derived from NLS_LANGUAGE
Modifiable
ALTER SESSION
Range of values
BINARY or any valid linguistic definition name
If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).
If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.
Note:
Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.
You must use the NLS_SORT operator with comparison operations if you want the linguistic sort behavior.
Property
Description
Parameter type
String
Syntax
NLS_COMP = { BINARY | LINGUISTIC | ANSI }
Default value
BINARY
Modifiable
ALTER SESSION
Basic
No
Values:
BINARY
Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.
LINGUISTIC
Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.
ANSI
A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC
注意:当使用了NLS_SORT或NLS_COMP,应该指定函数索引来提高数据库性能。如
create index [schema.]indexName on tableName(function(propertyName))
注意:虽然能在oracle查询的时候能用得上,但不知道为什么,当映射到hibernate上时却无效,依然按NLS_SORT=binary排序,不过可以使用@org.hibernate.annotation.OrderBy("nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M')")指定排序方法
发表评论
-
Oracle强制使用索引
2013-01-25 16:48 5340Oracle强制使用索引: select /*+ INDEX ... -
Oracle if exists(...)
2013-01-22 10:52 1201对于Oracle中没有 if exists(...) 的语法, ... -
oracle 赋debug权限
2013-01-15 15:29 4022pl/sql 报错 不能运行debug 时 ora-0131 ... -
Oracle 10g使用游标更新或删除数据
2013-01-04 16:03 1082在定义又表示必须要带 ... -
oracle 的 CONNECT BY 和START WITH
2013-01-04 15:00 1356在SELECT命令中使用CONNECT BY 和START W ... -
树-Oracle用Start with...Connect By子句递归查询
2013-01-04 14:59 1031Start with...Connect By子句递归查询一般 ... -
java.sql.SQLException: ORA-01436: 用户数据中的 CONNECT BY 循环
2013-01-04 14:55 2271SELECT r1.region_id,parent_id ... -
Oracle创建删除用户、角色、表空间、导入导出、...命令总结
2013-01-03 16:24 1277//创建临时表空间 create temporary ta ... -
ResultSet.getMetaData() 的使用
2012-08-12 11:22 2105java.sql.ResultSet.getMetaData( ... -
ORACLE中如何查找特定对象中的文本内容~
2012-04-30 19:37 1237[c-sharp] view plaincopy SQL> ... -
Oracle 下读取表/字段的备注信息
2012-04-30 19:19 1193Oracle 通过COMMENT ON TABLE / C ... -
Oracle 递归查询 树查询 start with connect by prior
2012-02-28 09:52 1089网页地址: http://www.iteye.com/topi ... -
java中oracle特殊字符的查询
2011-10-18 14:38 2392escape:指定某字符为特殊字符: 例如指定‘\’ 为特殊字 ... -
Oracle中的 单引号 和 双引号
2011-10-18 09:17 1290双引号的作用是:假如建立对象的时候,对象名、字段名加双引号,则 ... -
pl/sql case or decode
2011-10-16 00:19 1070SELECT CASE 3 WHEN 1 THEN ... -
Oracle存储过程返回游标 [转载]
2012-04-22 22:09 4641有俩种方法: 一种是声明系统游标,一种是声明自定义游标,然后 ... -
oracle索引表函数(集合函数)(下表为字符串的索引表举例红色)(嵌套表/索引表/可变数组举例绿色)
2011-10-12 11:40 0一、EXISTS函数 1、检测 ... -
pl/sql 集合代码 的遍历方式
2011-10-12 10:22 1472-- Created on 2011-10-12 by HUC ... -
浅谈oracle复合数据类型
2012-04-22 22:10 983--本文档可以直接拷贝运行。 /* oracle复合数 ... -
table array object record
2012-04-22 22:10 1123前段时间我恰好有关于这些的学习笔记,贴出来大家指点一下: ...
相关推荐
快速通过NLS_LANG切换字符集快捷工具设置客户端字符集,避免连接多个不同字符集的数据库导致PL/SQL工具显示乱码的问题。
nls_charset12.jar 之前在csdn上下载的这个文件有问题,导致程序调试了很久 我这边上传这个没有问题
oracle10g jdbc的一个包 nls_charset12.jar。 oracle11g jdbc的一个包 orai18n.jar
Oracle的NLS_DATE_FORMAT设置(日期格式设置)_ITPUB博客.mhtml
nls_charset12.jar oracle 驱动
ojdbc14.jar nls_charset12.jar
oracle 10G jdbc连接jar包 nls_charset12.jar,ojdbc14.jar
解决oracle11g存储过程传入数组空的问题。找好好久,测试才好使,提供给大家。 oracle class12.jar nls_charset12.jar存储过程接收数组空值
window下修改NLS_LANG环境变量快捷工具,此工具利用bat写的,可以看见源码,提供给大家参考
Charset cp1251 translation tables.
Charset cp852 translation tables.
oracle关于字符集的jdbc驱动,orai18n.jar nls_charset12.jar 支持集合的驱动程序 将java的集合转换为数据库集合类型 oracle10g及以下版本使用 nls_charset12.jar,以上的版本使用orai18n.jar
我已经通过了PB12.5连ORACLE10的免客户端设定。详细内容请看里面的 《PB连ORACLE10免客户端》
Script that solves and plot pulse propagation thru Split step Fourier method agrawal book
非线性薛定谔方程的数值求解,并计算脉冲的展宽
PowerBuilder如何连接Oracle数据库。
NLS-PT-850便携式条码采集器。采用可置换激光或拍摄式条码采集组件。采用32位高速CPU,8M内存,FLASH存储空间16M,可扩至128M;待机长达160小时。该产品防水、防尘、抗静电、抗跌落等重要指标达到或超过IP54工业等级...