Skip to Main Content
Information Technology Services
oneColumn

Syntax Standards

Branching and Labels

If you have programmed in Assembler or in an older version of FORTRAN, you are aware that the only form of flow control was using either a Jump or Goto. In early FORTRAN every line was required to be numbered. These numbers were used as labels for branching. Early BASIC also used this numbering scheme.

Procedural languages introduced labels that replaced line numbers. There is no need to number each line anymore. PL/SQL's Goto label causes execution to continue from statement where the label is located. Labels are variables with a '<<' prefix and a '>>' suffix. For example <<start>> is a label.

Do not use gotos

Gotos make for unreadable, and high maintenance code. There is only one instance where a Goto should be used in PL/SQL. That is for exiting a procedure. In addition to this, if you must use Gotos, then make them forward only. Typical use for a forward only Goto is to jump to an exit point so that any clean up code can be executed before exiting.

Conditional Statements

Conditional statements either skip or execute statements based on a conditional expression, which must evaluate to True or False. There are three variations of syntax for this statement:

IF.. THEN

IF.. THEN.. ELSE

IF.. THEN.. ELSIF

Keywords on Separate Lines

Place the keywords (IF, THEN, ELSE, ELSIF, ENDIF) on separate lines. This format is preferred for two reasons. Firstly, it places all the keywords in the same column making it easier to eyeball the logical structure. Secondly it creates white space around the keywords. Indent statements from the keywords by the customary 3 spaces.

Avoid Unnecessary Nested IFs

The following statements are equivalent. The flat structure expresses the logic more clearly and with less code.

Keywords on Separate Lines
Nested Flat
IF <condition1> IF <condition1>
THEN THEN
...
ELSE ELSIF <Condition2>
IF <Condition2> THEN
THEN
ELSIF <Condition3>
ELSE THEN
IF <Condition3>
THEN ELSIF <Condition4>
THEN
ELSE
IF <Condition4> END IF;
THEN
END IF;
END IF;
END IF;
END IF;

 

Generally, you will want to use an ELSIF statement instead of nested IFs. A good candidate for a nested IF, however, arises when one condition is much more resource-intensive than the other. Suppose condition A consumes .05 CPU seconds and condition B consumes 10 minutes. You don't want to execute B unless A is TRUE -- and you don't want to rely on the compiler to decide which clause is evaluated first.

IF condition A

THEN

IF condition B

THEN

END IF;

END IF;

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:

WHILEcondition

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_value

">LOOP

"> statements ">END LOOP;

">FOR lrec_my_record IN lcur_my_cursor

Cursor For Loop

">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 legitimate "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;