DISQUS

Robert Vollman · 3 years ago

Interesting. But is this behaviour dependable? And, if so, will it remain like this in future versions? And, even if it is, is that necessarily the best coding practise? Especially since you can just do this:

IF (2=1) THEN
IF (IS_GREATER_FUNC(1,2)) THEN
DBMS_OUTPUT.PUT_LINE('TRUE');
ELSE DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
ELSE DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;

That way, your intention (to only check the second case if the first case is true) is clear, and dependable. Sure, it's more lines of code, but easier to maintain.

Rob

Eddie Awad · 3 years ago

Good and valid points Rob. Thank you. Nevertheless, the short-circuit evaluation notion in PL/SQL is rather an interesting fact (as of 10gR2).

Gaza · 2 years ago

If (and only if) this behaviour is a standard
in PLSQL, then one could argue that the
more concise code that can be written
using this IS more maintainable. More
verbose code, especially if it requires the
setting up and initialisation of more
unnecessary variables, can reduce the maintainability of that code!

iG · 2 years ago

Its NOT ALWAYS guaranted the in

select from my_table where cond1 and cond2;

cond1 be first evaluated. For a query I was doing, a must use:

select /*+ no_cpu_costing */ from my_table where cond1 and cond2;

Look at

http://www.dba-oracle.com/oracle_tips_optimizer_cost_model.htm

Steven Meyer · 1 year ago

Quote iG "It is NOT ALWAYS guaranted..." then iG goes on to show an example in SQL.
But remember that the order of evaluation in SQL is not as written.
<pre>SQL> CREATE OR REPLACE FUNCTION this_is_y(x NUMBER) RETURN VARCHAR2 IS
2 BEGIN
3 dbms_output.put_line('Inside function this_is_Y(' || x || ')');
4 RETURN 'Y';
5 END this_is_Y;
6 /

Function created.

SQL> CREATE OR REPLACE FUNCTION this_is_n(x NUMBER) RETURN VARCHAR2 IS
2 BEGIN
3 dbms_output.put_line('Inside function this_is_N(' || x || ')');
4 RETURN 'N';
5 END this_is_n;
6 /

Function created.
</pre>
<pre>
SQL> SELECT *
2 FROM (SELECT object_id
3 FROM user_objects
4 WHERE 9 > rownum)
5 WHERE this_is_y(object_id) = 'Y'
6 AND this_is_n(object_id) = 'Y';

no rows selected

SQL>
</pre>
And the output is:
<pre>
Inside function this_is_N(174702)
Inside function this_is_N(592838)
Inside function this_is_N(592839)
Inside function this_is_N(592840)
Inside function this_is_N(592841)
Inside function this_is_N(592842)
Inside function this_is_N(592843)
Inside function this_is_N(592844)
</pre>