SQL stands for Structured Query Language. It is a powerful database computer language which was introduced in 1974. SQL is specifically designed to work with relational databases. All relational database systems such as Oracle, MySQL, MS SQL Server and others employ this standard database language. SQL is used to create, store, retrieve, change and delete data in relational database management systems. PL/SQL is the procedural extension language for SQL and provides many data types and useful programming structures. Users can create functions and procedures using database objects thanks to PL/SQL. Other important features include exception handling and support for Object-Oriented Programming. Prerequisites for learning PL/SQL include understanding SQL and the basics of programming.
Here we walk you through the PL/SQL IF THEN conditional construct. If you’re new to SQL, you can first take this introductory course on SQL.
Conditional Structure – IF THEN
The IF… THEN construct is a part of PL/SQL. It falls into the category of conditional statements. This means multiple actions can be taken based on defined or logic based decisions. There are three variants of this conditional construct. They are as follows
First variant of IF statement: Simple IF THEN
This is a single level conditional statement. This conditional structure is commonly used to manipulate the data in the tables from the command prompt.
The basic syntax of IF THEN statement is as follows
IF ( condition ) THEN statement END IF;
Note that the conditionals, and other statements, are written in all caps, in SQL. With the IF statement, we first check a condition. If it evaluates to true, the statement associated with THEN is executed. Otherwise, program control goes to the next statement of the program. Take a look at the following example.
Example 1: Program to check the value of a number
DECLARE num INTEGER(2) := 10; BEGIN IF ( num = 10 ) THEN DBMS_OUTPUT.PUT_LINE('The condition is true'); END IF; END; /
There are 2 sections of this program.
- DECLARE SECTION – In this section variables are declared.
- BEGIN and END block- The executable statements are placed within the BEGIN and END keywords. This is a mandatory section.
Note the / at the end is needed to execute the program from the command prompt. Learn how to write your own SQL programs with this course.
Example 2: Using IF THEN statement with EXISTS condition to delete records from a table
Here we work with an existing table called customer. One of its fields is called ID.
IF EXISTS(SELECT 1 FROM customer WHERE ID = 100 ) THEN DELETE FROM customer WHERE ID = 100 End If; End /
The EXISTS condition used here is a part of SQL. It accepts as parameter a subquery. Only if the subquery returns at least one row does this condition be true. In this example, the IF …THEN statement evaluates whether a record exists in the customer table. If the condition is true, then the program proceeds to delete the relevant records from the table. You can learn more about using SQL databases with this course.
Example 3: Using IF THEN statement with NOT EXISTS condition
Let’s see an exampling using IF THEN statement with NOT EXISTS condition to avoid inserting a duplicate record in given table
IF NOT EXISTS(SELECT 1 FROM customer WHERE ID = 100 ) THEN INSERT INTO customer (ID) VALUES (100) END IF; END; /
Here, the NOT EXISTS SQL condition is true only if the parameter subquery returns no records from the specified table. If the condition is true, one record is inserted into the customer table.
Second Variant of IF statement: IF THEN ELSE Statement
The syntax is as follows IF ( condition ) THEN statement; ELSE statement; END IF;
Here, the IF condition is evaluated first. It if it’s true then the associated statement is executed. If false, control will pass to the ELSE clause and the statement associated with ELSE is executed. Take a look at the given program.
Example 4: To determine the bonus component of a particular employee’s salary
Here, we are working with an existing table called employee which has fields employee_id and salary.
DECLARE sales NUMBER(8,2) := 15000; quota NUMBER(8,2) := 10000; bonus NUMBER(6,2); emp_id NUMBER(6) := 250; BEGIN IF sales > (quota + 200) THEN bonus := (sales - quota)/4; ELSE bonus := 100; END IF; UPDATE employee SET salary = salary + bonus WHERE employee_id = emp_id; END; /
In this program, the IF condition is evaluated first and if true, the bonus of the employee is calculated. However, if the condition is false, the program control goes to the ELSE clause where a fixed value is assigned to the bonus variable. Next the SQL UPDATE command is executed, which updates the salary field of the record containing the given employee_id in the employee table by adding the bonus value to the existing salary value.
Third variant of IF statement: IF…THEN…ELSIF…ENDIF
At times you may want to choose between different options. For this you can use the IF THEN ELSIF Statement. The syntax will be as follows
IF ( condition-1 ) THEN statement-1; ELSIF ( condition-2 ) THEN statement-2; ELSIF ( condition-3 ) THEN statement-3; ELSE statement; END IF;
Note that the keyword here is ELSIF and not ELSEIF or ELSE IF. There can be any number of ELSIF clauses. However the ELSE clause at the end is optional. Here the conditions are evaluated individually at a time in sequential order. If a particular condition is true, its associated statements are executed. After this, control goes to the program statement following the END IF. In the case where all the conditions are false, the statements associated with the ELSE clause are executed.
EXAMPLE 5: To determine employee bonus depending on different performance criteria
DECLARE sales NUMBER(8,2) := 50000; bonus NUMBER(6,2); emp_id NUMBER(6) := 120; BEGIN IF sales > 60000 THEN bonus := 2000; ELSIF sales > 35000 THEN bonus := 1000; ELSE bonus := 500; END IF; UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id; END; /
Now that we’ve walked you through so many examples, try figuring out this one yourself. Leave you explanation in the comments!
Learning any new programming language is always fun. If you’re pressed for time, you can take our crash course to learn SQL in just 6 days. It’s definitely worth it!