游标的替代 [英] Alternative for a cursor

问题描述

我有一个函数，每次调用都会更改其值.我想在一个查询中多次使用此值，但是我不想多次执行它，因为我想从第一次调用中获取该值.我已经尝试过:

I have a function that with each invocation changes its value. I would like to use this value more than once in a query, but I don't want to execute it more than once because I want the value from the first invocation. I have tried:

SELECT RESULT value1,
       RESULT value2,
       RESULT value3
  FROM (SELECT function_invocation() RESULT
          FROM dual);

但是每个VALUE列给我不同的值，这意味着该函数被多次调用.

But each VALUE column gives me different value which means that the function was invoked more than once.

一种替代方法是编写一个游标，但我想知道纯SQL是否可行.

An alternative would be to write a cursor but I was wondering if it's possible with pure SQL.

推荐答案

有一些技巧可以防止Oracle不必要地重新执行功能.这个主题很困难，因为99.9％的时间我们依靠Oracle自动重写查询以使其最佳运行.停止这些优化不是一个常见的任务.

There are a few tricks to prevent Oracle from unnecessarily re-executing functions. This topic is difficult because 99.9% of the time we depend on Oracle to automatically re-write queries to run optimally. Stopping those optimizations should not be a common task.

理论上，没有办法保证声明式SQL语句的操作顺序.实际上，有两种简单的技术可以帮助防止函数重新运行:标量子查询缓存和ROWNUM.

In theory, there is no way to guarantee the order of operations of a declarative SQL statement. In practice, there are two simple techniques that can help prevent functions from being re-run: scalar subquery caching and ROWNUM.

首先，让我尝试重现该问题.单个值引用返回三个不同的数字.

First, let me try to reproduce the problem. A single value reference returns three different numbers.

create or replace function function_invocation return number is
begin
    return dbms_random.value;
end;
/

SELECT RESULT value1,
       RESULT value2,
       RESULT value3
  FROM (SELECT function_invocation() RESULT
          FROM dual);

VALUE1   VALUE2   VALUE3
------   ------   ------
0.3089   0.7103   0.2885

重新编写查询以使用标量子查询似乎没有必要，但是这种形式使Oracle可以使用标量子查询缓存，这是Oracle避免重新运行代码的一种优化技术.现在，三列返回相同的值.

Re-writing the query to use a scalar subquery seems unnecessary, but this form enables Oracle to use scalar subquery caching, an optimization technique that Oracle uses to avoid re-running code. Now the three columns return the same value.

select result value1, result value1, result value1
from
(
    select (select function_invocation() from dual) result from dual
);

VALUE1   VALUE2   VALUE3
------   ------   ------
0.2450   0.2450   0.2450

或者，我们可以通过添加ROWNUM伪列来阻止优化转换:

Alternatively, we can prevent optimization transformations by adding a ROWNUM pseudo-column:

SELECT RESULT value1,
       RESULT value2,
       RESULT value3
  FROM (SELECT function_invocation() RESULT, rownum
          FROM dual);

VALUE1   VALUE2   VALUE3
------   ------   ------
0.1678   0.1678   0.1678

这些技术在实践中效果很好，可以使结果看起来正确.但是，该函数有可能在执行之前再秘密运行一次.该额外的运行用于解析和缓存，并且不会影响结果.但是，如果您具有只能运行一次的副作用的功能，则必须跳过一些额外的障碍.

These techniques work well in practice for getting the results to look the right way. However, it's possible that the function will be secretly run an additional time before the execution. That extra run is for parsing and caching, and won't affect the results. But if you have a function with side-effects that can only be run once, there are some extra hoops you must jump through.

这篇关于游标的替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！