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

Cursor in PL/SQL.

Experiment-13

Object: Write a program in PL/SQL to display the employee number and name of top 5 highest paid.

Requirements: Windows XP, Oracle 9i.

Program Code:

SQL> create table empdb

  2  (empname char(25),

  3  empsal number(10));

Table created.

SQL> insert into empdb values('Abhishek',25000);

1 row created.

SQL>  insert into empdb values('Abhinav',5000);

1 row created.

SQL>  insert into empdb values('Brijesh',7500);

1 row created.

SQL>  insert into empdb values('Anand',15000);

1 row created.

SQL>  insert into empdb values('Rahul',2500);

1 row created.

SQL> select * from empdb;

EMPNAME                      EMPSAL

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

Abhishek                      25000

Abhinav                        5000

Brijesh                        7500

Anand                         15000

Rahul                          2500

SQL> declare

  2  empname empdb.empname%type;

  3  empsal empdb.empsal%type;

  4  cursor temp1 is select empname,empsal from empdb order by empsal desc;

  5  begin open temp1;

  6  loop

  7  fetch temp1 into empname,empsal;

  8  exit when temp1%rowcount>5 or temp1%notfound;

  9  dbms_output.put_line(empname || empsal);

 10  end loop;

 11  close temp1;

 12  end;

 13  .

SQL> /

PL/SQL procedure successfully completed.

Output:

SQL> select * from empdb;

EMPNAME                      EMPSAL

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

Abhishek                      25000

Anand                         15000

Brijesh                        7500

Abhinav                        5000

Rahul                          2500

Result: Thus the above program in PL/SQL to display the employee number and name of top 5 highest paid is successfully executed.

Read More