RE: PL/SQL Question

From: David Barbour <DBarbour_at_connectsouth.com>
Date: Wed, 13 Sep 2000 10:09:31 -0500
Message-Id: <10618.116839@fatcity.com>

Helmut,

Your "problem" is pretty basic(which is why I can answer it!). There are several ways to accomplish what you're trying to do - how you choose to proceed depends on whether or not you're running a batch or diong individual records as well as personal preference. Easiest would probably be to make use of the %FOUND or %NOTFOUND. Since I don't know how you're going to get the variable to compare to the primary key, I can't be too specific. However, something like the following is one approach.

Create or replace procedure update_table(

                recnum number)
As

	Cursor primary_key is
		Select PK from table
		Where PK = recnum;

	v_PK		table.PK%type;

Begin
	Open primary_key;
	Fetch primary_key into v_PK;
	If v_PK%FOUND then
		Update table;
	Else;
		Insert into table;
	End If;

End
/
show errors                 

David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
dbarbour_at_connectsouth.com

-----Original Message-----
From: Helmut Daiminger [mailto:hdaiminger_at_vivonet.com] Sent: Tuesday, September 12, 2000 7:58 PM To: Multiple recipients of list ORACLE-L Subject: PL/SQL Question

Hi!

I do have a weird problem to solve. I wanna check if a specific record already exists in a table (select * from table where PrimaryKey = 1234). If it does, I wanna do an update on that record and if it doesn't, I wanna insert a record.

What would be the best (and least expensive) way to achieve this in a procedure?

Thanks,
Helmut

-- 
Author: Helmut Daiminger
  INET: hdaiminger_at_vivonet.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Sep 13 2000 - 10:09:31 CDT