PL/SQL Cursor If No Data & Output

Question

CREATE OR REPLACE procedure verify_data
IS
cursor c1 is 
select 
  e.id
from 
  table1 e 
where 
  id IN (select id from table1)
  and id in (select id from table2);
BEGIN
FOR ed in c1
   LOOP
      DBMS_OUTPUT.PUT_LINE(ed.id||ed.name);
   END LOOP;
END;
/

When i execute this PL/SQL , it output as

14Andrew R. Smith

I want to ask is how do i make it output as a normal result like this with the formating etc.. and also

Below is what happen if i run the select statement directly with at terminal

        ID NAME
---------- --------------------------------------------------
        14 Andrew R. Smith

First Question: How to make my DBMS_OUTPUT.PUT_LINE same as what appear on terminal. like the ID then below is the ID data , and NAME etc.

Next Question:

I want to ask is

how do i do a output that if cursor is empty or no result, i DBMS_OUTPUT.PUT_LINE("NO RECORDS");

Thanks for all help!!

Update:

I did this

CREATE OR REPLACE procedure verify_data
IS
cursor c1 is 
select 
  e.id
from 
  table1 e 
where 
  id IN (select id from table1)
  and id in (select id from table2);
BEGIN
FOR ed in c1
   LOOP
      DBMS_OUTPUT.PUT_LINE(ed.id||ed.name);

 if c1%notfound then
 DBMS_OUTPUT.PUT_LINE('Okay');
  end if;

END LOOP;

END;

But it does not print Okay when no record is fetch from the PL/SQL. I added the c1%notfound to after END LOOP

Answer 1

If you want to see columns name you have to add before your loop one more line:

DBMS_OUTPUT.PUT_LINE('col1 col2 ...');

I think this is only one way to do that.

Below code shows how to check cursor:

if c1%notfound then
  DBMS_OUTPUT.PUT_LINE('NO RECORDS');
end if;

Here you can find more information about cursor attributes.

CREATE OR REPLACE procedure verify_data
IS
id number;
name varchar;
cursor c1 is 
select 
  e.id,e.name
from 
  table1 e 
where 
  id IN (select id from table1)
  and id in (select id from table2);
BEGIN

   open c1;
   fetch c1 into id,name;

   if c1%notfound then
      DBMS_OUTPUT.PUT_LINE('OK')
      EXIT; 
   else   
      DBMS_OUTPUT.PUT_LINE(id||name)
   end if;

   close c1;

END;

Answer 2

Since you are using dbms_output you won't get any headings like in a normal sql select statement.

So the only way I know is printing your own headings. And if you initialize a counter before the loop and increase it inside the loop, you can check after the loop if there was a result. Then you can print a message like "NO RECORDS" if you want.