Re: Trouble with a simple stored procedure..

From: Thomas Kyte <thomas.kyte_at_oracle.com>
Date: 20 Nov 2004 07:30:24 -0800
Message-ID: <7b0834a8.0411200730.1ed0ef1e@posting.google.com>

gary_at_designercontrols.com (Gary) wrote in message news:<c542a6f3.0411191132.1e78a835_at_posting.google.com>...
> Hello, I'm a seasoned SQL Server developer and new to Oracle. I am
> trying to write a simple SP that detects if a record exists for a
> particular GUID.
>
> If not, it creates the record with a new id, the GUID and an e-mail
> address. If it does already exist, it updates the e-mail address.
> Either way, it returns the id back to the caller.
>
> The table looks like this..
>
> CREATE TABLE SCANA.SCOOPCREATORS
> (
> SCOOPID NUMBER(1) NOT NULL,
> GUID VARCHAR2(36) NOT NULL,
> EMAIL VARCHAR2(500) NOT NULL,
> CONSTRAINT SYS_IOT_TOP_193763
> PRIMARY KEY (SCOOPID) ENABLE VALIDATE
> )
>
>
>
>
> I'm trying to create the following SP but it fails. Can anyone help?
>
> Thanks,
> Gary
>

Even in sqlserver, a better way would be:

try to update (that does the existence check) and if no rows updated
insert

so, in Oracle's PLSQL (which you'll find to be very different from t-sql, I personally found PLSQL to be slightly more "usable" as a programming language -- but you'll want to at least peruse the documentation...)

ops$tkyte_at_ORA9IR2> create or replace procedure get_scoop_creator_id

  2  ( p_creatorid OUT integer,
  3    p_guid      in varchar2,
  4    p_email     in varchar2 )

5 is
6 begin

  7          update scoopcreators
  8             set email = p_email
  9           where guid = p_guid
 10           returning scoopid into p_creatorid;
 11
 12          if ( sql%rowcount = 0 )
 13          then
 14                  insert into scoopcreators ( scoopid, guid, email
)
 15                  values ( seq_scoopcreators.nextval, p_guid,
p_email )
 16                  returning scoopid into p_creatorid;
 17          end if;

18 end;
19 /

Procedure created.

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> variable n number
ops$tkyte_at_ORA9IR2> select * from scoopcreators;

no rows selected

ops$tkyte_at_ORA9IR2> exec get_scoop_creator_id( :n, 'abc', 'x_at_y' );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA9IR2> print n

ops$tkyte_at_ORA9IR2> select * from scoopcreators;

SCOOPID GUID EMAIL
---------- ---------- ----------

1 abc x_at_y

ops$tkyte_at_ORA9IR2> exec get_scoop_creator_id( :n, 'abc', 'a_at_b' );

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA9IR2> print n

ops$tkyte_at_ORA9IR2> select * from scoopcreators;

SCOOPID GUID EMAIL
---------- ---------- ----------

1 abc a_at_b

You probably don't mean "number(1)" in there for the create table.... that would hold 1 digit!

>
> CREATE OR REPLACE PROCEDURE SCANA.GET_SCOOP_CREATOR_ID (
> creatorId OUT INTEGER,
> guid IN VARCHAR2,
> email IN VARCHAR2 ) IS
>
> BEGIN
> IF NOT EXISTS (SELECT CREATORID FROM ScoopCreators WHERE GUID =
> guid) BEGIN
> INSERT INTO SCOOPCREATORS (CREATORID, GUID, EMAIL)
> VALUES(SCANA.SEQ_SCOOPCREATORS.NEXTVAL, guid, email);
> ELSE
> UPDATE SCOOPCREATORS SET EMAIL=email WHERE GUID=guid
> END
>
> SELECT CREATORID INTO creatorId FROM ScoopCreators WHERE GUID =
> guid
> END;
> /
Received on Sat Nov 20 2004 - 09:30:24 CST