select * from ta where f= 1
select * from ta where f= '1'
两个表ta,tb的某一字段f,均为VARCHAR2(8)
为什么查询两个表中f为1的记录时
一个表的上面两种方法都可以
而另一个表只能用=‘1’,而不可以用=1
select * from ta where f= '1'
两个表ta,tb的某一字段f,均为VARCHAR2(8)
为什么查询两个表中f为1的记录时
一个表的上面两种方法都可以
而另一个表只能用=‘1’,而不可以用=1
desc ta;
desc tb;看一下
不过向字符型这样的字段在查询或赋值时一般应该显示给出字段类型,不让要oracle帮你做字段类型的转换(影响性能)。
----------
1010
1010SQL> select * from t5 where id='1010';ID
----------
1010
1010SQL> create table t6(id number);Table created.SQL> insert into t6 values('1010');1 row created.SQL> insert into t6 values(1010);1 row created.SQL> select * from t6; ID
----------
1010
1010SQL> select * from t6 where id='1010'; ID
----------
1010
1010SQL> select * from t6 where id=1010; ID
----------
1010
1010
好像不管表中字段类型是数值还是字符
都可以查出来的BTW:其实我自己对ORACLE对字符和数字类型的自动转换也有些困惑
网上也没搜到相关资料
原因很简单,由于字段类型是 VARCHAR2(8),而你写【f=1】,这样oracle会对这个条件进行默认转换成
【where to_number(f)=1】,这种情况下 如果【f】字段中没有【字母】自然就是ok的,但是有【字母】的话,to_number 就会有问题了。下面是执行计划,可以参考,注意1 - filter(TO_NUMBER("NAME")=1)
[TEST@ora10gr1#2009-12-09/08:47:58] SQL>insert into t1 values(6,1);1 row created.[TEST@ora10gr1#2009-12-09/08:48:04] SQL>desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(10)[TEST@ora10gr1#2009-12-09/08:48:06] SQL>select * from t1; ID NAME
---------- ----------
5 a
6 1[TEST@ora10gr1#2009-12-09/08:48:42] SQL>explain plan for
2 select * from t1 where name = 1;Explained.[TEST@ora10gr1#2009-12-09/08:48:49] SQL>select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1931397137--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 5 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------- 1 - filter(TO_NUMBER("NAME")=1)13 rows selected.[TEST@ora10gr1#2009-12-09/08:49:06] SQL>select * from t1 where name = 1;
select * from t1 where name = 1
*
ERROR at line 1:
ORA-01722: invalid number
[TEST@ora10gr1#2009-12-09/08:50:12] SQL>delete from t1 where id = 5;1 row deleted.[TEST@ora10gr1#2009-12-09/08:50:26] SQL>select * from t1; ID NAME
---------- ----------
6 1[TEST@ora10gr1#2009-12-09/08:50:30] SQL>select * from t1 where name = 1; ID NAME
---------- ----------
6 1