oracle与sqlserver在定义变量时的一些区别

当在准备做oracle的批量插入时，发现插入不了，故此学习了一下，
记录下相同的结果，oracle与sqlserver语法的比较

 

create table t(id int,y int,z varchar(100));

----oracle
declare
i int ;
j int :=1;
begin
  while j<5 loop
  select nvl(max(id),0) +1 into i from t;
  insert into t values(i,j,'test'||i);
  j:=j+1;
  end loop;
end;

declare
i int :=1;
j int :=1;
begin
  while j<5 loop
  insert into t values(i,j,'test'||i);
  j:=j+1;
  end loop;
end;

 

-----sqlserver
declare @i int
declare @j int
set @i=1
set @j=1
while @j<5
begin
select @i=isnull(max(id),0)+1  from t
insert into t select @i,@j,'test'+cast(@i as varchar(100))
set @j=@j+1
end

declare @i int
declare @j int
set @i=1
set @j=1
while @j<5
begin
select @i=isnull(max(id),0)  from t
insert into t select @i,@j,'test'+cast(@i as varchar(100))
set @j=@j+1
end