0

Given v_ssn_ind INTEGER := IF TRIM(p_ssn) IS NULL THEN 0 ELSE 1 END IF;

I know I can do this: IF v_ssn_ind=1 THEN…

But can I do short-circuit evaluation, ie: IF v_ssn_ind THEN… ?

Mathieu Pagé
  • 10,764
  • 13
  • 48
  • 71
Jeromy French
  • 11,812
  • 19
  • 76
  • 129
  • And, for bonus points, is it any faster or slower? – Jeromy French Aug 23 '13 at 17:23
  • 2
    the expression `IF v_ssn_ind THEN` doesn't make sense (and won't compile if I'm not mistaken) unless `v_ssn_ind` is declared as a boolean. –  Aug 23 '13 at 17:28

2 Answers2

6

First off, what you are talking about does not appear to have anything to do with short-circuit evaluation. Short-circuit evaluation would be when code like

IF( quick_condition AND slow_condition )
THEN
  <<do something>>
END IF;

evaluates the second slow condition if and only if the initial quick condition evaluates to TRUE.

Second, your assignment of a value to v_ssn_ind is not syntactically valid.

Third, no, you cannot say

IF <<integer variable>>
THEN

because that would not make sense. What value would evaluate to TRUE and what value would evaluate to FALSE? If 0 is FALSE and 1 is TRUE, for example, what would 17 translate to?

If you are declaring some sort of indicator variable, it would generally make sense to use a BOOLEAN data type rather than an integer. If you use a boolean, then you can do 

IF <<boolean variable>>
THEN

because that eliminates the ambiguity. This won't be any faster than adding the = TRUE to your IF condition however.

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
0

I discourage you from putting any logic into a declaration block, in my opinion it makes code less clear.

Instead IF you can use CASE statement.

...
    v_ssn_ind INTEGER;
BEGIN
    v_ssn_ind := CASE TRIM(p_ssn) IS NULL WHEN TRUE THEN 0 ELSE 1 END;
...

But personally I would chose BOOLEAN type for v_ssn_ind

...
    v_ssn_ind BOOLEAN;
BEGIN
    v_ssn_ind := TRIM(p_ssn) IS NOT NULL;
...
Sebastian Cichosz
  • 889
  • 13
  • 22
  • Personally, I would expect a variable named "_ind" to refer to an index of some sort. Perhaps renaming the variable to something like `v_ssn_is_not_null` would make more sense. – Jeffrey Kemp Aug 26 '13 at 07:18
  • You are right, it will bring more meaning to the variable's name. – Sebastian Cichosz Aug 26 '13 at 09:07
  • @JeffreyKemp: `_ind` is short for indicator; at my first job it was the suggested appendage for boolean variables. – Jeromy French Aug 27 '13 at 13:53
  • Fair enough. As I said, it's a personal thing - "indicator" is not the first thing that pops into my head :) Ultimately, consistency would be the most important factor, rather than the actual suffix or prefix used. – Jeffrey Kemp Aug 28 '13 at 00:47