Like any procedural programming language, PL/SQL programs can also using Branching structures to control flow of logic. The conditional or branching statements of PL/SQL are
- IF-THEN-END IF
- IF-THEN-ELSE-END IF
- IF-THEN-ELSIF-END IF
- CASE-WHEN-ELSE-END
PL/SQL programs -Conditional Statements
The conditional statements are used in PL/SQL programs when you have to execute a set of statements when a specific condition is true.
IF-THEN-END IF
In the simple IF-THEN-END IF statement the condition given with IF is checked. If the condition is TRUE, the statements after THEN are executed. If the condition is FALSE, statements after END IF are executed.
Syntax
IF (condition) THEN
Then Statements block
END IF;
Example
DECLARE inVal NUMBER:=10; BEGIN IF (MOD(inVal,2)=0) THEN DBMS_OUTPUT.PUT_LINE('Even Number'); END IF; END;
IF-THEN-ELSE-END IF
In the IF-THEN-ELSE – END IF statement the condition given after IF is checked. If the condition is TRUE, the statements after THEN are executed. If the condition is FALSE, statements after ELSE are executed. After following either ELSE or THEN, the statements after END IF are executed
Syntax
IF (condition) THEN
Then Statements block
ELSE
ELSE Statements block
END IF;
DECLARE inVal NUMBER:=35; BEGIN IF (MOD(inVal,2)=0) THEN DBMS_OUTPUT.PUT_LINE('Even Number'); ELSE DBMS_OUTPUT.PUT_LINE('Odd Number'); END IF; END;
IF-THEN-ELSIF-THEN-END IF
In the IF-THEN-ELSIF-THEN – END IF statement, IF condition is checked at entry point. If the condition is TRUE, the statements after THEN are executed. If the condition is FALSE, the ELSIF condition is checked. If ELSIF condition becomes true the statements after THEN are executed else statements after ELSE are executed.
Syntax
IF (condition) THEN
ELSIF (contition) THEN
Then Statements block
ELSE
ELSE Statements block
END IF;
Example
DECLARE inVal NUMBER:=67; BEGIN IF (inVal=50) THEN DBMS_OUTPUT.PUT_LINE('Exact Half'); ELSIF (inVal>50) THEN DBMS_OUTPUT.PUT_LINE('Gone Forward'); ELSE DBMS_OUTPUT.PUT_LINE('Gone Backward'); END IF; END;
CASE-WHEN-ELSE-END CASE
CASE-WHEN-ELSE-END CASE statement is like a nested IF-THEN-ELSE statements. With CASE keyword the selector variable or expression is given. Wherever the values after WHEN matches the selected the following block of statements are executed. Then the control is passed to END CASE. If no WHEN value matches with the selector, the statements block of ELSE is executed.
Syntax
CASE selector-name|expression
WHEN value1 THEN Statement block 1;
WHEN value1 THEN Statement block 2;
WHEN value n THEN Statement block n;
[ELSE Statement block;]
END CASE;
Example
DECLARE intday NUMBER:=4; BEGIN CASE intday WHEN 1 THEN DBMS_OUTPUT.PUT_LINE ('Sunday'); WHEN 2 THEN DBMS_OUTPUT.PUT_LINE ('Monday'); WHEN 3 THEN DBMS_OUTPUT.PUT_LINE ('Tuesday'); WHEN 4 THEN DBMS_OUTPUT.PUT_LINE ('Wednesday'); WHEN 5 THEN DBMS_OUTPUT.PUT_LINE ('Thursday'); WHEN 6 THEN DBMS_OUTPUT.PUT_LINE ('Friday'); WHEN 7 THEN DBMS_OUTPUT.PUT_LINE ('Saturday'); ELSE DBMS_OUTPUT.PUT_LINE ('WRONG DAY NUMBER'); END CASE; END;
Note: You can nest the branching statements in your PL/SQL programs.
bohot acha example diye ho
Thanks Harsh.