Oracle SELECT-双引号还是没有双引号? [英] Oracle SELECT - Double quotes or no double quotes?

问题描述

全部

在为Oracle 11i数据库编写SELECT查询时，为什么Oracle中的某些表强制将引号应用到所选字段，而另一些则不行.

When writing SELECT queries for Oracle 11i databases, why do some tables in Oracle enforce applying quotation marks to the fields being selected, and others not.

我最近遇到的一个例子:

An example I've recently come across:

在Aqua Data Studio的查询分析器"窗口中，我试图从两个不同的表中选择相同的字段:

In Aqua Data Studio in a Query Analyzer window I have attempted to select the same field from two different tables:

    select _id from table1
    select _id from table2

table1和table2相差很大，但是只有table1执行此select语句时不会出错.当我尝试为table2执行此语句时，我得到以下信息:

table1 and table2 differ greatly, but Only table1 executes this select statement without an error. When I try to execute this statement for table2 I get the following:

ORA-00904: "_ID": invalid identifier
Script line 1, statement line 1, column 7 

但是，当我像这样执行第二条语句时，它可以正常运行:

However, when I execute the second statement like this it works perfectly:

    select "_id" from table2

有人知道这是怎么回事吗?为什么会这样?表之间的主要区别是什么导致的呢?

Does anyone know what is going on here, why is this the case, and what could the key differences be between the tables be that is causing this?

谢谢

推荐答案

关于大小写的答案和链接是正确的，但是您的情况超出了简单的案例问题，这不仅是因为您的列名以下划线开头，而且因为客户通常会向您隐瞒报价.

The answers and links about casing are correct, but your situation goes a little beyond a simple case issue, both because your column name started with an underscore and because your client is apaprently usually hiding the quoting from you.

如果您尝试创建带有名为 _id 的列的表而未引用该表，则将收到"ORA-00911:无效字符"错误，其原因文本为标识符不得以字母和数字以外的任何ASCII字符开头；这实际上也是错误的，因为它也不能以数字开头(例如， 0_id 给出'ORA-00904::无效标识符').这由数据库对象命名规则进行备份:

If you tried to create a table with a column called _id, without quoting it, then you'd get an 'ORA-00911: invalid character' error, the cause text of which says 'identifiers may not start with any ASCII character other than letters and numbers'; which is actually wrong as well since it can't start with a number either (for example, 0_id gives 'ORA-00904: : invalid identifier'). This is backed up by the database object naming rules:

未加引号的标识符必须以数据库字符集中的字母字符开头.带引号的标识符可以以任何开头字符.

Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

因此，似乎Aqua Data Studio遵循将您提供的对象名称的大写版本括在双引号中的惯例，这是链接文章之一中提到的一种做法.

So it looks like Aqua Data Studio is following a convention of enclosing the upper-case version of the object name you supply in double-quotes, a practice mentioned in one of the linked posts.

从您显示的内容中，将 select ... ...的ID传递给Oracle作为 select from ...的"_ID"，如果列名称已创建为"_ ID" .似乎是 table1 的情况，但是 table2 是作为"_ id" 创建的-因此，大小写不匹配会产生合法的ORA-00904在看.

From what you've shown, select _id from ... gets passed to Oracle as select "_ID" from ..., which is fine if the column name was created as "_ID". It appears that is the case for table1, but table2 was created as "_id" - so that case mismatch generates the legitimate ORA-00904 you're seeing.

您的客户端没有修改已经用双引号引起来的列名，因此从...中选择"_id" 会原样传递给Oracle，并且对于 table2 (但相反，对于 table1 而言将失败).

Your client is not modifying a column name that is already enclosed in double-quotes, so select "_id" from ... is passed through to Oracle as-is, and works OK for table2 (but, conversely, would fail for table1).

如果Oracle不遵循未加引号的标识符规则，并且名称是按加引号创建的，则Oracle必须将该名称用双引号引起来-除非原始加引号的值仍然有效，即遵循未加引号的规则并输入大写.由于您的列名以下划线开头，因此就Oracle而言，无论大小写，对它的所有引用都必须用双引号引起来.如果您自己没有引用，您的客户只会在后台执行该操作.

Oracle requires the name to be enclosed in double quotes if it doesn't follow the rules for unquoted identifiers, and if it was created as quoted - unless the original quoted value was valid anyway, i.e. follows the unquoted rules and was entered in uppercase. Since your column name starts with an underscore, as far as Oracle is concerned all references to it have to be enclosed in double-quotes regardless of the case. Your client is just doing that in the background if you haven't quoted it yourself.

按照他人的建议避免使用带引号的标识符，并始终使用有效的未加引号的名称可以避免此类问题.

Following the advice others have given to avoid quoted identifiers and to always use names that are valid unquoted would avoid issues like this.

这篇关于Oracle SELECT-双引号还是没有双引号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！