We help IT Professionals succeed at work.

exists in pl/sql

dojjol
dojjol asked
on
Hello All,
I am trying to run the block but it fails at where exists.

Please help

DECLARE
status_val number;
BEGIN
FOR to_delete_person IN
(
    SELECT ps.id
      FROM person ps;
)
  LOOP
  status_val := 0;
 
  select 1 into status_val from dual where exists
  (
      select status from person_status
      where person_status.id = ps.id
  );
 
  if(status_val = 1) then
    dbms_output.put_line(' person '||to_delete_person.id);
  end if;
 
  END LOOP;
END;
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Why use exists anyway?

select count(*) into status_val from person_status where person_status.id = ps.id;

if(status_val > 0) then
...
Commented:
>>  where person_status.id = ps.id

I don't see where ps is defined
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>I don't see where ps is defined

Missed that.  Yes it should be:

select count(*) into status_val from person_status where person_status.id = to_delete_person.id;




 

Commented:
>>Missed that.  Yes it should be:

Yeah, sorry, should've been more specific - I was referring to the OP, as your post was already clear enough!
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>I was referring to the OP

I figured that.  But it pointed out that my first post was incorrect because of it.  Good catch!
Most Valuable Expert 2011
Top Expert 2012
Commented:
is id unique?  based on the name I assume it is, but if not you may want to do something like this...


select count(*) into status_val from person_status where person_status.id = ps.id and rownum = 1;

or, if you'd rather have an exception when not exists


select 1 into status_val from person_status where person_status.id = ps.id and rownum = 1;

awking00Information Technology Specialist
Commented:
I think your procedure could be simplified to the attached.
proc.txt