Assignment
5 Cursor : SET C
marks4=# 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)
marks4=#
1)
marks4=# 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
marks4=# select
c1('Sadhashib Peth');
c1
----
(1 row)
marks4=# select
c1('Sadhashiv Peth');
NOTICE: Name:ABC
NOTICE: Subject Name:(1,Science)
NOTICE: Marks:80
NOTICE: Name:DEF
NOTICE: Subject Name:(1,Science)
NOTICE: Marks:85
NOTICE: Name:GHI
NOTICE: Subject Name:(1,Science)
NOTICE: Marks:81
NOTICE: Name:JKL
NOTICE: Subject Name:(2,Comp.Science)
NOTICE: Marks:76
NOTICE: Name:MNO
NOTICE: Subject Name:(3,Electronics)
NOTICE: Marks:71
c1
----
(1 row)
2)
marks4=# 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
marks4=# select
c22();
NOTICE: Roll Nos :103
NOTICE: Total : 221
NOTICE: Percentage:73
NOTICE: Roll Nos :101
NOTICE: Total : 248
NOTICE: Percentage:82
NOTICE: Roll Nos :105
NOTICE: Total : 239
NOTICE: Percentage:79
NOTICE: Roll Nos :104
NOTICE: Total : 214
NOTICE: Percentage:71
NOTICE: Roll Nos :102
NOTICE: Total : 228
NOTICE: Percentage:76
c22
-----
(1
row)
No comments:
Post a Comment