求高手帮忙转换下,小弟刚刚接触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
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
解决方案 »
- 我帮看下这两个查询语句有什么区别呢????
- oracle sqlldr中的WHEN条件
- Oracle数据库中怎么插入Date格式的数据
- 双向触发器更新表数据
- 使用SQL实现循环,在线等~~
- 关于远程数据库链接表数据(同义词)更新
- 请教高手, 这个 sql 怎么写?
- update t1 set CTNP= '" & vsCTNP & "' where recid='000001",更新失败,提示“字符串太长”
- 紧急求救oracle10G 导入导出问题。。。。拜托拉。。。
- Oracle for Linux于商业版本差别在哪里?
- 数据库启动控制触发器全部不执行的参数
- 求MSSQL2005的存储过程转换成Oracle存储过程的工具
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