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 /

PL/SQL procedure successfully completed.

SQL>expc p1(‘Angel’);

SQL>expc p2(‘State Bank’);

40,000

Engineer Portal - Prem Sasi Kumar Arivukalanjiam

Menu Bar

Saturday, March 10, 2012