在 Oracle 中使用 CREATE 命令将列名括在双引号内无法正常工作。为什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8628412/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Enclosing column names within double quoatation marks with CREATE command in Oracle doesn't work properly. Why?
提问by Lion
Lets consider a simple table say productsin Oracle (I tried on Oracle 9i). I'm creating this table with the following CREATE statement.
让我们考虑一下productsOracle 中的一个简单表（我在 Oracle 9i 上尝试过）。我正在使用以下 CREATE 语句创建此表。
CREATE TABLE products
("prod_id" varchar2(7) primary key, "product_name" varchar2(30) NOT NULL);
It is to be noted specially that I'm enclosing the column names within double quotation marks ""as not usually we do. It would obviously work and the productstable would be created with those two columns with the specified CONSTRAINTS.
需要特别指出的是，我将列名括在双引号中""，这与我们通常不这样做。它显然会起作用，并且products将使用具有指定约束的那两列创建表。
Now, lets insert some rows into this table using the following INSERT INTO command.
现在，让我们使用以下 INSERT INTO 命令向该表中插入一些行。
INSERT INTO products VALUES('P0001', 'Nokia-N97');
INSERT INTO products VALUES('P0002', 'Nokia-1208');
INSERT INTO products VALUES('P0003', 'Nokia-1115');
Would insert three rows into the productstable.
将在products表中插入三行。
To make sure that these rows have indeed inserted or not, we can issue a SELECT statement as follows.
为了确保这些行确实插入与否，我们可以发出如下的 SELECT 语句。
SELECT * FROM products;
Would work just fine and display three rows we inserted.
可以正常工作并显示我们插入的三行。
Now, the actual question here. When we issue the following SELECT statement,
现在，真正的问题在这里。当我们发出以下 SELECT 语句时，
SELECT prod_id, product_name FROM products;
would not work even though we didn't make any mistake in this SQL. Oracle would report instead that such columns don't exist. Why does this happen? There must be very specific reason behind it, I think.
即使我们在这个 SQL 中没有犯任何错误，也不会工作。Oracle 会报告这样的列不存在。为什么会发生这种情况？我想这背后一定有非常具体的原因。
I'm sure that enclosing column names unnecessarily within double quotation marks as I have just done may not be the best practice but just a question occurred to me.
我确信将列名不必要地用双引号括起来，就像我刚刚所做的那样可能不是最佳实践，但只是我想到了一个问题。
回答by Jens Schauder
Against common believe, Oracle is case sensitive in column and table names. It just converts everything to upper case by default.
与普遍看法相反，Oracle 在列名和表名中区分大小写。默认情况下，它只是将所有内容都转换为大写。
But if you use names in double quotes, you tell Oracle to create the column in the exact spelling you provided (lower case in the CREATEstatement).
但是，如果您在双引号中使用名称，则您会告诉 Oracle 按照您提供的确切拼写（CREATE语句中的小写）创建列。
Since in the SELECTstatement, you don't use quotes, the names are converted to upper case and are therefore not equal to the lower case names in the CREATEstatement.
由于在SELECT语句中不使用引号，名称被转换为大写，因此不等于CREATE语句中的小写名称。
So this should work:
所以这应该有效：
SELECT "prod_id", "product_name" FROM products;
If you don't know how column or table names are specified, you can look it up in the data dictionary. You will find lower case column names for your producttable, but upper case table name, since it wasn't quoted.
如果您不知道列名或表名是如何指定的，您可以在数据字典中查找。您会为您的product表找到小写的列名，但会找到大写的表名，因为它没有被引用。
