CODEWITHSUNDEEP
sqlstored-proceduresplsqloracle-sqldeveloperprocedure
Kevin
Kevin

Reputation: 46

PL/SQL How to display a custom text when no row is returned

I have this assignment where i have to get an overview from specialisations with a procedure from all the employees called "personeel","pers" or "per".(dutch tables and attributes)

If they don't have any specialisation, then it needs to return "no specialisations found".

The problem is when i use cursors (which is the way we NEED to solve this) and I use a for loop, I can't seem to test if the person has no specialisations. I also don't really know how to search this on the internet. All i seem to be finding is cursor attriubutes but that doesn't work.

This is my code (in sqldeveloper)

CREATE OR REPLACE PROCEDURE OVERZICHT_SPECIALISATIES AS

This cursor contains all the employees

cursor cur_per is
select voornaam, achternaam
from personeel;

This cursor contains the employee's their specialisation but if they have none it just returns no rows found which is what i want to test on

cursor cur_spec (p_vn PERSONEEL.VOORNAAM%type, p_an PERSONEEL.ACHTERNAAM%type) is
select p.code, s.titel, p.datum_behaald
from pers_specialisaties p, personeel per, specialisaties s
where per.persnr = p.persnr
and p.code = s.code
and voornaam = p_vn
and achternaam = p_an
group by p.code,s.titel, p.datum_behaald;

BEGIN
for r_per in cur_per
loop
dbms_output.put_line(r_per.voornaam || ' ' || r_per.achternaam);
dbms_output.put_line('---------------------------');

for r_spec in cur_spec (r_per.voornaam, r_per.achternaam)
loop

This is the IF statement were I test, but it doesn't seem to work

  IF (cur_spec%NOTFOUND)
  THEN
  dbms_output.put_line('no specialisations found');
  ELSE
  dbms_output.put_line(r_spec.code || '    ' || r_spec.titel || ' ' 
                        || r_spec.datum_behaald);
  END IF;
end loop;
dbms_output.put_line(' ');
end loop;

END OVERZICHT_SPECIALISATIES;

thanks in advance.

Upvotes: 0

Views: 111

Answers (2)

Alex Poole
Alex Poole

Reputation: 191315

You can use a flag that you set to false for each person, and then set to true inside the specialities loop - indicating that at least one speciality was found. Then test afterwards whether it is true or false for that person; if it is false then you didn't go into the loop, so there were no specialities:

declare
   found_record_flag boolean;
   ...
begin
  for r_per in cur_per
  loop
    dbms_output.put_line(r_per.voornaam || ' ' || r_per.achternaam);
    dbms_output.put_line('---------------------------');

    found_record_flag := false;
    for r_spec in cur_spec (r_per.voornaam, r_per.achternaam)
    loop
      found_record_flag := true;
      dbms_output.put_line(r_spec.code || '    ' || r_spec.titel || ' ' 
                            || r_spec.datum_behaald);
    end loop;

    if found_record_flag = false then
      dbms_output.put_line('no specialisations found');
    end if;

    dbms_output.put_line(' ');
  end loop;
...

Your second cursor is really assuming that no two people have the same combination of first and last name. It would be better to include persnr in the select list for the first clause, and only pass that in as the cursor parameter; then the second cursor wouldn't need to query the personeel table again:

cursor cur_per is
select persnr, voornaam, achternaam
from personeel;

cursor cur_spec (p_persnr personeel.persnr%type) is
select ps.code, s.titel, ps.datum_behaald
from pers_specialisaties ps
join specialisaties s
on s.code = ps.code
where ps.persnr = p_persnr;

I'm also not sure why you're grouping the values, unless you were getting duplicates because of a duplicate name. It shouldn't be necessary. And then later:

    for r_spec in cur_spec (r_per.persnr)
    loop

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can do this with left join. In general, you should use explicit join syntax -- it is more powerful than commas. In fact, follow a simple rule: never use commas in the from clause.

select p.code, coalesce(s.titel, 'no specialisations found') as titel, p.datum_behaald
from personeel per left join
     pers_specialisaties p
     on per.persnr = p.persnr left join
     specialisaties s
     on p.code = s.code
where per.voornaam = p_vn and per.achternaam = p_an;

This keeps all rows in personneel. If there is no matching s.titel, it gets the string that you want there.

I am not sure what the group by was doing. I don't see a reason why your query would be returning duplicate values. You can put it back in if necessary.

I'm also assuming that voornam and achternaam come from the per table.

Upvotes: 4

Related Questions