1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Put delay time or sleep time when executing sql

Discussion in 'Oracle' started by kirubagari, May 25, 2011.

  1. kirubagari

    kirubagari New Member

    Code:
    EXEC SQL EXECUTE 
         BEGIN 
            INSERT INTO caruser.fwcarrierhistory_hist
              (sysid, carrierid, carriertype, carrierstate, carrierstatus,
             componentqty, lotid, LOCATION, activity, txntime, txncomment,
             cleancount, usedcount, lastcleandate, grouphistkey, txnkey,
             username)
            SELECT sysid, carrierid, carriertype, carrierstate, carrierstatus,
             componentqty, lotid, LOCATION, activity, txntime, txncomment,
             cleancount, usedcount, lastcleandate, grouphistkey, txnkey,
             username
           FROM caruser.fwcarrierhistory
             WHERE sysid = :sysId; 
            DELETE FROM caruser.fwcarrierhistory
            WHERE sysid = :sysId;
            WAITFOR DELAY '00:00:59';
            INSERT INTO caruser.fwcarrierhistory_pn2m_hist 
               (fromid, linkname, SEQUENCE, valtype, valdata, keytype,
             keydata)
            SELECT fromid, linkname, SEQUENCE, valtype, valdata, keytype, keydata
           FROM caruser.fwcarrierhistory_pn2m 
            WHERE fromid = :sysId; 
            DELETE FROM caruser.fwcarrierhistory_pn2m 
            WHERE fromid = :sysId;
            WAITFOR DELAY '00:00:59';
            INSERT INTO caruser.fwcarriercomment_hist 
               (sysid, commentcode, briefdescription, detaildescription)
            SELECT sysid, commentcode, briefdescription, detaildescription
           FROM caruser.fwcarriercomment 
            WHERE sysid = :txnComment; 
             DELETE FROM caruser.fwcarriercomment 
           WHERE sysid = :txnComment;
             COMMIT;
         END; 
      END-EXEC
    ;

    Attach my sql and i need to put wait for delay command so that the sql will process after 59 second to optimize cpu usage.I dont know how to handle the scenario.
    The changes is i want to reduce no. of records per query compared to current one.I might need to put delay
     
  2. pein87

    pein87 New Member

Share This Page