Re: Help! Sybase SQL to Oracle...

mark rostron <markro2_at_atlantis.rosemount.com> wrote:

>Yep. The syntax of the update statement is different across SYBASE and ORACLE.
>In ORACLE, you have a construct called a cursor in ORACLE pl/sql (the equivalent to
>transactSQL in sybase) which you can use to emulate this.
No you don't......

>Apologies for any errors in the sybase stuff on account of it's been a little while:
 

>eg create table a(i integer,a1 integer,a2 integer);
> create table b(i integer,b1 integer,b2 integer);
> create table c(i integer,c1 integer,c2 integer);
 

>Field i is the foreign key relating tables a,b,c.
 

>now, say you want to update c from a selection across (a,b).
 

>SYBASE syntax:
>--------------
>update c
>set c1 = a.a1
> c2 = b.b2
>from a,b
>where a.i = c.i
>and b.i = c.i
>go

You can do the above in a single update in Oracle as well. I can't think of an example from Sybase using their extensions to the update/delete statment that cannot be done in a single query under Oracle as well.

ORACLE syntax:

                   from a, b
                  where a.i = c.i
                    and b.i = c.i )

>ORACLE syntax:
>--------------
>declare
> cursor ab_cursor
> is
> select a.i id, a.a1 afield, b.b1 bfield
> from a,b
> where a.i = b.i;
>begin
> for ab_rec in ab_cursor
> loop
> update c
> set
> c1=ab_rec.afield,
> c2=ab_rec.bfield
> where c.id = ab_rec.id;
> end loop;
>end;
>/
 

>ref manual on using cursors, cursor loops, also in the delete/update embedded
>statements, also look at 'for update of' and 'where current of' clauses in cursor
>syntax.
 

>mr

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Fri Jan 19 1996 - 00:00:00 CET