StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The answer for Oracle is it depends. The function will be called for every row selected UNLESS the Function is marked 'Deterministic' in which case it will only be called once.</p> <pre><code>CREATE OR REPLACE PACKAGE TestCallCount AS FUNCTION StringLen(SrcStr VARCHAR) RETURN INTEGER; FUNCTION StringLen2(SrcStr VARCHAR) RETURN INTEGER DETERMINISTIC; FUNCTION GetCallCount RETURN INTEGER; FUNCTION GetCallCount2 RETURN INTEGER; END TestCallCount; CREATE OR REPLACE PACKAGE BODY TestCallCount AS TotalFunctionCalls INTEGER := 0; TotalFunctionCalls2 INTEGER := 0; FUNCTION StringLen(SrcStr VARCHAR) RETURN INTEGER AS BEGIN TotalFunctionCalls := TotalFunctionCalls + 1; RETURN Length(SrcStr); END; FUNCTION GetCallCount RETURN INTEGER AS BEGIN RETURN TotalFunctionCalls; END; FUNCTION StringLen2(SrcStr VARCHAR) RETURN INTEGER DETERMINISTIC AS BEGIN TotalFunctionCalls2 := TotalFunctionCalls2 + 1; RETURN Length(SrcStr); END; FUNCTION GetCallCount2 RETURN INTEGER AS BEGIN RETURN TotalFunctionCalls2; END; END TestCallCount; SELECT a,TestCallCount.StringLen('foo') FROM( SELECT 0 as a FROM dual UNION SELECT 1 as a FROM dual UNION SELECT 2 as a FROM dual ); SELECT TestCallCount.GetCallCount() AS TotalFunctionCalls FROM dual; </code></pre> <p>Output:</p> <pre><code>A TESTCALLCOUNT.STRINGLEN('FOO') ---------------------- ------------------------------ 0 3 1 3 2 3 3 rows selected TOTALFUNCTIONCALLS ---------------------- 3 1 rows selected </code></pre> <p>So the StringLen() function was called three times in the first case. Now when executing with StringLen2() which is denoted deterministic:</p> <pre><code>SELECT a,TestCallCount.StringLen2('foo') from( select 0 as a from dual union select 1 as a from dual union select 2 as a from dual ); SELECT TestCallCount.GetCallCount2() AS TotalFunctionCalls FROM dual; </code></pre> <p>Results:</p> <pre><code>A TESTCALLCOUNT.STRINGLEN2('FOO') ---------------------- ------------------------------- 0 3 1 3 2 3 3 rows selected TOTALFUNCTIONCALLS ---------------------- 1 1 rows selected </code></pre> <p>So the StringLen2() function was only called once since it was marked deterministic.</p> <p>For a function not marked deterministic, you can get around this by modifying your query as such:</p> <pre><code>select a, b, c, hashed from my_table cross join ( select dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3) as hashed from dual ); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload