PL/SQL Functions & Procedures ex:8 DBMS Lab

PL/SQL Functions & Procedures

SQL> create table branch(bname varchar(30),loc varchar(20),assets varchar (30)); 

Table created. 

SQL> insert into branch values('&bname','&loc','&assets');

Enter value for bname: State Bank

Enter value for loc: Madurai

Enter value for assets: 12 Lakh

old   1: insert into branch values('&bname','&loc','&assets')

new   1: insert into branch values('State Bank','Madurai','12 Lakh') 

1 row created. 

SQL> /

Enter value for bname: TMB

Enter value for loc: Tirunagar\

Enter value for assets: 6 lakh

old   1: insert into branch values('&bname','&loc','&assets')

new   1: insert into branch values('TMB','Tirunagar\','6 lakh') 

1 row created. 

SQL> /

Enter value for bname: IOB

Enter value for loc: Theni

Enter value for assets: 6.5 lakh

old   1: insert into branch values('&bname','&loc','&assets')

new   1: insert into branch values('IOB','Theni','6.5 lakh') 

1 row created. 

SQL> /

Enter value for bname: Syndicate

Enter value for loc: Coimbatore

Enter value for assets: 7 lakh

old   1: insert into branch values('&bname','&loc','&assets')

new   1: insert into branch values('Syndicate','Coimbatore','7 lakh') 

1 row created. 

SQL> /

Enter value for bname: Baroda

Enter value for loc: Erode

Enter value for assets: 8 lakh

old   1: insert into branch values('&bname','&loc','&assets')

new   1: insert into branch values('Baroda','Erode','8 lakh') 

1 row created. 

SQL> select * from branch; 

BNAME                          LOC                          ASSETS

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

State Bank                     Madurai      12 Lakh 

TMB                            Tirunagar      6 lakh 

IOB                            Theni    6.5 lakh
 

BNAME                          LOC                          ASSETS

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

Syndicate                      Coimbatore 7 lakh 

Baroda                         Erode            8 lakh
 

SQL> create table account(accno integer, sdate date, balance integer); 

Table created. 

SQL> insert into account values(&accno,'&sdate',&balance);

Enter value for accno: 1701

Enter value for sdate: 15-JUL-1976

Enter value for balance: 17859

old   1: insert into account values(&accno,'&sdate',&balance)

new   1: insert into account values(1701,'15-JUL-1976',17859) 

1 row created. 

SQL> /

Enter value for accno: 1702

Enter value for sdate: 12-MAY-1985

Enter value for balance: 63875

old   1: insert into account values(&accno,'&sdate',&balance)

new   1: insert into account values(1702,'12-MAY-1985',63875) 

1 row created. 

SQL> /

Enter value for accno: 1703

Enter value for sdate: 26-SEP-1972

Enter value for balance: 52985

old   1: insert into account values(&accno,'&sdate',&balance)

new   1: insert into account values(1703,'26-SEP-1972',52985) 

1 row created. 

SQL> /

Enter value for accno: 1704

Enter value for sdate: 9-OCT-1990

Enter value for balance: 75426

old   1: insert into account values(&accno,'&sdate',&balance)

new   1: insert into account values(1704,'9-OCT-1990',75426) 

1 row created. 

SQL> /

Enter value for accno: 1705

Enter value for sdate: 8-FEB-1988

Enter value for balance: 42987

old   1: insert into account values(&accno,'&sdate',&balance)

new   1: insert into account values(1705,'8-FEB-1988',42987) 

1 row created. 

SQL> select * from account; 

     ACCNO SDATE        BALANCE

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

      1701 15-JUL-76      17859

      1702 12-MAY-85      63875

      1703 26-SEP-72      52985

      1704 09-OCT-90      75426

      1705 08-FEB-88      42987 

SQL> create table customer(custid integer,cname char(20),address char(20)); 

Table created. 

SQL> insert into customer values(&custid,'&cname','&address');

Enter value for custid: 111

Enter value for cname: Sunil

Enter value for address: 1,Nehru St,Erode

old   1: insert into customer values(&custid,'&cname','&address')

new   1: insert into customer values(111,'Sunil','1,Nehru St,Erode') 

1 row created. 

SQL> /

Enter value for custid: 112

Enter value for cname: Kishore

Enter value for address: 2,Gandi St,Mdu

old   1: insert into customer values(&custid,'&cname','&address')

new   1: insert into customer values(112,'Kishore','2,Gandi St,Mdu') 

1 row created. 

SQL> /

Enter value for custid: 113

Enter value for cname: Harish

Enter value for address: 3,Rajaji St,Cng

old   1: insert into customer values(&custid,'&cname','&address')

new   1: insert into customer values(113,'Harish','3,Rajaji St,Cng') 

1 row created. 

SQL> /

Enter value for custid: 114

Enter value for cname: Akil

Enter value for address: 4,Bose St,Vnr

old   1: insert into customer values(&custid,'&cname','&address')

new   1: insert into customer values(114,'Akil','4,Bose St,Vnr') 

1 row created. 

SQL> /

Enter value for custid: 115

Enter value for cname: Angel

Enter value for address: 5,PT St,Tmm

old   1: insert into customer values(&custid,'&cname','&address')

new   1: insert into customer values(115,'Angel','5,PT St,Tmm') 

1 row created. 

SQL> select * from customer; 

    CUSTID CNAME                ADDRESS

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

       111 Sunil                1,Nehru St,Erode

       112 Kishore              2,Gandi St,Mdu

       113 Harish               3,Rajaji St,Cng

       114 Akil                 4,Bose St,Vnr

       115 Angel                5,PT St,Tmm 

SQL> create table depositer(accno integer,custid integer,bname char(20)); 

Table created. 

SQL> insert into depositer values(&accno,&custid,'&bname');

Enter value for accno: 1701

Enter value for custid: 111

Enter value for bname: State Bank

old   1: insert into depositer values(&accno,&custid,'&bname')

new   1: insert into depositer values(1701,111,'State Bank') 

1 row created. 

SQL> /

Enter value for accno: 1702

Enter value for custid: 112

Enter value for bname: TMB

old   1: insert into depositer values(&accno,&custid,'&bname')

new   1: insert into depositer values(1702,112,'TMB') 

1 row created. 

SQL> /

Enter value for accno: 1703

Enter value for custid: 113

Enter value for bname: IOB

old   1: insert into depositer values(&accno,&custid,'&bname')

new   1: insert into depositer values(1703,113,'IOB') 

1 row created. 

SQL> /

Enter value for accno: 1704

Enter value for custid: 114

Enter value for bname: Syndicate

old   1: insert into depositer values(&accno,&custid,'&bname')

new   1: insert into depositer values(1704,114,'Syndicate') 

1 row created. 

SQL> /

Enter value for accno: 1705

Enter value for custid: 115

Enter value for bname: Baroda

old   1: insert into depositer values(&accno,&custid,'&bname')

new   1: insert into depositer values(1705,115,'Baroda') 

1 row created. 

SQL> create table employee(empno integer,empname char(20),address char(20), pay integer, qualification varchar(30)); 

Table created. 

SQL>  insert into empl values(&empno,'&empname','&address',&pay, '&qualification');

Enter value for empno: 101

Enter value for empname: Ashok

Enter value for address: 6,Lalu St,Mdu

Enter value for pay: 8000

Enter value for qualification: B.com

old   1:  insert into empl values(&empno,'&empname','&address',&pay,'&qualification')

new   1:  insert into empl values(101,'Ashok','6,Lalu St,Mdu',8000,'B.com') 

1 row created. 

SQL> /

Enter value for empno: 102

Enter value for empname: Sharan

Enter value for address: 7,NGO Nagar,Vnr

Enter value for pay: 7500

Enter value for qualification: B.Ed

old   1:  insert into empl values(&empno,'&empname','&address',&pay,'&qualification')

1 row created. 

SQL> /

Enter value for empno: 103

Enter value for empname: Tharun

Enter value for address: 8,KK Nagar,Ed

Enter value for pay: 12000

Enter value for qualification: B.E.

old   1:  insert into empl values(&empno,'&empname','&address',&pay,'&qualification')

new   1:  insert into empl values(103,'Tharun','8,KK Nagar,Ed',12000,'B.E.') 

1 row created. 

SQL> /

Enter value for empno: 104

Enter value for empname: Shailesh

Enter value for address: 9,PTN St,Tmm

Enter value for pay: 18000

Enter value for qualification: B.E.

old   1:  insert into empl values(&empno,'&empname','&address',&pay,'&qualification')

new   1:  insert into empl values(104,'Shailesh','9,PTN St,Tmm',18000,'B.E.') 

1 row created. 

CREATING FUNCTIONS & PROCEDURES:

SQL> create or replace function f1(newbname varchar)

2  return varchar

3  is

 4  newassets varchar(30);

5  BEGIN

6  select assets into newassets from branch where bname=newbname;

7  return newassets;

8  END;

9  /

Function created

SQL> create or replace function f2(neweno integer)

2  return integer

3  is

4  newitax integer;

5  pay1 integer;

6  BEGIN

7  select pay into pay1 from employee1 where empno=newwmpno;

8  if(pay<10000)then

9  newitax=0.1*pay1;

10  elsif(10000<pay1<20000)then

11  newitax=0.2*pay1;

12  elsif(20000<pay1<50000)then

13  newitax=0.35*pay1;

14  else(pay>50000)

15  newitax=0.4*pay1;

16 return new tax;

17 Endif;

18  end;

19 /

Function created.

SQL> create or replace function f3(newbname varchar)

2  return varchar

3  is

4  newaccno varchar(20);

5   BEGIN

6  select count(accno) into newaccno from depositer  where bname=newbname;

7  return newaccno;

8  END;

9  /

Function created.

SQL> set serveroutput on;

SQL>  create or replace procedure cusa(vcname varchar)

2   is

3   vno integer;

4   BEGIN

5   select count(*) into vno from depositer,customer where depositer.cid=customer.cid and customer.cname=vcname;

 6   dbms_output.put_line(vno);

7   END;

8  /

Procedure created.

SQL> create or replace procedure p2(newbname varchar)

2  is

3  nwamount integer;

4   BEGIN

5  select sum(balance)into nwamount from depositer,account2 where  depositer.bname=newbname and account2.accno=depositer.accno);

6  dbms_output.put_line(nwamount);

7  END;

8 /

Procedure created.

EXECUTION OF FUNCTIONS & PROCEDURES:

SQL>  set serveroutput on;

SQL>  BEGIN

2   dbms_output.put_line(f1('TMB'));

3  END;

4  /

6 lakh

PL/SQL procedure successfully completed.

          

SQL>  set serveroutput on;

SQL>  BEGIN

2   dbms_output.put_line(f2(12000));

3  END;

4  /

1200

PL/SQL procedure successfully completed.

SQL> set serveroutput on;

SQL> BEGIN

2  dbms_output.put_line(f3('1b'));

3  END;

4  /

0

PL/SQL procedure successfully completed.

SQL>expc p1(‘Angel’);

1

SQL>expc p2(‘State Bank’);

40,000