Proper IF Statements
Mike Ault
When using nested IF statements always place the IF logic that
will be exercised most often first. An example would be the IF exit,
usually programmers will place the various nested IF constructs that
actually perform code operations first, even if they are rarely
exercised, by placing the exit IF first the processing associated
with the other IF processing is avoided.
This is especially true of IF constructs contained within LOOP
structures. Using the PROFILER_CONTROL procedure as an example look
at Figure 1.
PROCEDURE profiler_control(
start_stop IN VARCHAR2, run_comm IN VARCHAR2, ret OUT BOOLEAN) AS
ret_code INTEGER;
BEGIN
ret_code:=dbms_profiler.internal_version_check;
IF ret_code !=0 THEN
ret:=FALSE;
ELSIF start_stop NOT IN ('START','STOP') THEN
ret:=FALSE;
ELSIF start_stop = 'START' THEN
ret_code:=DBMS_PROFILER.START_PROFILER(run_comment1=>run_comm);
IF ret_code=0 THEN
ret:=TRUE;
ELSE
ret:=FALSE;
END IF;
ELSIF start_stop = 'STOP' THEN
ret_code:=DBMS_PROFILER.FLUSH_DATA;
ret_code:=DBMS_PROFILER.STOP_PROFILER;
IF ret_code=0 THEN
ret:=TRUE;
ELSE
ret:=FALSE;
END IF;
END IF;
END profiler_control;
|
Figure 1: The PROFILER_CONTROL Package
Notice that most of time the procedure will exit without
generating an action, however, the exit is at the bottom of the IF-ELSIF
stack. By rearranging the code and using a BOOLEAN as the go-no go
we get a simplified, more efficient program as shown in Figure 2.
PROCEDURE profiler_control(
start_stop IN BOOLEAN, run_comm IN VARCHAR2, ret OUT BOOLEAN) AS
ret_code INTEGER;
BEGIN
IF start_stop IS NULL THEN
Ret:=TRUE;
ELSIF start_stop THEN
ret_code:=dbms_profiler.internal_version_check;
IF ret_code !=0 THEN
ret:=FALSE;
END IF;
ELSIF start_stop AND ret_code=0 THEN
ret_code:=DBMS_PROFILER.START_PROFILER(run_comment1=>run_comm);
IF ret_code=0 THEN
ret:=TRUE;
ELSE
ret:=FALSE;
END IF;
ELSIF NOT start_stop THEN
ret_code:=DBMS_PROFILER.FLUSH_DATA;
ret_code:=DBMS_PROFILER.STOP_PROFILER;
IF ret_code=0 THEN
ret:=TRUE;
ELSE
ret:=FALSE;
END IF;
END IF;
END profiler_control;
|
Figure 2: New and Improved PROFILER_CONTROL
We make the code more efficient and reduce the amount of CPU
cycles for all other packages that call PROFILER_CONTROL. If the IF
construct involved in contained within a LOOP structure the savings
can be substantial.
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
