Oracle 中带引号字段的 CREATE 语句 [英] A CREATE statement with quoted fields in Oracle

问题描述

我使用以下 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": 无效标识符

ORA-00904: "DESCRIPTION": invalid identifier

以下(相同)查询也是，

The following (same) query also,

SELECT id FROM test;

因错误而失败，

ORA-00904:ID":无效标识符

ORA-00904: "ID": invalid identifier

查询也是如此，

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.

背后的原因是什么?

_{请不要只是说，不要这样做.我很想知道背后的原因.}

_{Please don't just say, Don't do this. I'm interested in knowing the reason behind it.}

推荐答案

好吧，我不说了，我会大声思考.

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 语句中，您用带引号的标识符或不带引号的标识符来表示对象的名称.

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

• 带引号的标识符以双引号 (") 开头和结尾.如果使用带引号的标识符命名架构对象，那么在引用该对象时必须使用双引号.

未加引号的标识符没有被任何标点符号包围.

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 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！