在Oracle中，用CREATE命令将列名括在双引号内无法正常工作.为什么? [英] Enclosing column names within double quoatation marks with CREATE command in Oracle doesn't work properly. Why?

问题描述

让我们考虑一个简单的表，说它在Oracle中是products(我在Oracle 9i上尝试过).我正在使用以下CREATE语句创建该表.

Lets consider a simple table say products in Oracle (I tried on Oracle 9i). I'm creating this table with the following CREATE statement.

CREATE TABLE products

("prod_id" varchar2(7) primary key, "product_name" varchar2(30) NOT NULL);

要特别注意的是，我通常不这样做，而是将列名放在双引号""中.显然可以，并且products表将使用具有指定CONSTRAINTS的这两列来创建.

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 products table would be created with those two columns with the specified CONSTRAINTS.

现在，让我们使用下面的INSERT INTO命令向该表中插入一些行.

Now, lets insert some rows into this table using the following INSERT INTO command.

INSERT INTO products VALUES('P0001', 'Nokia-N97');
INSERT INTO products VALUES('P0002', 'Nokia-1208');
INSERT INTO products VALUES('P0003', 'Nokia-1115');

将三行插入到products表中.

为确保确实插入了这些行，我们可以发出SELECT语句，如下所示.

To make sure that these rows have indeed inserted or not, we can issue a SELECT statement as follows.

SELECT * FROM products;

可以正常工作并显示我们插入的三行.

Would work just fine and display three rows we inserted.

现在，这里是实际问题.当我们发出以下SELECT语句时，

Now, the actual question here. When we issue the following SELECT statement,

SELECT prod_id, product_name FROM products;

即使我们在此SQL中没有犯任何错误，也无法正常工作. Oracle将报告不存在此类列.为什么会这样?我认为背后一定有非常具体的原因.

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.

我敢肯定，像我刚刚做的那样，将列名不必要地用双引号引起来可能不是最佳实践，而对我来说只是一个问题.

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.

推荐答案

与众不同，Oracle在列名和表名中区分大小写.默认情况下，它只是将所有内容都转换为大写.

Against common believe, Oracle is case sensitive in column and table names. It just converts everything to upper case by default.

但是，如果在双引号中使用名称，则会告诉Oracle按照您提供的确切拼写创建列(在CREATE语句中为小写).

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 CREATE statement).

由于在SELECT语句中不使用引号，因此名称将转换为大写字母，因此不等于CREATE语句中的小写字母.

Since in the SELECT statement, you don't use quotes, the names are converted to upper case and are therefore not equal to the lower case names in the CREATE statement.

这应该可以工作:

SELECT "prod_id", "product_name" FROM products;

如果您不知道如何指定列名或表名，则可以在数据字典中查找.您会在product表中找到小写的列名，但是会找到大写的表名，因为它没有被引用.

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 product table, but upper case table name, since it wasn't quoted.

这篇关于在Oracle中，用CREATE命令将列名括在双引号内无法正常工作.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！