Write a function using cursor which accepts customer name and prints all account details of that customer. If the customer name is invalid, print appropriate message.
Write a function using cursor which accepts customer name and prints all account details of that customer. If the customer name is invalid, print appropriate message.
==--->>>
create or replace function cu( a varchar(50)) returns int as
'
declare
c1 cursor for select * from customer where cname = a ;
c2 cursor for select * from account;
r1 customer%rowtype;
r2 account%rowtype;
ch int :=0;
begin
open c1;
loop
fetch c1 into r1;
exit when not found;
open c2;
loop
fetch c2 into r2;
exit when not found;
if r1.cno = r2.cno then
raise notice ''% % % % '',r2.ano,r2.atype,r2.odate,r2.balance;
end if;
ch := ch+1;
end loop;
close c2;
end loop;
ch := ch+1;
close c1;
return ch;
end;
'language 'plpgsql';
Output of function
Acode2=# select cu ('bbbbb');
o\p
Acode2=# select cu ('bbbbb');
NOTICE: 222 zero 2006-12-04 ₹1,000.00
NOTICE: 666 zero 2017-07-30 ₹500.00
cu
----
7
(1 row)
0 Comments