RDBMS_PRACT_5_E

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=#