MSSQL2005存储过程转换成Oracle的

Question

求高手帮忙转换下，小弟刚刚接触Oracle的存储过程，参考下学习学习
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[p_GetTask]
@operid varchar(10),
@skillids varchar(250),
@time int,
@taskxml varchar(MAX) out,
@worktime int out,
@batchid varchar(250) out,
@ID varchar(250) out,
@workid int out,
@skillid varchar(50) out
AS
BEGIN
DECLARE @oldoperid varchar(50)
DECLARE @havefinishnum int
DECLARE @needopernum int
DECLARE @status int
DECLARE @recordcount int
DECLARE @gettaskcount int
-- DECLARE @sql nvarchar(256)
-- SET @sql = 'SELECT ID,WorkID,WorkInfo.OperID,TaskXML,[Status],WorkInfo.SkillID,HaveFinishNum,NeedOperNum,worktime,BatchID FROM WorkInfo WHERE NeedOperNum > HaveFinishNum AND WorkInfo.SkillID in('+@skillids+') ';
-- EXEC ('DECLARE TASKS CURSOR FOR ' + @sql + ' FOR UPDATE')
DECLARE TASKS CURSOR FOR
SELECT ID,WorkID,WorkInfo.OperID,TaskXML,[Status],WorkInfo.SkillID,HaveFinishNum,NeedOperNum,worktime,BatchID
FROM WorkInfo,OperSkill
WHERE NeedOperNum > HaveFinishNum AND WorkInfo.SkillID=OperSkill.SkillId AND OperSkill.OperId=@operid 
FOR UPDATE 
BEGIN TRY
 OPEN TASKS
END TRY
BEGIN CATCH
GOTO ERR2
END CATCH
BEGIN TRY
FETCH NEXT FROM TASKS INTO @ID,@workid,@oldoperid,@taskxml,@status,@skillid,@havefinishnum,@needopernum,@worktime,@batchid
END TRY
BEGIN CATCH
GOTO ERR3
END CATCH
IF @@error!=0 GOTO ERR
SET @recordcount = 0
WHILE @@fetch_status = 0
BEGIN
IF @oldoperid LIKE '%'+rtrim(@operid)+'%'
BEGIN TRY
FETCH NEXT FROM TASKS INTO @ID,@workid,@oldoperid,@taskxml,@status,@skillid,@havefinishnum,@needopernum,@worktime,@batchid
CONTINUE
END TRY
BEGIN CATCH
GOTO ERR4
END CATCH
IF @havefinishnum > 0
SET @oldoperid = @oldoperid + ',' + @operid
ELSE
SET @oldoperid = @operid
IF @needopernum = @havefinishnum + 1
SET @status = 100
ELSE
SET @status = @status + 1

IF @status>100
BEGIN
CONTINUE
        END

IF @gettaskcount > 5 GOTO ERR1
    BEGIN TRY UPDATE WorkInfo SET HaveFinishNum=@havefinishnum+1,OperID=@oldoperid,[status]=@status --where workid=@workid
--where SkillID= @skillid and ID =@ID
WHERE CURRENT OF TASKS
END TRY
BEGIN CATCH
SET @gettaskcount = @gettaskcount + 1
FETCH NEXT FROM TASKS INTO @ID,@workid,@oldoperid,@taskxml,@status,@skillid,@havefinishnum,@needopernum,@worktime,@batchid
CONTINUE
END CATCH
IF @@error!=0 GOTO ERR
SET @recordcount = 1-- BEGIN TRANSACTION trsinsert
INSERT INTO TempWorkInfo(WorkID,ID,OperID,WriteTime,Req_sub_Time) Values(@workid,@ID,@operid,getdate(),dateadd(s,@worktime+@time,getdate()))
-- INSERT INTO [MANAGE].[dbo].[RECOperWorkInfo](ID,BatchID,OperID,SkillID)  VALUES (@ID,@batchid,@operid,@skillid)
-- commit TRANSACTION trsinsert BREAK
END -- the end
CLOSE TASKS
DEALLOCATE TASKS
if @@fetch_status!=0
begin
return @@fetch_status;
end
else
begin
RETURN  @recordcount
end
-- for abnormal
ERR:
BEGIN
CLOSE TASKS
DEALLOCATE TASKS
RETURN -10
END

ERR1:
BEGIN
CLOSE TASKS
DEALLOCATE TASKS
RETURN -20
END ERR2:
BEGIN
CLOSE TASKS
DEALLOCATE TASKS
RETURN -30
END
ERR3:
BEGIN
CLOSE TASKS
DEALLOCATE TASKS
RETURN -40
END
ERR4:
BEGIN
CLOSE TASKS
DEALLOCATE TASKS
RETURN -50
END
END

Answer 1

下面这段怎么改：
    IF oldoperid LIKE '%'+ rtrim(@operid)+'%';    BEGIN TRY
      FETCH NEXT FROM TASKS INTO @ID,@workid,@oldoperid,@taskxml,@status,@skillid,@havefinishnum,@needopernum,@worktime,@batchid
      CONTINUE
    END TRY
    BEGIN CATCH
      GOTO ERR4
    END CATCH
    IF @havefinishnum > 0
      SET @oldoperid = @oldoperid + ',' + @operid
    ELSE
      SET @oldoperid = @operid
    IF @needopernum = @havefinishnum + 1
      status := 100
    ELSE
      status := status + 1
    
    IF @status>100
    BEGIN
      CONTINUE
        END
    
    IF @gettaskcount > 5 GOTO ERR1
      BEGIN TRY      UPDATE WorkInfo SET HaveFinishNum=@havefinishnum+1,OperID=@oldoperid,[status]=@status 
        WHERE CURRENT OF TASKS
    END TRY
    BEGIN CATCH
      SET @gettaskcount = @gettaskcount + 1
      FETCH NEXT FROM TASKS INTO @ID,@workid,@oldoperid,@taskxml,@status,@skillid,@havefinishnum,@needopernum,@worktime,@batchid  
      CONTINUE
    END CATCH
    IF @@error!=0 GOTO ERR
    SET @recordcount = 1
    INSERT INTO TempWorkInfo(WorkID,ID,OperID,WriteTime,Req_sub_Time) Values(@workid,@ID,@operid,getdate(),dateadd(s,@worktime+@time,getdate()))
    BREAK
  END

Answer 2

大部分语句都是mssqlserver的。oracle是不支持这样的。太多了，搂主去查查资料先。

Answer 3

哎我是初次接触Oracle的存储过程的，语法很多不熟悉，能推荐下有什么好的资料嘛  

Answer 4

楼主用这个工具：http://www.swissql.com/products/sqlserver-to-oracle/sql-server-to-oracle.html