最近要将sqlserver存储过程转为oracle,有一个不知如何转,向高手求教!
sqlserver code:create procedure dzws
@idtype int,
@planid int,
@newid int,
@uid int output
as
if not exists(select * from manage)
begin
return
end
declare @tempid int
declare @index int
declare @flag intset @flag=0
set @index=0
if @idtype=101
begin
select @id=zk_id from manage
while @index<1000
begin
set @tempid=@id+@index
if(@tempid>=1000)
set @tempid=@tempid-1000
if(@tempid<>0)
begin
if not exists(select * from manage where mid=@tempid+1000)
begin
set @id=@tempid+1000
set @flag=1
update manage set zk_id=@tempid+1
break
end
end
set @index=@index+1
end
sqlserver code:create procedure dzws
@idtype int,
@planid int,
@newid int,
@uid int output
as
if not exists(select * from manage)
begin
return
end
declare @tempid int
declare @index int
declare @flag intset @flag=0
set @index=0
if @idtype=101
begin
select @id=zk_id from manage
while @index<1000
begin
set @tempid=@id+@index
if(@tempid>=1000)
set @tempid=@tempid-1000
if(@tempid<>0)
begin
if not exists(select * from manage where mid=@tempid+1000)
begin
set @id=@tempid+1000
set @flag=1
update manage set zk_id=@tempid+1
break
end
end
set @index=@index+1
end
create procedure dzws
(idtype in number,
planid in number,
newid in number,
uuid out number
)
is
tempid number(10) := 0;
vn_index number(10) := 0;
flag number(10) := 0;
in_cnt number(10) := 0;
vn_zkid number(10) := 0;
vn_id number(10) := 0;
cursor c_zk_id is select zk_id from manager where rownum < 1000 + 1;
begin
select count(1) into in_cnt from manage;
if in_cnt = 0 then
return;
end if;
if idtype = 101 then
for rec_zk_id in c_zk_id
loop
tempid := rec_zk_id.zk_id + vn_index;
if tempid <> 0 then
select count(1) into in_cnt from manage where mid=tempid + 1000;
if in_cnt = 0 then
vn_id := tempid + 1000;
flag := 1;
update manage set zk_id = vn_id + 1;
commit;
end if;
end if;
vn_index := vn_index + 1;
end loop
end if;
end dzws;
for rec_zk_id in c_zk_id
loop
tempid := rec_zk_id.zk_id + vn_index;
小白一个,请别见笑!