Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    21

    Question Unanswered: Cursor selecting, updating and inserting data

    Hi,

    i need to create stored procedure with four arguments
    IN T1 TIMESTAMP,
    IN T2 TIMESTAMP,
    IN COEFFICIENT INTEGER,
    OUT ROWS_AFFECTED

    This procedure should read rows from table TAB1 and mark rows with date between T1 and T2 as invalid (by setting STATE flag to 0, for example), but it also shoud create new rows with some old values and with the new value -COEFFCIENT and return numbr of processed rows.

    Something like this:

    Code:
    CREATE PROCEDURE SP1 (args...)
    BEGIN
    
    ? CREATE CURSOR c1 FOR SELECT ID, VAL1, VAL2, Tx FROM TAB1 WHERE (T1 < Tx AND Tx < T2);
    
    OPEN c1;
    
    ?  ROWS_AFFECTED = c1.NUMEBER_OF_ROWS in the cursor
    
    ? WHILE READ FROM CURSOR, FETCH or something like this---
    
    ? UPDATE TAB1 SET STATE = 0 WHERE ID = c1.ID;
    
    ? INSERT INTO TAB1 (VAL1, VAL2, COEFF, Tx) VALUES (c1.VAL1, c1.VAL2, COEFF, c1.Tx);
    
    CLOSE c1;
    
    END;

    But there is a lot of things I don't know how to write. Is it posible to create cursor for select that updates data? How get number of affected rows, what is the syntax and so on...

    Can somebody help me, please?

    DB2 v9.5.0.808 (Express), OS Win 2003 Server

    Thanks a lot.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Unless you are doing complex algorithms on the data for the insert, you can do it without the cursor. Something like this:

    Code:
    CREATE PROCEDURE SP1 (args...)
    BEGIN
     INSERT INTO TAB1 (VAL1, VAL2, COEFF, Tx) select c1,c2,c3,c4 from ...
    
    
     UPDATE TAB1 SET STATE = 0 WHERE ...
    
    CLOSE c1;
    
    END@

    Andy

  3. #3
    Join Date
    Apr 2009
    Posts
    21
    Yes, but in that case row that was not in the SELECT can be UPDATED even if the WHERE is the same, cannot?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There would be a slight possibility, depending on the concurrency rules being used. You could declare a temp table insert the rows into it, then do the update based on the contents of the temp table then do the insert also based on the contents of the temp table.

    You could use a cursor, but then you have to declare a condition holder, then a loop to process through the cursor. I just try to avoid them if possible.

    Andy

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Besides: if you can use set-oriented processing in SQL instead of procedural logic, you have a very good chance of achieving better performance. The reason is that databases systems are/were tuned for decades to set processing.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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