Ensure Conditions in ELSIF Clauses are Exclusive
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 lv_sal BETWEEN 0 AND 10000
THEN
....
ELSIF lv_sal BETWEEN 10000 AND 20000
THEN
.....
ELSIF lv_sal BETWEEN 20000 AND 30000
THEN
.....
END IF;
Use Boolean Elements to Improve Readability
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 lv_total_sal BETWEEN 10000 AND 50000 AND
lv_emp_status (emp_rec.empno) = 'N' AND
(MONTHS_BETWEEN
(emp_rec.hiredate, SYSDATE) > 10)
THEN
give_raise (emp_rec.empno);
END IF;
IF eligible_for_raise (emp_rec.empno)
THEN
give_raise (emp_rec.empno);
END IF;
Avoid IF with Boolean Variables
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
Case Statements
An even better way to handle conditional logic is with a case statement. CASE statements are supported in PL/SQL in Oracle 9i and higher. There are two kinds of CASE statements:
- A simple case statement that allows you to choose to execute sequences of PL/SQL statements based on the results of a single expression – often referred to as a test case statement.
- A searched case statement that evaluates a list of Boolean expressions and executes a sequence of PL/SQL statements when the first expression that evaluated to TRUE is encountered.
In general, CASE statements, when supported, are a preferable alternative to IF..THEN..ELSIF logic.
Examples:
Test Case Statement:
DECLARE
lv_TestVar NUMBER := 1;
BEGIN
CASE lv_TestVar
WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('One!');
WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('Two!');
WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('Three!')
WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('Four!');
ELSE DBMS_OUTPUT.PUT_LINE (‘No Match!’);
END CASE;
END
Search Case Statement:
DECLARE
lv_Test1 NUMBER := 2;
lv_Test2 VARCHAR2(20) := 'Goodbye';
BEGIN
CASE
WHEN lv_Test1 = 1 THEN
DBMS_OUTPUT.PUT_LINE('One!');
DBMS_OUTPUT.PUT_LINE('Another one!');
WHEN lv_Test1 > 1 THEN
DBMS_OUTPUT.PUT_LINE('> 1!');
DBMS_OUTPUT.PUT_LINE('Still > 1!');
WHEN lv_Test2 = 'Goodbye' THEN
DBMS_OUTPUT.PUT_LINE('Goodbye!');
DBMS_OUTPUT.PUT_LINE('Adios!');
ELSE
DBMS_OUTPUT.PUT_LINE('No match');
END CASE;
END
Although not required for compilation, it’s generally advisable to include an else clause in case statements. Oracle will raise an exception if a condition is found which is not handled.
Repetition
Programming involves performing a task repeatedly, until a particular condition is met. Looping constructs of a language support this need. There are three basic types of looping. 0 or more loops, 1 or more loops, and Loop for a specified number of times. PL/SQL supports all three types.
0 or More Times Loop
These are loops where the testing is done at the beginning of a loop. If the condition evaluates to true then the statements with in the loop are executed. Otherwise the statements are skipped and execution is transferred to the statements following the loop.
WHILE LOOP ... END LOOP
The syntax for a WHILE...LOOP is as follows:
WHILE condition
LOOP
Statements
END LOOP
Again keep all the keywords in the same column, while indenting the keyword columns by 3 spaces.
1 or more times loop
These are loops where testing is done as part of the executable portion of the loop, or at the bottom of the loop. PL/SQL only provides a basic type of a loop, which is an infinite loop. The statements with in the loop are executed an infinite number of times.
LOOP...END LOOP
LOOP
statements;
END LOOP;
This loop can be turned into a 1 or more times loop by using the EXIT WHEN clause.
LOOP
Statements;
EXIT WHEN Condition;
END LOOP;
Loop for specified number of times
PL/SQL provides for two types of looping constructs. They are the NUMERIC FOR loop and the CURSOR FOR loop.
Numeric For Loop
FOR lv_for_index IN low_value .. high_valueLOOP
statements
END LOOP;
Cursor For Loop
FOR lrec_my_record IN lcur_my_cursor
LOOP
statements
END LOOP;
Again notice that the indentation is consistent at 3 spaces.
Never Declare the FOR Loop index
FOR lv_year_idx IN 1 .. 20
LOOP
calc_profits (lv_year_idx);
END LOOP;
Do not declare the loop index variable (lv_year_idx in the example above). PL/SQL does that for you automatically. For both numeric and cursor FOR loops, the identifier after the FOR keyword is automatically declared by PL/SQL with type BINARY_INTEGER or a record to match the cursor. If you declare a variable with same name as loop index, it is a different variable.
You could refer to the FOR loop index outside the loop and your code will compile, but it will not be doing what you think or what you want.
This code will compile, but it will not work as intended. This kind of code is very hard to understand and debug.
DECLARE
CURSOR lcur_emp IS
SELECT empno, ename FROM emp;
lrec_emp emp_cur%ROWTYPE;
BEGIN
FOR lrec_emp IN lcur_emp
LOOP
display_emp (lrec_emp.ename);
END LOOP;
IF lrec_emp.ename = 'SMITH'
THEN
give_raise
(lrec_emp.empno, 1000000);
END IF;
END;
Suppose you need the value of the loop index (lv_year_idx in the following example) for debugging:
DECLARE lv_year_idx INTEGER := NULL;
BEGIN
FOR lv_year_idx IN 1 .. 20
LOOP
calc_pnl (lv_year_idx);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE
('Error in year ' ||
TO_CHAR (lv_year_idx));
END;
In this case use a local variable and copy the loop index to local variable:
DECLARE lv_my_count INTEGER := NULL;
BEGIN
FOR lv_year_idx IN 1 .. 20
LOOP
my_count := lv_year_idx;
calc_pnl (lv_year_idx);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE
('Error in year ' ||
TO_CHAR(lv_my_count));
END;
Avoid Unstructured Exits from Loops
- Do not EXIT or RETURN out of a FOR loop.
- A FOR loop should only be used when you want to execute the body a fixed number of times.
- Stay for the duration or use a different loop construct.
- Do not use the EXIT syntax in a WHILE loop.
- The loop should be terminated only when the condition in the boundary evaluates to FALSE.
Note: if an exception is raised and the loop stops, that is a legitamate "early termination".
Do Not Use PL/SQL Where You Can Use a SQL Statement Instead
The SQL statement will often be much faster. You should replace PL/SQL loops with single SQL statements when possible.
Slower PL/SQL Version
FOR year_count IN 1 .. 20
LOOP
INSERT INTO v1table1
SELECT * FROM v1table2
WHERE yr_nu = year_count;
END LOOP;
Faster, Simpler SQL Version
INSERT INTO v1table1
SELECT * FROM v1table2
WHERE yr_nu BETWEEN 1 AND 20;
