Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   Put delay time or sleep time when executing sql (http://www.go4expert.com/forums/delay-time-sleep-time-executing-sql-t25896/)

kirubagari 25May2011 13:33

Put delay time or sleep time when executing sql
 
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

pein87 26May2011 07:07

Re: Put delay time or sleep time when executing sql
 
http://msdn.microsoft.com/en-us/library/ms187331.aspx


All times are GMT +5.5. The time now is 09:42.