sqlserver的存储过程转oracle

Question

实力有限，这里有个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

Answer 1

建议把里面用到的表的表结构也提供一下，这样答题人可以自行验证下是否正确。
这个基本功能是可以实现的，但是做不到百分百的转换，比如最后面这个获取错误信息，两个数据库里的定义是不一样的，有几个值在oracle里没有，还有那个host也是要根据这个存储过程的调用方式来看是加到数据库触发器里还是加到应用代码里。再一个就是“@WaitList”这玩意建议提前建一个临时表，就不需要转成oracle的数组来处理了

---

下面这个语法基本已经调整得查不多了，但是没有你的表，就没有全部改完，尤其是有些字段类型不知道是什么，比如说日期处理什么的，像那个convert我就暂时没改，剩下的你自己应该能改了吧

---

已按对应的字段类型，修改了相关函数，编译无报错了，这个全局临时表是会话级的，多会话并发不会产生冲突，会话结束后表里就没数据了。
不确定你的oracle的版本，如果是18c以上，可以用私有临时表（或叫专用临时表），效果和你之前的这个差不多。

create global temporary table WaitList (wlId int,
    isremark int,orderid int,
    Entertime date, NewEntertime date,
    ConcludeEntertime date,
    ReasonName varchar2(100), 
    UpdateFlag Int);
CREATE OR REPLACE FUNCTION fn_getname
   RETURN VARCHAR2
IS
   l_owner    VARCHAR2 (30);
   l_name     VARCHAR2 (30);
   l_lineno   NUMBER;
   l_type     VARCHAR2 (30);
BEGIN
   OWA_UTIL.who_called_me (l_owner, l_name, l_lineno, l_type);
   RETURN l_owner || '.' || l_name;
END;
/
create or replace procedure P_SetPosition_Waitlist_mz(sys_id       varchar2, --子系统ID
                                                      ctrllerid    varchar2, --诊区ID
                                                      queueid      varchar2, --队列ID
                                                      userid       varchar2, --用户
                                                      callerid     varchar2, --诊室
                                                      worktype     in out varchar2, --班别
                                                      smsno        varchar2, --ID唯一值
                                                      ReasonName   varchar2, --备注说明
                                                      BeLateForMin int, --迟到分钟
                                                      neworderid   in out int, --调整位置
                                                      Flag         int, --标志
                                                      O_Error      Out int, --错误标志
                                                      ErrMsg       Out varchar2 --错误提示
                                                      ) as

BEGIN

  O_Error := 0;
  ErrMsg  := '';
  --处理记录  
  if worktype IS NULL THEN
    worktype := (case
                  when TO_CHAR(SYSDATE, 'HH24:MI:SS') <
                       '13' || CHR(58) || '30' || CHR(58) || '00' then
                   0
                  else
                   1
                end);
  END IF;
  if sys_id IS NULL THEN
  
    O_Error := 2;
    ErrMsg  := '子系统ID为空值';
  end IF;
  if ctrllerid IS NULL THEN
    O_Error := 2;
    ErrMsg  := '诊区ID为空值';
  end IF;
  if queueid IS NULL THEN
    O_Error := 3;
    ErrMsg  := '队列ID为空值';
  end IF;
  if smsno IS NULL THEN
    ErrMsg  := 'ID唯一值为空值';
    O_Error := 4;
  end IF;

  if NVL(neworderid, 0) < 1 THEN
    O_Error := 5;
    ErrMsg  := '调整位置必须大于0';
  end IF;

  delete WaitList where 1 = 1;

  insert into 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, nvl(PriorId, 0) DESC, isremark DESC /*1优先号*/, ConcludeEnterTime ASC, qno ASC) as orderid,
           isremark,
           EnterTime,
           NewEnterTime,
           ConcludeEnterTime,
           remark1,
           0
      from (select wlID,
                   isremark,
                   to_date( EnterTime,'yyyy-mm-dd hh24:mi:ss') EnterTime,
                   NewEnterTime,
                   ConcludeEnterTime,
                   qno,
                   remark1,
                   nvl(PriorId, 0) as PriorId
              From v_JH_waitlist_mz
             where CtrllerID = CtrllerID
               and QueueID = QueueID
               and Remark1 <> '未报到'
               and worktype = worktype
               and trunc(to_date(EnterTime,'yyyy-mm-dd hh24:mi:ss'))=trunc( sysdate)
               and curstatus = 0
               and wlID <> smsno
            union all
            select wlID,
                   isremark,
                   to_date( EnterTime, 'yyyy-mm-dd hh24:mi:ss') EnterTime,
                   NewEnterTime,
                   ConcludeEnterTime,
                   qno,
                   remark1,
                   nvl(PriorId, 0) as PriorId
              From v_JH_waitlist_mz
             where wlID = smsno
               and worktype = worktype) v;

  declare
    tmporderId    int;
    TempwlId      int;
    tmporderId2   int;
    countOrderId2 int;
    EnterTime     varchar2(30);
  begin
  
    select Orderid, EnterTime
      into tmporderId2, EnterTime
      from WaitList
     where wlID = smsno;
  
    if Flag = 1 then
      if (sysdate - to_date(EnterTime, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 <
         BeLateForMin then
        neworderid := 1; --未迟到 迢回排在第一位
      end if;
    end if;
  
    --print 'aa'
    --print tmporderId2
    if tmporderId2 > neworderid then
    
      --设置新序号
      --print '原序号'+ cast(tmporderId as varchar2(30))+'>新序号'+cast(neworderid as varchar2(30)) 
      update WaitList set orderid = neworderid where wlID = smsno;
      IF sql%ROWCOUNT > 0 then
      
        ErrMsg := cast(tmporderId2 as varchar2) || '>' ||
                  cast(neworderid as varchar2);
      
      ELSE
      
        ErrMsg := cast(tmporderId2 as varchar2) || '!>' ||
                  cast(neworderid as varchar2);
      END if;
    
      for Order_Cursor in (select WlId, orderid
                             from WaitList
                            where wlID <> smsno) loop
      
        TempwlId   := Order_Cursor.WlId;
        tmporderId := Order_Cursor.orderid;
      
        if (tmporderId < tmporderId2) and (tmporderId >= neworderid) then
        
          update WaitList
             set orderid = tmporderId + 1
           where wlID = TempwlId;
        end if;
      
      End loop;
    
    elsif tmporderId2 < neworderid then
    
      --print '原序号'+ cast(tmporderId as varchar2(30))+'<新序号'+cast(neworderid as varchar2(30)) 
      update WaitList set orderid = neworderid where wlID = smsno;
      IF sql%ROWCOUNT > 0 then
      
        ErrMsg := cast(tmporderId2 as varchar2) || '<' ||
                  cast(neworderid as varchar2);
      
      ELSE
      
        ErrMsg := cast(tmporderId2 as varchar2) || '!<' ||
                  cast(neworderid as varchar2);
      end if;
      for Order_Cursor2 in (select WlId, orderid
                              from WaitList
                             where wlID <> smsno) loop
      
        TempwlId   := Order_Cursor2.WlId;
        tmporderId := Order_Cursor2.orderid;
      
        if (tmporderId > tmporderId2) and (tmporderId <= neworderid) then
        
          update WaitList
             set orderid = tmporderId - 1
           where wlID = TempwlId;
        
        End if;
      end loop;
    else
    
      --print '原序号'+ cast(tmporderId as varchar2(30))+'=新序号'+cast(neworderid as varchar2(30)) 
      ErrMsg := cast(tmporderId2 as varchar2) || '=' ||
                cast(neworderid as varchar2);
    end if;
  
    declare
      isremark2     int;
      newEntertime2 date;
      ReasonName2   varchar2(50);
    begin
      if (tmporderId2 = neworderid) then
      
        select isremark, ConcludeEnterTime - 1 / 24 / 60 / 60, ReasonName
          into isremark2, newEntertime2, ReasonName2
          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;
        O_Error := O_Error + O_Error;
      
      elsif (neworderid = 1) and (tmporderId2 <> neworderid) then
      
        --print neworderid
        select isremark, ConcludeEnterTime - 1 / 24 / 60 / 60, ReasonName
          into isremark2, newEntertime2, ReasonName2
          from WaitList
         where orderid = 2;
        --print newEntertime2
        --print '新序号'+cast(neworderid as varchar2(30))+';排序时间:'+cast(newEntertime2 as varchar2(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;
        O_Error := O_Error + O_Error;
      
      elsif (neworderid > 1) and (tmporderId2 <> neworderid) then
      
        select COUNT(orderid) into countOrderId2 from WaitList;
        IF countOrderId2 >= neworderid then
        
          select isremark, ConcludeEnterTime + 1 / 24 / 60 / 60, ReasonName
            into isremark2, newEntertime2, ReasonName2
            from WaitList
           where orderid = neworderid - 1;
        
        ELSE
        
          select isremark, ConcludeEnterTime + 1 / 24 / 60 / 60, ReasonName
            into isremark2, newEntertime2, ReasonName2
            from WaitList
           where orderid = countOrderId2 - 1;
        END if;
        --print '新序号'+cast(neworderid as varchar2(30))+';排序时间:'+cast(newEntertime2 as varchar2(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;
        O_Error := O_Error + O_Error;
      
      end if;
    end;
  end;
  /* if  Flag<2 THEN
  
      select  Error INTO  Error,ErrMsg INTO  ErrMsg FROM DUAL;
  end IF;*/ --这一段不了解业务含义
  /*
  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,sysdate)=0 and curstatus=0
  Order by isremark,ConcludeEnterTime,qno
  */

exception
  when others then
    rollback;
    BEGIN
      DECLARE
        o_error_number    int;
        o_error_message   nvarchar2(2048);
        o_error_severity  int;
        o_error_state     int;
        o_error_line      int;
        o_error_procedure nvarchar2(126);
        o_user_name       nvarchar2(128);
        o_host_name       nvarchar2(128);
      begin
      
        o_error_number    := SQLCODE;
        o_error_message   := sqlerrm || ErrMsg;
        o_error_severity  := null; --ERROR_SEVERITY();
        o_error_state     := null; --ERROR_STATE();
        o_error_line      := dbms_utility.format_error_backtrace();
        o_error_procedure := fn_getname();
        o_user_name       := user; --SUSER_SNAME();
        o_host_name       := null; --HOST_NAME();
      /*  INSERT into dbo.Mx_ErrorLog
          (ferror_number,
           ferror_message,
           ferror_severity,
           ferror_state,
           ferror_line,
           ferror_procedure,
           fuser_name,
           fhost_name,
           Findate)
        VALUES
          (o_error_number,
           o_error_message,
           o_error_severity,
           o_error_state,
           o_error_line,
           o_error_procedure,
           o_user_name,
           o_host_name,
           sysdate);
        commit;*/
        EXECUTE IMMEDIATE Q'{ SELECT 'User: :1,
         Host: :2, Procedure: :3, Error :4, Level :5, 
         State :6, Line :7, Message: :8 '
          FROM DUAL }'
          INTO o_error_message
          USING o_user_name, o_host_name, o_error_procedure, o_error_number, o_error_severity, o_error_state, o_error_line, o_error_message;
        Raise_application_error(-20001, o_error_message);
      
      end;
    END;
END;
/