Oracle PL/SQL Control Statements Quiz

Control Structures in PL/SQL

• Conditional control allows the program to flow based on a condition, which can be a variable or expression that returns a Boolean value (TRUE, FALSE, or NULL).
• There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.

IF-THEN Statement

• Tests a simple condition; if the condition evaluates to TRUE, one or more lines of code are executed.
• If the condition evaluates to FALSE, program control is passed to the next statement after the test.

IF-THEN-ELSE Statement

• Similar to IF, except that when the condition evaluates to FALSE, one or more statements following the ELSE are executed.

IF-THEN-ELSIF Statement

• An alternative to using nested IF-THEN-ELSE constructs.

Procedural Language Capability of PL/SQL

• Consists of procedural language constructs such as conditional statements (if-else statements) and loops like (FOR loops).
• Better performance due to processing multiple SQL statements simultaneously as a single block, reducing network traffic.
• Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program.

PL/SQL Control Structures

• According to the structure theorem, any computer program can be written using the basic control structures, which can be combined in any way necessary to deal with a given problem.
• Conditional Structure tests a condition, and then executes one sequence of statements instead of another, depending on whether the condition is true or false.
• Iterative Structure executes a sequence of statements repeatedly as long as a condition holds true.
• Sequence Structure simply executes a sequence of statements in the order in which they occur.

Iterative Control

• LOOP statements let you execute a sequence of statements multiple times.
• There are three forms of LOOP statements: LOOP, WHILE-LOOP, and FOR-LOOP.
• LOOP: simplest form of LOOP statement, which encloses a sequence of statements between the keywords LOOP and END LOOP.
• WHILE-LOOP: has a condition that is evaluated at the beginning of each iteration; if the condition is true, the statements in the loop are executed.
• FOR-LOOP: iterates over a specified range of integers; the range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.

Sequential Control

• Normally, execution proceeds sequentially within the block of the code.
• The sequence can be changed conditionally as well as unconditionally using the GOTO statement.

PL/SQL Exceptions

• Pre-defined exceptions: PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program.
• User-defined exceptions: PL/SQL allows you to define your own exceptions according to the need of your program.

SQL%ROWCOUNT Attribute

• Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.
• Example: updating the table and increasing the salary of each customer by 500, and using the SQL%ROWCOUNT attribute to determine the number of rows affected.