If condition in PL/SQL script with cursor and loop

Question

I would like to ask for some help or advice in this particular case.

I have table called "Teams". The table contains 3 columns - Issue, Responsible_team and More_Info (all varchar2).

I have a PL/SQL script with cursor and loop for selecting as many teams as issue description you type (some random word you think it might help you find the responsible team). This part works great for me.

But I do not know how to compile the IF condition in there. If no team is found according to typed word description, I would like to get some basic output dbms_output.put_line('Responsible team is not found'). There are 2 ways how I wrote the script. Classic loop and while loop. I would be happy for any advice.

1.script

set verify off
DECLARE
    v_issue teams.issue%type; --variable for issue column from teams table
    v_respteam teams.responsible_team%type; --variable for responsible_team column from teams table
    v_info teams.more_info%type; --variable for more_info column from teams table

--cursor declaration
    CURSOR c_respteam
    RETURN teams%ROWTYPE
    IS
        SELECT issue, responsible_team, more_info
        FROM teams
        WHERE lower(issue) like '%&Describe_Issue%';
BEGIN
    OPEN c_respteam;
    LOOP
      FETCH c_respteam into v_issue, v_respteam, v_info;
      EXIT when c_respteam%NOTFOUND;
      dbms_output.put_line('Responsible team is '|| v_respteam || ' --> ' || v_info);        
    END LOOP;
    CLOSE c_respteam;
end;
/

2.script

-- cursor with while loop
set verify off
DECLARE
    v_issue teams.issue%type; --variable for issue column from teams table
    v_respteam teams.responsible_team%type; --variable for responsible_team column from teams table
    v_info teams.more_info%type; --variable for more_info column from teams table

CURSOR c_respteam
RETURN teams%ROWTYPE IS
    SELECT issue, responsible_team, more_info
    FROM teams
    WHERE lower(issue) like '%&Describe_Issue%';

BEGIN
OPEN c_respteam;
FETCH c_respteam INTO v_issue, v_respteam, v_info;
WHILE c_respteam%FOUND
LOOP
dbms_output.put_line('Responsible team is '|| v_respteam || ' --> ' || v_info);
FETCH c_respteam INTO v_issue, v_respteam, v_info;
END LOOP;
CLOSE c_respteam;
END;
/

Answer 1

You need to have a counter variable [ETA: ooh, I like Rene's boolean variable idea instead; either way, you need an extra variable!] to work out if any rows were returned or not. I'm not sure why you're using an explicit cursor fetch, rather than using the cursor-for-loop? Cursor-for-loops are not only easier to write, read and maintain, but Oracle have put some behind-the-scenes optimisation in, to aid performance.

Of course, depending on what you're actually doing with the data returned by your cursor (dbms_output.put_line being something that you should never have in your production code), it's debatable that you would need to loop through a cursor at all.

Anyway, with that said, here's an example demonstrating how I would handle your requirement to check for no rows returned by the cursor:

declare
  cursor cur (p_val varchar2)
  is
    select dummy
    from   dual
    where  dummy like '%'||p_val||'%';

  v_counter integer := 0;
begin
  for rec in cur('Y')
  loop
    dbms_output.put_line('value of dummy = '||rec.dummy);
    v_counter := v_counter + 1;
  end loop;
  if v_counter = 0 then
    dbms_output.put_line('no rows returned');
  end if;
end;
/

no rows returned

declare
  cursor cur (p_val varchar2)
  is
    select dummy
    from   dual
    where  dummy like '%'||p_val||'%';

  v_counter integer := 0;
begin
  for rec in cur('X')
  loop
    dbms_output.put_line('value of dummy = '||rec.dummy);
    v_counter := v_counter + 1;
  end loop;
  if v_counter = 0 then
    dbms_output.put_line('no rows returned');
  end if;
end;
/

value of dummy = X

---

To expand on what I said in my comment below, it sounds like you just need a single sql statement, rather than using PL/SQL and relying on dbms_output.

Eg., say you have the following statement:

select lvl
from   (select 'X'||level lvl from dual connect by level <= 10)
where  lvl like '%&val%';

with &val blank, you get:

LVL                                      
-----------------------------------------
X1                                       
X2                                       
X3                                       
X4                                       
X5                                       
X6                                       
X7                                       
X8                                       
X9                                       
X10    

With &val = 2 you get:

LVL                                      
-----------------------------------------
X2                                       

With &val = 100 you get:

no rows selected.

Answer 2

You could rewrite to:

declare
   l_found boolean :=false;

   cursor c_respteam is
      select issue
            ,responsible_team
            ,more_info
        from teams
       where lower(issue) like '%&Describe_Issue%';
begin
   for r in c_respteam
   loop
      l_found := true;
      dbms_output.put_line('Responsible team is ' || r.responsible_team || ' --> ' || r.more_info);
   end loop;

   if not l_found
   then
      dbms_output.put_line('No records found');
   end if;
end;
/