PL/SQL Constructs - Oracle

Single-statement subprograms are quite useful, but the real power lies in a subprogram’s ability to encapsulate and execute several statements, including conditional logic and iteration. In the following sections, we touch on the most important constructs.

Conditionals

Basing task execution on run-time information (e.g., from user input) is key for wielding tight control over the results of the task execution. Subprogram syntax offers two well-known constructs for performing conditional evaluation: the
IF-THEN-[ELSIF][-ELSE]-END IFstatement and theCASEstatement. Both are introduced in this section.

IF-THEN-[ELSIF][-ELSE]-END IF

The IF-THEN-[ELSIF][-ELSE]-END IF statement is one of the most common means for evaluating conditional statements. In fact, even if you’re a novice programmer, you’ve likely already used it on numerous occasions. Therefore, this introduction should be quite familiar. The prototype looks like this:

IF condition THEN statement_list
   [ELSIF condition THEN statement_list] . . .
   [ELSE statement_list]
END IF

Caution  The keyword for specifying alternate condition testing in anIF . . . END IFstatement isELSIF. In many other programming languages it might beELSE IForELSEIF, but in PL/SQL it’s one word:ELSIF.

For example, let’s say you want to adjust employee’s bonuses in proportion to their sales. Your conditional logic would look somewhat like the following:

IF sales > 50000 THEN
   bonus := 1500;
ELSIF sales > 35000 THEN
   bonus := 500;
ELSE
   bonus := 100;
END IF;
UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;

For employees who are not in the sales department (sales = 0) or whose sales are $35,000 or less, the conditional logic assigns a bonus of $100.