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.
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.
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.