从表中选择*，并在单个命名列上执行一些函数

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;

选择ID、DESCRIPTION、TO_CHAR(CHANGE_DATE，'YYYY-MM-DD HH24:MI:SS')作为MY_TABLE的FORMATED_DATE;

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.

这对这三列都很好。但是，该表实际上将有更多的列，并且可以连接到其他表上。我希望能够使用通配符来获取所有列，并且仍然能够执行TO_CHAR转换。

Something like : SELECT *, (CHANGE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE FROM MY_TABLE;

从MY_TABLE中选择* (CHANGE_DATE， 'YYYY-MM-DD HH24:MI:SS')作为FORMATED_DATE;

As you would have guessed from TO_CHAR, I am using an Oracle so I'm using PLSQL.

正如您可能从TO_CHAR中猜到的，我使用的是Oracle，所以我使用的是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.

所以我的问题是:是否有一种语法允许我选择所有列(通过*)，并且仍然能够在这些列中的单个列上调用函数。

5 个解决方案

#1

The closest you could do is something like:

你能做的最接近的事情是:

SELECT 
     MY_TABLE.*, 
     (CHANGE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE 
FROM MY_TABLE;

#2

Rather than lecture you, here. Oracle is a little fussier than MSSQL about it, but this worked for me.

而不是在这里讲课。Oracle比MSSQL对它有点挑剔，但这对我很有效。

SELECT GENERAL.GOREMAL.* ,rownum ,current_date from GENERAL.GOREMAL

选择GENERAL.GOREMAL。*，rownum,current_date from GENERAL.GOREMAL

#3

The following is acceptable:

以下是可以接受的:

SELECT T1.*, T2.*, x + y as some_Z

or perhaps

或者

SELECT compute_foo() as aColumn, *

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.

最后一个将始终将您的特殊列保持在相同的位置—在开始—无论通配符包含多少列。

#4

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.

在SQL Server中，您所编写的内容是完全有效的，我假设它也应该在Oracle中工作。请注意，您将返回日期列两次，一次在它的组织形式，一次在正式形式。

FYI Using SELECT * should probally be avoided but that's for another question:-)

应避免使用SELECT *，但这是另一个问题:-)

#5

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.

特别要避免选择*，因为它浪费服务器和网络资源，特别是因为所有的连接字段都有相同的信息。像“select *”这样的垃圾代码会产生性能问题，当系统中的每个查询都写得很糟糕时，这些问题就很难修复。我知道在SQL Server中，可以从对象浏览器中拖拽列，如果ORacle有类似的东西，也不会感到奇怪。

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.

此外，select *可以随着数据表的更改而创建许多以后出现的错误。不按照您想要的特定顺序来命名列是很糟糕的做法。

#1