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:


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.

Unless you are doing complex algorithms on the data for the insert, you can do it without the cursor. Something like this:


Andy

Yes, but in that case row that was not in the SELECT can be UPDATED even if the WHERE is the same, cannot?

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

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.