Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
This article contins information about PL/SQL Conditional Statements such as IF,IF ELSE,ELSEIF,CASE.
Conditional statements allow to execute commands according to a certain condition.
The condition in conditional statements is created by using the operators in Oracle PL / SQL.
What are the PL/SQL Conditional Statements?
IF Statement in Oracle PL/SQL
If the specified condition is true, commands run in the block.
1 2 3 | IF Your_Condition THEN -- Commands to be execute if condition is true END IF; |
Sample IF Condition Usage is as follows;
1 2 3 4 5 6 | SET SERVEROUTPUT ON; BEGIN IF TRUE THEN DBMS_OUTPUT.put_line('Hello, I am Yusuf SEZER'); END IF; END; |
IF ELSE Statement in Oracle PL/SQL
ELSE condition contains commands to run if the condition set by IF is not true.
1 2 3 4 5 | IF Your_Condition THEN -- Commands to be executed if condition is true ELSE -- Commands to be executed if condition is false END IF; |
Sample IF ELSE Condition Usage is as follows;
1 2 3 4 5 6 7 8 | SET SERVEROUTPUT ON; BEGIN IF FALSE THEN DBMS_OUTPUT.put_line('Hello, I am Yusuf SEZER'); ELSE DBMS_OUTPUT.put_line('There may be other commands in this section.'); END IF; END; |
ELSIF Statement in Oracle PL/SQL
Used to specify multiple conditions.
1 2 3 4 5 6 7 8 9 10 11 | IF Your_Condition1 THEN -- Commands-1 ELSIF Your_Condition2 THEN -- Comamnds-2 ELSIF Your_Condition3 THEN -- Comamnds-3 ELSIF Your_Condition4 THEN -- Comamnds-4 ELSE -- Comamnds-5 END IF; |
Sample ELSIF Condition Usage is as follows;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 7; BEGIN IF v_number = 0 THEN DBMS_OUTPUT.put_line('zero'); ELSIF v_number = 1 THEN DBMS_OUTPUT.put_line('one'); ELSIF v_number = 2 THEN DBMS_OUTPUT.put_line('two'); ELSIF v_number = 3 THEN DBMS_OUTPUT.put_line('three'); ELSIF v_number = 4 THEN DBMS_OUTPUT.put_line('four'); ELSE DBMS_OUTPUT.put_line('unknown number.'); END IF; END; |
CASE Statement in Oracle PL/SQL
IF AND ELSIF cause multiple operators to be used for a value.
The CASE keyword is used to check the result of a single value.
1 2 3 4 5 6 7 8 | CASE v_variable WHEN 'value-1' THEN -- commands-1 WHEN 'value-2' THEN -- commands-2 ELSE -- commands-3 END CASE; |
Sample CASE Condition Usage is as follows;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 7; BEGIN CASE v_number WHEN 0 THEN DBMS_OUTPUT.put_line('zero'); 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('unknown number.'); END CASE; END; |
You see that less operators are used compared to the previous ELSIF example.
The CASE condition can also be used with operators.
1 2 3 4 5 6 7 8 9 10 | CASE WHEN Your_Condition_1 THEN -- commands-1 WHEN Your_Condition_2 THEN -- commands-2 WHEN Your_Condition_3 THEN -- commands-3 ELSE -- commands-4 END CASE; |
Sample usage is as follows;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SET SERVEROUTPUT ON; DECLARE v_number PLS_INTEGER := 3; BEGIN CASE WHEN v_number = 0 THEN DBMS_OUTPUT.put_line('zero'); WHEN v_number = 1 THEN DBMS_OUTPUT.put_line('one'); WHEN v_number = 2 THEN DBMS_OUTPUT.put_line('two'); WHEN v_number = 3 THEN DBMS_OUTPUT.put_line('three'); WHEN v_number = 4 THEN DBMS_OUTPUT.put_line('four'); ELSE DBMS_OUTPUT.put_line('unknown number.'); END CASE; END; |
Conditional expressions can also be used in loops.
You can find more detailed information about below topics in the below link.
You will find below topics in this article.
- What is PL/SQL
- Oracle PL/SQL Data Types and Variables and Literals
- Oracle PL/SQL Operators
- Oracle PL/SQL Conditional Statements
- Oracle PL/SQL Loops
- Oracle PL/SQL Procedures and Procedure Parameters
- Oracle PL/SQL Functions
- Oracle PL/SQL Cursor
- Oracle PL/SQL Records
- Oracle PL/SQL Exception
- Oracle PL/SQL Trigger
- Oracle PL/SQL Packages
- Oracle PL/SQL Collections
You can find more information about Conditinal Statement at docs.oracle.com
