EXPLAIN CONTROL STRUCTURE IN PL/SQL

The flow of control statements can be classified into the following categories:

Ø  Conditional Control

Ø  Iterative Control

Ø  Sequential Control

Conditional Control

PL/SQL allows the use of an IF statement to control the execution of a block of code. In PL/SQL, the IF - THEN - ELSIF - ELSE - END IF construct in code blocks allow specifying certain conditions under which a specific block of code should be executed.

Syntax:

IF < Condition > THEN

           

ELSIF THEN

           
ELSE

< Action >

END IF;

Example:

Write a PL/SQL code block that will accept an account number from the user, check if the users balance is less than the minimum balance, only then deduct Rs.100/- from the balance. The process is fired on the ACCT_MSTR table.

DECLARE

mCUR_BAL number(ll,2);

mACCT_NO varchar2(7);

mFINE number(4) := 100;

mMIN_BAL constant number(7,2) := 5000.00;

BEGIN

mACCTJSTO := &mACCT_NO;

SELECT CURBAL INTO mCUR_BAL FROM ACCT_MSTR

WHERE ACCT_NO= mACCT_NO;

IF mCUR_BAL < mMIN_BAL THEN

UPDATE ACCT_MSTR SET CURBAL = CURBAL - mFINE

WHERE ACCT_NO = mACCT_NO;

END IF;

END;

Output:

Enter value for macct_no: 'SB9'

OLD :   mACCT_NO := &mACCT__NO;

NEW : mACCTNO : = 'SB9'

PL/SQL procedure successfully completed.

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 a 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

End loop;

Example :

Create a simple loop such that a message is displayed when a loop exceeds a particular value.

DECLARE

i number := 0;

BEGIN

LOOP

i := i + 2;

EXIT WHEN i> 10;

END LOOP;

dbms_output.put_line('Loop exited as the value of i has reached' || to_char(i));

END;

Output:

Loop exited as the value of i has reached 12

PL/SQL procedure successfully completed.

The WHILE loop

Syntax:

WHILE

LOOP

< Action >

END LOOP;

Example:

Write a PL/SQL code block to calculate the area of a circle for a value of radius varying from 3 to 7. Store the radius and the corresponding values of calculated area in an empty table named Areas, consisting of two columns Radius and Area.

Table Name: Areas

RADIUS	AREA

Create the table AREAS as:

CREATE TABLE AREAS (RADIUS NUMBER(5), AREA NUMBER(14,2));

DECLARE

pi constant number(4,2) := 3.14 ;

radius number(5);

area number(14,2);

BEGIN

radius := 3;

WHILE RADIUS <= 7

LOOP

area :=pi * power(radius,2);

INSERT INTO areas VALUES (radius, area);

radius := radius + 1;

END LOOP;

END;

The above PL/SQL code block initializes a variable radius to hold the value of 3. The area calculations are required for the radius between 3 and 7. The value for area is calculated first with radius 3, and the radius and area are inserted into the table Areas. Now, the variable holding the value of radius is incremented by 1, i.e. it now holds the value 4. Since the code is held within a loop structure, the code continues to fire till the radius value reaches 7. Each time the value of radius and area is inserted into the areas table.

After the loop is completed the table will now hold the following:

Table Name: Areas

RADIUS	AREA
3	28.26
4	50.24
5	78.5
6	113.04
7	153.86

The FOR Loop

Syntax:

FOR variable IN [REVERSE] start..end

LOOP

< Action >

END LOOP;

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.

Example :

Write a PL/SQL block of code for inverting a number 5639 to 9365.

DECLARE

given_number varchar(5) := '5639';

str_length number(2);

inverted_number varchar(5);

BEGIN

str_length := length(given_number);

FOR cntr IN REVERSE l..str_length

LOOP

inverted_number := inverted_number || substr(given_number, cntr, 1);

END LOOP;

dbms_output.put_line (The Given number is ' || givenjiumber);

dbms_output.put_line (The Inverted number is ' || inverted_number);

END;

Output:

The Given number is 5639 The Inverted number is 9365

The above PL/SQL code block stores the given number as well its length in two variables. Since the FOR loop is set to repeat till the length of the number is reached and in reverse order, the loop will fire 4 times beginning from the last digit i.e. 9. This digit is obtained using the function SUBSTR, and stored in a variable. The loop now fires again to fetch and store the second last digit of the given number. This is appended to the last digit stored previously. This repeats till each digit of the number is obtained and stored.

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 <>. The GOTO statement can then make use of this user-defined name to jump into that block of code for execution.

Syntax:

GOTO ;

Example :

Write a PL/SQL block of code to achieve the following: If there are no transactions taken place in the last 365 days then mark the account status as inactive, and then record the account number, the opening date and the type of account in the INACTV_ACCT_MSTR table.

Table Name: INACTV_ACCT_MSTR

ACCT NO	OPNDT	TYPE

Create the table INACTV_ACCT_MSTR as:

CREATE TABLE INACTV_ACCT_MSTR (

ACCT_NO VARCHAR2(10), OPNDT DATE, TYPE VARCHAR2(2));

DECLARE

mACCT_NO VARCHAR2(10);

mANS VARCHAR2(3);

mOPNDT DATE;

mTYPE VARCHAR2(2);

BEGIN

            mACCT_NO := &mACCT_NO;

SELECT 'YES' INTO mANS FROM TRANS JMSTR

WHERE ACCT_NO = mACCT_NO

GROUP BY ACCT_NO HAVING MAX(SYSDATE - DT) >365;

IF mANS = 'YES' THEN

GOTO mark_status;

ELSE

dbms_output.put_line('Account number: ' || mACCT_NO || 'is active');

END IF;

<>

UPDATE ACCTJVISTR SET STATUS = T WHERE ACCT_NO = mACCT_NO;

SELECT OPNDT, TYPE INTO mOPNDT, mTYPE FROM ACCT_MSTR

WHERE ACCT_NO = mACCT_NO;

INSERT INTO INACTV_ACCT_MSTR (ACCT_NO, OPNDT,TYPE)

VALUES (mACCT_NO, mOPNDT, mTYPE);

dbms_output.put_line(‘Account number: ‘ || mACCT_TO || 'is marked as inactive');

END;

The PL/SQL code first fetches the Account number from the user into a variable mACCT_NO. It then verifies using an SQL statement, whether any transactions are performed within last 365 days.

If they are, then a message stating “Account Number ________ is active” is displayed.

But if there are no transactions performed in the last 365 days (i.e. 1 year) then a value "YES" is stored in a variable named mANS.

Based on the value held in this variable the ACCT_MSTR table is updated by setting the value held in the field STATUS to I.

This is followed by an insert statement, which inserts the account number the opening date and the type of that account in the INACTV_ACCT_MSTR table.

Finally a message stating "Account Number________ is marked as inactive” is displayed.