76. Examples - 9

PL/SQL programs in oracle

Write a PL/SQL block to find out the square of a number.

sql>ed  //to open a file

Q. Set Serveroutput on

   Declare

      x number := &x;

      s number;

   Begin

      s := x*x;

      Dbms_output.put_line('Square of'||x||'='||s);

   end;

   /

sql>@  //to run the PL/SQL block

Finding areas using PL/SQL blocks

Write a PL/SQL block to compute the area of the circle

and square.

Q. Set Serveroutput on

   Declare

       r number(5,2) := &r;

       s number(5,2) := &s;

       ca number(5,2);

       sa number(5,2);

   Begin

       ca := (3.14)*r*r;

       sa := s * s;

       Dbms_Output.put_line('area of circle='||ca);

       Dbms_Output.put_line('area of square='||sa);

   End;

   / 

PL/SQL program for temparature conversion

Write a PL/SQL block to convert celsius to fahrenheat and 

viceversa.

Q. set serveroutput on

declare

  ct number(5,2) := &ct;

  ftc number(5,2);

  ft number(5,2) := &ft;

  ctc number(5,2);

begin

  ftc := (ct*9/5)+32;

  dbms_output.put_line('Faran heat temparature='||ftc);

  ctc := (ft-32)*5/9;

  dbms_output.put_line('Celsius temparature='||ctc);

end;

/

PL/SQL program to find big among 3 nos

Write a PL/SQL block to find big among 3 nos.

Q. set serveroutput on

declare

   a number := &a;

   b number := &b;

   c number := &c;

begin

   if(a>b and a>c)

   then

        dbms_output.put_line('big number='||a);

   elsif(b>c)

   then

          dbms_output.put_line('big number='||b);

   else

         dbms_output.put_line('big number='||c);

   end if;

 end;

/

PL/SQL program to find factorial of a number

Write a PL/SQL block to find factorial of a number.

set serveroutput on

declare

   n number := &n;

   fact number :=1;

   i number :=1;

begin

   for i in 1..n

   loop

     fact := fact*i;

   end loop;

   dbms_output.put_line('factorial='||fact);

end;

/ 

PL/SQL program to find leap year or not 

Write a PL/SQL block to find leap year or not.

set serveroutput on

declare

    year number:= &year;

begin

    if(mod(year,4) = 0)

    then

      dbms_output.put_line('Leap year');

    else

      dbms_output.put_line('Not leap year');

    end if;

end;

/

PL/SQL block to display first 20 odd numbers 

To display first 20 odd numbers.

set serveroutput on

declare

   ctr number(2) :=1;

   i number :=1;

begin

   while ctr <=20 loop

    if(mod(i,2)!=0)

     then

      ctr := ctr+1;

      dbms_output.put_line(i);

    end if;

    i := i+1;

   end loop;

end;

/ 

PL/SQL block to display first 10 numbers divisible by 3 and 5

To display first 10 numbers divisible by 3 and 5.

set serveroutput on

declare

   ctr number(2) :=1;

   i number :=1;

begin

   while ctr <=10 loop

    if(mod(i,3)=0 and mod(i,5)=0)

     then

      ctr := ctr+1;

      dbms_output.put_line(i);

    end if;

    i := i+1;

   end loop;

end;

/

PL/SQL block to display student result

To display student result

set serveroutput on

declare

sub1 number := &sub1;

sub2 number := &sub2;

sub3 number := &sub3;

total number;

avge number(5,2);

begin

total := sub1+sub2+sub3;

avge := total/3;

dbms_output.put_line('total='||total);

dbms_output.put_line('average='||avge);

if(avge<50 o:p="">

then

   dbms_output.put_line('Fail');

elsif(avge>=50 or avge<60 o:p="">

then

   dbms_output.put_line('Second division');

elsif(avge>=60 or avge<75 o:p="">

then

   dbms_output.put_line('First division');

elsif(avge>=75)

then

   dbms_output.put_line('Distinction');

end if;

end;

/

Procedures and functions in oracle

Function for cube of number in oracle 

Create a function for cube of any number.

create or replace function fncube(n number)

return number

is

begin

  return n*n*n;

end;

Running a function

sql>@

sql>function create successfully

sql>select (parameter) from dual;

Function to find simple interest in oracle 

Create a function to find simple interest.

create or replace function simple(p number,n number,r number)

return number

is

begin

  return(p*n*r/100);

end;

/

Running a function

sql>@

sql>function create successfully

sql>select (parameter) from dual;

Finding factorial using recurssion in oracle

Finding factorial using recurssion

create or replace function rfact(n positive) return integer  is

begin

   if(n=1) then

     return 1;

   else

     return n*rfact(n-1);

   end if;

end;

/

Fibnacci series using recurssion in oracle 

Fibnacci series using recurssion

create or replace function rfact(n positive) return integer  is

begin

   if(n=1) then

     return 1;

   else

     return n*rfact(n-1);

   end if;

end;

/

Fibnacci series using recurssion in oracle 

Fibnacci series using recurssion

create or replace function rfact(n positive) return integer  is

begin

   if(n=1) then

     return 1;

   else

     return n*rfact(n-1);

   end if;

end;

/

Procedures and functions in oracle

Function for cube of number in oracle 

Create a function for cube of any number.

create or replace function fncube(n number)

return number

is

begin

  return n*n*n;

end;

Running a function

sql>@

sql>function create successfully

sql>select (parameter) from dual;

Function to find simple interest in oracle 

Create a function to find simple interest.

create or replace function simple(p number,n number,r number)

return number

is

begin

  return(p*n*r/100);

end;

/

Running a function

sql>@

sql>function create successfully

sql>select (parameter) from dual;

Finding factorial using recurssion in oracle

Finding factorial using recurssion

create or replace function rfact(n positive) return integer  is

begin

   if(n=1) then

     return 1;

   else

     return n*rfact(n-1);

   end if;

end;

/