问题描述
我使用以下 CREATE 语句在 Oracle 10g 中创建了一个表.
I have created a table in Oracle 10g using the following CREATE statement.
CREATE TABLE test ("id" NUMBER(35, 0) primary key, "description" VARCHAR2(250) not null);
基本的表结构如下所示.
The basic table structure looks like as follows.
--------------------------------------------------------------------------------
Column Name Data Type Nullable Default Primary Key
--------------------------------------------------------------------------------
id NUMBER(35, 0) No - 1
description VARCHAR2(250) No - -
需要特别注意的是，这个 CREATE 语句中的列名用双引号括起来只是为了好玩:)
It should precisely be noted that the column names in this CREATE statement are enclosed within double quotes just for having a fun :)
发出这个 DDL 语句后，我发出了三个 DML 语句来添加这么多行，如下所示.
After issuing this DDL statement, I issued three DML statements to add this many rows as follows.
INSERT INTO test VALUES (1, 'aaa');
INSERT INTO test VALUES (2, 'bbb');
INSERT INTO test VALUES (3, 'ccc');
最后，执行以下 SELECT 语句以验证是否插入了这些行.
And finally, the following SELECT statement was executed to verify, if those rows were inserted.
SELECT * FROM test;
Oracle 确实显示了与执行此查询时插入的完全相同的三行.
Oracle indeed displays three rows exactly as inserted on executing this query.
但是当我发出以下 SELECT 查询时，
But when I issue the following SELECT query,
SELECT id, description FROM test;
Oracle 抱怨，
ORA-00904: "DESCRIPTION": 无效标识符
以下(相同)查询也是，
The following (same) query also,
SELECT id FROM test;
因错误而失败，
ORA-00904:ID":无效标识符
查询也是如此，
SELECT description FROM test;
唯一带有元字符 * 的 SELECT 查询有效.在 SELECT 子句中列出字段不起作用.将 SELECT 子句中的列名大写也不起作用.
The only SELECT query with the meta character * works. Listing fields in the SELECT clause doesn't work. Capitalizing the column names in the SELECT clause also doesn't work.
背后的原因是什么?
推荐答案
好吧，我不说了，我会大声思考.
OK, I won't say it, I'll just think it loudly.
文档 明确指出，如果您有引用标识符，你必须在任何地方引用它们(我用斜体表示强调):
The documentation clearly says that if you have quoted identifiers, you have to quote them everywhere (my italics for emphasis):
每个数据库对象都有一个名称.在 SQL 语句中，您用带引号的标识符或不带引号的标识符来表示对象的名称.
带引号的标识符以双引号 (") 开头和结尾.如果使用带引号的标识符命名架构对象，那么在引用该对象时必须使用双引号.
未加引号的标识符没有被任何标点符号包围.
A nonquoted identifier is not surrounded by any punctuation.
所以你总是必须这样做:
So you always have to do:
SELECT "id", "description" FROM test;
这是一种痛苦.但显然我也只是这么想，并没有真正说出来.
Which is a pain. But obviously I'm just thinking that too, not really saying it.
这篇关于Oracle 中带引号字段的 CREATE 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！