今天create一个表的时候, 字段加了引号,结果select 该字段的时候告诉字段找不着,示例如下:
SQL> create table test("id" varchar2(10));
Table created.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
id VARCHAR2(10)
SQL> insert into test values('1');
1 row created.
SQL> commit;
Commit complete.
SQL> select id from test;
select id from test
*
ERROR at line 1:
ORA-00904: "ID": invalid identifier
SQL> select "id" from test;
id
----------
1
SQL> select * from test;
id
----------
1
SQL>
SQL> create table test(id varchar2(10));
Table created.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID VARCHAR2(10)
SQL> insert into test values('2');
1 row created.
SQL> commit;
Commit complete.
SQL> select id from test;
ID
----------
2
SQL> select ID from test;
ID
----------
2
SQL> select * from test;
ID
----------
2
SQL> select "id" from test;
select "id" from test
*
ERROR at line 1:
ORA-00904: "id": invalid identifier
SQL> select "ID" from test;
ID
----------
2
SQL>
具体如下:
id=ID="ID" 第1个id默认转换成大写ID, 第二个ID也默认转换成大写ID, 第三个不作转换, 由于引号引住,本身就是大写, 所以三个相等.
≠ (上面一行不等于下面一行)
"id"="id" 第1个"id"由于是引号引住,并且是小写, 所以引用该字段时只能用"id"引用,其它任何形式都会报错.
结论: oracle 的字段, 在不加引号时, 全部默认转换成大写.
加引号时, 则不会自动转换, 写成什么样就是什么样, 在引用该字段时,如果大小写不匹配则报错 ORA-00904: "id": invalid identifier
由于存在上述特性,则可能出现2个字段名称一样的现象, 如下:
SQL> drop table test;
Table dropped.
SQL> create table test("id" varchar2(10), id varchar2(10));
Table created.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
id VARCHAR2(10)
ID VARCHAR2(10)
SQL> insert into test values('3','3');
1 row created.
SQL> select * from test;
id ID
---------- ----------
3 3