1. Conditional statements
2. Loop statements
3.Sequential control statements
Conditional selection statements:IF statement is used mostly for conditional execution of statements or block of statements. IF has three different form:
1. IF THEN statement - condition is evaluated to true then statements are executed else does nothing.
IF condition THEN executable statements -- if condition is true, statements is executed END IF;
IF condition THEN executable statements -- if condition is true, statements is executed ELSE else_statements -- -- if condition is false,NULL else_statements is executed END IF;
IF condition_1 THEN executable statements_1 -- if condition_1 is true, statements is executed ELSIF condition_2 THEN executable statements_2 -- if condition_2 is true, statements is executed [ ELSIF condition_3 THEN -- optional executable statements_3 ] [ ELSE --optional else_statements ] END IF;
When we execute this PL/SQL program, "Very Good" is displayed, since grade value is B and first IF comparison (grade = 'A' ) resolves to false , so control goes to to next ELSIF statement and it is true so its DBMS_OUTPUT prints Very Good and control reaches to END IF.
CASE Statement is used in PL/SQL for execution of statements based on selector value match and condition match. CASE statement based on selector match is called simple case and syntax for this type is as follows. Here selector value is matched with selector_1, then statement_1 is executed otherwise it matches with other selector and finally if does not matches any selector ELSE is executed, if available.
CASE selector WHEN selector_value_1 THEN statements_1 WHEN selector_value_2 THEN statements_2 ... WHEN selector_value_n THEN statements_n [ ELSE else_statements ] END CASE;]
and CASE statement defined based on condition match is termed as searched case and syntax of this is follows.Here we do not have selector, one by one condition_1, condition_2 is executed and which one gets evaluated true first corresponding statement is executed, and remaining conditions are not evaluated. If none of conditions is evaluated to true then else is executed , if available.
CASE WHEN condition_1 THEN statements_1 WHEN condition_2 THEN statements_2 ... WHEN condition_n THEN statements_n [ ELSE else_statements ] END CASE;]
Here grade is initialized with value 'B', when program executed first condition is evaluated and it returns false, so next condition is evaluated and it returns true, Very Good is obtained as output.
LOOP StatementsLoop statements are used to execute a PL/SQL statement/s multiple times base on certain condition. Loop statements can be of different types and its features are as follows :
- Different varieties of Loop are : Basic LOOP, FOR LOOP, CURSOR LOOP, WHILE LOOP
- Loop cannot be run forever , it need some exit or terminating statements which are :
EXIT, EXIT THEN
- Some times we need to stop current iteration/skip current iteration and continue next iteration, in such case we use CONTINUE and CONTINUE WHEN statement.
- LOOP statements can be labeled, and LOOP statements can be nested.It is mandatory to match label in the END LOOP statement to the label at the beginning of the same loop statement (the compiler does not check).
- Assigning a label to LOOP is optional and it is recommended to add it in nested looping to improve readability. <<LABEL_NAME>> is used to label LOOP(both << and >> are part of syntax.)
[ label ] LOOP statements END LOOP [ label ];
========Output of above program========
Print even number from 1 to 10
Exiting loop now, reached count up to 10
We can modify above program with EXIT WHEN statement and it length shorten and becomes more readable. Just comment inner IF statement (form IF to END IF) and replace it with
EXIT WHEN even_Count >=10
CONTINUE and CONTINUE WHEN can also be use in PL/SQL. Below is the sample program to print multiple of 5 between 1 to 30 and it should not be even number.
In above program, we starts from 1, check divisibility by 5 using REMAINDER function. If it is divisible by 5 then check for divisibility by 2. If given number is divisible by 2 then CONTINUE WHEN returns control back to LOOP and start same process with incremented value.
Print non even number which is multiple of 5
FOR LOOP Statement executes statements until the loop index is in a specified range. Consider the FOR LOOP syntax and sample program to illustrate structure of the syntax.
[ label ] FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP statements END LOOP [ label ];
Notes: Some important about index and lower/upper bound in FOR LOOP:
- index is just a variable (any valid identifier) and sometimes index is also termed as loop counter.
- The index of a FOR LOOP statement is implicitly declared as a variable of type PLS_INTEGER that is local to the loop.
- The statements in the loop can read the value of the index, but cannot modify it.
- FOR LOOP Statement Index with Same Name as Variable : If index name is same as a variable declared in DECLARE block then local declaration hides the global declaration.
- Lower Bound and Upper Bound value should be of numeric type(literal, variable) or numeric expressions, otherwise PL/SQL will throw VALUE_ERROR exception.
- Accessing references Variable with Same Name as Index: Using TO_CHAR function
TO_CHAR(<block_name>.i) -- Qualify reference with block label.
In above program, global i is accessed via block_name[dot]variable name.
Print odd number between 1 to 10 in reverse order
9, 7, 5, 3, 1,
In FOR LOOP also EXIT,EXIT WHEN, CONTINUE and CONTINUE WHEN can be used as we saw in Basic LOOP earlier.
FOR LOOP is most commonly used with cursor type, for sake of completeness I am writing sample code for the same, it can be ignored as of now, we will revisit it again.
1 City is Southlake
2 City is South San Francisco
3 City is South Brunswick
WHILE LOOP Statement runs one or more statements while a condition is true. It has following structure:
[ label ] WHILE condition LOOP statements END LOOP [ label ];
Sequential Control StatementsGOTO Statement and NULL Statement belong to this category. It is recommended not to use GOTO statements even tough it makes life easy, it degrade readability.
GOTO Statement - The GOTO statement transfers control to a label unconditionally.
NULL Statement - It just passes control to next statement and termed as no operation statements. It is used to : To create placeholders and stub subprograms and To improve readability by making the meaning and action of conditional statements clear.
|Invalid anonymous PL/SQL block - Body cannot be empty||Valid anonymous PL/SQL block - NULL; makes it valid|
BEGIN -- nothing is body,
NULL; -- No-op,makes program valid one,
In next post we will learn PL/SQL Composite data type - Collections & Record and followed by reference types - cursors.
Previous: PL/SQL- Language fundamentals and Data Types Next: PL/SQL Composite data type