On Friday, January 25, 2013 12:42:51 PM UTC-8, Charles Monteiro wrote: > > so here's a complication to my oracle stored proc translation , namely I > need to update the specific object (row) I'm on. I am now using limit to > spin thru the dataset so I'm getting rows represented as hashes. I need to > update that row which that current hash in the iteration represents. > > Here's a stored proc snippet that illustrates my woes: > > FOR row IN my_cursor > LOOP > IF (row.status = 'OP') AND > ((row.direction = 'I') OR > (row.status != 'NT' AND row.status != 'TA')) THEN > IF (row.status != 'RJ') THEN > UPDATE my_table > SET status = 'HD', > status2 = 'Held Prior Days Msg' > WHERE CURRENT OF my_cursor; > END IF; > IF row.seq_nbr > (seq + 1) THEN > epn_seq := seq + 1; > UPDATE hdr_msg > SET seq_nbr = seq > WHERE CURRENT OF my_cursor; > UPDATE my_table2 > SET Hdr_Nbr = epn_seq > WHERE Link_Name = row.Link_Name > AND Direction = row.Direction > AND Hdr_Nbr = row.seq_nbr; > ELSE > epn_seq := row.seq_nbr; > END IF; > ELSE > DELETE FROM hdr_msg > WHERE CURRENT OF my_cursor; > DELETE FROM dtl_msg > WHERE Link_Name = row.Link_Name > AND Direction = row.Direction > AND Hdr_Nbr = row.seq_nbr; > END IF; > END LOOP; > > so that beast above has this notion of Oracle's Where Current Of > > which is: > > Oracle/PLSQL: WHERE CURRENT OF Statement > > If you plan on updating or deleting records that have been referenced by a > Select > For Update <http://www.techonthenet.com/oracle/cursors/for_update.php> > statement, > you can use the *Where Current Of* statement. > > The syntax for the *Where Current Of* statement is either: > > UPDATE table_name > SET set_clause > WHERE CURRENT OF cursor_name; > > OR > > DELETE FROM table_name > WHERE CURRENT OF cursor_name; > > The Where Current Of statement allows you to update or delete the record > that was last fetched by the cursor. > This seems to be non ansi i.e. oracle, derby, db2 have the construct but > I'm guessing there's no support for this in sequel. However, since I do > have to port this what would be the suggested alternative and is there any > way of ball parking the relative delta in update performance ? >
If you are planning on dropping down to the driver level via synchronize, and using cursors to iterate over your rows, that SQL should work directly. If you plan to do this at the Sequel level, the way to do what you want is to reference the primary key for the row when updating/deleting: DB[:table].where(:primary_key_column=>current_primary_key_value).update(hash_of_new_values) DB[:table].where(:primary_key_column=>current_primary_key_value).delete No idea what the difference is in update performance, though. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
