Home arrow Oracle arrow Page 3 - Oracle PL/SQL Subprogram Conditionals and Variables

PL/SQL Constructs - Oracle

This article is excerpted from chapter 36 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

  1. Oracle PL/SQL Subprogram Conditionals and Variables
  2. Declaring and Setting Variables in Oracle Subprograms
  3. PL/SQL Constructs
By: Apress Publishing
Rating: starstarstarstarstar / 1
March 22, 2011

print this article



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]

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;
bonus := 100;
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.

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: