1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

multiple cursor with date handling issue reg

Discussion in 'SQL PL/SQL' started by laxman, Mar 10, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear all,
    i am using three cursors in one pl/sql block each of this three cursor will fetch data based on column of date data type.i believe due to each condition relate to same table,contradiction may arise i.e

    1> when code will execute , cursor c1 will do the job perfectly but c2 & c3 here may not be able to solve the purpose since the job has already been done by cursor c1 which i am not sure.my doubts i need all cursor to work based on each condition.
    any suggestion from your side could sort out this issues.

    Thanks n regards
    Laxman



    Code (SQL):
    DECLARE

    BEGIN

    FOR c1 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 8/24 AND statuscode=1 AND assigned_personid IS NOT NULL)loop
                         
                         UPDATE srequest SET STATUS=OPEN WHERE reqid=c1.reqid;
                         commit;
             
              END loop;

    FOR c2 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 1 AND statuscode=1 AND assigned_personid IS NULL)loop
                         
                         UPDATE srequest SET STATUS=OPEN WHERE reqid=c2.reqid;
                         commit;
             
              END loop;

    FOR c3 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 14 AND statuscode=1 AND assigned_personid IS NULL)loop
                         
                         UPDATE srequest SET STATUS=OPEN WHERE reqid=c3.reqid;
                         commit;
             
              END loop;

    END;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The real problem with this code is the commits inside loops. Rewrite the code like this:

    Code (SQL):
    BEGIN

    FOR c1 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 8/24 AND statuscode=1 AND assigned_personid IS NOT NULL)loop
                         
                         UPDATE srequest SET STATUS=OPEN WHERE reqid=c1.reqid;
             
              END loop;

    commit;

    FOR c2 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 1 AND statuscode=1 AND assigned_personid IS NULL)loop
                         
                         UPDATE srequest SET STATUS=OPEN WHERE reqid=c2.reqid;
             
              END loop;

    commit;

    FOR c3 IN(SELECT reqid FROM request WHERE lastmoddate<sysdate - 14 AND statuscode=1 AND assigned_personid IS NULL)loop
                         
                         UPDATE srequest SET STATUS=OPEN WHERE reqid=c3.reqid;
             
              END loop;

    commit;

    END;
    /
     
    As for your concern that the cursors 'may not be able to solve the purpose' I wonder why you think such things. I suggest you run the queries in sequence and note that they do, indeed, 'solve the purpose'.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thanks sir for ur kind responce,yes i agree this will definitly work,what my intention was that all the three cursor are doing the same job based on different condition.if u can see c2 and c3 .

    cursor c2---> lastmoddate< sysdate - 1

    cursor c3 ---> lastmoddate< sysdate - 14

    job of cursor c3 is already been done by c2 since sysdate - 14 < sysdate - 1,this may cause a replicate of data when c3 will start execute.what i need here is in order to avoid the replicate of data can i merge all the job into single cursor ??? will it work !!!

    need ur suggestion kindly

    Regards
    Laxman
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It could be merged as


    begin

    Code (SQL):

    FOR c IN(SELECT reqid
              FROM request
             WHERE statuscode=1
               AND (( assigned_personid IS NULL  AND ( sysdate - 1 OR lastmoddate<sysdate - 14  ))
                    OR
                    ( assigned_personid IS NOT NULL  AND ( lastmoddate<sysdate - 8/24 )))
             )
                   loop
                         
                         UPDATE srequest SET STATUS='open' WHERE reqid=c.reqid;
             
              END loop;

    commit;
    Or using straight SQL as follows


    Code (SQL):
    UPDATE  request
       SET  STATUS='Open'
     WHERE statuscode=1
       AND (( assigned_personid IS NULL  AND ( sysdate - 1 OR lastmoddate<sysdate - 14  ))
            OR
            ( assigned_personid IS NOT NULL  AND ( lastmoddate<sysdate - 8/24 )));
     
  5. sfardin

    sfardin Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    you can use a single cursor :

    FOR c IN(SELECT reqid
    FROM request
    WHERE statuscode=1
    AND (( assigned_personid IS NULL AND ( lastmoddate<sysdate - 14 ))
    )
    loop

    UPDATE srequest SET STATUS='open' WHERE reqid=c.reqid;

    END loop;


    The condition lastmoddate<sysdate - 14 will pickup all the records meeting criteria sysdate -1 and sysdate - 8/24
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    laxman: You're right when you say "c3 is already being done by c2" and they can be merged into one (as the other conditions on the table are identical).

    sfardin: It looks like you've not taken into account the conditions in c1 where "assigned_personid IS NOT NULL".

    One cursor can be used to do the update (see code below) and generally that's what's recommended as far as possible to optimize:

    Code (SQL):
    FOR c IN
      (SELECT reqid
        FROM request
        WHERE statuscode = 1
          AND ( ( assigned_personid IS NULL
                  AND lastmoddate < SYSDATE - 1 )
             OR ( assigned_personid IS NOT NULL
                  AND lastmoddate < SYSDATE - 8/24 ) )
    LOOP
      UPDATE srequest SET STATUS='open' WHERE reqid=c.reqid;
    END LOOP;

    COMMIT;
    Regards,
    Rajen.