Assignment
5 Cursor : SET E
buss4=# \d
List of relations
Schema |
Name | Type |
Owner
--------+--------+-------+----------
public | bd
| table | postgres
public | bus
| table | postgres
public | driver | table | postgres
public | route
| table | postgres
(4 rows)
buss4=# 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)
buss4=# select
dname,licenes_no,salary from driver;
dname
| licenes_no | salary
-----------+------------+--------
darshan
| 100 | 2000
vidya
| 200 | 4000
vidyadhar | 300 |
5000
(3 rows)
buss4=# \d
driver;
Table "public.driver"
Column
| Type | Modifiers
------------+-----------------------+-----------
dno
| integer | not null
dname
| character varying(20) |
licenes_no | integer |
addr
| character(20) |
d_age
| integer |
salary
| double precision |
1)
buss4=# 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
buss4=# select
e1('darshan');
NOTICE: Driver Name:darshan
NOTICE: Licenes Nos:100
NOTICE: Salary:2000
NOTICE: Driver Name:darshan
NOTICE: Licenes Nos:200
NOTICE: Salary:4000
NOTICE: Driver Name:darshan
NOTICE: Licenes Nos:300
NOTICE: Salary:5000
e1
----
(1 row)
2)
buss4=# 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
buss4=# select
e2();
NOTICE: Source:xyz
NOTICE: Destination:abc
NOTICE: Source:pqr
NOTICE: Destinatio:efg
e2
----
(1 row)
buss4=#
No comments:
Post a Comment