« Previous | 1 | 2 | 3 | Next »

PlSQL - Conditions

Decision-making structures require that the programmer specify one or more conditions to be evaluated or tested by the program, along with a statement or statements to be executed if the condition is determined to be true, and optionally, other statements to be executed if the condition is determined to be false.

Following is the general from of a typical conditional (i.e., decision making) structure found in most of the programming languages:

PL/SQL programming language provides following types of decision-making statements. Click the following links to check their detail.

Statement	Description
IF - THEN statement	The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition is true, the statements get executed and if the condition is false or NULL then the IF statement does nothing.
IF-THEN-ELSE statement	IF statement adds the keyword ELSE followed by an alternative sequence of statement. If the condition is false or NULL , then only the alternative sequence of statements get executed. It ensures that either of the sequence of statements is executed.
IF-THEN-ELSIF statement	It allows you to choose between several alternatives.
Case statement	Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions. A selector is an expression whose value is used to select one of several alternatives.
Searched CASE statement	The searched CASE statement has no selector, and it's WHEN clauses contain search conditions that yield Boolean values.
nested IF-THEN-ELSE	You can use one IF-THEN or IF-THEN-ELSIF statement inside another IF-THEN or IF-THEN-ELSIF statement(s).

IF - THEN statement

It is the simplest form of IF control statement, frequently used in decision making and changing the control flow of the program execution.
The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition is TRUE, the statements get executed, and if the condition is FALSE or NULL, then the IF statement does nothing.

Syntax:

Syntax for IF-THEN statement is:

IF condition THEN
S;
END IF;

Where condition is a Boolean or relational condition and S is a simple or compound statement. Example of an IF-THEN statement is:

IF (a <= 20) THEN
c:= c+1;
END IF;

If the Boolean expression condition evaluates to true, then the block of code inside the if statement will be executed. If Boolean expression evaluates to false, then the first set of code after the end of the if statement (after the closing end if) will be executed.

Example 1:

Let us try a complete example that would illustrate the concept:

DECLARE
a number(2) := 10;
BEGIN a:= 10;
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
/

When the above code is executed at SQL prompt, it produces the following result:

a is less than 20
value of a is : 10
PL/SQL procedure successfully completed.

Example 2:

Consider we have a table and few records in the table as we had created in PL/SQL Variable Types

DECLARE
c_id customers.id%type := 1;
c_sal customers.salary%type;
BEGIN
SELECT salary
INTO c_sal
FROM customers
WHERE id = c_id;
IF (c_sal <= 2000) THEN
UPDATE customers
SET salary = salary + 1000
WHERE id = c_id;
dbms_output.put_line ('Salary updated');
END IF;
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Salary updated
PL/SQL procedure successfully completed.

IF-THEN-ELSE statement

A sequence of IF-THEN statements can be followed by an optional sequence of ELSE statements, which execute when the condition is FALSE.

Syntax:

Syntax for the IF-THEN-ELSE statement is:

IF condition THEN
S1;
ELSE
S2;
END IF;

Where, S1 and S2 are different sequence of statements. In the IF-THEN-ELSE statements, when the test condition is TRUE, the statement S1 is executed and S2 is skipped; when the test condition is FALSE, then S1 is bypassed and statement S2 is executed. For example:

IF color = red THEN
dbms_output.put_line('You have chosen a red car')
ELSE
dbms_output.put_line('Please choose a color for your car');
END IF;

If the Boolean expression condition evaluates to true, then the if-then block of code will be executed, otherwise the else block of code will be executed.

Example:

Let us try a complete example that would illustrate the concept:

DECLARE
a number(3) := 100;
BEGIN
-- check the boolean condition using if statement
IF( a < 20 ) THEN
-- if condition is true then print the following
dbms_output.put_line('a is less than 20 ' );
ELSE
dbms_output.put_line('a is not less than 20 ' );
END IF;
dbms_output.put_line('value of a is : ' || a);
END;
/

When the above code is executed at SQL prompt, it produces the following result:

a is not less than 20
value of a is : 100
PL/SQL procedure successfully completed.

IF-THEN-ELSIF statement

The IF-THEN-ELSIFstatement allows you to choose between several alternatives. An IF-THENstatement can be followed by an optional ELSIF...ELSEtatement. The ELSIFclause lets you add additional conditions.
When using IF-THEN-ELSIFstatements, there are few points to keep in mind.

It's ELSIF, not ELSEIF
An IF-THEN statement can have zero or one ELSE's and it must come after any ELSIF's.
An IF-THEN statement can have zero to many ELSIF's and they must come before the ELSE.
Once an ELSIF succeeds, none of the remaining ELSIF's or ELSE's will be tested.

Syntax:

The syntax of an IF-THEN-ELSIF Statement in PL/SQL programming language is:

IF(boolean_expression 1)THEN
S1; -- Executes when the boolean expression 1 is true
ELSIF( boolean_expression 2) THEN
S2; -- Executes when the boolean expression 2 is true
ELSIF( boolean_expression 3) THEN
S3; -- Executes when the boolean expression 3 is true
ELSE
S4; -- executes when the none of the above condition is true
END IF;

Example:

DECLARE
a number(3) := 100;
BEGIN
IF ( a = 10 ) THEN
dbms_output.put_line('Value of a is 10' );
ELSIF ( a = 20 ) THEN
dbms_output.put_line('Value of a is 20' );
ELSIF ( a = 30 ) THEN
dbms_output.put_line('Value of a is 30' );
ELSE
dbms_output.put_line('None of the values is matching');
END IF;
dbms_output.put_line('Exact value of a is: '|| a );
END;
/

When the above code is executed at SQL prompt, it produces the following result:

None of the values is matching
Exact value of a is: 100
PL/SQL procedure successfully completed.

Case statement

Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions. A selector is an expression, whose value is used to select one of several alternatives.

Syntax:

The syntax for case statement in PL/SQL is:

CASE selector
WHEN 'value1' THEN S1;
WHEN 'value2' THEN S2;
WHEN 'value3' THEN S3;
...
ELSE Sn; -- default case
END CASE;

Example:

DECLARE
grade char(1) := 'A';
BEGIN
CASE grade
when 'A' then dbms_output.put_line('Excellent');
when 'B' then dbms_output.put_line('Very good');
when 'C' then dbms_output.put_line('Well done');
when 'D' then dbms_output.put_line('You passed');
when 'F' then dbms_output.put_line('Better try again');
else dbms_output.put_line('No such grade');
END CASE;
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Excellent
PL/SQL procedure successfully completed.

Searched CASE statement

The searched CASE statement has no selector and its WHEN clauses contain search conditions that give Boolean values.

Syntax:

The syntax for searched case statement in PL/SQL is:

CASE
WHEN selector = 'value1' THEN S1;
WHEN selector = 'value2' THEN S2;
WHEN selector = 'value3' THEN S3;
...
ELSE Sn; -- default case
END CASE;

Example:

DECLARE
grade char(1) := 'B';
BEGIN
case
when grade = 'A' then dbms_output.put_line('Excellent');
when grade = 'B' then dbms_output.put_line('Very good');
when grade = 'C' then dbms_output.put_line('Well done');
when grade = 'D' then dbms_output.put_line('You passed');
when grade = 'F' then dbms_output.put_line('Better try again');
else dbms_output.put_line('No such grade');
end case;
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Very good
PL/SQL procedure successfully completed.

Nested IF-THEN-ELSE

It is always legal in PL/SQL programming to nest IF-ELSE statements, which means you can use one IF or ELSE IF statement inside another IF or ELSE IF statement(s).

Syntax:

IF( boolean_expression 1)THEN
-- executes when the boolean expression 1 is true
IF(boolean_expression 2) THEN
-- executes when the boolean expression 2 is true
sequence-of-statements;
END IF;
ELSE
-- executes when the boolean expression 1 is not true
else-statements;
END IF;

Example:

DECLARE
a number(3) := 100;
b number(3) := 200;
BEGIN
-- check the boolean condition
IF( a = 100 ) THEN
-- if condition is true then check the following
IF( b = 200 ) THEN
-- if condition is true then print the following
dbms_output.put_line('Value of a is 100 and b is 200' );
END IF;
END IF;
dbms_output.put_line('Exact value of a is : ' || a );
dbms_output.put_line('Exact value of b is : ' || b );
END;
/

When the above code is executed at SQL prompt, it produces the following result:

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.

« Previous | 1 | 2 | 3 | Next »

The largest Interview Solution Library on the web

PlSQL - Conditions