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;
/

Rene · Accepted Answer · 2015-07-17 10:57:44Z

1

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;
/

answered Jul 17, 2015 at 10:57

Rene

10.6k5 gold badges37 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Ondriska Over a year ago

Thank you a lot. I was thinking about boolean but I did not know how to compile as I am kind of newbie.

Boneist · Accepted Answer · 2015-07-21 09:47:44Z

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.

edited Jul 21, 2015 at 9:47

answered Jul 17, 2015 at 9:36

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

2 Comments

Michael Ondriska Over a year ago

As I mentioned in Rene's comment I am newbie. I am still learning. This script is not specifically for production environment but for my needs at work. Simply said, exact part of my job is responsibility for taking ownerships of tickets with problems. I need to know who is responsible for exact issue. And I am talking about lots of issue and responsible teams. So this is just for me as I created my own table and script is faster solution how to find the team. Thank you Boneist anyway. It is very helpful.

Boneist Over a year ago

In that case, I would have thought that a single select statement would suit your needs better, rather than pl/sql!

Collectives™ on Stack Overflow

If condition in PL/SQL script with cursor and loop

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related