java2db

IF THEN ELSE and CASE in PL/SQL



IF THEN ELSE and CASE  are called conditional control statements.These statements are necessary to control the PL/SQL program, based on specific conditions.

 

Example table : Create a table with the below sql statements and insert few records .

/*create table*/
CREATE TABLE EMP_SAL_DETAILS(E_NUM NUMBER, E_NAME VARCHAR2(30),GROSS_SAL FLOAT);
/*insert few records with the below statements*/
INSERT INTO EMP_SAL_DETAILS VALUES(1,’Rose’,16000);
INSERT INTO EMP_SAL_DETAILS VALUES(2,’Mark’,17000);
INSERT INTO EMP_SAL_DETAILS VALUES(3,’Ali’,15000);
E_NUM
E_NAME
GROSS_SAL
1 Rose 16000
2 Mark 17000
3 Ali 15000

Now ,the table is ready to test our examples.

 

IF THEN ELSE and CASE in PL/SQL IF THEN Statement :

This is the basic statement to check whether the given condition is true or false.It ends with END IF.If the given condition is true , the control will passes to execute the block of statements.

Syntax:

IF <condition> THEN
<Statements to execute>
END IF;

Example: The below example will insert a new record in EMP_SAL_DETAILS table .

 

IF THEN ELSE and CASE in PL/SQL IF THEN ELSE Statement :

If the given condition is true then the control will passes to the next statements of the IF block otherwise,enters to the ELSE block.Even though the condition is false , the alternative block of code will execute.

Syntax:

IF <condition> THEN
<Statements to execute>
ELSE
<Statements to execute>
END IF;

IF THEN ELSE Example: 

The above code will update the record  with new salary , because the record already exists.So the ELSE statement will execute.

 

IF THEN ELSE and CASE in PL/SQL IF THEN ELSIF Statement :
It will check more than one ELSIF alternative conditions .If no condition is true, then ELSE block will execute finally.

Syntax:

IF <condition> THEN
<Statements to execute>
ELSIF <condition> THEN
<Statements to execute>
ELSIF <condition> THEN
<Statements to execute>
ELSIF <condition> THEN
<Statements to execute>
———–
———–
ELSE
<Statements to execute>
END IF;

Example: The below example will insert a record , if the count is 0 , updates when the count is 1 .

 

IF THEN ELSE and CASE in PL/SQL CASE Statement  :
The CASE statement will select one  from the sequence of statements like IF THEN ELSIF . But it will use a selector rather than multiple boolean expressions which are using in IF THEN ELSIF.The CASE statement is more readable and more efficient.

Syntax:

CASE <selector_value>
WHEN <value1> THEN <Statements to execute>;
WHEN <value2> THEN <Statements to execute>;
WHEN <value3>THEN <Statements to execute>;
—————————————— ;
ELSE <Statements to execute>;
END CASE;

 

 Searched CASE statement :
The searched CASE statement has no selector.Its WHEN clause contain search conditions that yield Boolean value.If any one of the conditions is true, the control will pass to the particular block to execute the statements.

Syntax:

CASE
WHEN <condition> THEN <Statements to execute>;
WHEN <condition> THEN <Statements to execute>;
WHEN <condition>THEN <Statements to execute>;
—————————————— ;
ELSE <Statements to execute>;
END CASE;

Example: We can possible to write the above (CASE Statement) example as follows .




Related Posts :
PL/SQL Arrays and nested tables ( Collections )
PL/SQL FOR and WHILE loop controls
PL/SQL Operators
PL/SQL Procedures
PL/SQL Triggers
PL/SQL Functions
PL/SQL Variables
   [will not be published]



^ <