December 20, 2002
Oracle v SQL Server, Part 5
The hacking continues. The very last thing I have to convert from Oracle to SQL Server is a script which performs some conditional processing on my data.
In Oracle I wrote a fairly simple script in PL/SQL which opened in turn and nested three cursors. The nesting is important here, as values from the outer cursor results sets were passed to the inner cursors when they were opened as cursor parameters. Simple, and fairly routine in PL/SQL.
Taking this and converting it to Transact SQL I found a problem. There didn't seem to be any way of saying "open cursor b with this value I have just retrieved from cursor a". Well, you can, but it cannot be repeated. The problem, it seems, is one of scope.
PL/SQL follows the standard convention of most programming languages in that code units are comprised of a declaration section, followed by an execution section (encloded by some kind of markers, e.g. { } or BEGIN END). Transact SQL doesn't do this. So instead of;
DECLARE
cursor a ...
cursor b ...
BEGIN
open a
fetch a row from a
loop
open b with values from current value of a
process
end loop
END;
We have to do something like;
DECLARE a
open a
fetch a value from a
DECLARE b using the value from a
loop
fetch a row from b
process
end loop
Which took a little bit of getting used to. Still, plus ca change.
Posted by Andy Todd at December 20, 2002 03:10 PM