PL/SQL Procedure for automatic INSERT/UPDATE

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;
/ 
Advertisements

2 thoughts on “PL/SQL Procedure for automatic INSERT/UPDATE

  1. You might want to avoid using cursors when they are not necessary, which is most of the time.

    The operation you want to perfom is a merge: http://en.wikipedia.org/wiki/Merge_(SQL) which is part of the SQL standard, even if it’s not well known.

    A couple of resources on cursors:
    http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html
    http://www.oracle.com/technetwork/issue-archive/2008/08-nov/o68plsql-088608.html

    Be aware of what you read on the internet about Oracle technologies. There are many myths that usually translate into suboptimal ways to do the same thing. And with suboptimal I don’t mind not the best but it does the work, I mean bad, as in bad things will happen to you later (as in ORA-01000: maximum open cursors exceeded or performance problems)

    By the way, why are you comitting the insert but not the update?

  2. Thanks for your comments Julio. I didn’t know the existence of MERGE, it is more useful than this procedure. In any case I can keep this as an example of how to write a simple procedure.
    I have added the commit in the update statement, it was a typo.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s