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

Write a program in PL/SQL to print the following pattern * ** *** **** *****

Experiment-12

Object: Write a program in PL/SQL to print the following pattern

*

**

***

****

*****

Requirements: Windows XP, Oracle 9i.

Program Code:

SQL>  declare

  2   i number(5);

  3   j number(5);

  4   begin

  5   for i in 1..5

  6   loop

  7   for j in 1..i

  8   loop

  9  dbms_output.put('*');

 10  end loop;

 11  dbms_output.new_line;

 12  end loop;

 13  end;

 14  .

Output:

SQL> /

*

**

***

****

*****

PL/SQL procedure successfully completed.

Result:Thus the above program in PL/SQL to print a pattern has been successfully executed.

Read More

Write a program in PL/SQL to print the factorial of a number.

In this post I will explain how to get the factorial of any given number. For that first you need to know what is the procedure to find the factorial of a number.

To get the factorial of any given number we have to multiply that number to every number less than that number till 1.

Suppose we have to find the factorial of 5. Then,

factorial(5)=5*4*3*2*1

                  =120

So in this way we can calculate the factorial of any number.

If you want to know the procedure to find the Fibonacci Sequence Click Here

Program Code:

SQL> set serveroutput on

SQL> declare

  2  i number(5);

  3  j number (5);

  4  f number(10);

  5  begin

  6  i:=&i;

  7  f:=1;

  8  for j in 1..i

  9  loop

 10  f:=f*j;

 11  end loop;

 12  dbms_output.put_line(f);

 13  end;

 14  .

SQL> /

Output:

Enter value for i: 3

old   6: i:=&i;

new   6: i:=3;

6

PL/SQL procedure successfully completed.

Enter value for i: 5

old   6: i:=&i;

new   6: i:=5;

120

Result: Thus the above program to print the factorial of a number was executed successfully.

So above is a simple program how to find Factorial of any given number. If there is any query regarding anything please comment.

Thanks

You might be interested in

PL/SQL Basics

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 perform mathematical operations

Read More

To calculate the areas of circles of given radius till radius 8 using PL/SQL.

Experiment-10

Object: To calculate the areas of circles of given radius till radius 8 using PL/SQL.

Requirements: Windows XP, Oracle 9i.

Program Code:

SQL>create table mycircle

      2 (area       number(10),

      3 radius     number(2));

Table Created.

SQL>set serevroutput on;

Declare

Area number(10);

Radius number(2);

Pi constant number(3,2):=3.14;

Begin

Radius:=&radius;

While radius<8

Loop

Area:=pi*power(radius,2);

Insert into mycircle values(area,radius);

Radius:=radius+1;

end loop;

end;

Output:

Enter value for radius:5

Old 6:radius:=&radius;

New6:radius:=5;

PL/SQ procedure successfully completed.

SQL>select * from mycircle;

AREA                    RADIUS

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

79                           5

113                         6

154                         7

           

Result: Thus the above program was compiled and executed without any error.

Read More

To perform mathematical operation of two numbers using PL/SQL.

Experiment-09

Object: To perform mathematical operation of two numbers using PL/SQL.

Requirement: Windows XP, Oracle 9i.

Program Code:

SQL>  declare

  2   a number(2);

  3   b number(2);

  4   c number(4);

  5  d number(4);

  6  e number(4);

  7  f number(4);

  8  begin

  9  a:=&a;

 10  b:=&b;

 11  c:=a+b;

 12  d:=a-b;

 13  e:=a*b;

 14  f:=a/b;

 15  dbms_output.put_line(c);

 16  dbms_output.put_line(d);

 17  dbms_output.put_line(e);

 18  dbms_output.put_line(f);

 19  end;

 20  .

SQL> /

Output:

Enter value for a: 3

old   9: a:=&a;

new   9: a:=3;

Enter value for b: 2

old  10: b:=&b;

new  10: b:=2;

5

1

6

2

PL/SQL procedure successfully completed.

Result: The above command to perform the mathematical  operation on two number has been successfully executed.

Read More

To perform the string function in simple database table.

Experiment-08

Object: To perform the string function in simple database table.

Requirement: Windows XP, Oracle 9i.

Program Code And Output :

SQL> select lower('ABHI')"Lower" from dual;

Lower

----

abhi

SQL> select upper('abhi')"Upper" from dual;

Upper

----

ABHI

SQL> select initcap('abhi anand')"init" from dual;

init

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

AbhiAnand

SQL> select substr('ABHI',2,3)"SubString"from dual;

SubS

----

BHI

SQL> select ascii('a'),ascii('A') from dual;

ASCII('a') ASCII('A')

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

        97         65

SQL>  select length('abhi')  from dual;

LENGTH('ABHI')

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

                 5

SQL> select ltrim('Abhi','A') from dual;

LTRIM('A

--------

bhia

SQL> select rtrim('Abhi','a') from dual;

RTRIM

-----

Abhi

SQL> select concat('Priya','nka') from dual;

CONCAT('

--------

Priyanka

SQL> create table emp09(Emp_Name varchar2(20));

Table created.

SQL>insert  into emp09 values(‘adit’);

1.row created.

SQL>insert  into emp09 values(‘raj’);

1.row created.

SQL>insert  into emp09 values(‘rajesh’);

1.row created.

SQL>insert  into emp09 values(‘rajindra’);

1.row created.

SQL> select Emp_Name from emp09 where soundex(Emp_Name)=soundex('raj');

EMP_NAME

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

raj

SQL> select Emp_Name from emp09 where Emp_Name like 'r%';

EMP_NAME

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

raj

rajesh

rajindra

SQL> select Emp_Name from emp09 where Emp_Name like '%a';

EMP_NAME

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

rajindra

SQL> select Emp_Name from emp09 where Emp_Name like '_a%';

EMP_NAME

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

raj

rajesh

rajindra

Result:The string function in simple database table has been implemented successfully.

Read More

To perform the numeric function in simple database table

Experiment-07

Object: To perform the numeric function in simple database table.

Requirement: Windows XP, Oracle 9i.

Program Code And Output :

SQL> select abs(-90) from dual;

 ABS(-90)

---------

      90

SQL> select floor(9227.97) from dual;

FLOOR(9227.97)

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

          9227

SQL> select ceil(9227.97) from dual;

CEIL(9227.97)

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

         9228

SQL> select mod(60,7) from dual;

MOD(60,7)

---------

        4

SQL> select power(3,2) from dual;

POWER(3,2)

----------

         9

SQL> select round(15.19,1) from dual;

ROUND(15.19,1)

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

          15.2

SQL> select sqrt(25) from dual;

 SQRT(25)

---------

        5

SQL> select exp(5) from dual;

 EXP(5)

---------

148.41316

SQL> select greatest(4,5,17) from dual;

GREATEST(4,5,17)

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

              17

SQL> select least(4,5,17) from dual;

LEAST(4,5,17)

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

            4

Result:The above mathematical functions has been successfully executed.

Read More

To perform logical operations in simple database table using AND,OR,NOT,BETWEEN,GROUP BY,ORDER BY, truncate, RENAME

Experiment-06

Object: To perform logical operations in simple database table using AND,OR,NOT,BETWEEN,GROUP BY,ORDER BY, truncate, RENAME.

Requirement: Windows XP, Oracle 9i.

Program Code:

SQL>create table abhik

      2 (name             char(20),

      3  id                     number(20),

      4  branch            char(5),

      5  class                char(10));

     Table created.

SQL>insert  into abhik values(‘abhi’,’15’,’cse’,’a’);

1 row created.

SQL>insert  into abhik values(‘jatin’,’82’,’cse’,’b’);

1 row created.

SQL>insert  into abhik values(‘ashish’,’41’,’cse’,’a’);

1 row created.

Output:

SQL>desc abhik;

Name                                             Null?     Type

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

NAME                                                     CHAR(20)

ID                                                            NUMBER(20)

BRANCH                                                 CHAR(5)

CLASS                                                      CHAR(10)

SQL>select name from abhik where branch=’cse’ and class=’a’;

NAME

---------

abhi

ashish

SQL>select name from abhik where branch=’cse’ or class=’a’;

NAME

---------

abhi

jatin

ashish

SQL>select name from abhik where not branch=’cse’;

NAME

---------

ashish

SQL>select name from abhik where id between 10 and 50;

NAME

---------

abhi

ashish

SQL>select name from abhik order by name;

NAME

---------

abhi

jatin

ashish

SQL>rename abhik to abhikeshri;

Table renamed.

Result: The above  logical operator has been successfully executed on a database table.

Read More