Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Stored Procedures: If then else

Re: Stored Procedures: If then else

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Thu, 30 Mar 2000 16:31:41 GMT
Message-ID: <8bvvhd$4q7$1@nnrp1.deja.com>


In article <8bvgbt$9g$1_at_weber.a2000.nl>, "J. Park" <johan_park_at_hotmail.com> wrote:
> 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
>

You would need to:
select count(*) into L_cnt from table_leave where.... if ( l_cnt > 0 ) then
...
else
...
end;
but actually, the procedure should just be: CREATE OR REPLACE PROCEDURE TR_CREATE_LEAVE ( p_nAME VARCHAR2, p_STATUS VARCHAR2,
p_ID VARCHAR2 )
IS
BEGIN
update tbl_leave set name = p_name, status = p_status where id = p_id;
if ( sql%rowcount = 0 )
then
insert into tbl_leave values ( p_id, p_name, p_status ); end if;
end;
/
if you believe that you will mostly be updating and inserting infrequently.
if you believe you'll mostly be inserting, perhaps: CREATE OR REPLACE PROCEDURE TR_CREATE_LEAVE ( p_nAME VARCHAR2, p_STATUS VARCHAR2,
p_ID VARCHAR2 )
IS
BEGIN
insert into tbl_leave values ( p_id, p_name, p_status ); exception
when dup_val_on_index then
update tbl_leave set name = p_name, status = p_status where id = p_id;
end;
/
might be better.
>

--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Mar 30 2000 - 10:31:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US