Cursor Vs temporary table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Cursor Vs temporary table

  1. #1
    Join Date
    Jan 2001
    Posts
    318

    Lightbulb

    I need help in deciding if the temporary table ( which I already have in db) would be a good idea to use instead of a cursor in the following eg. as it gives me time out error.
    Here is just the part of the code...

    If inProject > 0 then
    If inRole > 0 then
    /*Cursor to read each resource (Project and Role for slecetion Criteria)*/
    Declare cursor resource_cursor
    IS
    Select Res_ID
    from mwebRes
    where Res_Status <> 20 AND Res_Entity_Type = 3
    AND (Res_ID IN (Select AuthAttr_Res_ID
    From MwebAuthAttrib
    where AuthAttr_Res_ID = Res_ID
    AND (AuthAttr_Work_ID IN
    (Select Work_ID
    From MwebWork
    Where DECODE(inWorkLevel,2,Work_Par2,3,Work_Par3,4,Work_Par4,5,
    Work_Par5,6,Work_par6,7,Work_par7 ,8 ,work_par8,Work_Par9 ) = inProject
    AND Work_Entity_Type = 4 AND Work_Status <> 40))
    AND AuthAttr_Role_ID = inRole));
    Begin
    /*Begin Cursor*/
    Open resource_cursor;
    loop
    Fetch resource_cursor
    into inResID;
    Exit when resource_cursor%notfound ;
    /*updates MwebAuthAttrib with the resources new cost or rate*/
    If inCostorRate = 10 then
    /*Math to determine new figure if its a percentage increase*/
    If inAdjustmentType = 10 AND inDirection = 10 then
    Update MwebAuthAttrib
    Set AuthAttr_Amount = AuthAttr_Amount + (AuthAttr_Amount * InAmountNew)
    Where AuthAttr_Res_ID = inResID
    AND(AuthAttr_Work_ID IN
    (Select Work_ID
    From MwebWork
    Where DECODE(inWorkLevel,2,Work_Par2,3,Work_Par3,4,Work_Par4,5,
    Work_Par5,6,Work_par6,7,Work_par7,8,work_par8,Work_Par9)= inProject))
    AND AuthAttr_Role_ID = inRole AND AuthAttr_Category = 152
    AND AuthAttr_Start_Date >= dtToday;

    Insert into MwebAuthAttrib(AuthAttr_id, AuthAttr_Work_ID, AuthAttr_Res_ID,
    AuthAttr_Auth_ID, AuthAttr_Category, AuthAttr_Start_Date,
    AuthAttr_Finish_Date, AuthAttr_Amount, AuthAttr_Role_ID)
    Select MwebAuthAttrib_SEQ.NextVal,AuthAttr_Work_ID, AuthAttr_Res_ID,
    AuthAttr_Auth_ID, AuthAttr_Category, dtToday, AuthAttr_Finish_Date,
    AuthAttr_Amount + (AuthAttr_Amount * InAmountNew), AuthAttr_Role_ID
    From MwebAuthAttrib
    Where AuthAttr_Res_ID = inResID
    AND(AuthAttr_Work_ID IN
    (Select Work_ID
    From MwebWork
    Where DECODE(inWorkLevel,2,Work_Par2,3,Work_Par3,4,Work_Par4,5,
    Work_Par5,6,Work_par6,7,Work_par7,8,work_par8,Work_Par9)= inProject))
    AND AuthAttr_Role_ID = inRole AND AuthAttr_Category = 152
    AND AuthAttr_Start_Date < dtToday AND AuthAttr_Finish_Date >= dtToday;

    /*Math to determine new figure if its a percentage decrease*/
    Elsif and tons of combinations...
    Sonali

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    There will be overhead on using TEMPORARY TABLE. Cursor is the best option.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width