Thursday, March 22, 2012

Use AND, OR in IF statement if they are related.

Kinda simple isn't it? However, in my working experience I saw people using IF for no apparent reason. Example of a simple and easy to understand IF statement:

If start_date = 31/12/9999 then start_date := sysdate; end if;
If end_date = 31/12/9999 then end_date := sysdate; end if;

Very straight forward. Set the default date to sysdate if value is found as 31/12/9999 for start_date and end_date.

Then I saw people turn the above if into something below

if start_date = 31/12/9999 and end_date = 31/12/9999 then

start_date := sysdate;
end_date := start_date;

end if;

There are nothing wrong with the IF statement until I asked the question:

Are start_date and end_date are related? Why do they have AND relationship?

Then the developer provided a lot of explanation where all boils down to less keystroke (are they?)

In short, the AND or OR in IF statement always represent a relation that can't be ignore by asking question like below:

Are they related (changing one would affect the other)? Why do they have AND/OR relationship?

Probably there is no need of AND or OR if the above question can't be answered. Furthermore, the downside is just more keystrokes for a complete IF statement.

P.S. Changing the AND to OR in the IF statement will cause more problems.

Monday, March 5, 2012

RIP "select value into variable from dual" in PL/SQL world.

I got an email on the other day that "select value into variable from dual" will be fully retired from pl/sql script. Well, it's understandable how it get there it the first place.

Initially, SQL script has created and run using the sql plus environment. In order to create and assign a value to variable, "execute select value into variable from dual" is commonly used. There's nothing wrong with it and it always get the job done. Until a new system came along and developer is able to use procedures and functions. However, old habit is hard to die when the developers migrated the old script to new system, they brought along the "select value into variable from dual" with them.

Well, I can't blame them as there are thousand of scripts to be migrated and time wasn't on their side. Now, with almost everything gone into the system, they finally realized that "select value into variable from dual" wasn't efficient and DBA wasn't too happy when they saw so many "select value into variable from dual" pop up in their screen. In the end, developers have to clean up the mess one by one and changing the script using "variable := value;" instead.

RIP "select value into variable from dual". We gonna missed you. :P