"Topic- PL/SQL Control Structures"

Control structures in programming are used to control the flow of execution in a program. They determine the order in which statements are executed based on specified conditions. Control structures allow us to make decisions, repeat blocks of code, and handle different scenarios.

Control structures in PL/SQL can be divided into three main categories:

1. Conditional Control Structures
2. Iterative Control Structures
3. Unconditional Control Structures

LET'S DEEP DIVE:

1)Conditional Control Structure OR Selection structure:

The selection structure tests a condition, then executes one sequence of statements instead of another, depending on whether the condition is true or false. A condition is any variable or expression that returns a BOOLEAN value (TRUE or FALSE).

LET US NOW ABOUT THE 2 TYPES OF CONDITIONAL STATEMENTS:

• Testing Conditions: IF and CASE Statements
• IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.
• Simple CASE Statement, Searched CASE Statement.

Firstly, Let's learn about IF STATEMENT AND It's 3 Types of IF Statements.

IF STATEMENT:

IF statement is a type of control-flow statement that executes a block of SQL code, based on a specified condition.

A block of SQL code is specified between the IF, and END IF keywords. If the condition in the, IF clause evaluates to TRUE, then the statements below the IF, clause are executed.

Otherwise, the statement block is skipped, and program control is transferred to the statement after the END, keyword.

There are 3 Types of IF Statements:

1) #IF_THEN: It executes a set of SQL statements based on a specific condition.

Syntax:

IF condition THEN

-- statements to be executed if the condition is true

END IF;

IF-THEN-Syntax-Explanation: IF-THEN statement allows us to conditionally execute a block of code based on a specified condition. If the condition evaluates to TRUE, the statements within the block will be executed. If the condition is FALSE, the statements will be skipped, and the program will continue to the next line of code after the END IF statement.

Example:

-------------------------------------------------

DECLARE

num NUMBER := 10;

BEGIN

IF num > 0 THEN

DBMS_OUTPUT.PUT_LINE('The number is positive');

END IF;

END;

/

-------------------------------------------------

This example checks:

• The IF statement is used to check if the value of "num" is greater than 0. If the condition is true, the block of code inside the IF statement will be executed.
• Inside the IF block, we have a single statement: DBMS_OUTPUT.PUT_LINE. This statement is used to display a message to the console. In this case, it will print the message "The number is positive" if the value of "num" is indeed greater than 0.
• The END IF statement marks the end of the IF block. It indicates that there are no more statements to be executed if the condition is true.
• Finally, the PL/SQL block is ended with the "/" symbol, which is used to execute the block.

2) #IF_THEN_ELSE: It executes another set SQL statements when the condition in IF branch does not evaluate to TRUE.

Syntax:

IF condition THEN

-- statements to be executed if the condition is true

ELSE

-- statements to be executed if the condition is false

END IF;

IF-THEN-ELSE-Syntax-Explanation: The IF-THEN-ELSE statement extends the functionality of the IF-THEN statement by providing an alternative block of code to execute when the condition evaluates to false. If the condition is true, the statements within the IF block are executed; otherwise, the statements within the ELSE block are executed.

Example:

-------------------------------------------------

DECLARE

num NUMBER := 10;

BEGIN

IF num > 0 THEN

DBMS_OUTPUT.PUT_LINE('The number is positive');

ELSE

DBMS_OUTPUT.PUT_LINE('The number is non-positive');

END IF;

END;

/

-------------------------------------------------

• In this example, the condition "num > 0" is evaluated. If the value of "num" is greater than 0, the message "The number is positive" will be displayed. Otherwise, if the value is 0 or negative, the message "The number is non-positive" will be displayed.

3) #IF_THEN_ELSEIF_ELSE: It executes a set of SQL statements based on multiple conditions.

Syntax:

IF condition1 THEN

-- statements to be executed if condition1 is true

ELSIF condition2 THEN

-- statements to be executed if condition2 is true

...

ELSE

-- statements to be executed if none of the above conditions are true

END IF;

IF-THEN-ELSEIF-ELSE-Syntax-Explanation: The IF-THEN-ELSIF statement is used when there are multiple conditions to be checked. It allows us to specify multiple condition blocks and execute the statements corresponding to the first condition that evaluates to true. If none of the conditions are true, the statements within the ELSE block (if present) are executed.

Example:

-------------------------------------------------

DECLARE

num NUMBER := 10;

BEGIN

IF num > 0 THEN

DBMS_OUTPUT.PUT_LINE('The number is positive');

ELSIF num < 0 THEN

DBMS_OUTPUT.PUT_LINE('The number is negative');

ELSE

DBMS_OUTPUT.PUT_LINE('The number is zero');

END IF;

END;

/

-------------------------------------------------

In this example, the condition num > 0 is evaluated first. If it is true, the message "The number is positive" is printed. If it is false, the condition num < 0 is evaluated. If it is true, the message "The number is negative" is printed. If both conditions are false, the else block is executed and the message "The number is zero" is printed. In this case, since the value of num is 10, which is greater than 0, the output would be "The number is positive".

Next, let's learn about CASE STATEMENT AND It's 2 Types of CASE Statements.

CASE STATEMENT:

CASE statement is a type of control-flow statement used in stored procedures to create conditional statements that makes the code more readable and efficient.

It extends the functionality of the IF statement.

There are 2 types of CASE statements.

1) #Simple_CASE_Statement: It checks for equality and cannot be used with NULL that returns FALSE. It sequentially compares the case_value or value to find if it is within any of the several when_value declared until it finds a match.

Syntax:

CASE expression

WHEN value1 THEN

-- statements to be executed when expression = value1

WHEN value2 THEN

-- statements to be executed when expression = value2

...

[ELSE

-- statements to be executed when none of the above conditions match]

END CASE;

SIMPLE-CASE-Syntax-Explanation: The CASE statement allows us to compare a single expression against multiple values and execute different statements based on the matching condition/based on value of expression. If the expression matches any of the specified values, the corresponding block of statements is executed. If none of the conditions match, the statements within the ELSE block (if provided) are executed.

Example:

-------------------------------------------------

DECLARE

grade CHAR := 'B';

BEGIN

CASE grade

WHEN 'A' THEN

DBMS_OUTPUT.PUT_LINE('Excellent!');

WHEN 'B' THEN

DBMS_OUTPUT.PUT_LINE('Good!');

ELSE

DBMS_OUTPUT.PUT_LINE('Needs improvement!');

END CASE;

END;

/

-------------------------------------------------

In this example, the CASE statement is used to evaluate the value of the variable 'grade'. Depending on the value, different messages are printed to the console. If 'grade' is 'A', it prints 'Excellent!', if 'grade' is 'B', it prints 'Good!', and if 'grade' is any other value, it prints 'Needs improvement!'.

2) #Searched_CASE_Statement:

The second type of CASE statement is the Searched CASE statement. It is similar to the IF statement and is considerably more readable.

It is used to perform complex matchings such as ranges.

Syntax:

CASE

  WHEN condition1 THEN

   -- statements to be executed when condition1 is true

  WHEN condition2 THEN

   -- statements to be executed when condition2 is true

  ...

  [ELSE

   -- statements to be executed when none of the above conditions are true]

END CASE;

Searched- CASE-Syntax-Explanation: It evaluates each search_condition in each WHEN clause until it finds a condition that evaluates to TRUE. It then executes the corresponding THEN clause statements. If no search_condition evaluates to true, the searched CASE will execute the else_statements available in the ELSE clause.

Example:

-------------------------------------------------

DECLARE

num NUMBER := 10;

BEGIN

CASE

WHEN num > 0 THEN

DBMS_OUTPUT.PUT_LINE('The number is positive');

WHEN num < 0 THEN

DBMS_OUTPUT.PUT_LINE('The number is negative');

ELSE

DBMS_OUTPUT.PUT_LINE('The number is zero');

END CASE;

END;

/

-------------------------------------------------

In the example, the variable num is assigned the value 10. The Searched Case statement is used to check the value of num and perform different actions based on its value.

• If num is greater than 0, it executes the statement 'The number is positive', indicating that the number is positive.
• If num is less than 0, it executes the statement 'The number is negative', indicating that the number is negative.
• If none of the above conditions are true, it executes the statement 'The number is zero', indicating that the number is zero.

2) Iterative Control Statements OR Loop Control Structures:

The iteration structure executes a sequence of statements repeatedly as long as a condition holds true.

LET US NOW ABOUT ITERATIVE CONTROL STATEMENTS- LOOP STATEMENT AND It's 3 TYPES:

• Controlling Loop Iterations: LOOP Statement
• Basic LOOP
• WHILE loop
• FOR loop

LOOP STATEMENT:

Loops are a programming structure that repeats a set of instructions until a specific condition is satisfied. They are crucial for saving time and reducing errors.

There are 3 types of loops:

1) #BASIC_LOOP: The basic loop, also known as the simple loop, is a loop construct in PL/SQL that allows us to repeatedly execute a block of code until a specific condition is met.

Syntax:

LOOP

-- statements to be executed

-- exit condition

END LOOP;

LOOP-Syntax-Explanation:

• The code enclosed within the LOOP and END LOOP keywords represents the block of statements that will be executed repeatedly.
• The statements within the loop will continue to execute until the exit condition is met.
• The exit condition is typically defined using an IF statement or a combination of logical conditions.
• Once the exit condition evaluates to true, the control will exit the loop and continue with the next statement after the loop.

Example:

-------------------------------------------------

DECLARE

counter NUMBER := 1;

BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE('Count: ' || counter);

counter := counter + 1;

EXIT WHEN counter > 5;

END LOOP;

END;

/

-------------------------------------------------

• In this example, we initialize a variable counter to 1.
• Inside the loop, we use the DBMS_OUTPUT.PUT_LINE procedure to display the value of counter.
• After each iteration, we increment the value of counter by 1.
• We have defined an exit condition using the EXIT WHEN statement. When the value of counter becomes greater than 5, the loop will be exited.
• The loop will execute five times, displaying the values of counter from 1 to 5.

2) #WHILE_LOOP: The while loop statement is a control structure that executes one or more statements repeatedly as long as a condition is TRUE. It is also known as a pretest loop because it checks the search condition before executing the statements.

Syntax:

WHILE condition LOOP

-- statements to be executed

END LOOP;

WHILE-LOOP-Syntax-Explanation:

• The condition is evaluated before each iteration of the loop. If the condition is true, the loop will continue to execute; otherwise, it will exit.
• The statements within the loop will be executed as long as the condition remains true.
• It is important to ensure that the condition within the WHILE loop will eventually become false to avoid an infinite loop.

Example:

-------------------------------------------------

DECLARE

counter NUMBER := 1;

BEGIN

WHILE counter <= 5 LOOP

DBMS_OUTPUT.PUT_LINE('Count: ' || counter);

counter := counter + 1;

END LOOP;

END;

/

-------------------------------------------------

• In this example, we initialize a variable counter to 1.
• The WHILE loop condition is counter <= 5, which means the loop will continue executing as long as counter is less than or equal to 5.
• Inside the loop, we use the DBMS_OUTPUT.PUT_LINE procedure to display the value of counter.
• After each iteration, we increment the value of counter by 1.
• The loop will execute five times, displaying the values of counter from 1 to 5.

3) #FOR_LOOP: The FOR loop is a control structure that is used to iterate over a specified range of values. It allows us to define a loop variable and specify the starting, ending and increment values. The loop iterates through each value in the range, executing the statements within the loop block.

Syntax:

FOR loop_variable IN [REVERSE] lower_bound..upper_bound [INCREMENT increment_value]

LOOP

-- statements to be executed

END LOOP;

FOR-LOOP-Syntax-Explanation:

• loop_variable: It is a variable that takes on values from the specified range in each iteration of the loop.
• lower_bound: It is the starting value for the loop variable.
• upper_bound: It is the ending value for the loop variable.
• INCREMENT: It is an optional clause that specifies the increment value by which the loop variable should be incremented or decremented in each iteration.
• REVERSE: It is an optional keyword that indicates the loop should iterate in reverse order.

Example:

-------------------------------------------------

BEGIN

FOR i IN 1..5

LOOP

DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);

END LOOP;

END;

/

-------------------------------------------------

In this example, the loop variable 'i' takes on values from 1 to 5 (inclusive) in each iteration of the loop. The statements inside the loop are executed for each value of 'i', and the value of 'i' is displayed using the DBMS_OUTPUT.PUT_LINE statement.

The output of this example would be:

Value of i: 1

Value of i: 2

Value of i: 3

Value of i: 4

Value of i: 5

The FOR loop provides a convenient way to iterate over a range of values without the need to explicitly manage a loop counter. It simplifies the process of performing repetitive tasks and iterating through collections in PL/SQL programs.

3) Unconditional Control Structures OR Sequence Control Structures:

The sequence-structure simply executes a sequence of statements in the order in which they occur.

LET US KNOW ABOUT 2 SEQUENCE CONTROL STATEMENTS:

• Sequential Controls: GOTO and NULL Statements
• GOTO statement
• NULL Statement

1) #GOTO_Statement: The GOTO statement allows us to transfer control to a specified label within the same block of code. It provides an unconditional jump to the target label, bypassing any code in between.

Syntax:

GOTO label_name;

GOTO-Syntax-Explanation:

• label_name: It is a user-defined label that marks a specific section of code to which the execution will be transferred.

Example:

-------------------------------------------------

DECLARE

num NUMBER := 10;

BEGIN

IF num > 0 THEN

DBMS_OUTPUT.PUT_LINE('Positive number');

GOTO end_label;

ELSE

DBMS_OUTPUT.PUT_LINE('Negative number');

END IF;

<<end_label>>

DBMS_OUTPUT.PUT_LINE('End of program');

END;

/

-------------------------------------------------

In this example, the GOTO statement is used to transfer the execution of the code to the end_label section if the number num is greater than 0. If the condition is true, it will print "Positive number" and then jump to the end_label section. If the condition is false, it will print "Negative number" and continue executing the subsequent code.

The <<end_label>> is a user-defined label that marks the end of the program. After the GOTO statement jumps to this label, it will execute the code following the label, which in this case is the line that prints "End of program".

2) #NULL_Statement: The NULL statement is a placeholder that does nothing. It is often used when a statement is required syntactically but no action is needed. It can be useful in program logic where we want to indicate a deliberate omission of code.

Syntax: NULL;

NULL-Syntax-Explanation:

• The NULL statement is simply the keyword "NULL" followed by a semicolon.
• The NULL statement is commonly used in conditional control structures like IF-THEN-ELSE or CASE statements when we want to have an empty block of code for a particular condition.

Example:

-------------------------------------------------

DECLARE

num NUMBER := 10;

BEGIN

IF num > 0 THEN

DBMS_OUTPUT.PUT_LINE('Positive number');

ELSE

-- No action required for negative or zero number

NULL;

END IF;

END;

/

-------------------------------------------------

In this example, if the value of num is greater than 0, it will print "Positive number" using the DBMS_OUTPUT.PUT_LINE statement. If the value of num is negative or zero, there is no specific action needed. In such cases, the NULL statement is used to represent an empty block of code. It serves as a placeholder to indicate that no action should be performed for that condition.

Overall Summary:

• IF-THEN control structure allows us to perform certain actions based on a condition. It evaluates a condition and executes a block of code if the condition is true.
• IF-THEN-ELSE control structure extends the basic IF-THEN structure by providing an alternative block of code to execute when the condition is false.
• IF-THEN-ELSIF control structure allows us to test multiple conditions sequentially and execute different blocks of code based on the first condition that evaluates to true.
• CASE control structure allows us to evaluate an expression or variable against a set of values or conditions and execute the block of code that corresponds to the matching value or condition.
• LOOP control structure provides a way to repeatedly execute a block of code until a certain condition is met.
• FOR loop is a specialized loop that iterates over a range of values or a collection, executing a block of code for each iteration.
• WHILE loop is a condition-controlled loop that repeatedly executes a block of code as long as the specified condition is true.
• EXIT statement allows us to terminate the execution of a loop or block of code prematurely.
• GOTO statement provides a way to transfer the control of the program to a labeled statement within the same program unit.
• NULL statement is a placeholder statement that does nothing. It can be used to indicate intentional omission of code or to serve as a placeholder for future code implementation.