Conditional Statements in Oracle

Conditional Control :- PL/SQL allows the use of an IF statement to control the execution of a block of code. Sequence statements can be executed based on some conditional checking using the ‘If ’ statement. Three forms of if statements are.

            (1) If then

            (2) If then else

            (3) If then else…if. 

ðIf Then:-

* Syntax:-       If <condition> then

                                    < action >;

                        end if;

* Example:-   If a = 5 then

                                    DBMS_Output.Put_line (a);

                    End if;

ðIf Then Else:-

* Syntax:-    If <condition> then

                        <action>;

                        else

                                    <action>;

                        end if;

ð If Then Else If:-

* Syntax:-       If <condition> then

                                    <action>;

                        elsif <condition> then

                                    <action>;

                        elsif <condition> then

                                    <action>;

                        else

                                    <action>;

                        end if;

Iterative Control

Iterative control indicates the ability to repeat or skip sections of a code block. A loop marks a sequence of statements that has to be repeated. The keyword loop has to be placed before the first statement in the sequence of statements to be repeated, while the keyword end loop is placed immediately after the last statement in the sequence. Once loop begins to execute, it will go on forever. Hence a conditional statement that controls the number of times a loop is executed always accompanies loops.

PL/SQL supports the following structures for iterative control:

Simple Loop

In simple loop, the key word loop should be placed before the first statement in the sequence and the keyword end loop should be written at the end of the sequence to end the loop.

Syntax:

            Loop

                        <Sequence of statements>

            End loop; 

Example:-

clear screen

set serveroutput on

declare

            num number(3);

begin

            num:=1;

            loop

                        exit when num>5;

                        dbms_output.put_line(num);

                        num:=num+1;

            end loop;

end;

/

The WHILE loop

Syntax:

            WHILE <Condition>

            LOOP

                        <Action>

            END LOOP;

Example:-

clear screen

set serveroutput on

declare

            num number(3);

begin

            num:=1;

            while(num<=5)

            loop

                        dbms_output.put_line(num);

                        num:=num+1;

            end loop;

end;

/

The FOR Loop

Syntax:

            FOR variable I IN [REVERSE] start..end

            LOOP

                        <Action>

            END LOOP;

Example:-

1)declare

            i number(3);

begin

 for i in 1..10

 loop

  dbms_output.put_line(i);

 end loop;

end;

/

2)clear screen

set serveroutput on

declare

            i number(3);

begin

            for i in REVERSE 1..10

            loop

                        dbms_output.put_line(i);

            end loop;

end;

/

Note: The variable in the For Loop need not be declared. Also the increment value cannot be specified. The For Loop variable is always incremented by 1.

Sequential control 

The GOTO Statement

The GOTO statement changes the flow of control within a PL/SQL block. This statement allows execution of a section of code, which is not in the normal flow of control. The entry point into such a block of code is marked using the tags <<userdefined name>>. The GOTO statement can then make use of this user-defined name to jump into that block of code for execution.

Syntax:

            GOTO <codeblock name>; 

PL/SQL Attributes

Attributes allow us to refer to data types and objects from the database. PL/SQL variables and constants can have attributes. The following are the types of attributes supported by PL/SQL.

1. %type
2. %rowtype

%type - This attribute is used when declaring variables that refer to database column.

Eg.

            empname emp.ename%type;

Where empname is a variable name. emp is the name of the table and ename is the name of the column.

Advantages

·         We do not need to know the exact data type of the column.

·         If database definition of a column of a table changes then the data type of variable changes accordingly.

%rowtype – This attribute provides a record type that represents a row in the table. The record fetched can store entire row of data selected from the table or fetched by a cursor.

Eg.

            emprec emp%rowtype;

Example:-

set serveroutput on

set verify off

declare

            temprec emp%rowtype;

begin

            select * into temprec from emp where  empno=7369;

            dbms_output.put_line('Rollno' || temprec.empno);

            dbms_output.put_line('Name' || temprec.ename);

            dbms_output.put_line('Marks' || temprec.job);

end;

/

Advantages

·         Declaration for multiple variables is not required. Using only one variable name, different fields of a single record can be accessed.

·         We do not need to know the exact data type of column.

·         If database definition of a column of a table changes then the data type of variable changes according to that data type.