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 ?
thanks again
-Charles
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from 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.
