Refactoring
Example
Add Parameter. An existing method needs information that was not passed in before.
Consolidate Conditional Expression. Combine sequence of conditional tests into a single conditional expression and extract it.
Before
After
CREATE OR REPLACE FUNCTION GetAccountAverageBalance
( inAccountID IN NUMBER)
RETURN NUMBER;
AS
averageBalance := 0;
BEGIN
IF inAccountID > 10000 THEN
RETURN 0;
END IF;IF inAccountID = 123456 THEN
RETURN 0;
END IF;IF inAcountID = 987654 THEN
RETURN 0;
END IF;– Code to calculate the average balance
RETURN averageBalance;
END;
CREATE OR REPLACE FUNCTION GetAccountAverageBalance
( inAccountID IN NUMBER)
RETURN NUMBER;
AS
averageBalance := 0;
BEGIN
IF inAccountID < 10000 || inAccountID = 123456 || inAcountID = 987654 THEN
RETURN 0;
END IF;– Code to calculate the average balance
RETURN averageBalance;
END;
Decompose Conditional. Extract methods from the condition.
Before
After
CREATE OR REPLACE FUNCTION CalculateInterest
( inBalance IN NUMBER )
RETURN NUMBER;
AS
lowBalance NUMBER;
highBalance NUMBER;
lowInterestRate NUMBER;
highInterestRate NUMBER;BEGIN
lowBalance := GetLowBalance();
highBalance := GetHighBalance();
lowInterestRate := GetLowInterestRate();
highInterestRate := GetHighInterestRate();IF inBalance < lowBalance THEN
RETURN 0;
END IFIF inBalance >= lowBalance && inBalance <= highBalance THEN
RETURN inBalance * lowInterestRate;
ELSE
RETURN inBalance * highInterestRate;
END IF;
END;
CREATE OR REPLACE FUNCTION CalculateInterest
( inBalance IN NUMBER )
RETURN NUMBER;
AS
BEGINIF BalanceIsInsufficient( inBalance ) THEN
RETURN 0;
END IFIF IsLowInterestBalance( inBalance ) THEN
RETURN CalculateLowInterest( inBalance );
ELSE
RETURN CalculateHighInterest( inBalance );
END IF;
END;
Extract Method. Turn the code fragment into a method whose name explains the purpose of the method.
Before
After
CREATE OR REPLACE FUNCTION CalculateAccountInterest
( inAccountID IN NUMBER,
inStart IN DATE,
inEnd IN DATE )
RETURN NUMBER;
AS
medianBalance NUMBER;
startBalance NUMBER;
endBalance NUMBER;
interest := 0;
BEGIN
BEGIN
— Determine the starting balance
SELECT Balance INTO startBalance
FROM DailyEndBalance
WHERE AccountID = inAccountID && PostingDate = inStart;
EXCEPTION WHEN NO_DATA_FOUND THEN
startBalance := 0;– Determine the ending balance
SELECT Balance INTO endBalance
FROM DailyEndBalance
WHERE AccountID = inAccountID && PostingDate = inEnd;
EXCEPTION WHEN NO_DATA_FOUND THEN
endBalance := 0;
END;medianBalance := ( startBalance + endBalance ) / 2;
IF medianBalance < 0 THEN
medianBalance := 0;
END IF;
IF medianBalance >= 500 THEN
interest := medianBalance * 0.01;
END IF;RETURN interest;
END;
CREATE OR REPLACE FUNCTION CalculateAccountInterest
( inAccountID IN NUMBER,
inStart IN DATE,
inEnd IN DATE )
RETURN NUMBER;
AS
medianBalance NUMBER;
startBalance NUMBER;
endBalance NUMBER;
BEGIN
startBalance := GetDailyEndBalance ( inAccountID, inStart );
endBalance:= GetDailyEndBalance ( inAccountID, inEnd );
medianBalance := CalculateMedianBalance ( startBalance, endBalance );
RETURN CalculateInterest ( medianBalance );
END;
Introduce Variable. Put the result of the expression, or parts of the expression, in a temporary variable with a name that explains the purpose.
Before
After
CREATE OR REPLACE FUNCTION DetermineAccountStatus
( inAccountID IN NUMBER,
inStart IN DATE,
inEnd IN DATE )
RETURN VARCHAR;
AS
lastAccessedDate DATE;
BEGIN– Some code to calculate lastAccessDateIF ( inDate < lastAccessDate && outdate > lastAccessDate )
&& ( inAccountID > 10000 )
&& ( inAccountID != 123456 && inAcountID != 987654) THEN
— do somethingEND IF;
— do another thing
END;
CREATE OR REPLACE FUNCTION DetermineAccountStatus
( inAccountID IN NUMBER,
inStart IN DATE,
inEnd IN DATE )
RETURN VARCHAR;
AS
lastAccessedDate DATE;
isBetweenDates BOOLEAN;
isValidAccountID BOOLEAN;
isNotTestAccount BOOLEAN
BEGIN
— Some code to calculate lastAccessDate
isBetweenDates := inDate < lastAccessDate && outdate > lastAccessDate;
isValidAccountID := inAccountID > 100000;
isNotTestAccount := inAccountID != 123456 && inAcountID != 987654;
IF isBetweenDates && isValidAccountID && isNotTestAccount THEN
— do something
END IF;
— do another thing
END;
Parameterize Methods. Create one method that uses a parameter for the different values.
Remove Control Flag. Use remove or break instead of a variable acting as a control flag.
Before
After
DECLARE
controlFlag := 0;
anotherVariable := 0;
BEGIN
WHILE controlFlag = 0 LOOP
— Do something
IF anotherVariable > 20 THEN
controlFlag = 1;
ELSE
— Do something else
END IF;
END LOOP;
END;
DECLARE
anotherVariable := 0;
BEGIN
WHILE anotherVariable <= 20 LOOP
— Do something
— Do something else
END LOOP;
END;
Remove Middleman. Get the caller to call the method directly.
Before
After
CREATE OR REPLACE PROCEDURE AProcedure
parameter1 IN NUMBER;
“¦
parameterN IN VARCHAR;
AS
BEGIN
EXECUTE AnotherProcedure ( parameter1, “¦, parameterN );
END;
Remove Parameter. Remove a parameter no longer used by the method body.
Rename Method. Rename an existing method with a name that explains the purpose.
Reorder Parameters. Change the order of the parameters of a method.
Replace Literal With Table Lookup. Replace Code constants with values from database tables.
Before
After
CREATE OR REPLACE FUNCTION CalculateInterest
( inBalance IN NUMBER )
RETURN NUMBER;
AS
interest := 0;
BEGIN
IF inBalance >= 500 THEN
interest := medianBalance * 0.01;
END IF;
RETURN interest;
END;
CREATE OR REPLACE FUNCTION CalculateInterest
( inBalance IN NUMBER )
RETURN NUMBER;
AS
interest := 0;
minimumBalance NUMBER;
interestRate NUMBER;
BEGIN
minimumBalance := GetMinimumBalance();
interestRate := GetInterestRate();
IF inBalance >= minimumBalance THEN
interest := medianBalance * interestRate;
END IF;
RETURN interest;
END;
Replace Nested Expression With Guard Clauses. Remove nested if conditions with a series of separate IF statements.
Before
After
BEGIN
IF condition1 THEN
— do something 1
ELSE
IF condition2 THEN
— do something 2
ELSE
IF condition3 THEN
— do something 3
END IF;
END IF;
END IF;
END;
BEGIN
IF condition1 THEN
— do something 1
RETURN;
END IF;IF condition2 THEN
— do something 2
RETURN;
END IF;IF condition3 THEN
— do something 3
RETURN;
END IF;
END;
Replace Parameter With Specific Methods. Create a separate method for each value of the parameter.
Split Temporary Variable. Make a separate temporary variable for each assignment.
Before
After
DECLARE
aTemporaryVariable := 0;
farenheitTemperature := 0;
lengthInInches := 0;
BEGIN
— retrieve farenheitTemperature
aTemporaryVariable := (farenheitTemperature – 32 ) * 5 / 9;
— do something
— retrieve lengthInInches
aTemporaryVariable := lengthInInches * 2.54;
— do something
END;
DECLARE
celciusTemperature := 0;
farenheitTemperature := 0;
lengthInCentimeters := 0;
lengthInInches := 0;
BEGIN
— retrieve farenheitTemperature
celciusTemperature := (farenheitTemperature – 32 ) * 5 / 9;
— do something
— retrieve lengthInInches
lengthInCentimeters := lengthInInches * 2.54;
— do something
END;
Substitute Algorithm. Replace the body of the method with the new algorithm.