Control Structures in Oracle Database
Key Concepts
1. IF-THEN Statement
The IF-THEN statement is the simplest form of control structure. It executes a block of code if a specified condition is true.
Example:
Executing a block of code if a condition is true:
IF salary > 5000 THEN
dbms_output.put_line('High Salary');
END IF;
2. IF-THEN-ELSE Statement
The IF-THEN-ELSE statement extends the IF-THEN statement by providing an alternative block of code to execute if the condition is false.
Example:
Executing different blocks of code based on a condition:
IF salary > 5000 THEN
dbms_output.put_line('High Salary');
ELSE
dbms_output.put_line('Low Salary');
END IF;
3. IF-THEN-ELSIF Statement
The IF-THEN-ELSIF statement allows for multiple conditions to be checked, executing different blocks of code based on which condition is true.
Example:
Executing different blocks of code based on multiple conditions:
IF salary > 10000 THEN
dbms_output.put_line('Very High Salary');
ELSIF salary > 5000 THEN
dbms_output.put_line('High Salary');
ELSE
dbms_output.put_line('Low Salary');
END IF;
4. CASE Statement
The CASE statement provides a way to select one of many code blocks to execute based on the value of an expression.
Example:
Executing different blocks of code based on the value of an expression:
CASE department_id
WHEN 10 THEN dbms_output.put_line('Department 10');
WHEN 20 THEN dbms_output.put_line('Department 20');
ELSE dbms_output.put_line('Other Department');
END CASE;
5. SEARCHED CASE Statement
The SEARCHED CASE statement allows for more complex conditions to be evaluated, similar to the IF-THEN-ELSIF statement.
Example:
Executing different blocks of code based on complex conditions:
CASE
WHEN salary > 10000 THEN dbms_output.put_line('Very High Salary');
WHEN salary > 5000 THEN dbms_output.put_line('High Salary');
ELSE dbms_output.put_line('Low Salary');
END CASE;
6. LOOP Statement
The LOOP statement is a basic loop structure that repeatedly executes a block of code until an EXIT condition is met.
Example:
Repeating a block of code until a condition is met:
LOOP
dbms_output.put_line('Iteration: ' || counter);
counter := counter + 1;
EXIT WHEN counter > 5;
END LOOP;
7. WHILE LOOP Statement
The WHILE LOOP statement repeatedly executes a block of code as long as a specified condition is true.
Example:
Repeating a block of code while a condition is true:
WHILE counter <= 5 LOOP
dbms_output.put_line('Iteration: ' || counter);
counter := counter + 1;
END LOOP;
8. FOR LOOP Statement
The FOR LOOP statement executes a block of code a specified number of times, using a loop counter.
Example:
Repeating a block of code a specified number of times:
FOR i IN 1..5 LOOP
dbms_output.put_line('Iteration: ' || i);
END LOOP;
9. CONTINUE Statement
The CONTINUE statement skips the remaining code in the current iteration of a loop and continues with the next iteration.
Example:
Skipping an iteration based on a condition:
FOR i IN 1..5 LOOP
CONTINUE WHEN i = 3;
dbms_output.put_line('Iteration: ' || i);
END LOOP;
10. EXIT Statement
The EXIT statement terminates the execution of a loop immediately.
Example:
Exiting a loop based on a condition:
FOR i IN 1..5 LOOP
dbms_output.put_line('Iteration: ' || i);
EXIT WHEN i = 3;
END LOOP;
11. GOTO Statement
The GOTO statement transfers control to a specified label within the same block of code. It is generally discouraged due to its potential to make code difficult to read and maintain.
Example:
Transferring control to a label:
IF salary > 5000 THEN
GOTO high_salary;
ELSE
dbms_output.put_line('Low Salary');
END IF;
<>
dbms_output.put_line('High Salary');
12. NULL Statement
The NULL statement does nothing. It is often used as a placeholder where a statement is required syntactically but no action is needed.
Example:
Using the NULL statement as a placeholder:
IF salary > 5000 THEN
dbms_output.put_line('High Salary');
ELSE
NULL;
END IF;
13. Nested IF Statements
Nested IF statements involve placing an IF statement inside another IF statement. This allows for more complex decision-making logic.
Example:
Using nested IF statements for complex decision-making:
IF salary > 5000 THEN
IF department_id = 10 THEN
dbms_output.put_line('High Salary in Department 10');
END IF;
END IF;
14. Nested LOOP Statements
Nested LOOP statements involve placing one loop inside another. This allows for more complex iterative logic.
Example:
Using nested LOOP statements for complex iteration:
FOR i IN 1..3 LOOP
FOR j IN 1..2 LOOP
dbms_output.put_line('Outer: ' || i || ' Inner: ' || j);
END LOOP;
END LOOP;
15. EXIT-WHEN Statement
The EXIT-WHEN statement combines the EXIT statement with a condition, exiting the loop when the condition is true.
Example:
Exiting a loop based on a condition:
LOOP
dbms_output.put_line('Iteration: ' || counter);
counter := counter + 1;
EXIT WHEN counter > 5;
END LOOP;
16. CONTINUE-WHEN Statement
The CONTINUE-WHEN statement combines the CONTINUE statement with a condition, skipping the current iteration when the condition is true.
Example:
Skipping an iteration based on a condition:
FOR i IN 1..5 LOOP
CONTINUE WHEN i = 3;
dbms_output.put_line('Iteration: ' || i);
END LOOP;
17. Labeling Blocks
Labeling blocks allows for more control over the flow of execution, especially in nested control structures. Labels can be used with GOTO statements or to identify blocks of code.
Example:
Labeling a block of code:
<>
FOR i IN 1..3 LOOP
<>
FOR j IN 1..2 LOOP
dbms_output.put_line('Outer: ' || i || ' Inner: ' || j);
END LOOP inner_loop;
END LOOP outer_loop;
18. Exception Handling
Exception handling allows for the management of errors and exceptional conditions within PL/SQL code. It uses the EXCEPTION block to handle errors gracefully.
Example:
Handling an exception:
BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 100;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Employee not found');
END;