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
