Display message if table is empty

Question

I have a table and if data exists I have to display data with dbms_output. If the table is empty I have to write a message "table is empty'.

I want to use cursors and I don't know how the condition should look like when the table is empty.

That works fine:

declare
cursor cursor_name is select nr, name from branch;
begin 
    for i in cursor_name
    loop
        dbms_output.put_line('Number: ' || to_char(i.nr) || ' Name: ' || i.name);
    end loop;
end;
/

Answer 1

You can use such a simple code block

SQL> SET serveroutput ON
SQL> DECLARE
  v_mes VARCHAR2(50);
BEGIN
  SELECT DECODE(COUNT(*),0,'Table is empty')
    INTO v_mes
    FROM t;

  DBMS_OUTPUT.PUT_LINE(v_mes);
END;
/  

if your aim is displaying only for the case that the table has no data

Answer 2

If you want to use a cursor, then this might be one option:

SQL> set serveroutput on;

Creating an empty table:

SQL> create table branch (nr number, name varchar2(20));

Table created.

Anonymous PL/SQL block: declare a cursor and its variable, fetch, check whether anything has been found:

SQL> declare
  2    cursor c1 is select nr, name from branch;
  3    c1r c1%rowtype;
  4  begin
  5    open c1;
  6    fetch c1 into c1r;
  7    if c1%notfound then
  8       dbms_output.put_line('Table is empty');
  9    end if;
 10    close c1;
 11  end;
 12  /
Table is empty                                 --> as expected

PL/SQL procedure successfully completed.

SQL>

Answer 3

Just use a variable to store a flag which you can set if rows are found:

DECLARE
  CURSOR cursor_name IS
    SELECT nr, name FROM branch;
  no_rows BOOLEAN := TRUE;
BEGIN
  FOR i IN cursor_name LOOP
    DBMS_OUTPUT.PUT_LINE('Number: ' || to_char(i.nr) || ' Name: ' || i.name);
    no_rows := FALSE;
  END LOOP;

  IF no_rows THEN
    DBMS_OUTPUT.PUT_LINE('Table is empty');
  END IF;
END;
/

Or:

DECLARE
  CURSOR cursor_name IS
    SELECT nr, name FROM branch;
  cursor_row cursor_name%ROWTYPE;
BEGIN
  OPEN cursor_name;
  
  FETCH cursor_name INTO cursor_row;
  IF cursor_name%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('Table is empty');
  END IF;
  
  LOOP
    EXIT WHEN cursor_name%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(
      'Number: ' || to_char(cursor_row.nr) || ' Name: ' || cursor_row.name
    );
    FETCH cursor_name INTO cursor_row;
  END LOOP;

  CLOSE cursor_name;
END;
/

Which, for the sample data:

CREATE TABLE branch (nr, name) AS
SELECT 1, 'Aspen' FROM DUAL UNION ALL
SELECT 2, 'Beech' FROM DUAL UNION ALL
SELECT 3, 'Cedar' FROM DUAL;

Both output:

Number: 1 Name: Aspen
Number: 2 Name: Beech
Number: 3 Name: Cedar

and after:

DELETE FROM branch;

Both output:

Table is empty

db<>fiddle here

Answer 4

Integrated solution using a database view - use with cursor and dbms_outputas you like:

create view v_branch as
select nr, name from branch
union all
select null, 'Table is empty' name from dual
where 0 = (select count(*) from branch);

select * from v_branch;


        NR NAME          
---------- --------------
         1 x      

delete from branch;
select * from v_branch;

        NR NAME          
---------- --------------
           Table is empty

You will not want to use this for a very large tables - but it works fine for tables where you intend to print every row with put_line.