PL/SQL - Conditions

Complete Tutorial

Conditional Statements – IF
  • The selection structure tests a condition, then executes one sequence of statements instead of another, depending on the condition
  • There are three forms of statements
    • IF-THEN
    • IF-THEN-ELSE
    • IF-THEN-ELSIF
  • Sequence of statements is executed only if the condition evaluates to TRUE
  • If condition evaluates to FALSE or NULL, it does nothing
  • In either case control passes to next statement after the IF-THEN structure
IF <condition> THEN
statements;
END IF;
  • Sequence of statements in the ELSE clause is executed only if the condition evaluates to FALSE or NULL
IF <condition> THEN
statements;
ELSE
statements;
END IF;
--------------------------------------------------------------------------------
declare
   /*Simple if condition */
    x number;
  begin
        x :=  &x;
        if x >= 35 then
        dbms_output.put_line('Passed');
         else
          dbms_output.put_line('Failed');
         end if;
end;
-----------------------------------------------------

IF-THEN-ELSIF Structure

  • This construct allows selection of action from several mutually exclusive alternatives
  • The IF statement can have any number of ELSIF clauses
  • The final ELSE is optional
  • Conditions are evaluated one by one from top to bottom
 Syntax

IF <condition1> THEN
statements;
ELSIF <condition2> THEN
statements;
ELSIF <condition3> THEN
statements;
ELSE
statements;
END IF;

Example 1 –

Declare
     y number;
     /*Multiple ifs */
 Begin
                    y := &y;
                
             if y >= 70 then
              dbms_output.put_line('Distinction');
             elsif y >= 60 then
              dbms_output.put_line('First class');
              elsif y >= 50 then
              dbms_output.put_line('Second class');
              elsif y >= 35 then
              dbms_output.put_line('Passed');
              else
              dbms_output.put_line('Failed');
              end if;
end;

Example 2 

 create table adm
(Name varchar2(30),
Marks number(3),
 College varchar2(30),
 Fees number(5));

/*Use of multiple if's
    Accept name and marks from user.
    Depending upon marks entered the college and fees should be decided
    and the record should be entered in the adm table.*/

Declare
     n adm.name%type;
     m adm.marks%type;
     c adm.college%type;
     f adm.fees%type;

Begin
         n := '&n';
         m := &m;

            if m >= 95 then
                c  :=  'COEP';
                 f :=   10000;
           elsif  m >= 90 then
                c :=  'MIT';
                f := 15000;
            elsif  m >= 85 then
                c := 'VIT';
                f := 22000;
            elsif   m >= 80 then
                c :=  'D Y Patil';
                f := 27000;
            elsif   m >= 75 then
                c := 'Pune Vidyarthi';
                f := 33000;
              else
                   dbms_output.put_line('Cannot get admission');
         end if;

            if c is not null and f is not null then
            dbms_output.put_line('Your College is  '|| c || '  and fees are ' || f);
              Insert into adm
                values(n,m,c,f);
                  commit;
              end if;

end;


Nested Blocks


declare
 x number;

begin
 x := 80;
dbms_output.put_line(‘abc’);
                         declare
                         y number;
                        begin
                        y := 90;
                        dbms_output.put_line('Inner Block variable value ' || y);
                        end;

dbms_output.put_line('Outer Block variable value ' || x);
end;


Scope of variables

A variable declared in the outer block is accessible in the inner block. But a variable declared in the inner block is accessible only in the inner block.

declare
 outer number;
begin
 outer := 80;
                        declare
                          inner number;
                         begin
                         inner := 90;
                        dbms_output.put_line('Inner Block variable value ' || inner);
                        dbms_output.put_line('Outer block variable is accessible in the inner
                        block’);
                        dbms_output.put_line('Outer block variable value ' || outer);  
                        end;

dbms_output.put_line('Outer Block variable value ' || outer);
dbms_output.put_line('Inner Block variable value ' || inner);
end;
/

Labels

If the variables names of the outer and inner blocks are same then labels have to be used within the inner block to avoid ambiguity.


<<outer_block>>
declare
 x number;
begin
<<inner_block>> 
declare
     x  number := 100;
  begin
     dbms_output.put_line('Value of the inner block x is ' || x);
    -- Giving value of x of the inner block to the outer block x
     outer_block.x :=inner_block. x;
  end;

  x := x + 500;
dbms_output.put_line('Value of the outer block x is ' || x);
end;
/


<<<Previous                                                                                                                                                                                  Next>>>

Complete Tutorial 

*/

No comments:

Post a Comment