RDBMS_PRACT_5_D

Assignment 5 Cursor : SET D

railway4=# \d

           List of relations

 Schema |   Name    | Type  |  Owner  

--------+-----------+-------+----------

 public | passenger | table | postgres

 public | ticket    | table | postgres

 public | train     | table | postgres

(3 rows)

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)

railway4=#

1)

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

railway4=# select fu1();

NOTICE:  Passenger Name: mrs patil           

 fu1

-----

(1 row)

2)

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

railway4=# select fu4();

NOTICE:  Total: 2

 fu4

-----

(1 row)