从表中选择 * 并仍然对单个命名列执行某些功能
我希望能够返回表中或联接结果表中的所有列，并且仍然能够按名称将日期转换为字符串。
例如
选择 ID、DESCRIPTION、TO_CHAR(CHANGE_DATE,'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE FROM MY_TABLE;
对于这三个专栏来说，这一切都很好。 但是，该表实际上会有更多的列，并且可能会连接到其他表上。 我希望能够使用通配符来获取所有列，并且仍然能够执行 TO_CHAR 转换。
就像是 ： SELECT *, (CHANGE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE FROM MY_TABLE;
正如您从 TO_CHAR 中猜到的那样，我使用的是 Oracle，所以我使用的是 PLSQL。
所以我的具体问题是：是否有一种语法允许我选择所有列（通过 *）并且仍然能够在这些列中的单个列上调用函数。
I'd like to be able to return all columns in a table or in the resulting table of a join and still be able to transform a date to a string by name.
For example
Select ID, DESCRIPTION, TO_CHAR(CHANGE_DATE,'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE FROM MY_TABLE;
This is all well and good for just these three columns. But, the table will actually have many more columns and may be joined onto other tables. I'd like to be able to use a wildcard to get all the columns and still be able to perform the TO_CHAR transformation.
Something like :
SELECT *, (CHANGE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE FROM MY_TABLE;
As you would have guessed from TO_CHAR, I am using an Oracle so I'm using PLSQL.
So my specific question is: Is there a syntax that would allow me to select all columns (via *) and still be able to call a function on single column within those columns.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
你能做的最接近的是：
The closest you could do is something like:
而不是在这里教训你。 Oracle 在这方面比 MSSQL 更挑剔，但这对我有用。
选择
戈雷马尔将军。*
,rownum
，当前日期
来自 戈雷马尔将军
Rather than lecture you, here. Oracle is a little fussier than MSSQL about it, but this worked for me.
SELECT
GENERAL.GOREMAL.*
,rownum
,current_date
from GENERAL.GOREMAL
以下是可以接受的：
或者
最后一个将始终将您的特殊列保留在同一位置 - 在开头 - 无论通配符引入多少列。
The following is acceptable:
or perhaps
The last of which will always keep your special columns in the same place - at the beginning - no matter how many columns the wildcard brings in.
在 SQL Server 中，您编写的内容完全有效，我认为它也应该在 Oracle 中工作。 请注意，您将返回日期列两次，一次以原始形式返回，一次以格式化形式返回。
仅供参考，可能应该避免使用 SELECT * 但这是另一个问题:-)
In SQL Server what you wrote is perfectly valid, I'd assume it should work in Oracle as well. Just be aware you will be returning date column twice once in its orginal form and once in the Formated form.
FYI Using SELECT * should probally be avoided but that's for another question:-)
仅供参考，如果您有联接，尤其要避免 select *，因为它会浪费服务器和网络资源，特别是因为所有联接字段都具有相同的信息。 像“select *”这样的垃圾编码会产生性能问题，当系统中的每个查询都编写得不好时，这些问题就变得很难修复。 我知道在 SQL Server 中，您可以从对象浏览器中拖动列，如果 ORACLE 有类似的东西，也不会感到惊讶。
此外，随着数据表的更改， select * 可能会产生很多很多后续错误。 不按照您想要的特定顺序命名列是不好的做法。
FYI, if you have joins, select * is especially to be avoided as it wastes server and network resources especially since all join fields have the same information. Junk coding like "select *" creates performance problems that become very difficult to fix when every query in the system is poorly written. I know in SQL Server you can drag the columns over from the object browser, wouldn't be surprised if ORacle had something similar.
In addition select * can create many, many later bugs as the data tables change. It is poor practice to not name your columns in the specific order you want them.