Home » SQL & PL/SQL » SQL & PL/SQL » checking a record exists (Oracle 10g)
checking a record exists [message #575352] Tue, 22 January 2013 07:27 Go to next message
sss111ind
Messages: 502
Registered: April 2012
Location: India
Senior Member

Hi All,

Which is the best way to check whether a record exists.

DECLARE
  l_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO l_count FROM emp WHERE empno=7839;
  IF l_count=1 THEN
    dbms_output.put_line('exists');
  ELSE
    dbms_output.put_line('not exists');
  END IF;
END;

DECLARE
  l_ename VARCHAR2(20);
  l_count NUMBER;
BEGIN
  BEGIN
    SELECT ename INTO l_ename FROM emp WHERE empno=7839;
  EXCEPTION
  WHEN no_data_found THEN
    l_count:=0;
  END;
  IF l_count=1 THEN
    dbms_output.put_line('exists');
  ELSE
    dbms_output.put_line('not exists');
  END IF;
END;


DECLARE
  l_ename VARCHAR2(20);
  l_count NUMBER;
BEGIN
  BEGIN
    SELECT ename INTO l_ename FROM emp WHERE empno=7839;
    IF SQL%found THEN
      l_count:=1;
    END IF;
  EXCEPTION
  WHEN no_data_found THEN
    l_count:=0;
  END;
  IF l_count=1 THEN
    dbms_output.put_line('exists');
  ELSE
    dbms_output.put_line('not exists');
  END IF;
END;



Regards,
Nathan

[Updated on: Tue, 22 January 2013 07:30]

Report message to a moderator

Re: checking a record exists [message #575354 is a reply to message #575352] Tue, 22 January 2013 07:54 Go to previous messageGo to next message
cookiemonster
Messages: 11282
Registered: September 2008
Location: Rainy Manchester
Senior Member
Depends what you want to do with the record once it's found. If you want to update it then you should just issue the update, same for delete. If you want to display it to the user then you should just select it obviously.
Checking for existance by its self is rarely worth bothering with.

To address the three examples though:
1) I'd never use count when I expect the result to be one or zero.
2 and 3) the l_count variable is unecessary and the sql%found check is completely pointless (if it wasn't found you'd be in the exception handler). Additionaly I wouldn't select a column if you don't care what the value is. Both could be more simply written as:
DECLARE
  n VARCHAR2(1);
BEGIN
  BEGIN
    SELECT null INTO n FROM emp WHERE empno=7839;
    dbms_output.put_line('exists');

  EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('not exists');
  END;
END;

[Updated on: Tue, 22 January 2013 07:55]

Report message to a moderator

Re: checking a record exists [message #575358 is a reply to message #575354] Tue, 22 January 2013 08:21 Go to previous messageGo to next message
sss111ind
Messages: 502
Registered: April 2012
Location: India
Senior Member

Hi CookieMonster,

I have doubt for below things,correct me if I am wrong.
1)In first example no exception handler is required, because never an exception will occur.
2)In second example exception handler must require(specifically no_data_found exception).

We can do the above things using cursors also ,but not required.

DECLARE
  CURSOR c1
  IS
    SELECT * FROM emp WHERE empno=7839;
BEGIN
  FOR i IN c1
  LOOP
    dbms_output.put_line('exists');
  END LOOP;
END;



Regards,
Nathan

[Updated on: Tue, 22 January 2013 08:22]

Report message to a moderator

Re: checking a record exists [message #575359 is a reply to message #575354] Tue, 22 January 2013 08:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2077
Registered: January 2010
Senior Member
Here, empno is unique. However, if column in question is not unique adding AND ROWNUM = 1 would make it faster. In fact, I always try to add it - who knows, column may becone non unique at some point in the future.

SY.
Re: checking a record exists [message #575360 is a reply to message #575358] Tue, 22 January 2013 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59978
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But you have not the message 'not exists".

cookiemonster's answer is the best one to answer the question.

More, you can remove the exception handler then if the row does not exist you get the exception otherwise you get the message.
As cookiemonster said, it all depends on what you want to do with this information.

Regards
Michel
Re: checking a record exists [message #575361 is a reply to message #575360] Tue, 22 January 2013 08:43 Go to previous messageGo to next message
sss111ind
Messages: 502
Registered: April 2012
Location: India
Senior Member

Hi Michel,

I have tried a lot with first example to get an exception but never I got.So I removed the handlers from my programs .Moreover I am using the first one only regular basis and I am fearing the exceptions.

Regards,
Nathan

Re: checking a record exists [message #575362 is a reply to message #575361] Tue, 22 January 2013 09:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59978
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am fearing the exceptions.


They are one of the most valuable parts of PL/SQL leading (almost) all the programming that was designed at the same time.

Regards
Michel
Re: checking a record exists [message #575363 is a reply to message #575362] Tue, 22 January 2013 09:20 Go to previous message
sss111ind
Messages: 502
Registered: April 2012
Location: India
Senior Member

Thank you all,

I just said like that. Smile

Regards,
Nathan
Previous Topic: Maintaining log table
Next Topic: simple Shell Script - howto do the same in PL/SQL
Goto Forum:
  


Current Time: Thu Dec 18 07:37:33 CST 2014

Total time taken to generate the page: 0.13300 seconds