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