RDBMS_PRACT_6_A

Assignment 6 Exception Handling : SET A

1)

 create or replace function f1(nm varchar(30))returns text as '

declare rec record;

cnt int;

begin

for rec in select * from branch

loop

if(rec.bname<>nm)then

raise notice''Invalid'';

else

select count(cno) into cnt from bcl where bid in(select bid from branch where rec.bname=nm);

end if;

end loop;

raise notice''customer count is : %'',cnt;

return'' '';

end '

language 'plpgsql';

CREATE FUNCTION

bank4=# select f1('Deccan');

NOTICE:  Invalid

NOTICE:  Invalid

NOTICE:  Invalid

NOTICE:  Invalid

NOTICE:  Invalid

NOTICE:  Invalid

NOTICE:  customer count is : 10

 f1

----

 

(1 row)

2)

 create or replace function f2()returns text as'

declare rec record;

begin

for rec in select * from la

loop

if(rec.appmoney<''$10000'')then

raise notice ''amount is less than 10000 '';

else

update la set appmoney=appmoney+(appmoney*0.2)where lano=rec.lano;

end if;

end loop;

return '''';

end'

language'plpgsql';

CREATE FUNCTION

bank4=# select f2();

NOTICE:  amount is less than 10000

NOTICE:  amount is less than 10000

NOTICE:  amount is less than 10000

NOTICE:  amount is less than 10000

NOTICE:  amount is less than 10000

 f2

----

(1 row)