Home » SQL & PL/SQL » SQL & PL/SQL » select logic
select logic [message #2171] Tue, 25 June 2002 23:02 Go to next message
chphang
Messages: 3
Registered: June 2002
Junior Member
hi! i need to do the following:

Cursor a_cur is
Select Field_A, Field_B from Table1;
a_rec a_cur%rowtype;
begin

if the a_cur%rowcount ='0' then
htp.p('No record')
exit (i.e. don't print anything else)

else
htp.p('The following are records available');
print in loop
htp.p(a_rec.fieldA);
htp.p(a_rec.fieldB);
htp.p('next');
end if;

how do i achieve the above?

thx.
Re: select logic [message #2172 is a reply to message #2171] Wed, 26 June 2002 00:37 Go to previous messageGo to next message
Balaji
Messages: 102
Registered: October 2000
Senior Member
Declare
cursor cur1 is select empno,ename from emp;
empdet cur1%rowtype;
exc1 exception;
begin
open cur1;
loop
fetch cur1 into empdet;
dbms_output.put_line(empdet.empno);
dbms_output.put_line(empdet.ename);
exit when cur1%notfound;
end loop;
if cur1%rowcount=0 then
raise exc1;
else
null;
end if;
close cur1;
exception
when exc1 then
dbms_output.put_line('No Data Found');
end;
Re: select logic [message #2175 is a reply to message #2171] Wed, 26 June 2002 01:13 Go to previous messageGo to next message
chphang
Messages: 3
Registered: June 2002
Junior Member
hi! thx for the prompt reply.
what if i need something like:

if no record, just print "No record found",

else

print table header (no need to loop)
loop print record details.

will i need 2 loops then? thx.
Re: select logic [message #2178 is a reply to message #2171] Wed, 26 June 2002 03:32 Go to previous messageGo to next message
Balaji
Messages: 102
Registered: October 2000
Senior Member
what is tableheader?. is it the table name or the column names ?
try this

Declare
cursor cur1 is select empno,ename from emp;
empdet cur1%rowtype;
exc1 exception;
begin
dbms_output.put_line(' Emp Table ');
dbms_output.put_line('EmpNO ' || ' Emp Name');
dbms_output.put_line('-----------------------------------------------');
open cur1;
loop
fetch cur1 into empdet;
dbms_output.put_line(empdet.empno);
dbms_output.put_line(empdet.ename);
exit when cur1%notfound;
end loop;
if cur1%rowcount=0 then
raise exc1;
else
null;
end if;
close cur1;
exception
when exc1 then
dbms_output.put_line('No Data Found');
end;
Re: select logic [message #2191 is a reply to message #2178] Wed, 26 June 2002 19:22 Go to previous message
chphang
Messages: 3
Registered: June 2002
Junior Member
hi! yes, it's the columns name.

so if there's no record, just print
"No record"

else print

Dept_Name Dept_Tel (print once only)
ABC 123 (these are records retrieved)
DEF 456
GHI 789
"End of records"

thx. sorry for the trouble, i'm very confused by the loops.
Previous Topic: ORA-00600:[12333]
Next Topic: Re: plsql fuction
Goto Forum:
  


Current Time: Tue Oct 15 02:41:31 CDT 2019