Home » SQL & PL/SQL » SQL & PL/SQL » suggest to user sql%found or sql%rowcount
suggest to user sql%found or sql%rowcount [message #253911] Wed, 25 July 2007 05:22 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have a table, and i am just verifying whether a particular
row exists or not, if not, insert, else update

i have 2 logics here, can anyone suggest me the better one to use?



declare
	l_id number;
	BEGIN
            SELECT id INTO l_ID FROM ECFN WHERE ID = 200;
	    if sql%found then

	    --update statemtn

	    else

	    --insert statement
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('Missing FunctionalityID: 200');
            
         END;







declare
	l_id number;
	BEGIN
            SELECT id INTO l_ID FROM ECFN WHERE ID = 200;
	    if sql%rowcount=0 then

	    --insert statement

	    else

	    --update statement
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('Missing FunctionalityID: 200');
            
         END;



or any other better alternative?
Re: suggest to user sql%found or sql%rowcount [message #253917 is a reply to message #253911] Wed, 25 July 2007 05:27 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
sorry forget to put end if
Re: suggest to user sql%found or sql%rowcount [message #253919 is a reply to message #253911] Wed, 25 July 2007 05:32 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member

SQL> select * from ecfn;

        ID
----------
         3
         3




declare
	l_id number;
	BEGIN
            SELECT id INTO l_id FROM ECFN WHERE ID = 3;
	    if sql%found then

	    --update statemtn
	    update ecfn set id=3;
	end if;
	EXCEPTION
            WHEN NO_DATA_FOUND THEN
	   insert into ecfn values(3);
       END;


Re: suggest to user sql%found or sql%rowcount [message #253921 is a reply to message #253917] Wed, 25 July 2007 05:35 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Both will fail with a no_data_found if id 200 is not present in your table:
SQL> set serverout on
SQL>
SQL> DECLARE
  2     l_id   NUMBER;
  3  BEGIN
  4     SELECT 1
  5     INTO   l_id
  6     FROM   DUAL
  7     WHERE  1 = 2;
  8
  9     IF SQL%FOUND
 10     THEN
 11        DBMS_OUTPUT.put_line ('update statement');
 12     ELSE
 13        DBMS_OUTPUT.put_line ('insert statement');
 14     END IF;
 15  EXCEPTION
 16     WHEN NO_DATA_FOUND
 17     THEN
 18        DBMS_OUTPUT.put_line ('Missing FunctionalityID: 200');
 19        RAISE;
 20  END;
 21  /
Missing FunctionalityID: 200
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 19


MHE
Re: suggest to user sql%found or sql%rowcount [message #253923 is a reply to message #253919] Wed, 25 July 2007 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, sql%rowcount gives you the number of rows you already fetched.

Regards
Michel
Re: suggest to user sql%found or sql%rowcount [message #253948 is a reply to message #253911] Wed, 25 July 2007 06:19 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
i have a table, and i am just verifying whether a particular
row exists or not, if not, insert, else update

i have 2 logics here, can anyone suggest me the better one to use?

None of them.
Use the MERGE statement, that's what it's for.
Re: suggest to user sql%found or sql%rowcount [message #253949 is a reply to message #253911] Wed, 25 July 2007 06:27 Go to previous message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
there u go, i think this works

any suggestions?


SQL> insert into ecfn values(1,'one');

1 row created.

SQL> 




declare
	l_id number;
	BEGIN
            SELECT id INTO l_id FROM ECFN WHERE ID =3;
	    if sql%found then

	   
	    update ecfn set id=3,name='three' where id=3;
	end if;
	EXCEPTION
            WHEN NO_DATA_FOUND THEN
	   insert into ecfn values(3,'three');
       END;



even if i run it again with 3, it only updates
Previous Topic: how to put record in error log file
Next Topic: alter table with dynamic sql
Goto Forum:
  


Current Time: Mon Dec 05 02:28:45 CST 2016

Total time taken to generate the page: 0.07765 seconds