Hello,Mr.S
2022-05-20 17:44
采纳率: 100%
浏览 200

sqlserver的存储过程转oracle

实力有限,这里有个sqlserver版本的存储过程需要转换成oracle版本的

以下是存储过程的语句

create procedure P_SetPosition_Waitlist_mz
    @sys_id  varchar(30),       --子系统ID
    @ctrllerid varchar(30),     --诊区ID
    @queueid varchar(30),       --队列ID
    @userid varchar(30),        --用户
    @callerid varchar(30),      --诊室
    @worktype varchar(30),      --班别
    @smsno varchar(20),            --ID唯一值
    @ReasonName varchar(100),    --备注说明
    @BeLateForMin int,            --迟到分钟
    @neworderid int,            --调整位置
    @Flag int,                  --标志
    @Error int Output,          --错误标志
    @ErrMsg varchar(250) Output    --错误提示
as

SET @Error=0
SET @ErrMsg=''
BEGIN TRY 
    --处理记录  
    if isnull(@worktype,'')=''
    begin
        set @worktype=(case when SubString(Convert(varchar(50),GetDate(),120),12,8)<'13'+CHAR(58)+'30'+CHAR(58)+'00' then 0 else 1 end)
    end
    if (isnull(@sys_id,'')='') 
    begin
         SET @Error=2
         SET @ErrMsg='子系统ID为空值'
    end
    if (isnull(@ctrllerid,'')='')
    begin
         SET @Error=2
         SET @ErrMsg='诊区ID为空值'
    end
    if (isnull(@queueid,'')='')
    begin
         SET @Error=3
         SET @ErrMsg='队列ID为空值'
    end
    if isnull(@smsno,'')=''
    begin
        SET @smsno='ID唯一值为空值'
        SET @Error=4
    end
    if isnull(@neworderid,0)<1
    begin
         SET @Error=5
         SET @ErrMsg='调整位置必须大于0'
    end
    Declare @WaitList Table(wlId int,
    isremark int,orderid int,
    Entertime datetime, NewEntertime datetime,
    ConcludeEntertime datetime,
    ReasonName VARCHAR(100), 
    UpdateFlag tinyInt)
    insert @WaitList (wlId,orderid,isremark,Entertime,NewEntertime,ConcludeEntertime,ReasonName,UpdateFlag)
    select wlID,ROW_NUMBER() OVER (order by (Case When Remark1='未报到' then 0 else 1 end) DESC,isnull(PriorId,0) DESC,isremark DESC/*1优先号*/,ConcludeEnterTime ASC,qno ASC) as orderid,isremark,EnterTime,NewEnterTime,ConcludeEnterTime,remark1,0
    from (
    select wlID,isremark,EnterTime=Convert(datetime,EnterTime,121),NewEnterTime,ConcludeEnterTime,qno,remark1,isnull(PriorId,0) as PriorId
    From v_JH_waitlist_mz where CtrllerID=@CtrllerID and QueueID=@QueueID 
    and Remark1<>'未报到'
    and worktype=@worktype 
    and datediff(d,EnterTime,getdate())=0 and curstatus=0 and wlID<>@smsno
    union all
    select wlID,isremark,EnterTime=Convert(datetime,EnterTime,121),NewEnterTime,ConcludeEnterTime,qno,remark1,isnull(PriorId,0) as PriorId
    From v_JH_waitlist_mz where  wlID=@smsno and worktype=@worktype
    )v
    declare @tmporderId int;
    declare @TempwlId int;
    declare @tmporderId2 int;
    declare @countOrderId2 int;
    declare @EnterTime varchar(30);
    select @tmporderId2=Orderid,@EnterTime=EnterTime from @WaitList where wlID=@smsno
    if @Flag=1 
    begin
       if datediff(m,@EnterTime,getdate())<@BeLateForMin
       begin
           set @neworderid=1 --未迟到 迢回排在第一位
       end
    end
    --print 'aa'
    --print @tmporderId2
    if @tmporderId2>@neworderid
    begin
        --设置新序号
        --print '原序号'+ cast(@tmporderId as varchar(30))+'>新序号'+cast(@neworderid as varchar(30)) 
        update @WaitList set orderid=@neworderid where wlID=@smsno
        IF @@ROWCOUNT>0
        BEGIN
            SET @ErrMsg=cast(@tmporderId2 as varchar(30))+'>'+cast(@neworderid as varchar(30)) 
        END
        ELSE
        BEGIN
            SET @ErrMsg=cast(@tmporderId2 as varchar(30))+'!>'+cast(@neworderid as varchar(30)) 
        END
        declare Order_Cursor cursor for select  WlId,orderid from @WaitList where wlID<>@smsno
        open Order_Cursor
        fetch next from Order_Cursor into @TempwlId,@tmporderId
        while @@FETCH_STATUS=0
        Begin
            if (@tmporderId<@tmporderId2) and (@tmporderId>=@neworderid)
            begin
              update @WaitList set orderid=@tmporderId+1 where wlID=@TempwlId
            end
            fetch next from Order_Cursor into @TempwlId,@tmporderId
        End
        CLOSE Order_Cursor
        DEALLOCATE Order_Cursor
    end
    else if @tmporderId2<@neworderid
    begin
        --print '原序号'+ cast(@tmporderId as varchar(30))+'<新序号'+cast(@neworderid as varchar(30)) 
        update @WaitList set orderid=@neworderid where wlID=@smsno
        IF @@ROWCOUNT>0
        BEGIN
            SET @ErrMsg=cast(@tmporderId2 as varchar(30))+'<'+cast(@neworderid as varchar(30))
        END
        ELSE
        BEGIN
            SET @ErrMsg=cast(@tmporderId2 as varchar(30))+'!<'+cast(@neworderid as varchar(30)) 
        END
        declare Order_Cursor2 cursor for select  WlId,orderid from @WaitList where wlID<>@smsno
        open Order_Cursor2
        fetch next from Order_Cursor2 into @TempwlId,@tmporderId
        while @@FETCH_STATUS=0
        Begin
            if (@tmporderId>@tmporderId2) and (@tmporderId<=@neworderid)
            begin
              update @WaitList set orderid=@tmporderId-1 where wlID=@TempwlId
            end
            fetch next from Order_Cursor2 into @TempwlId,@tmporderId
        End
        CLOSE Order_Cursor2
        DEALLOCATE Order_Cursor2
    end
    else
    begin
        --print '原序号'+ cast(@tmporderId as varchar(30))+'=新序号'+cast(@neworderid as varchar(30)) 
        SET @ErrMsg=cast(@tmporderId2 as varchar(30))+'='+cast(@neworderid as varchar(30)) 
    end
    declare @isremark2 int, @newEntertime2 datetime,@ReasonName2 varchar(50);
    if (@tmporderId2=@neworderid)
    begin
       select @isremark2=isremark,@newEntertime2= dateAdd(s,-1,ConcludeEnterTime),@ReasonName2=ReasonName from @WaitList where orderid=@neworderid
       update WaitList_mz set isremark=@isremark2,newentertime=@newEntertime2,remark1=@ReasonName,CallbackReason=@ReasonName2,curstatus=0,issendled=0,issound=0,IfTurnEarlier=1 where wlID=@smsno
       set @Error = @Error + @@ERROR
    end
    else if (@neworderid=1) and (@tmporderId2<>@neworderid)
    begin
       --print @neworderid
       select @isremark2=isremark,@newEntertime2= dateAdd(s,-1,ConcludeEnterTime),@ReasonName2=ReasonName from @WaitList where orderid=2
       --print @newEntertime2
       --print '新序号'+cast(@neworderid as varchar(30))+';排序时间:'+cast(@newEntertime2 as varchar(30)) 
       update WaitList_mz set isremark=@isremark2,newentertime=@newEntertime2,remark1=@ReasonName,CallbackReason=@ReasonName2,curstatus=0,issendled=0,issound=0,IfTurnEarlier=1 where wlID=@smsno
       update stat_mz set memo=@ReasonName where waitid=@smsno
       set @Error = @Error + @@ERROR
    end
    else if (@neworderid>1) and (@tmporderId2<>@neworderid)
    begin
       select @countOrderId2=COUNT(orderid) from @WaitList
       IF @countOrderId2>=@neworderid
       BEGIN
          select @isremark2=isremark,@newEntertime2= dateAdd(s,1,ConcludeEnterTime),@ReasonName2=ReasonName from @WaitList where orderid=@neworderid-1
       END
       ELSE
       BEGIN
          select @isremark2=isremark,@newEntertime2= dateAdd(s,1,ConcludeEnterTime),@ReasonName2=ReasonName from @WaitList where orderid=@countOrderId2-1
       END
       --print '新序号'+cast(@neworderid as varchar(30))+';排序时间:'+cast(@newEntertime2 as varchar(30)) 
       update WaitList_mz set isremark=@isremark2,newentertime=@newEntertime2,remark1=@ReasonName,CallbackReason=@ReasonName2,curstatus=0,issendled=0,issound=0,IfTurnEarlier=1 where wlID=@smsno
       update stat_mz set memo=@ReasonName where waitid=@smsno
       set @Error = @Error + @@ERROR
       
    end
    if @Flag<2
    begin
        select  @Error as Error,@ErrMsg as ErrMsg 
    end
    /*
    select wlID,isremark,EnterTime=Convert(datetime,EnterTime,121),NewEnterTime,ConcludeEnterTime,qno
    From v_JH_waitlist where CtrllerID=@CtrllerID and QueueID=@QueueID 
    and worktype=@worktype 
    and datediff(d,EnterTime,getdate())=0 and curstatus=0
    Order by isremark,ConcludeEnterTime,qno
    */
END TRY
BEGIN CATCH
       DECLARE
              @_error_number int,
              @_error_message nvarchar(2048),
              @_error_severity int,
              @_error_state int,
              @_error_line int,
              @_error_procedure nvarchar(126),
              @_user_name nvarchar(128),
              @_host_name nvarchar(128)
 
       SELECT
              @_error_number = ERROR_NUMBER(),
              @_error_message = ERROR_MESSAGE()+@ErrMsg,
              @_error_severity = ERROR_SEVERITY(),
              @_error_state = ERROR_STATE(),
              @_error_line = ERROR_LINE(),
              @_error_procedure = ERROR_PROCEDURE(),
              @_user_name = SUSER_SNAME(),
              @_host_name = HOST_NAME()
       IF XACT_STATE() = 0  
              INSERT dbo.Mx_ErrorLog(
                     ferror_number,
                     ferror_message,
                     ferror_severity,
                     ferror_state,
                     ferror_line,
                     ferror_procedure,
                     fuser_name,
                     fhost_name,
                     Findate)
              VALUES(
                     @_error_number,
                     @_error_message,
                     @_error_severity,
                     @_error_state,
                     @_error_line,
                     @_error_procedure,
                     @_user_name,
                     @_host_name,
                     GETDATE())
       RAISERROR(
              N'User: %s, Host: %s, Procedure: %s, Error %d, Level %d, State %d, Line %d, Message: %s ',
              @_error_severity,
              1,
              @_user_name,
              @_host_name,
              @_error_procedure,
              @_error_number,
              @_error_severity,
              @_error_state,
              @_error_line,
              @_error_message)
END CATCH


  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

6条回答 默认 最新

相关推荐 更多相似问题