PL/SQL Tutorial

Introduction to PL/SQL

During 1980s we were using the SQL which is not a procedural language, so because of this we were facing a lot of issues. So a new programming language was developed by Oracle Corporation in the late 1980s as procedural extension language for SQL and the Oracle relational database which was named as PL/SQL.

PL/SQL stands for Procedural Language extension of SQL.PL/SQL is a combination of SQL along with the procedural features of programming languages.

Features of PL/SQL

PL/SQL has the following features:

·         PL/SQL is tightly integrated with SQL

·         It offers extensive error checking

·         It is completely portable and high performance language

·         It offers a variety of programming structures

·         It supports structured programming through functions and procedures

·         It supports developing web applications and server pages

·         PL/SQL provides a built-in interpreted and OS independent programming environment.

·         PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.

·         Applications written in PL/SQL are fully portable.

·         PL/SQL provides high security level.

·         PL/SQL provides access to predefined SQL packages.

A Simple PL/SQL Block

PL/SQL block consists of 3 sections:

• The Declaration section (optional).
• The Execution section (mandatory).
• The Exception Handling section (optional).

Declaration Section:

This section is optional and is used to declare any placeholders. These placeholders can be variables, constants cursors etc. The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE.

Execution Section:

Unlike declaration section, it is a mandatory section where the logic of the program will be written. The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. We can use different types of constructs as for, while loops and conditional statements in this section. All the SQL queries will be also part of this section.

Exception Section:

This section is optional. The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. So if we think that there can be any error or exception then we can use this section and handle that exception.

DECLARE
     Variable declaration
BEGIN
     Program Execution
EXCEPTION
     Exception handling
END;

Simple Program:

DECLARE

   msg_to_user  varchar2(20):= 'Hello, World! This is my first program';

BEGIN

   dbms_output.put_line(msg_to_user  );

END;

/

Hello, World! This is my first program

PL/SQL procedure successfully completed.

You might be interested in

Cursor in PL/SQL

Triggers in PL/SQL

Create process in PL/SQL

PL/SQL program to find Greatest

PL/SQL program to get the area of circle

PL/SQL program to print different patterns

PL/SQL program to print Fibonacci Sequence
PL/SQL program to print the factorial of number

PL/SQL program to perform mathematical operations

Read More

Write a PL/SQL code to find the greatest number among the given set of numbers.

Experiment-18

Object: Write a PL/SQL code to find the greatest number among the given set of numbers.

Requirement: Windows XP, Oracle 9i.

Program Code:

SQL>  declare

  2   a number:=&enter_a;

  3   b number:=&enter_b;

  4   c number:=&enter_c;

  5   begin

  6   if a>b then

  7  if a>c then

  8  dbms_output.put_line('A is greatest');

  9  else

 10  dbms_output.put_line('B is greatest');

 11   end if;

 12   else

 13   if b>c then

 14   dbms_output.put_line('C is greatest');

 15  end if;

 16  end if;

 17  end;

 18  .

So in above program code i have declared three variables in line 2,3 and 4 after that i have checked if a is greater than b,if this is true then check if a is also greater than c and if yes then a is greatest otherwise b is greatest.So this program has very simple logic.

OUTPUT:

SQL> /

Enter value for enter_a: 3

old   2:  a number:=&enter_a;

new   2:  a number:=3;

Enter value for enter_b: 5

old   3:  b number:=&enter_b;

new   3:  b number:=5;

Enter value for enter_c: 1

old   4:  c number:=&enter_c;

new   4:  c number:=1;

C is greatest

PL/SQL procedure successfully completed

RESULT : The above program to find the greatest number among the given set of numbers has been successfully executed.

Read More

Write a PL/SQL code to create and use process.

Experiment-17

Object: Write a PL/SQL code to create and use process.

Requirement: Windows XP, Oracle 9i.

Program Code:

SQL> create or replace procedure PROCESS(A in number,B in number,C out number,D out number,E out number,F out number) is

  2  begin

  3  C:=A+B;

  4  D:=A-B;

  5  E:=A*B;

  6  F:=A/B;

  7  end;

  8  .

OUTPUT :

SQL> /

Procedure created.

PROGRAM CODE :

SQL>  declare

  2   A number;

  3   B number;

  4   C number;

  5   D number;

  6   E number;

  7   F number;

  8   begin

  9   A:=&firstnumber;

 10   B:=&secondnumber;

 11   PROCESS(A,B,C,D,E,F);

 12   dbms_output.put_line('Addition is '||C);

 13   dbms_output.put_line('Subtraction is '||D);

 14   dbms_output.put_line('Multiplication is '||E);

 15   dbms_output.put_line('Division is'||F);

 16   end;

 17  .

OUTPUT :

SQL> /

Enter value for firstnumber: 4

old   9:  A:=&firstnumber;

new   9:  A:=4;

Enter value for secondnumber: 2

old  10:  B:=&secondnumber;

new  10:  B:=2;

Addition is 6

Subtraction is 2

Multiplication is 8

Division is2

PL/SQL procedure successfully completed.

RESULT: The above program to create and use the process has been successfully executed.

Read More

Write a PL/SQL code to get the Fibonacci Sequence

First, I will explain what is Fibonacci Sequence and how to get this series.
So, Fibonacci Sequence is a series of numbers 0,1,1,2,3,5,8,13,21.............

In Fibonacci Sequence, first and second elements are 0 and 1 and to get the next elements we will add the previous elements and it will generate the next element.
So, first element=0
      second element =1
      third element=sum of last 2 elements (first element + second element)
                           =0+1
                           =1
      fourth element=second element + third element
                             =1+1
                             =2
      fifth element=third element + fourth element
                           =1+2
                           =3
So in this way we can generate a Fibonacci Sequence.
Now how to generate a Fibonacci Sequence using a program?

•  First thing we want to know the length of sequence because its length will be infinite, so we want to know length of sequence to be generated by program. I have taken it as 6
• Second thing we will need first two elements of sequence and for that we will use 2 variables, a=0, b=1
•  Now we will need one extra variable which will be the sum of last two elements which in our program is c
• Now we have to use a for loop and just put c=a+b inside that so that the sequence will be generated

Object: Write a PL/SQL code to get the Fibonacci series.

Requirement: Windows XP, Oracle 9i.

Program Code:

SQL> declare

  2  a number(5);

  3  b number(5);

  4  c number(5);

  5  n number(5);

  6  i number(5);

  7  begin

  8  n:=6;

  9  a:=0;

 10  b:=1;

 11  for i in 1..n

 12  loop

 13  c:=a+b;

 14  a:=b;

 15  b:=c;

 16   dbms_output.put_line(c);

 17  end loop;

 18  end;

 19  .

OUTPUT :

SQL> /

1

2

3

5

8

13

PL/SQL procedure successfully completed.

                       

RESULT : The above program in PL/SQL to get the Fibonacci series has been executed successfully.


So above is a simple program how to generate Fibonacci Sequence. If there is any query regarding anything please comment.

Thanks 

Read More

Write a PL/SQL code to implement after row trigger.

Experiment-15

Object: Write a PL/SQL code to implement after row trigger. Create two tables as student_master and audit_student.

Requirement: Windows XP, Oracle 9i.

Program Code:

SQL> create table student_master(rollno number(2), name varchar2(20), age number(2

),city varchar2(20));

Table created.

SQL> drop table student_audit;

Table dropped.

SQL> create table student_audit(rollno number(2), name varchar2(20), operation var

pdate date);

Table created.

SQL> insert into student_master values(1,'sharad',20,9818923723,'delhi');

1 row created.

SQL>  insert into student_master values(2,'abhi',21,9775645621,'haryana');

1 row created.

SQL>  create or replace trigger mytrigger

  2   after update or delete on student_master

  3   for each row

  4   declare

  5   operation varchar2(10);

  6   roll_no number(10);

  7   name varchar2(20);

  8   begin

  9   if updating then

 10   operation:='update';

 11   end if;

 12   if deleting then

 13   operation:='delete';

 14   end if;

 15   roll_no:=:old.roll_no;

 16   name:=:old.name;

 17   insert into audit_student values(roll_no,name,operation,sysdate);

 18   end;

Output:

STUDENT_MASTER

FIELD NAME       DATA TYPE          SIZE

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

RollNo                 Number               2

Name                   Varchar2             20

Age                      Number               2

Phone                  Number               10    

City                      Varchar2             20

STUDENT_AUDIT

FIELD NAME                 TYPE           SIZE

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

Tot_Num             Number               10

Tot_Age               Number               6

OUTPUT AS RESULT :

Tot_Age(20)

1 row created.

Tot_Age(41)

1 row created.

Result: The program to create a trigger has been executed successfully.

Read More

Write a PL/SQL code to calculate total sal of emp having empno 100. Table emp1 having following columns: empno,ename,bp,da,hra,total.

Experiment-14

Object : Write a PL/SQL code to calculate total sal of emp having empno 100. Table emp1 having following columns: empno,ename,bp,da,hra,total.

Requirement: windows XP, Oracle 9i.

Program Code:

SQL> create table emp1

  2  (empno varchar(5),

  3  empname char(20),

  4  bp number(5),

  5  da number(5),

  6  hra number(5),

  7  total number(10));

Table created.

SQL>  insert into emp1 values('E0001','Abhishek',5000,6000,7000,NULL);

1 row created.

SQL> select * from emp1;

EMPNO EMPNAME                     BP        DA       HRA     TOTAL

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

E0001 Abhishek                  5000      6000      7000

SQL> declare

  2  d number(5);

  3  h number(5);

  4  b number(5);

  5  t number(10);

  6  begin

  7  select bp, da, hra into b,d,h from emp1 where empno='E0001';

  8  t:=b+d+h;

  9  update emp1 set total=t where empno='E0001';

 10  end;

 11  .

Output: SQL> /

PL/SQL procedure successfully completed.

SQL> select * from emp1 ;

EMPNO EMPNAME                     BP        DA       HRA     TOTAL

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

E0001 Abhishek                  5000      6000      7000     18000

Result:The above program  was created executed successfully without any error.

Read More