我正在寻找任何参考文档或受信任的指南，以便在返回结果后确定选择语句列的名称。
让我通过一些例子来解释——
示例 1 -
SELECT C.FIRST_NAME
FROM CLIENT C;
此处的输出列名称是 -
FIRST_NAME
示例 2 -
SELECT C.FIRST_NAME AS "First Name"
FROM CLIENT C;
此处的输出列名称是 -
First Name
示例 3 -
SELECT COUNT(C.FIRST_NAME)
FROM CLIENT C;
此处的输出列名称是 -
COUNT(C.FIRST_NAME)
从上面看， 我们可以看到，列名可以是别名、所选表的列名，甚至是整个语句本身。
我的问题是，Oracle 是否提供了任何定义的准则/规则来确定输出列名称是什么？
Oracle 似乎有一种算法，涉及删除空格和大写内容。 但是，它为投影提供的结果列别名似乎不遵守您和我所遵循的标识符的常规规则。 出于这个原因，我认为尝试复制Oracle的内部算法对于你想要完成的任何事情都没有价值 - 因为即使你做得完美，也不能保证你最终会得到一个你可以使用的标识符。
举个例子：
SELECT * FROM
(
SELECT OWNER, COUNT(distinct object_name || 'This is a really long string in my expression, don''t you think? Actually, it''s really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, ridiculously long!')
FROM DBA_OBJECTS
WHERE ROWNUM <= 100
GROUP BY OWNER )
ORDER BY 2 DESC;
。对于懒得向右滚动的读者来说，以COUNT(distinct object_name...开头的表达式真的非常非常长。
让我们运行它，看看 Oracle 使用什么来投影内部查询。
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| OWNER | COUNT(DISTINCTOBJECT_NAME||'THISISAREALLYLONGSTRINGINMYEXPRESSION,DON''TYOUTHINK?ACTUALLY,IT''SREALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SYS | 100 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
我们将使用DBMS_XPLAN来查看投影信息。
SELECT *
FROM TABLE (DBMS_XPLAN.display_cursor (null, null,
'ADVANCED LAST'));
列投影信息(由操作 ID 标识(：
1 - (#keys=1) INTERNAL_FUNCTION("from$_subquery$_001"."COUNT(DISTINCTOBJECT_NAME||'THISISAREALLYLONGS TRINGINMYEXPRESSION,DON''TYOUTHINK?ACTUALLY,IT''SREALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY ,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALL Y,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REAL LY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REA LLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,RE ALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,REALLY,R IDICULOUSLYLONG!')")[22], "from$_subquery$_001"."OWNER"[VARCHAR2,128]
这是内部使用的 688 个字符的标识符。 祝你好运利用那个！
更多证据表明 Oracle 确实在内部使用所有 688 个字符进行投影......
我采用了上面的示例查询并复制了非常(非常！(长的表达式。 然后，我卡了一个FETCH FIRST 1 ROW ONLY. 正如 Oracle 12c 中众所周知的那样，由于 Oracle 处理FETCH子句的方式，如果查询中有重复的列别名，它将不起作用。
SELECT OWNER,
COUNT(distinct object_name || 'This is a really long string in my expression, don''t you think? Actually, it''s really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, ridiculously long!'),
COUNT(distinct object_name || 'This is a really long string in my expression, don''t you think? Actually, it''s really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, ridiculously long!') FROM DBA_OBJECTS WHERE ROWNUM <= 100 GROUP BY OWNER FETCH FIRST 1 ROW ONLY;
ORA-00918: column ambiguously defined 00918. 00000 - "column ambiguously defined" *Cause: *Action:
现在，重复但更改表达式中的最后一个字符(将"！"更改为"？"(，您将看到它运行良好。
同样，Oracle 通过其内部未记录的逻辑生成的列别名不遵守标识符的标准规则。 在我的书中，这使得它们无法使用，即使你对确定它们的逻辑进行了逆向工程。
我认为您的困惑是第一种情况：
SELECT C.FIRST_NAME
为什么这是FIRST_NAME而不是"C.FIRST_NAME"？
我认为理由是有三种情况。 您可以准确表示后两个：用户分配的别名和没有别名的表达式(甚至是子查询(。
但是，选择简单列时，对表(或视图或子查询(中的列的引用。 Oracle 确定列名就足够了，而不是限定列名。 因此，这将返回具有相同别名的两列：
SELECT C.FIRST_NAME, FIRST_NAME
如果列涉及表达式(除了括号的简单使用(，则保留限定的列名。
我应该指出：这个解释是基于观察。 我不知道这是否有记录，也许其他人知道提供官方解释的参考资料。
这是一个很好的指南，不是官方的，但涵盖了最重要的主题。
https://www.oracletutorial.com/oracle-basics/oracle-alias/
现在，我个人将把我获取列的方式分为 2：别名或表达式，所以如果你想让一些可读的东西使用别名，它们可以括在双引号中，或者你甚至不必使用"as"。 如果您不使用别名，Oracle 默认会返回表达式，如果您保留列名或具有函数，则
返回表达式。我希望这对你有所帮助。