Develop the following PL/SQL programs • Simple PL/SQL programs • PL/SQL programs Using Control structures. o Conditional structures o Iterative structures • PL/SQL program using the following exception handling mechanisms. o Pre defined exceptions o user defined exceptions.

 

EXERCISE : 11

AIM : Develop the following PL/SQL programs
• Simple PL/SQL programs
• PL/SQL programs Using Control structures.
       o Conditional structures                     o Iterative structures
• PL/SQL program using the following exception handling mechanisms.
       o Pre defined exceptions                    o user defined exceptions.

Description :

Simple PL/SQL programs .:

1. Aim:-Sum of two numbers

Sourcecode:-

SQL> declare

                        x integer;

                        y integer;

                        z integer;

            begin

                        x:=10;

                        y:=20;

                        z:=x+y;

            dbms_output.put_line('sum is' ||Z);

            end;

            /

Output:-

sum is30

PL/SQL procedure successfully completed.

2.                  Aim:-Sum of two numbers reading input from user

Sourcecode:-

SQL> declare

                        x integer;

                        y integer;

                        z integer;

            begin

                        x:=&x;

                        y:=&y;

                        z:=x+y;

            dbms_output.put_line(x||'+'||y||'='||z);

            end;

            /

Output:-

Enter value for x: 2

old   6: x:=&x;

new   6: x:=2;

Enter value for y: 2

old   7: y:=&y;

new   7: y:=2;

2+2=4

PL/SQL procedure successfully completed.

 

PL/SQL programs using control structures :

o   Conditional structures :

IF-THEN-ELSIF statement

1. Aim:-Greatest of three numbers

Sourcecode;-

SQL> declare

                        a number:=46;

                        b number:=67;

                        c number:=21;

            begin

                        if  a>b and a>c then

                        dbms_output.put_line('greatest number is'||a);

                        elsif b>a and b>c then

                        dbms_output.put_line('greatest number is'||b);

                        else

                        dbms_output.put_line('greatest number is'||c);

                        end if;

            end;

            /

 

OUTPUT:-

greatest number is67

PL/SQL procedure successfully completed.

 

CASE

DECLARE  

   grade char(1) := 'C';  

BEGIN  

   CASE grade  

      when 'A' then dbms_output.put_line('Distinction');  

      when 'B' then dbms_output.put_line('First class');  

      when 'C' then dbms_output.put_line('Second class');  

      when 'D' then dbms_output.put_line('Pass class');    

      else dbms_output.put_line('Failed');  

   END CASE;  

END;  

/

o    Iterative structures :

                   Exit loop.

DECLARE  

            i NUMBER := 1;  

BEGIN  

LOOP  

            EXIT WHEN i>5;  

            dbms_output.put_line(i);  

            i := i+1;  

            END LOOP;  

END;

1

2

3

4

5

 

PL/SQL procedure successfully completed.

While loop

1. AIM:-SUM OF EVEN NUMBERS USER INPUT DYNAMICALLY

SOURCODE:-

SQL> declare

                        x integer:=2;

                        y integer;

                        s integer:=0;

            begin

                        y:=&y;

                        while x<=y loop

                                    dbms_output.put_line(x);

                        s:=s+x;

                        x:=x+2;

                        end loop;

            dbms_output.put_line('sum of even numbers is' || s);

            end;

            /

Output:-

Enter value for y: 10

old   6:    y:=&y;

new   6:    y:=10;

2

4

6

8

10

sum of even numbers is30

PL/SQL procedure successfully completed.

 

 

 

 

 

For loop

1. Aim:-TO PRINT NATURAL NUMBERS

Sourcecode:-

SQL> declare

                        a integer;

            begin

                        for a in 10 .. 20 loop

                        dbms_output.put_line('value of a:'||a);

                        end loop;

                        end;

                        /

Output:-

value of a:10

value of a:11

value of a:12

value of a:13

value of a:14

value of a:15

value of a:16

value of a:17

value of a:18

value of a:19

value of a:20

 

PL/SQL procedure successfully completed.

 

PL/SQL GOTO Statement

DECLARE  

   a number(2) := 50;  

BEGIN  

   <<loopstart>>  

   -- while loop execution   

   WHILE a < 60LOOP  

      dbms_output.put_line ('value of a: ' || a);  

      a := a + 1;  

      IF a = 55 THEN  

         a := a + 1;  

         GOTO loopstart;  

      END IF;  

   END LOOP;  

END;  

/

Output:

value of a: 50

value of a: 51

value of a: 52

value of a: 53

value of a: 54

value of a: 56

value of a: 57

value of a: 58

value of a: 59

 

PL/SQL program using the following exception handling mechanisms .

 

·         Exceptions are runtime errors or unexpected events that occur during the execution of a PL/SQL code block.

·         The oracle engine is the first one to identify such an exception and it immediately tries to resolve it by default exception handler.

·         The default exception handler is a block of code predefined in the memory to take the appropriate action against exceptions.

• Exception handling can be done in the EXCEPTION part of PL/SQL program code block.

Following is the syntax for it:

 

DECLARE

            -- Declaration statements;

BEGIN

            -- SQL statements;

            -- Procedural statements;

EXCEPTION

            -- Exception handling statements;

END;

 

There are two types of exceptions:

System (pre-defined) Exceptions

User-defined Exceptions

Let's cover both types of exceptions one by one.

In order to handle common exceptions that occur while running PL/SQL code, there are two types of exception handlers in oracle:

·         Named Exception Handler

·         Numbered Exception Handler

Named Exception Handling

Such exceptions are the predefined names given by oracle for those exceptions that occur most commonly.

Following is the syntax for handling named exception:

EXCEPTION

            WHEN<exception_name>THEN

Named Exception	Meaning
LOGIN_DENIED	Occurs when invalid username or invalid password is given while connecting to Oracle.
TOO_MANY_ROWS	Occurs when select statement returns more than one row.
VALUE_ERROR	Occurs when invalid datatype or size is given by the user.
NO_DATA_FOUND	Occurs when no records are found.
DUP_VAL_ON_INDEX	Occurs when a unique constraint is applied on some column and execution of Insert or Update leads to creation of duplicate records for that column.
PROGRAM_ERROR	Occurs when internal error arise in program.
ZERO_DIVIDE	Occurs when the division of any variable value is done by zero.

                        -- take action

There are number of pre-defined named exceptions available by default. Few of them are shown in the table below, along with their meanings:

 

Below we have a simple PL/SQL code block, to demonstrate the use of Named Exception Handler,

set serveroutput on;

DECLARE

            a int;

            b int;

            c int;

BEGIN

            a := &a;

            b := &b;

            c := a/b;

            dbms_output.put_line('RESULT=' || c);

EXCEPTION

            when ZERO_DIVIDE then

                        dbms_output.put_line('Division by 0 is not possible');

END;

Output:-

Enter the value for a:10

Enter the value for b:0

Division by 0 is not possible

PL/SQL procedure successfully completed.

 

Numbered Exception Handling

In oracle, some of the pre-defined exceptions are numbered in the form of four integers preceded by a hyphen symbol. To handle such exceptions we should assign a name to them before using them. This can be done by using the Pragma exception technique in which a numbered exception handler is bound to a name. For this purpose, we use a keyword in PL/SQL program and write a statement that binds a name to a numbered exception using the following syntax and this statement is written in the DECLARE section of program:

pragma exception_init(exception_name, exception _number);

 

where, pragma exception_init(case doesn't matter) is a keyword indicating Pragma exception technique with two arguments:

exception_name, which is a user-defined name given to a predefined numbered exception if it occurs.

exception_number, is the number allotted to the exception by oracle.

Below we have a table with Student's data in it.

ROLLNO	SNAME	AGE	COURSE
11	Anu	20	BSC
12	Asha	21	BCOM
13	Arpit	18	BCA
14	Chetan	20	BCA
15	Nihal	19	BBA

 

In the PL/SQL program below, we will be using the above table student to demonstrate the use of Numbered Exception,

set serveroutput on;

DECLARE

            sno student.rollno%type;

            snm student.sname%type;

            s_age student.age%type;

            cr student.course%type;

            -- Exception name declared below

            already_exist EXCEPTION;

            -- pragma statement to provide name to numbered exception

            pragma exception_init(already_exist, -1);

BEGIN

            sno:=&rollno;

            snm:='&sname';

            s_age:=&age;

            cr:='&course';

            INSERT into student values(sno, snm, s_age, cr);

            dbms_output.put_line('Record inserted');

            EXCEPTION

                        WHEN already_exist THEN

                                    dbms_output.put_line('Record already exist');

END;

 

Enter the value for sno:11

Enter the value for snm:heena

Enter the value for s_age:20

Enter the value for cr:bsc

Record already exist

PL/SQL procedure successfully completed.

 

Description: In the above program, whenever a primary key concept(records should be unique and not null) is violated oracle generates a numbered exception by -1 and that is why when rollno entered by user during execution of above program was 11. The exception section of the program comes into action and message is displayed before the user Record already exist.

Using pragma keyword in the declare section of the program already_exist string is mapped to a numbered exception -1.

User-defined Exception

In any program, there is a possibility that a number of errors can occur that may not be considered as exceptions by oracle. In that case, an exception can be defined by the programmer while writing the code such type of exceptions are called User-defined exception. User defined exceptions are in general defined to handle special cases where our code can generate exception due to our code logic.

Also, in your code logic, you can explicitly specify to genrate an exception using the RAISE keyword and then handle it using the EXCEPTION block.

Following is the syntax for it,

DECLARE

            <exception name> EXCEPTION

BEGIN

            <sql sentence>

            If <test_condition> THEN 

                        RAISE <exception_name>;

            END IF;

            EXCEPTION

                        WHEN <exception_name> THEN

                                    -- some action

END;

Let's take an example to understand how to use user-defined exception. Below we have a simple example,

 

ROLLNO	SNAME	Total_Courses
11	Anu	2
12	Asha	1
13	Arpit	3
14	Chetan	1

 

In the PL/SQL program below, we will be using the above table student to demonstrate the use of User-defined Exception,

 

 

 

 

set serveroutput on;

 

DECLARE

            sno student.rollno%type;

            snm student.sname%type;

            crno student.total_course%type;

            invalid_total EXCEPTION;

BEGIN

            sno := &rollno;

            snm := '&sname';

            crno:=total_courses;

            IF (crno > 3) THEN 

                        RAISE invalid_total;

            END IF;

            INSERT into student values(sno, snm, crno);

            EXCEPTION

                        WHEN invalid_total THEN

                                    dbms_output.put_line('Total number of courses cannot be more than 3');

END;

Output:

Enter the value for sno:15

Enter the value for snm:Akash

Enter the value for crno:5

Total number of courses cannot be more than 3

PL/SQL procedure successfully completed.

 

User-defined exception called invalid_total is used which is generated when total number of courses is greater than 3(when a student can be enrolled maximum in 3 courses)

 

VIVA QUESTIONS

1. What is PL/SQL?

2. What is Exception Handling?