自定义惰性评估/功能参数短路的语法 [英] A syntax for custom lazy-evaluation/short-circuiting of function parameters

问题描述

Oracle定义了几种结构，这些结构利用了看似懒惰的评估但实际上是短路的评估.

Oracle defines several structures that make use of what looks like lazy evaluation but what's actually short-circuiting.

例如:

x := case when 1 = 2 then count_all_prime_numbers_below(100000000)
          else 2*2
     end;

永远不会调用count_all(...)函数.

The function count_all(...) will never be called.

但是，我更感兴趣的是看起来像常规函数调用的语法:

However, what I'm more interested in is the syntax that looks like regular function call:

x := coalesce(null, 42, hundreth_digit_of_pi());

由于合并不是常规函数，因此不会调用Hundreth_digit_of_pi()，但是语法糖看起来像一个-对于常规变量，调用该函数时会评估参数.

Hundreth_digit_of_pi() will not be called since coalesce is not a regular function, but a syntax sugar that looks like one - for regular ones parameters get evaluated when the function is called.

问题是: 是否可以在plsql中定义一个行为相同的自定义过程/函数?

The question is: is it possible do define in plsql a custom procedure/function that would behave in the same way?

如果您不确信我会举一个可能有用的示例:

If you're not convinced I'll give an example when that could be useful:

我们使用框架"进行记录. 要跟踪您调用的过程，请执行以下操作:

We use ''framework'' for logging. To trace something you call a procedure:

trace_something('A text to be saved somewhere');

Trace_something是执行以下步骤的"pragma自主交易"过程: 1.查看是否启用了任何跟踪方法(例如，文件/数据库表) 2.对于每个启用的方法，请使用该方法将参数保存在某处. 3.如果已保存到数据库，请提交.

Trace_something is 'pragma autonomous transaction' procedure that does the following steps: 1. See, if any tracing methods (for example file / db table) are enabled 2. For every enabled method use that method to save parameter somewhere. 3. If it was saved to DB, commit.

在构建要跟踪的实际字符串时可能会花费大量时间，并且即使一开始就没有启用跟踪，我们也不想花费它.

A problem occurs when building the actual string to be traced might take noticable amount of time, and we wouldn't want to have to spend it, if tracing isn't even enabled in the first place.

目标将是伪代码:

procedure lazily_trace_something(some_text lazily_eval_type) {
    if do_i_have_to_trace() = TRUE then
        trace_something(evaluate(some_text));
    else
        NULL; -- in which case, some_text doesn't get evaluated     
    end if;
}


/*

*/

lazily_trace_something(first_50_paragraphs_of_lorem_ipsum(a_rowtype_variable));

是否可以在plsql中完成?

Is it possible to be done in plsql?

推荐答案

可以使用ref游标，条件编译或立即执行来(部分)实现惰性评估. ANYDATA类型可用于传递通用数据.

Lazy evaluation can be (partially) implemented using ref cursors, conditional compilation, or execute immediate. The ANYDATA type can be used to pass generic data.

可以使用静态SQL语句打开Ref游标，将其作为参数传递，并且直到需要时才执行.

Ref cursors can be opened with a static SQL statement, passed as arguments, and will not execute until needed.

尽管这从字面上回答了您有关懒惰评估的问题，但我不确定它是否真正可行.这不是ref游标的预期用途.而且必须将SQL添加到所有内容中可能并不方便.

While this literally answers your question about lazy evaluation I'm not sure if it's truly practical. This isn't the intended use of ref cursors. And it may not be convenient to have to add SQL to everything.

首先，要证明慢速功能正在运行，请创建一个仅休眠几秒钟的功能:

First, to prove that the slow function is running, create a function that simply sleeps for a few seconds:

grant execute on sys.dbms_lock to <your_user>;

create or replace function sleep(seconds number) return number is
begin
    dbms_lock.sleep(seconds);
    return 1;
end;
/

创建一个函数来确定是否需要评估:

Create a function to determine whether evaltuation is necessary:

create or replace function do_i_have_to_trace return boolean is
begin
    return true;
end;
/

此函数可以通过执行SQL语句来执行工作.即使您可能不需要返回值，SQL语句也必须返回某些内容.

This function may perform the work by executing the SQL statement. The SQL statement must return something, even though you may not want a return value.

create or replace procedure trace_something(p_cursor sys_refcursor) is
    v_dummy varchar2(1);
begin
    if do_i_have_to_trace then
        fetch p_cursor into v_dummy;
    end if;
end;
/

现在创建将始终调用跟踪但不必花费时间评估参数的过程.

Now create the procedure that will always call trace but will not necessarily spend time evaluating the arguments.

create or replace procedure lazily_trace_something(some_number in number) is
    v_cursor sys_refcursor;
begin
    open v_cursor for select sleep(some_number) from dual;
    trace_something(v_cursor);
end;
/

默认情况下，它正在工作并且很慢:

By default it's doing the work and is slow:

--Takes 2 seconds to run:
begin
    lazily_trace_something(2);
end;
/

但是，当您将DO_I_HAVE_TO_TRACE更改为false时，即使传递了慢速参数，该过程也是快速的.

But when you change DO_I_HAVE_TO_TRACE to return false the procedure is fast, even though it's passing a slow argument.

create or replace function do_i_have_to_trace return boolean is
begin
    return false;
end;
/

--Runs in 0 seconds.
begin
    lazily_trace_something(2);
end;
/

其他选项

传统上，条件编译用于启用或禁用检测.例如:

Other Options

Conditional compilation is more traditionally used to enable or disable instrumentation. For example:

create or replace package constants is
    c_is_trace_enabled constant boolean := false;
end;
/

declare
    v_dummy number;
begin
    $if constants.c_is_trace_enabled $then
        v_dummy := sleep(1);
        This line of code does not even need to be valid!
        (Until you change the constant anyway)
    $else
        null;
    $end
end;
/

您可能还想重新考虑动态SQL.编程风格和一些语法糖可以在这里起到很大的作用.简而言之，替代的报价语法和简单的模板可以使动态SQL更具可读性.有关更多详细信息，请参见我的帖子此处

You may also want to re-consider dynamic SQL. Programming style and some syntactic sugar can make a big difference here. In short, the alternative quote syntax and simple templates can make dynamic SQL much more readable. For more details see my post here.

ANY类型可用于存储和传递任何可以想象的数据类型.不幸的是，每种行类型都没有本机数据类型.您需要为每个表创建一个TYPE.这些自定义类型非常简单，因此可以在必要时自动执行步骤.

The ANY types can be use to store and pass any imaginable data type. Unfortunately there's no native data type for each row type. You'll need to create a TYPE for each table. Those custom types are very simple so that step can be automated if necessary.

create table some_table(a number, b number);
create or replace type some_table_type is object(a number, b number);

declare
    a_rowtype_variable some_table_type;
    v_anydata anydata;
    v_cursor sys_refcursor;
begin
    a_rowtype_variable := some_table_type(1,2);
    v_anydata := anydata.ConvertObject(a_rowtype_variable);
    open v_cursor for select v_anydata from dual;
    trace_something(v_cursor);
end;
/

这篇关于自定义惰性评估/功能参数短路的语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！