--- Colin Wetherbee <[EMAIL PROTECTED]> wrote:
> Hardy Merrill said:
> > Kristian Nielsen [EMAIL PROTECTED] wrote:
> >> Could you briefly explain what an INSERT/UPDATE cursor is?
> >
> > I also don't know what an INSERT/UPDATE cursor is.
>
> I know how a SELECT cursor works, but INSERT/UPDATE seems voodoo to me.
> My current PostgreSQL session doesn't seem to like it too much, either.
> It can't parse INSERT or UPDATE after CURSOR FOR. I looked in my
> PostgreSQL reference book, and I don't see a thing on it.
>
> Would anyone care to elaborate on the INSERT/UPDATE cursor? I can't seem
> to wrap my head around how it would work, seeing as there wouldn't really
> be a result set to traverse, like there is with a SELECT cursor.
In Oracle, "update cursor" is used to describe a cursor which has
the "FOR UPDATE" clause. This is a regular select cursor with
two additional features: 1) rows are locked for update , 2) you can
identify the given row in a subsequent UPDATE statement by referencing
the cursor instead of row values. For example:
declare
cursor X is select * from <table> FOR UPDATE;
begin
open X;
loop
UPDATE <table> set <column> = <value> where CURRENT OF X;
end loop;
end;
Obviously this minimal example would be silly in practice
since the result is the same as a simpler single update statement.
But in real life, one sometimes needs to manually loop over
rows, perform some more complex operations than are practical
in a single update statement, and possibly decide to update
a given row. Without the "WHERE CURRENT OF <cursor>" clause,
you'd have to identify the particular row in some other,
more cumbersome way.
The original poster was correct that you can't do this directly in
DBI. However, below is a technique which is functionally identical:
$sth = $dbi->prepare("select ROWID, x.* from <table> x FOR UPDATE");
$stu = $dbh->prepare("update <table> set <column> = <value> where ROWID = ?");
$sth->execute;
while ($r = $sth->fetchrow_hashref) {
$stu->execute($r->{ROWID});
}
This is essentially what's happening with the WHERE CURRENT OF clause.
Mark
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
