pl/sql函数调用了多少次? [英] pl/sql function called how many times?

问题描述

假设您有以下更新:

Update table set col1 = func(col2)
where col1<>func(col2)

func 函数的每一行评估两次，还是每行评估一次?

The func function is evaluated two times for every row, or once for every row?

谢谢

推荐答案

这种情况是一些有用的实验(以10g进行).使用以下查询，我们可以知道每次调用相同功能的正常函数(在这种情况下，都不使用):

This is the kind of situation where some experimentation is useful (this was conducted on 10g). Using the following query, we can tell that normal functions, using the same parameters (in this case, none) will be executed each time they are called:

select dbms_random.value() from all_tables

这是因为Oracle假设一个函数将不会一致地返回相同的值，除非您另有说明.我们可以通过使用deterministic关键字创建函数来实现:

This is because Oracle assumes that a function will not return the same value consistently unless you tell it otherwise. We can do that by creating a function using the deterministic keyword:

CREATE FUNCTION rand_det
   RETURN NUMBER
   DETERMINISTIC AS
BEGIN
   RETURN DBMS_RANDOM.VALUE ();
END;

在第一个查询中使用此函数而不是dbms_random可以告诉我们，尽管调用很多，但查询仅执行一次.但这仅阐明了select部分.如果我们在select和where子句中使用相同的确定性函数，该怎么办.我们可以使用以下查询进行测试:

Using this function instead of dbms_random in the first query tells us that the query is being executed only once, despite the many calls. But this only clarifies the select section. What if we use the same deterministic function in both a select and a where clause. We can test that using the following query:

SELECT rand_det
FROM   all_tables
WHERE  rand_det > .5;

您可能必须运行几次才能看到我们的证明，但是最终，您将看到小于0.5的值列表.这为我们提供了证据，即使确定性函数也被执行两次:对于每个出现在其中的部分，执行一次.或者，您可以按如下所示修改我们的确定性函数，然后运行后续查询，这将显示写入DBMS_OUTPUT.

You may have to run this several times to see our proof, but, eventually, you'll see a list of values less than 0.5. This provides us with evidence that even the deterministic function is being executed twice: once for each section it appears in. As an alternative, you can modify our deterministic function as follows, then run the subsequent query, which will reveal 2 lines written to DBMS_OUTPUT.

CREATE OR REPLACE FUNCTION rand_det
   RETURN NUMBER
   DETERMINISTIC AS
BEGIN
   DBMS_OUTPUT.put_line ('Called!');
   RETURN DBMS_RANDOM.VALUE ();
END;

SELECT rand_det
FROM   all_tables;

这篇关于pl/sql函数调用了多少次?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！