Decision-making programs or structures require that the developer mentions conditions to be evaluated or tested by the program, along with statements to be executed if the condition is determined to be true, and also, statements which need to be executed if the condition is determined to be false.
PL/SQL Conditional Statements
PL/SQL IF-THEN Statement
The IF statement accompanies a condition with a sequence of statements enclosed by END IF and THEN. If the condition is true, the statements get executed and if the condition is FALSE or NULL then it does nothing. The control passes to the succeeding statements in any case.
Syntax
|
1 2 3 |
IF condition THEN Statements END IF; |
Example
|
1 2 3 4 5 6 7 8 9 |
declare var1 integer; begin var1:=&var1; if var1>20 then dbms_output.put_line('Number is Greater Than 20'); end if; end; / |
Output
PL/SQL IF-THEN-ELSE Statement
This statement uses an IF condition along with an ELSE block. If the condition is TRUE, then the statements in the IF block will be executed and if the condition is NULL or FALSE, then the alternative sequence of statements gets executed that is written in ELSE Block. This statement ensures that at least one of the sequence of statements is executed.
Syntax
|
1 2 3 4 5 |
IF condition THEN Statements ELSE Statements END IF |
Example
|
1 2 3 4 5 6 7 8 9 10 11 |
declare var1 integer; begin var1:=&var1; if var1>10 and var1<20 then dbms_output.put_line('Number is between 10 and 20'); else dbms_output.put_line('Number is out of range'); end if; end; / |
Output
PL/SQL IF-THEN-ELSIF Statement
There may arise a situation wherein you need to choose one of the several alternatives. If the first condition is FALSE or NULL, the ELSIF block tests another condition. An IF statement can have multiple ELSIF blocks and the final ELSE clause is optional. Conditions are evaluated from top to bottom one by one. If any condition is true, the sequence of statements in that particular block is executed and control passes to the next statement. If all the succeeding conditions are FALSE, the sequence in the ELSE block is executed. Consider the following example.
Syntax
|
1 2 3 4 5 6 7 |
IF condition THEN Statements ELSEIF condition THEN Statements ELSE Statements END IF |
PL/SQL CASE Statement
Like the IF-THEN-ELSEIF statement, the CASE statement selects one sequence of statements to execute from several alternatives.
The CASE statement uses a selector whereas IF-THEN and IF-THEN-ELSIF statements use Boolean expressions. A selector is an expression developed by the programmer whose value is used to select one of several alternatives mentioned in the program under different Cases.
The CASE statement is efficient compared to IF statements and hence if the IF-THEN-ELSE programs become lengthy, it is recommended to use CASE statements.
The CASE statement begins with the keyword CASE and is followed by a selector rather than an expression. The selector expression is evaluated only once.The value it yields can be a BFILE, PL/SQL record, BLOB, an index-by-table, an object type, a varray, or a nested table. The selector expression can also contain function calls which may be a complex one. It consists of a single variable generally.
The ELSE clause is not mandatory to write. If you omit it, PL/SQL adds the ELSE block implicitly.
Syntax
|
1 2 3 4 5 |
CASE selector WHEN 'value1' THEN Statement1; WHEN 'value2' THEN Statement2; ELSE Statement END CASE; |
