Second Attemp...

Occasionally, as I come across interesting Oracle Database related issues, I’ll post my thoughts and opinions and who knows what else and perhaps, just maybe, others may find it interesting or useful as well.

Let the fun begin …


One needs to understand how Oracle works in order to use it safely.

----------------------------------------------------------------------
Jan-2017.

This is my second blogging attempt, I originally created the Oracle Blog to talk about Oracle, my learning and findings in Oracle world.

But as we move on, I started working on Big Data Analytics, DevOps and Cloud Computing. So here is second attempt of blogging on Cloud Computing (AWS, Google Cloud), Big Data technologies , concepts and DevOps tools.

Tuesday, July 20, 2010

IF statements

Avoid Unnecessary Nested IFs

The following statements are equivalent. The flat structure expresses the logic more clearly and with less code.
Nested:

IF
THEN
...
ELSE
IF
THEN
...
ELSE
IF
THEN
...
ELSE
IF
THEN
...
END IF;
END IF;
END IF;
END IF;
Flat:

IF
THEN
...
ELSIF
THEN
...
ELSIF
THEN
...
ELSIF
THEN
...
END IF;

-----
Generally, you will want to use an ELSIF statement instead of nested IFs.
A good candidate for a nested IF, however, arises when one condition is much more resource-intensive than the other. Suppose condition A consumes .05 CPU seconds and condition B consumes 10 minutes. You don’t want to execute B unless A is TRUE -- and you don’t want to rely on the compiler to decide which clause is evaluated first.

IF condition A
THEN
IF condition B
THEN
...
END IF;
END IF;

-----
The implication of ELSIF clauses is that if one condition is fulfilled, all others would fail -- they are mutually exclusive. The following IF statement is a classic misuse of ELSIF clauses. It might not cause any errors, but that would just be a matter of luck. In many cases, the issue of exclusivity is less obviously determined.

IF sal BETWEEN 0 AND 10000
THEN
...
ELSIF sal BETWEEN 10000 AND 20000
THEN
...
ELSIF sal BETWEEN 20000 AND 30000
THEN
...
END IF;

-----
You can code real Boolean variables and literals (TRUE, FALSE and NULL values) in PL/SQL.
Boolean variables and functions allow you to greatly improve readability of programs. You can hide complex expressions behind a name, which describes the expression.

Compare the two IF statements below.

IF total_sal BETWEEN 10000 AND 50000 AND
emp_status (emp_rec.empno) = 'N' AND
(MONTHS_BETWEEN
(emp_rec.hiredate, SYSDATE) > 10)
THEN
give_raise (emp_rec.empno);
END IF;
The second IF statement:

IF eligible_for_raise (emp_rec.empno)
THEN
give_raise (emp_rec.empno);
END IF;

-----
Avoid IF With Booleans
Sometimes you will code or come across conditional statements which, while valid, are unnecessary and cumbersome. Replace this IF statement:

IF hiredate < SYSDATE
THEN
date_in_past := TRUE;
ELSE
date_in_past := FALSE;
END IF;
With this:

date_in_past :=
hiredate < SYSDATE;
You can assign a Boolean expression directly to a Boolean variable

-----

Employ indentation rigorously with IF statements to show logical levels. Each IF, THEN, ELSE, ELSIF, and END IF should align within each level. For example:
Nested IFs:

IF
THEN
...
ELSE
IF
THEN
...
ELSE
END IF;
END IF;
IF with multiple ELSIFs:

IF
THEN
...
ELSIF
THEN
...
ELSIF
THEN
...
END IF;

No comments:

Post a Comment

Amazon AWS Certified !!!

Today I passed the AWS Certified Developer - Associate exam with 92%  Wow, I have been working on AWS since last one year and mainly usin...