29. PL/SQL INTRODUCTION

Introduction:

·         This is a procedural language or programming language

·         It consist of unit blocks

·         Makes the application design easy

·         Provides security and is portable

·         It is used to handle multiple statements

·         To define our own logics we use Pl/sql

·         It supports control statements like IF conditions, Loops, go to.. etc.

·         By using pl/sql we can handle runtime errors

·         Explicitly we can define our own cursors in pl/sql. It allows Boolean data types

·         PL/SQL has tight integration with oracle data types.

Blocks

Unnamed Blocks:-

Syntax:

Declarative Section:-

·         We use declarative section to define variables, constants, cursors, exceptions, etc.,

·         We have to define the things in declarative section which are not understand by PL/SQL engine which is optional?

Excitable Section:-

          ·         Here we define or we provide coding path

          ·         Execution takes place in executable section which is mandatory

          ·         It starts from begin to end

Exception section:-  We use exception section to handle run time errors and which is optional

  

Nested Blocks:  Block within another block is called nested block

Example:-

1. Write a program to display the message on to the screen?

begin

dbms_output.put_line('hello welcome to pl/sql');

dbms_output.put_line('this is Narayana Reddy');

end;

Output:-

--------------------------------------------------------------------------------------------------------------------

Using ‘NULL’ as a statement:-

-------------------------------------------------------------------------------------------------------------------

2. Using Constant:-  We don’t change modifications in entire block

Declare

   A   constant number (5,3) := 3.142;

   B   number (5) := 100;

  Begin

          B:=50;

          Dbms_output.put_line(a+b);

   End;

Output:

-------------------------------------------------------------------------------------------------------------------

3. Change output automatically by using the program:

Declare

A number (5): = &n;

B number (5):= &m;

Begin

Dbms_output.put_line (a+b);

End;

-------------------------------------------------------------------------------

4. Write a program to calculate the area of the circle by  knowing the radius:

Declare

a  number (5,3);

r  number (5) := &n;

p  constant number (5,3) := 3.142;

Begin

a:=p*power(r,2);                     (or you can use p*r*r)

dbms_output.put_line('Area of the circle '||a);

End;

/

Out Put:

Nesting of comments is not possible

Ex:-

/*

Stmts

/*

Stmts

*/

stmts */