Description
PLSQL Conditions depend on the conditional block and may or may not be executed. If the case is said to be true, it executes the statement block, and if the condition is said to be false, then the statement block doesn't get executed. Oracle PL/SQL provides a very robust support for case evaluation in PL/SQL and makes it simple and easy to understand the constructs. There are two types of PLSQL Conditions statements. The IF statement and the other type is the CASE statement and expressions.
Conceptual
figure
Description
The IF PLSQL Conditions statement starts with the IF keyword, followed by a condition or a group of conditions. If the condition is true, then the group of statements inside the IF clause will be executed. The IF proclamation ends with the END IF statement followed by a semicolon and can declare any variables inside the IF clause.
Syntax
IF <Condition>THEN
statements
END IF;
Condition =>Value assigned in the condition.
Statement =>Executes true or false statements.
Examples
The below example describes the if statement.
[c]SQL> DECLARE
1 a number(2):=10;
2 BEGIN
3 a:=10;
4 -- check the boolean condition using if statement
5 IF(a<20)THEN
6 -- if condition is true then print the following
7 dbms_output.put_line('a is less than 20');
8 END IF;
9 dbms_output.put_line('value of a is :'||a);
10 END;
11 /
a is less than 20
value of a is : 10
PL/SQL procedure successfully completed.[/c]
Description
The statement of IF-THEN is succeed by an alternate successions of ELSE proclamation, which assassinate when the case is said to be False.
Conceptual
figure
Syntax
IF condition THEN
S1;
ELSE
S2;
END IF;
Condition =>Value assigned in the condition.
Statement =>Executes true or false statements s1 value or s2 value.
Examples
The below example explains the if-else statement.
[c]SQL> DECLARE
2 a number(3):=100;
3 BEGIN
4 --check the boolean condition using if statement
5 IF(a<20)THEN
6 -- if condition is true then print the following
7 dbms_output.put_line('a is less than 20');
8 ELSE
9 dbms_output.put_line('a is not less than 20');
10 END IF;
11 dbms_output.put_line('value of a is:'||a);
12 END;
13 /
PL/SQL is successfully executed.[/c]
Description
This is the third form of IF statement. ELSE clause is optional when using ELSE IF. And, ELSE IF clause is also followed by a condition as IF clause and contains any number of ELSE IF statements. Oracle keeps evaluating the IF and ELSE IF statements from top to bottom until the condition is set to be true.
Syntax
IF <condition> THEN
statements
ELSIF<case>THEN
statements
ELSE
statements
END IF;
Condition =>Value assigned in the condition.
Statement =>Executes true or false statements.
Examples
The below example describes the if-else-if statement.
[c]SQL> DECLARE
2 a number(3):=100;
3 BEGIN
4 IF (a=10)THEN
5 dbms_output.put_line('value of a is 10');
6 ELSIF(a=20)THEN
7 dbms_output.put_line('value of a is 20');
8 ELSIF(a=30)THEN
9 dbms_output.put_line('value of a is 30 ');
10 ELSE
11 dbms_output.put_line('None of the values is matching');
12 END IF;
13 dbms_output.put_line('Exact value of a is:'||a);
14 END;
15 /
None of the values is matching
Exact value of a is:100
PL/SQL procedure is successfully completed.[/c]
Description
The nesting PLSQL Conditions can happen anywhere within the IF, ELSE IF or ELSE clauses. To prevent the code from becoming too complex, just maintain one or two level of nesting and evaluate different ways of writing the logic to keep nesting to a minimum.
Syntax
IF(boolean_expression 1)THEN
--assassinate when the boolean explanations 1 is true
IF(boolean_expression 2)THEN
--assassinate when the boolean explanations 2 is true
arrangement-of-proclamations;
END IF;
ELSE
--assassinate when the boolean explanations 1 is not true
else-statements;
END IF;
Condition =>Value assigned in the condition.
Statement =>Executes true or false statements.
Examples
The below example illustrates the nesting statement.
[c]SQL> DECLARE
2 a number(3):=100;
3 b number(3):=200;
4 BEGIN
5 -- check the boolean condition
6 IF(a=100) THEN
7 -- if condition is true then check the following
8 IF(b=200)THEN
9 --if condition is true then print the following
10 dbms_output.put_line('value of a is 100 and b is 200');
11 END IF;
12 END IF;
13 dbms_output.put_line('Exact value of a is :'||a);
14 dbms_output.put_line('Exact value of b is :'||b);
15 END;
16 /
value of a is 100 and b is 200
Exact value of a is : 100
Exact value of b is : 200
PL/SQL procedure successfully completed.[/c]
Description
Case statement is an alternate to the IF statement and makes the code more readable & compact. The case can be expressed as a statement where one can execute the conditional logic based on value comparisons or conditions. It can also be written as an expression where assigned values are returned to variables. This is very useful while executing the conditional code and assigning the values to the same variable. In the case statement, the variable values to different alternates will be compared and returns the very first match. It starts with a case keyword followed by a variable or an expression. And, the results are evaluated in the when clause from top to bottom and the clause ELSE is alternate. If the clause ELSE is not defined and there is no match, the case statement will raise a CASE_NOT_FOUND exception. The CASE END ends the CASE statement.
Conceptual
figure
Syntax
CASE{variable or expression}
WHEN value_1 THEN
statements_1
WHEN value_n THEN
statement_n
[ELSE
statements defaults]
END CASE;
Condition =>Value assigned in the condition.
Statement =>Executes true or false statements.
Examples
The below example illustrates the case statement.
[c]SQL> DECLARE
2 grade char(1):='A';
3 BEGIN
4 CASE grade
5 when 'A'then dbms_output.put_line('Excellent');
6 when 'B'then dbms_output.put_line('Very good');
7 when 'C'then dbms_output.put_line('Well done');
8 when 'D'then dbms_output.put_line('You passed');
9 when 'F'then dbms_output.put_line('Better try again');
10 else dbms_output.put_line('No such grade');
11 END CASE;
12 END;
13 /
Excellent
PL/SQL procedure successfully completed.[/c]
Description
The simple case expression is similar to the simple case statement, which compares a value or an expression to the values in the when clause, and returns the values from the first match to when clause. However, the difference is that here it assigns the written values of the case expression directly to a variable in the PL/SQL section. The returned value should match the datatype of the variable, it is assigned to a number, data, and character. So, for the simple case statement, assigning values to a single variable inside the when clause simplifies the code and makes it more complete and readable. There will be no END CASE clause, but contains the END clause to end the case statement. The ELSE clause is optional like case statement. If the ELSE clause is omitted, then no match will be found with the WHEN clause and doesn't return CASE_NOT_FOUND exception instead returns only the NULL VALUE.
Syntax
CASE{value or expression}
WHEN VALUE_1 THEN
return_value_1
WHEN value_2
[ELSE
return_value3]
END;
Condition =>Esteem assigned in the condition.
Statement =>Executes true or false statements.
Examples
The below example illustrates the case expressions.
[c]SQL> DECLARE
2 I_ticket_priority VARCHAR2(8):='MEDIUM';
3 I_support_tier NUMBER;
4 BEGIN
5 CASE I_ticket_priority
6 WHEN 'HIGH'THEN
7 I_support_tier:=1;
8 WHEN 'MEDIUM'THEN
9 I_support_tier:=2;
10 WHEN 'LOW'THEN
11 I_support_tier:=3;
12 ELSE
13 I_support_tier:=0;
14 ELSE
15 CASE;
16 DBMS_OUTPUT.PUT_LINE(I_support_tier);
17 END;
18 /
PL/SQL program successfully executed.
[/c]
Key Points
- PLSQL Conditions execution - The conditional statement describes which code has to be executed.
- IF statement - Is a programming PLSQL Conditions statement that executes a function if the case is appropriate.
- IF else statement - If the case is true, it executes some code and if the case is false, it executes the other code
- code.
- IF else-if statement - For two or more codes, it executes different codes.
- Nesting - Nesting of explicit transactions.
- Case statement - Is an alternate to the if statement.
- Case expression - Is similar to the simple case statement.