CONTROL STRUCTURES IN PL/SQL:

a) To write programs that reflect the real time requirement, we need:
         1) BRANCHING
         2) SELECTION
         3) LOOPING
  1) BRANCHING STATEMENTS:

              i) In PL/SQL branching is implemented using the IF statements.
              ii) PL/SQL provides three types of conditional control.
                        * Simple IF  * ELSEIF  * ELSE IF
              iii) The branching standards can be implemented either as
                        * Simple IF
                        * Nested IF
                        * ELSE IF ladders
                  
                    GENERAL SYNTAX:
                           IF Condition1 THEN
                                    Statement1;
                                    Statement2;
                                         :
                                         :
                           END IF;
              iv) IF is a reversed word and marks the beggning of the IF statements.
              v) The END IF is a reversed phrase that indicates the end of the IF..THEN construct.
              vi) When IF..THEN is executed, a condition is evaluated to either TRUE or FALSE.

                Example:
                  SQL > DECLARE
                              V_Number1 NUMBER:=5;
                              V_Number2 NUMBER:=3;
                              V_Temp  NUMBER;
                        BEGIN
                              DBMS_OUTPUT.PUT_LINE('Oriinal V_Number1='||V_Number1);
                              DBMS_OUTPUT.PUT_LINE('Oriinal V_Number2='||V_Number2);
                          IF V_Number1>V-Number2 THEN
                                     V_TEMP:=V_Number1;
                      V_Number1:=V_Number2;
                         V_Number2:=V_TEMP;
                              END IF;
                   /* Print the values from variable which are swapped */
                        END;


         ->IF-THEN-ELSE STATEMENT:
                i) IF-THEN-ELSE statements enables to specify two groups of statements.
                ii) One group is evaluated when the condition evaluates to TRUE, the next group is evaluated when the condition evaluates to FALSE.
                   
                      SYNTAX:
                              IF Condition THEN
                                      Statement 1;
                              ELSE
                                      STATEMENT 2;
                              END IF;
               iii) This concept should be used when trying to choose between two mutually exclusive actions.
                      Example:
                                DECLARE
                                       V_Num NUMBER:= &ENTERNumber;
                                BEGIN
                                       IF MOD(V_Num,2)=0 THEN
                                           DBMS_OUTPUT.PUT_LINE(V_Num||'Is Even');
                                       ELSE
                                           DBMS_OUTPUT.PUT_LINE(V_Num||'Is Odd');
                                       END IF;
                                END;

         ->BEHAVIOR OF NULL'S:
                i) If a condition is evaluated to NULL, then the statements will not be executed, instead the control will be passed to the first executable statement after the END IF,
                ii) In IF...THEN...ELSE construct the FALSE block is executed whenever the condition evaluates to NULL.
                          QL > DECLARE
                                      V_Num1 NUMBER:=&EnterNumber1;           
                                      V_Num2 NUMBER:=&EnterNumber2;
                               BEGIN
                                      IF V_Num1=V_Num2 THEN
                                           DBMS_OUTPUT.PUT_LINE('Given Number are Equal');
                                      END IF;
                                           DBMS_OUTPUT.PUT_LINE('Did You Watch the NULL Effect.......');                    
                               END;
                               DISCUSS: EVENODD2.SQL
         ->ELSEIF STATEMENTS:
      
                   Syntax:
                           IF Condition1 THEN
                                 Statement1;
                           ELSEIF Condition2 THEN
                                 Statement2;
                           ELSEIF Condition3 THEN
                                 Statement3;
                                     :
                                     :
                           ELSE
                                 Statement n;
                           END IF;
                  i) In this construct for every false statement of an IF condition another IF Follows.
                  ii) This construct is also specified as ELSEIF ladders.
                  iii) The application syntax can also be produced with ELSE if condition structure.
                  iv) For clarity purpose rather than ELSEIF, ELSE IF construct is suggested.
          
                         Example:            \Ifstatements\simpleIF2.sql
                               
                                  SQL > DECLARE
                                              V_Day VARCHAR2(3):=TO_CHAR(TO_DATE('& YourDate', 'DD-MON-YY'), 'DY');
                                        BEGIN
                                           IF V_Day='SAT' then
                                              DBMS_OUTPUT.PUT_LINE('Enjoy Weekend');
                                           ELSEIF V_Day='SUN' then
                                              DBMS_OUTPUT.PUT_LINE('Enjoy Weekend');
                                           ELSE
                                              DBMS_OUTPUT.PUT_LINE('Have a nice day');
                                           END IF
                                           END; 
          -> NESTED IF STATEMENTS:
 
                   i) The IF statements can be nested into one another as per requirements.
                   ii) NESTED IF is a situation in which an IF follows another IF immediately for every true statement of an IF.
                   iii) Each IF is considered as an individual Block of IF and needs proper nesting.
   
                            Syntax:
                                    IF Condition THEN
                                         IF Condition THEN
                                                Statement1;
                                         ELSE
                                             IF Condition THEN
                                                    Statement2;
                                             ELSE
                                                    Statement2;
                                             END IF;
                                        END IF;
                                    ELSE
                                        Statement3;
                                    END IF;
     
                              Example:  \IFStatements\Nestedif5.sql
                                  
                                     SQL > DECLARE
                                                  V_Year NUMBER:=&YEAR;
                                           BEGIN
                                                  IF MOD(V_Year,4)=0 THEN
                                                       IF MOD(V_Year,100)=0 THEN
                                                            DBMS_OUTPUT.PUT_LINE(V_Year||'is a Leap Year');
                                                       ELSE
                                                          IF MOD(V_Year,400)=0 THEN
                                                               DBMS_OUTPUT.PUT_LINE(V_Year||'is a Leap Year');
                                                          ELSE
                                                               DBMS_OUTPUT.PUT_LINE(V_Year||'is Not a Leap Year');
                                                          END IF;
                                                       END IF;
                                                  ELSE
                                                     DBMS_OUTPUT.PUT_LINE(V_Year||'is Not a Leap Year');
                                                  END IF;
                                           END;

            -> ELSE IF LADDER:
                      
                         i) This is a situation where a condition is followed for every FALSE statement of an IF Condition.
                         ii) Each FALSE statement is immediately associated with a condition of its own.

                              Syntax:
                                     IF Condition THEN
                                                Statement1;
                                     ELSEIF Condition THEN
                              Statement2;
                        ELSEIF Condition THEN
                        Statement3;
                        ELSE
                         Statement4;
                    END IF;

                       Note:  Do not mistake this syntax with construt of : IF Cond THEN
                                         :
                                         ELSE
                                             IF Condition THEN
                                                 :
                  Example:  \IF Statements\ELSEIFLadder.sql
                        
                               DECLARE
                                     V_Operator VARCHAR2(2):='&Operator';
                                     V_Number1 NUMBER :=&Operator1;
                                     V_Number2 NUMBER :=&Operator1;
                               BEGIN
                                     IF V_Operator ='+' THEN
                                        DBMS_OUTPUT.PUT_LINE(TO_NUMBER(V_Number1+V_Number(2));                             
                                     ELSEIF V_Operator ='-' THEN                                    
                                        DBMS_OUTPUT.PUT_LINE(TO_NUMBER(V_Number1-V_Number(2));
                                     ELSEIF V_Operator ='*' THEN
                                        DBMS_OUTPUT.PUT_LINE(TO_NUMBER(V_Number1*V_Number(2));
                                     ELSEIF V_Operator ='/' THEN
                                        DBMS_OUTPUT.PUT_LINE(TO_NUMBER(V_Number1/V_Number(2));
                                     ELSE
                                        DBMS_OUTPUT.PUT_LINE('Check Opreator');
                                     ENDIF;
                               END;
                             NOTE:  The same program can be written using IF...THEN
                                                                          ELSE
                                                                          IF...THEN
   2)SELECTIONS IN PL/SQL:
 

             a) The SELECTION stratergy is implemented in PL/SQL   using CASE STATEMENTS.
             b) The CASE Statements in PL/SQL has two forms
                   i)  Simple CASE
                   ii) Searched CASE
             c) In Simple CASE we have to specify a SELECTOR, which detemines which group of actions to get executed.
             d) In Searched Case, the SELECTOR is not present, it has search conditions that are evaluated in order to determine which group of actions to take place.
       
                     Simple CASE Syntax:

                                      CASE SELECTOR
                                                WHEN Expr1 THEN Statement1;
                                                WHEN Expr2 THEN Statement2;
                                                             :
                                                ELSE
                                                       Statement n;
                                       END CASE;
             e) The Reserved word CASE marks the begning of the CASE statement.
             f) The SELECTOR is a value that determines, which WHEN clause should be executed.
             g) Each WHEN clause contains are EXPRESSION and One or more executable statements associated with it.
             h) The ELSE clause is optional.
             i) Each CASE statement is marked with END CASE.
             j) The SELECTOR is evaluated only once.
                    Example: \Case statements\Simple CASE user3.sql

                          SQL >  DECLARE
                                       V_Grade CHAR:= UPPER('&EnterGrade');
                                 BEGIN
                                    CASE V_Grade
                                        WHEN 'A' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade A');
                                        WHEN 'B' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade B');
                                        WHEN 'C' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade C');
                                        ELSE
                                             DBMS_OUTPUT.PUT_LINE(' No Grade');
                                    END CASE;
                                 END;
         ->SEARCHED CASE:
                  a) A SEARCHED CASE statement has searched conditions that yield Boolean values TRUE, FALSE or NULL.
                  b) When a particular search condition evaluates to TRUE, the group of statements associated with this condition are executed.

                           Syntax:
                                   CASE
                                   WHEN  SearchCondition THEN Statement1;
                                   WHEN  SearchCondition THEN Statement2;
                                                        :
                                   END StatementN;
                                   END CASE;
                  c) When a SEARCH CONDITION evaluates to TRUE, Control is passed to the statement associated with it.
                  d) If no SEARCH CONDITION yields to TRUE, then statements associated with ELSE caluse are executed.
     
                           Example:
                                 SQL >  DECLARE
                                       V_Grade CHAR:= UPPER('&EnterGrade');
                                 BEGIN
                                    CASE
                                        WHEN V_Grade='A' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade A');
                                        WHEN V_Grade='B' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade B');
                                        WHEN V_Grade='C' THEN
                                             DBMS_OUTPUT.PUT_LINE('Grade C');
                                        ELSE
                                             DBMS_OUTPUT.PUT_LINE(' No Grade');
                                    END CASE;
                                 END;                                              
                                       
          -> CASE EXPRESSION:
                  a) The result of an expression yields a single value that is assigned to a variable.
                  b) A CASE Expression is an expression where the CASE evaluated an expression to a single value and then it si assigned to a variable.
                  c) A CASE expression has a structure similar to a CASE statement.

                 
                     DIFFERENCE TO NOTE......
                  
                           CASE STATEMENT                      CASE EXPRESSION
                
                           CASE V_Flag                   CASE V_Flag
                             WHEN 0 THEN                       WHEN 0 THEN
                               DBMS_OUTPUT                             V_Num ||'is even'
                               PUT_LINE('PRINT')
                             ELSE                               ELSE
                               DMBS_OUTPUT                           V_Num ||'is odd'
                               PUT_LINE('PRINT')
                           END CASE;                END;
                  d) In CASE expression the END CASE is not implemented, instead only END, is used.
                  e) If any value is returned through CASE expression, then ensure that it always return the same datatype as executed.

                          Example:  \Casestatements\CaseExpr.Sql
 
                              Sql>DECLARE
                                      V_NumberNUMBER:=&NUMBER;
                                      V_Result VARCHAR2(30);
                                  BEGIN
                                      V_RESULT:= CASE WHEN MOD(V_Number,2)=0
                                                 THEN
                                                    V_Number||'is an Even Number'
                                                 ELSE
                                                    V_Number||'is an Odd Number'
                                                 END;
                                      DBMS_OUTPUT.PUT_LINE(V_Result);
                                  END;

                           Discuss:   \CaseStatements\CaseExpr6.sql

           -> UNCONDITIONAL BRANCHING USING GOTO:

                   a) GOTO provides the ability to jump through a program from one place to another.
                   b) GOTO can be used with complex exception handlers in nested blocks, to make the execution section more readable.
                   c) It is better to have a limited usage of GOTO in programming block.
                   d) When a jump is proposed using GOTO it is assocoated with an appropriate label.

                           Syntax:    GOTO LabelName;
                            
                           RULES TO CROSS CHECK:
                                   a) GOTO cannot reference a label in a Nested Block.
                                   b) GOTO cannot be executed outside an IF clause to label inside the IF clause.
                                   c) GOTO cannot be executed from inside an IF clause to a label inside another IF clause.
                                   d) GOTO cannot navigate from the EXCEPTION section to any other section of the block.

                              Example:
                                     SQL > DECLARE
                                                V_MyNumber NUMBER:=&GiveNumber;
                                           BEGIN
                                                IF MOD(V_MyNumber,2)=0 THEN
                                                       GOTO L_EVEN;
                                                ELSE
                                                       GOTO L_ODD;
                                                END IF;
                                             
                                                <>
                                                       DBMS_OUTPUT.PUT_LINE('Even Number');
                                                       RETURN;

                                                <>
                                                       DBMS_OUTPUT.PUT_LINE('Odd Number');
                                                       RETURN;
                                          END;

                  e) GOTO always needs a proper label to jump.
                  f) A Label does not need any GOTO for exception.
                  g) Hence to keep proper meaning within the sequence RETURN should be used .