by Ursego » 21 Feb 2013, 10:48
Don't populate the variable, used for looping control, twice (before and inside the loop body). Populate it only once, inside the loop.
You can ask - why? We see that kind of duplication in most programming books! The answer is simple: we have to avoid any sort of code duplication, so there is no reason to write twice something which can be written only once to achieve the same goal.
*** BAD code: ***
PB:
- Code: Select all
ll_row = dw_cust.GetNextModified(ll_row, Primary!)
do while ll_row > 0
[do something with the modified row]
ll_row = dw_cust.GetNextModified(ll_row, Primary!) -- deja vu!
loop
PL/SQL:
- Code: Select all
FETCH emp_cursor INTO v_row;
WHILE emp_cursor%FOUND LOOP
[do something]
FETCH emp_cursor INTO v_row; -- deja vu!
END LOOP;
An eternal loop with 'break' ('exit when' in PL/SQL) is a very elegant solution in such a situation:
*** GOOD code: ***
PB:
- Code: Select all
do while true
ll_row = dw_cust.GetNextModified(ll_row, Primary!)
if ll_row < 1 then exit
[do something with the modified row]
loop
PL/SQL:
- Code: Select all
LOOP
FETCH emp_cursor INTO v_row;
EXIT WHEN emp_cursor%NOTFOUND; -- or "IF emp_cursor%NOTFOUND THEN BREAK;"
[do something]
END LOOP;
From the book "PL/SQL Best Practices":
Use a simple loop to avoid redundant code required by a WHILE loop.
Generally, you should use a simple loop if you always want the body of the loop to execute at least once. You use a WHILE loop if you want to check before executing the body the first time. Since the WHILE loop performs its check "up front," the variables in the boundary expression must be initialized. The code to initialize is often the same code needed to move to the next iteration in the WHILE loop. This redundancy creates a challenge in both debugging and maintaining the code: how do you remember to look at and update both?
If you find yourself writing and running the same code before the WHILE loop and at end of the WHILE loop body, consider switching to a simple loop.
Benefits
You avoid redundant code, always bad news in a program, since it increases maintenance costs and the chance of introducing bugs into your code.