Re: Stored Procedures: If then else - thanks

Thank you all.
It works.

Johan

"Thomas J. Kyte" <tkyte_at_us.oracle.com> wrote in message news:8bvvhd$4q7$1_at_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

>
>