Imagine we have a table like the following one:
CREATE TABLE MYUPDATE_TABLE ( OUID NUMBER(10) NOT NULL, SERVICE NUMBER(10) NOT NULL, DAY DATE NOT NULL, NUM NUMBER(10) NOT NULL )
This table stores the number of events that a SERVICE has in a given DAY in the column NUM.
Most of the times we will want just to update the number stored at NUM for the given date and servive given by the columns DAY and SERVICE.
But we only can make an update if that row exists, if not we must to create it with an INSERT.
This process can be donde by checking first if the desired row exists. If not exists insert this NUM value into the table for the given DAY and SERVICE.
If it exists then get the number NUM and add it the new desired value.
This is done with the following Oracle procedure:
create or replace procedure MYUPDATE_TABLE(nService NUMBER,nDay DATE, nNUM NUMBER) is sOUID NUMBER(19); oldNUM NUMBER(19); cursor c1 is SELECT OUID, NUM FROM VE_EMLOGSERVICE WHERE SERVICE=nService AND DAY=nDAY; begin open c1; fetch c1 into sOUID,oldNUM; if (c1%notfound) then INSERT INTO MY_TABLE(OUID,SERVICE,DAY) VALUES (MY_SEQ.nextval,nService,nDay,nNUM); commit; else UPDATE VE_EMLOGSERVICE SET NUM = oldNUM+nNUM WHERE OUID = sOUID; commit; end if; close c1; end; /