char类型与char型或字符常量的比较,在比较时使用补齐空格的方式进行比较。
解决方案 »
- oracle连接失败,可能是win7权限问题
- plsql9以上版本和8有啥区别?
- 关于IIS中网站日志通过ODBC日志记录写入ORACLE的问题
- 求高手赐一条SQL的写法
- 关于修改表中某个字段数据类型和长度问题。
- 在oracle数据库中,如何建立两帐数据表的主外键关系?
- 运行过程发生错误,请大家帮忙看看!
- ORDER BY 的问题请帮忙
- 在ORACLE10g中用dbms_logmnr_d.build 分析日志文件时出错。求教各位大侠!!!!!!!!!!
- 急,高分,怎样将一个.xml文件导入到数据库中?
- oracle数据查询优化
- 选择当月每天的销售单量,select出的结果有2天没数据的没显示,怎么让没数据的那几天显示0?
char不同于varchar2。
varchar2类型与varchar2类型,char型和字符常量的比较,在比较时不补充空格,直接比较。
补齐空格的方式我能理解,
但是现在定义的
kbn char(1);
nymd char(7);kbn补齐空格不就是 ' ' 一个空格
nymd 补齐空格不就是 ' ' 七个空格难道是比较的时候按最大长度的那个进行补齐空格?
补齐空格的方式我能理解,
但是现在定义的
kbn char(1);
nymd char(7);kbn补齐空格不就是 ' ' 一个空格
nymd 补齐空格不就是 ' ' 七个空格难道是比较的时候按最大长度的那个进行补齐空格?
是的,是将两个串长度补成一致以后再进行比较,与定义无关。
做了下测试:
--1个空格和2个空格竟然真的相等了
SQL> select * from dual where ' '=' ';
DUMMY
-----
X
--同样,'1'和'1 '也会相等
SQL> select 1 from dual where ' 1 1'=' 1 1 ';
1
----------
1--可以认为,在等式两端直接用字符串来比较,会被解析成char类型,比较时会根据较长的那个来匹配,自动补上空格
--创建一张测试表,看看系统会为'1 '给什么类型
SQL> create table tmp_1 as select '1 ' c from dual ;
Table created
SQL> desc tmp_1
Name Type Nullable Default Comments
---- ------- -------- ------- --------
C CHAR(2) Y
SQL> alter table tmp_1 add c1 char(7) default '1';
Table altered
SQL> select * from tmp_1 where c=c1;
C C1
-- -------
1 1
--上面的是两个char类型的字符串进行比较,如果是varchar2呢
SQL> select * from tmp_1 where cast(c as varchar2(2))=c1;
C C1
-- ---------不等了。当等式一端的字符串是varchar2类型,则按变长字符串来比较
SQL> 这是个很奇特的问题
在metalink上找到这么一段解释:Applies to:
PL/SQL - Version: 10.2.0.4
This problem can occur on any platform.Symptoms
When attempting to execute a PLSQL block which uses equality operator with space padded literals
gives different result on 9.2.0.8 and 10+ releaseSample 1 :
set serveroutput on
begin
If 'ABCD'||' ' = 'ABCD' THEN --concatenate with a space
dbms_output.put_line ('equal');
else
dbms_output.put_line ('different');
end if;
end;
/Sample2 :
set serveroutput on
begin
If 'ABCD ' = 'ABCD' THEN
dbms_output.put_line ('equal');
else
dbms_output.put_line ('different');
end if;
end;
/Sample Output :
表格见下图Cause
The cause of this problem has been identified in
Bug 4872783 : No space-pad in comparing with concatinated variablesIn releases between 8.0 and 9.2, the result of a string concatenation operation was always of type CHAR. However, beginning in 10.1, the result type was always VARCHAR2. This is generally only of importance when the result is not immediately assigned into a variable, but is used directly in an expression sensitive to the type of its arguments. In particular, '=' (comparison) returns different values depending on the types of its arguments.The 'ABCD ' = 'ABCD' is always treated as a comparison between CHAR 'ABCD ' and CHAR 'ABCD' - these are treated as equal because when doing CHAR comparison we pad all values out to the same
length with spaces which is the case in 9.2 because CHAR datatype is used internal.The 'ABCD'||' ' = 'ABCD' was treated as a comparison between CHAR 'ABCD ' and CHAR 'ABCD' in 9.2 but as a comparison between VARCHAR2 'ABCD ' and CHAR 'ABCD' in 10g and later. In mixed mode comparison we don't pad out and hence the values are treated as different. Solution
In order to maintain the behaviour of 9.2, you can use a backout switch.1. Flush shared poolconn sys/xxxx as sysdbaalter system flush shared_pool;2. Set the backout switch at session levelalter session set events='10946 trace name context forever, level 16384';
如果用'ABCD'和'ABCD'||' '来比较,则发生了变化,9i中,还是用char来比较,两个字符串相等。而从10g开始,'ABCD'||' '会被当成varchar2类型,等式不等但是,上面讨论的只限于PL/SQL,在SQL语句中,'ABCD'||' '和'ABCD '一样,都是char类型这属于一个bug
开发过程中要加以注意
如果有一边是varchar2类型的字段,或是在pl/sql代码块中,字符串以'xxx'||' '形式出现,则没有空格的为题
填补空格的语义比较:在短的字符串后面加上空格,然后比较,适用于char nchar
非填补空格的语义比较,适用于varchar,varchar2
http://bbs.csdn.net/topics/390805419敢问包子帝:现在到底是制造恐怖主义,还是反恐?
连http://www.android-x86.org/、http://developer.android.com这些纯技术类型的网站都上不了!
干脆,让全国所有与android有关联的IT公司都强制关闭了吧!
它妈的奶娘的B!还让不让我们屁民活啊!!!!!!