Re: Stored Procedures: If then else

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 30 Mar 2000 14:35:51 +0200
Message-ID: <954419803.881.0.pluto.d4ee154e@news.demon.nl>

J. Park <johan_park_at_hotmail.com> wrote in message news:8bvgbt$9g$1_at_weber.a2000.nl...
> I've made a procedure that should check if the record
> already exists, if so modify the record, if not insert a new
> record. The sqlplus says that the procedure is compiled
> with errors. I think the error is in the line with select statement.
>
> CREATE OR REPLACE PROCEDURE TR_CREATE_LEAVE (
> NAME VARCHAR2, STATUS VARCHAR2, ID VARCHAR2
> ) IS
> BEGIN
> DECLARE
> NAME_NEW TBL_LEAVE.NAME%TYPE := NAME;
> STATUS_NEW TBL_LEAVE.STATUS%TYPE := STATUS;
> ID_NEW TBL_LEAVE.ID%TYPE := ID;
> BEGIN
> IF SELECT COUNT(*) FROM tbl_leave where ID=ID_NEW > 0 THEN
> UPDATE tbl_leave SET NAME=NAME_NEW WHERE ID=ID_NEW;
> UPDATE tbl_leave SET STATUS=STATUS_NEW WHERE ID=ID_NEW;
> ELSE
> INSERT INTO tbl_leave VALUES (ID, NAME, STATUS);
> END IF;
> END;
> END;
>
> thanks in advance,
> Johan
>
>
>
>

Looks like SQL-server sp
A typical Oracle approach is

CREATE OR REPLACE PROCEDURE TR_CREATE_LEAVE ( NAME_NEW VARCHAR2, STATUS_NEW VARCHAR2, ID VARCHAR2 ) IS

       curs_tbl_leave (p_id in number) CURSOR IS
       select name, status
       from tbl_leave
       where id = p_id
       for update of name, status;

 rec_tbl_leave curs_tbl_leave%ROWTYPE;
 BEGIN
 OPEN curs_tbl_leave(id);
 fetch curs_tbl_leave into rec_tbl_leave; -- assuming id is UNIQUE  IF curs_tbl_leave%FOUND then

     update tbl_leave set name = name_new, status = status_new
     where current of curs_tbl_leave;
 else
      insert into tbl_leave values (id, name_new, status_new); -- this is

necessary because of scoping reasons
end if;
close cur_tbl_leave;
end;
/

This one should work as efficient as possible (you really don't need the two update statements)

Regards,

Sybrand Bakker, Oracle DBA Received on Thu Mar 30 2000 - 06:35:51 CST