oracle - SQL避免在select语句中调用同一个函数两次

Question

我有一个 select 语句调用同一个函数两次以返回两个不同的列，因为同一个函数被调用两次，它会产生性能问题。

我只想调用函数一次并将其值复制到另一列。在 Oracle 中有可能吗？

SELECT ID
    ,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "BLOCK" 
    ,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "MASK"
    from table 
    where ID=condition;

Answer 1

您可以使用 with clause一次获取数据

with block as (
select PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) as block 
from table
 where ID=condition;
) select ID, block.block as "BLOCK" , block.block as "MASK" from block 

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.