-- ********************************************** 
-- *** CURSOR . . . FOR UPDATE; OPTIMIZATION ****
-- **********************************************

-- ***************************************************
-- *** PROBLEM: This is PL/SQL code from a trigger ***
-- *** The purpose of the code was to update or    ***
-- *** insert values in a second table based on    ***
-- *** values inserted in the (interface) trigger  ***
-- *** table.                                      ***
-- ***                                             ***
-- *** By using the FOR UPDATE update of a cursor  ***
-- *** instead of first checking if the record     ***
-- *** was existing, and then UPDATE or INSERT     ***
-- *** records, speed increased by 10 times,       ***
-- *** because the trigger caused more UPDATES     ***
-- *** than INSERTS.                               ***
-- ***************************************************

-- ************************************************************
-- *** Feel free to use this SQL-code, but please           ***
-- *** do not remove these copyright lines.                 ***
-- *** (C) Roger Felix - Lund - Sweden                      ***
-- *** [roger_felix@hotmail.com, web_master@work.utfors.se] ***
-- *** URL: http://hem.fyristorg.com/publish/index.html     ***
-- ************************************************************

-- *****************
-- *** SLOW CODE ***
-- *****************

DECLARE
  cnt  number(1);  /* 0 OR 1 */
BEGIN
   select count(*) into cnt from THE_TABLE
       where TABLE_ID = :NEW.THE_CODE;  /* PK */
   IF cnt = 0 THEN
     INSERT INTO THE_TABLE ( 
          . . . . ,
          . . . .
       ) VALUES (
          :NEW.THE_CODE,
          . . . . ,
          . . . .
      );
   ELSE
       UPDATE THE TABLE SET 
          . . . .  = . . . . ,
          . . . .  = . . . . ,
          . . . .  = . . . . 
       WHERE TABLE_ID = :NEW.THE_CODE;
   END IF;
END;



-- *****************
-- *** FAST CODE ***
-- *****************

DECLARE
   cursor the_cursor(v in number) is select TABLE_ID from THE_TABLE
       where TABLE_ID = v FOR UPDATE;
   foundflag varchar2(12) := 'FALSE';
BEGIN
   for c_loop in the_cursor(:new.the_code) loop
        foundflag := 'TRUE';
       UPDATE THE_TABLE SET 
          . . .  = . . . ,
          . . .  = . . . 
      WHERE CURRENT OF THE_CURSOR;
   end loop;

   IF foundflag = 'FALSE' THEN
     INSERT INTO THE_TABLE ( 
          . . . . ,
          . . . .
       ) VALUES (
          :NEW.THE_CODE,
          . . . . ,
          . . . .
      );
    END IF;
END;
/

=============================END==============================