Assignment 3: Cursors
PL/SQL Cursors provide a way to select multiple rows of data from the database and then to process each row individually. Using a cursor, we can traverse up and down a result set and retrieve only those rows which are explicitly requested. Cursors basically help an application to efficiently use a static result set.
Declaring Cursor Variables
All access to cursors in PL/pgSQL goes through cursor variables, which are always of the special data type refcursor. We can declare a cursor variable either as a bound cursor variable or an unbound cursor variable.
a. To create an unbound cursor variable , just declare it as a variable of type refcursor.
b. To create a bound cursor variable, use the following cursor declaration.
syntax :
name CURSOR [ ( arguments ) ] FOR query;
where arguments, if specified, is a comma-separated list of pairs ‘name datatype’ that define names to be replaced by parameter values in the given query. The actual values to substitute for these names will be specified later, when the cursor is opened (parameterized cursors).
Opening Cursors
Before a cursor can be used to retrieve rows, it must be opened. PL/pgSQL has three forms of the OPEN statement, two of which use unbound cursor variables while the third uses a bound cursor variable.
Syntax for OPEN FOR query:
OPEN unbound_cursorvar FOR query;
Syntax for OPEN FOR EXECUTE :
OPEN unbound_cursorvar FOR EXECUTE query_string USING expression [, ... ] ];
Syntax for Opening a Bound Cursor :
OPEN bound_cursorvar [ ( argument_values ) ];
Using Cursors
Once a cursor has been opened, it can be manipulated with the statements described below.
FETCH
Syntax :
FETCH [ direction { FROM | IN } ] cursor INTO target;
The direction clause can be any of the following variants :
NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, or BACKWARD. Default is NEXT.
MOVE : MOVE repositions a cursor without retrieving any data.
Syntax :
MOVE [ direction { FROM | IN } ] cursor;
The direction clause can be any of the variants NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, ALL, FORWARD [ count | ALL ], or BACKWARD [ count | ALL ].
CLOSE : CLOSE closes the portal underlying an open cursor. This can be used to release resources earlier than end of transaction, or to free up the cursor variable to be opened again.
Syntax :
CLOSE cursor;
Assignments:-
SET A:-
Using the Warehouse database
a) Write a stored function using cursors to accept a city from the user and to list all warehouses in the city.
create function fun2(nm varchar(20))returns void as '
declare c1 cursor for select wname from warehouse where city=nm;
wn char(30);
Begin
open c1;
loop
fetch c1 into wn;
exit when not found;
raise notice''warehouse name:-%'',wn;
end loop;
close c1;
end '
language 'plpgsql';
CREATE FUNCTION
b) Write a stored function using cursors to find the list of items whose cost is between Rs.5000 to 10000
create function fun4()returns void as '
declare c3 cursor for select ino,description from items where cost between 500 and 1000;
ino int;
d text;
begin
open c3;
loop
fetch c3 into ino,d;
exit when not found;
raise notice''item nos:%'',ino;
raise notice''description:%'',d;
end loop;
close c3;
end '
language 'plpgsql';
CREATE FUNCTION
SET B
Company –Person database
Company(Name varchar(30),address (50),city varchar(20), phone varchar(10), share _value money)
Person(pname varchar(30),pcity varchar (20))
Company_Person are related with M to M relationship with descriptive attribute No_of_shares. Integer
company4=# create table company(name varchar(20) primary key,add varchar(50),phone varchar(20),share_value money);NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "company_pkey" for table "company"
CREATE TABLE
company4=# create table person(pname varchar(30) primary key,pcity varchar(20));NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "person_pkey" for table "person"
CREATE TABLE
company4=# create table cp(name varchar(30) references company(name) on delete cascade,pname varchar(30) references person(pname) on delete cascade,nos_of_share int);
CREATE TABLE
company4=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | company | table | postgres
public | cp | table | postgres
public | person | table | postgres
(3 rows)
company4=# insert into company values('infosys','abc','020-665544','$5000000');
INSERT 0 1
company4=# insert into company values('infosystem','abc','020-665545','$6000000');
INSERT 0 1
company4=# insert into company values('info','pune','020-665545','$7000000');
INSERT 0 1
company4=# insert into company values('infotec','pune','020-665543','$5500000');
INSERT 0 1
company4=# insert into company values('wipro','pune','020-665345','$6500000');
INSERT 0 1
company4=# insert into person values('Rahul','pune');
INSERT 0 1
company4=# insert into person values('Vidhyadhar','pune');
INSERT 0 1
company4=# insert into person values('sachin','mumbai');
INSERT 0 1
company4=# insert into person values('darshan','mumbai');
INSERT 0 1
company4=# insert into person values('shiva','mumbai');
INSERT 0 1
company4=# insert into cp values('infosys','Rahul',10);
INSERT 0 1
company4=# insert into cp values('infosystem','Vidhyadhar',25);
INSERT 0 1
company4=# insert into cp values('infotec','darshan',5);
INSERT 0 1
company4=# insert into cp values('info','sachin',15);
INSERT 0 1
company4=# insert into cp values('wipro','shiva',12);
INSERT 0 1
company4=# select * from company;
name | add | phone | share_value
------------+------+------------+---------------
infosys | abc | 020-665544 | $5,000,000.00
infosystem | abc | 020-665545 | $6,000,000.00
info | pune | 020-665545 | $7,000,000.00
infotec | pune | 020-665543 | $5,500,000.00
wipro | pune | 020-665345 | $6,500,000.00
(5 rows)
company4=# select * from person;
pname | pcity
------------+--------
Rahul | pune
Vidhyadhar | pune
sachin | mumbai
darshan | mumbai
shiva | mumbai
(5 rows)
company4=# select * from cp;
name | pname | nos_of_share
------------+------------+--------------
infosys | Rahul | 10
infosystem | Vidhyadhar | 25
infotec | darshan | 5
info | sachin | 15
wipro | shiva | 12
(5 rows)
a) Write a stored function using cursors to transfer the shares owned by ‘Sachin ‘ to ‘Rahul’.
create function b11()returns void as '
declare cb1 cursor for select pname,nos_of_share from cp where pname=''sachin'';
p varchar(30);
n int;
begin
open cb1;
loop
fetch cb1 into p,n;
exit when not found;
update cp set nos_of_share=nos_of_share+n where pname=''Rahul'';
update cp set nos_of_share=nos_of_share-n where pname=p;
end loop;
close cb1;
end '
language 'plpgsql';
CREATE FUNCTION
b) Write a stored function using cursors to print the total number of distinct investors along with its total invested value.
create function f11()returns void as '
declare c10 cursor for select count(cp.name),sum(share_value) from person,company,cp where person.pname=cp.pname and company.name=cp.name;
cnt int;
sm money;
begin
open c10;
loop
fetch c10 into cnt,sm;
exit when not found;
raise notice''Count : %'',cnt;
raise notice''Sm : %'',sm;
end loop;
close c10;
end '
language 'plpgsql';
SET C
Student –Marks database
Student (rollno integer,name varchar(30),address varchar(50),class varchar(10)) Subject(Scode varchar(10),subject name varchar(20))
student and subject are related with M-M relationship with attributes marks scored. Create a RDB in 3NF for the above and solve the following.
select * from student;
rno | name | addrs | class
-----+-----+---------------+------
101 | ABC | Sadhashiv Peth | Fy
102 | DEF | Sadhashiv Peth | Fy
103 | GHI | Sadhashiv Peth | Fy
104 | JKL | Nana Peth | Sy
105 | MNO | Nana Peth | Sy
(5 rows)
marks4=# select * from subject;
scode | sname
-------+-------------
1 | Science
2 | Comp.Science
3 | Electronics
(3 rows)
marks4=# select * from ss;
rno | scode | marks
-----+------+------
101 | 1 | 80
102 | 1 | 85
103 | 1 | 81
104 | 2 | 76
105 | 3 | 71
104 | 1 | 69
105 | 1 | 79
101 | 2 | 79
101 | 3 | 89
102 | 2 | 71
102 | 3 | 72
103 | 2 | 76
103 | 3 | 64
104 | 3 | 69
105 | 2 | 89
(15 rows)
a) Write a stored function using cursors, to accept a address from the user and display the name,subject and the marks of the students staying at that address.
create function c1(addrs varchar(20))returns void as '
declare c11 cursor for select name,subject,marks from student,subject,ss where student.rno=ss.rno and subject.scode=ss.scode and addrs=''Sadhashiv Peth'';
n varchar(20);
s varchar(20);
m int;
begin
open c11;
loop
fetch c11 into n,s,m;
exit when not found;
raise notice''Name:%'',n;
raise notice''Subject Name:%'',s;
raise notice''Marks:%'',m;
end loop;
close c11;
end '
language 'plpgsql';
CREATE FUNCTION
b) Write a stored function using cursors which will calculate total and percentage of each student
create function c22()returns void as '
declare c21 cursor for select rno,count(scode),sum(marks) from ss group by rno;
r int;
s int;
m int;
p float;
begin
open c21;
loop
fetch c21 into r,s,m;
exit when not found;
p=(m*100)/(s*100);
raise notice''Roll Nos :% '',r;
raise notice''Total : %'',m;
raise notice''Percentage:%'',p;
end loop;
close c21;
end '
language 'plpgsql';
CREATE FUNCTION
Railway Reservation Database
Consider a railway reservation Database of passengers. Passengers reserve berths of a bogie of trains. The bogie capacity of all the bogies of a train is same.
1. TRAIN (TRAIN_NO INT, TRAIN_NAME VARCHAR(20), DEPART_TIME TIME , ARRIVAL_TIME TIME, SOURCE_STN VARCHAR(20) , DEST_STN VARCHAR (20), NO_OF_RES_BOGIES INT , BOGIE_CAPACITY INT)
2. PASSENGER (PASSENGER_ID INT, PASSENGER_NAME VARCHAR(20), ADDRESS VARCHAR(30), AGE INT , GENDER CHAR) Relationship is as follows:
TRAIN _PASSENGER : M-M with descriptive attributes as follows :
TICKET ( TRAIN_NO INT , PASSENGER_ID INT, TICKET_NO INT COMPOSITE KEY, BOGIE_NO INT, NO_OF_BERTHS INT , DATE DATE , TICKET_AMT DECIMAL(7,2),STATUS
CHAR)
The status of a particular berth can be ‘W‘ (waiting) or ‘C‘ (confirmed).
railway4=# select * from passenger;
pid | pname | addr | age | gender
-----+------------+------+-----+--------
11 | mr jadhav | abc | 20 | male
22 | mr patil | xyz | 30 | male
33 | mrs patil | xyz | 40 | female
44 | mrs jadhav | sty | 50 | female
(4 rows)
railway4=# select * from ticket;
tno | pid | ticket_no | b_no | no_berths | date | ticket_amt | status | da
-----+-----+-----------+------+-----------+------------+------------+--------+------------
101 | 11 | 1 | 12 | 5 | 2009-05-03 | | |
102 | 22 | 2 | 15 | 3 | 2009-04-02 | | |
104 | 44 | 5 | 12 | 5 | 2010-05-03 | 4000.00 | w |
102 | 22 | 6 | 12 | 5 | 2010-03-03 | 4000.00 | w |
103 | 33 | 4 | 12 | 5 | 2010-05-03 | 2000.00 | c | 2010-05-03
101 | 11 | 3 | 12 | 5 | 2010-05-03 | 2500.00 | w |
(6 rows)
railway4=# select * from train;
tno | tname | d_time | a_time | s_stin | dest_stin | no_of_bogies | bogies_capacity
-----+-----------------+----------+----------+--------+-----------+--------------+-----------------
101 | chennai express | 11:00:00 | 21:30:00 | abc | xyz | 12 | 45
102 | mumbai express | 12:00:00 | 21:30:00 | def | pqr | 20 | 60
103 | pune express | 12:00:00 | 22:30:00 | jkl | stu | 30 | 60
104 | express | 12:00:00 | 22:30:00 | mno | ghi | 40 | 80
(4 rows)
Using railway reservation database
a) Write a stored function using cursors to find the confirmed bookings of all the trains on 18-05-2009
create function fu1()returns void as '
declare cf1 cursor for select pname from passenger where pid in(select pid from ticket where da=''2010-05-03'' and status=''c'');
paname char(20);
begin
open cf1;
loop
fetch cf1 into paname;
exit when not found;
raise notice ''Passenger Name: %'',paname;
end loop;
close cf1;
end '
language 'plpgsql';
CREATE FUNCTION
b) Write a stored function using cursors to find the total number of berths not reserved for all the trains on 18-05-2009.
create function fu4()returns void as '
declare cf2 cursor for select count(pname) from passenger where pid in(select pid from ticket where date=''2010-05-03'' and status=''w'');
cnt int;
begin
open cf2;
loop
fetch cf2 into cnt;
exit when not found;
raise notice ''Total: %'',cnt;
end loop;
close cf2;
end '
language 'plpgsql';
CREATE FUNCTION
Bus transport Database
Consider the following Database of Bus transport system . Many buses run on one route. Drivers are allotted to the buses shiftwise.
Following are the tables:
BUS (BUS_NO INT , CAPACITY INT , DEPOT_NAME VARCHAR(20)) ROUTE (ROUTE_NO INT, SOURCE CHAR(20), DESTINATION CHAR(20), NO_OF_STATIONS INT)
DRIVER (DRIVER_NO INT , DRIVER_NAME CHAR(20), LICENSE_NO INT, ADDRESS CHAR(20), D_AGE INT , SALARY FLOAT)
The relationships are as follows: BUS_ROUTE : M-1
BUS_DRIVER : M-M with descriptive attributes Date of duty allotted and Shift — it can be 1(Morning) or 2 ( Evening ).
Constraints :1. License_no is unique. 2. Bus capacity is not null.
select * from bd
buss4-# ;
bno | dno | duty_date | shift
-----+-----+------------+-------
11 | 1 | 2009-05-03 | m
22 | 2 | 2009-04-03 | e
33 | 3 | 2009-03-03 | m
(3 rows)
buss4=# select * from bus;
bno | capacity | dname | rno
-----+----------+------------+-----
11 | 40 | shivam | 101
22 | 30 | shivshakti | 102
33 | 50 | shiv | 103
(3 rows)
buss4=# select * from driver;
dno | dname | licenes_no | addr | d_age | salary
-----+-----------+------------+----------------------+-------+--------
1 | darshan | 100 | aba | 30 | 2000
2 | vidya | 200 | abb | 20 | 4000
3 | vidyadhar | 300 | bbb | 40 | 5000
(3 rows)
buss4=# select * from route;
rno | source | destination | no_of_stations
-----+----------------------+----------------------+----------------
101 | xyz | abc | 2
102 | pqr | efg | 3
103 | uvw | hij | 5
(3 rows)
Using bus driver database
a) Write a stored function using cursors to display the details of a driver, (Accept driver name as input parameter).
create function e1(dname varchar(20))returns void as '
declare e11 cursor for select dname,licenes_no,salary from driver;
n varchar(20);
l int;
s float;
begin
open e11;
loop
fetch e11 into n,l,s;
exit when not found;
raise notice''Driver Name:%'',n;
raise notice''Licenes Nos:%'',l;
raise notice''Salary:%'',s;
end loop;
close e11;
end '
language 'plpgsql';
CREATE FUNCTION
b) Write a stored function using cursors to display the details of the buses that run on routes 1,2 (Use two different cursors for route_no = 1 and route_no = 2).
create function e2()returns void as '
declare e21 cursor for select source,destination from route where rno=101;
declare e22 cursor for select source,destination from route where rno=102;
s char(20);
d char(20);
sa char(20);
da char(20);
begin
open e21;
loop
fetch e21 into s,d;
exit when not found;
raise notice''Source:%'',s;
raise notice''Destination:%'',d;
end loop;
close e21;
open e22;
loop
fetch e22 into sa,da;
exit when not found;
raise notice''Source:%'',sa;
raise notice''Destinatio:%'',da;
end loop;
close e22;
end '
language 'plpgsql';
CREATE FUNCTION
No comments:
Post a Comment