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
建议把里面用到的表的表结构也提供一下，这样答题人可以自行验证下是否正确。
这个基本功能是可以实现的，但是做不到百分百的转换，比如最后面这个获取错误信息，两个数据库里的定义是不一样的，有几个值在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;
/
这个存储过程涉及其它表和字段，
尽量把相关的信息都公布出来，
才会完整的改造
朋友可以参考一下
http://blog.csdn.net/xiooix2012/article/details/6895594
- declare
2. type empdtlrec is record (empno number(4)，
3. ename varchar2(20)，
4. deptno number(2));
5. empdtl empdtlrec;
6. begin
7. execute immediate ‘select empno， ename， deptno ’ ||
8. ‘from emp where empno = 7934’
9. into empdtl;
10. end;
补充一下上面涉及的几张表，v_JH_waitlist_mz是个视图，我这边临时直接改成一张表，方便各位看字段类型
create table v_JH_waitlist_mz(
WlId number(10,0) not null,
QueueId varchar2(50) ,
QNo varchar2(30) ,
OrderId number(10,0) ,
CustId varchar2(50) ,
CustName varchar2(100) ,
ItemCode varchar2(30) ,
ItemName varchar2(100) ,
EnterTime varchar2(50) ,
NewEnterTime date ,
CurTime varchar2(50) default sysdate ,
ConcludeEnterTime date,
EnterTime_Text varchar2(50),
ConcludeEnterTime_Text date,
period number(10,0) ,
CallerId varchar2(10) ,
PriorId number(10,0) ,
CurStatus number(10,0) default 0,
History varchar2(200) ,
Source number(10,0) ,
SmsNo varchar2(100) ,
UserId varchar2(30) default ('0'),
custsex varchar2(100) ,
custage varchar2(50) ,
roomname varchar2(50) ,
Remark1 varchar2(100) ,
Remark2 varchar2(100) ,
CtrllerId varchar2(10) ,
CallNum number(10,0) ,
HisSort varchar2(30) ,
NextQno varchar2(30) ,
isremark number(10,0) default 0,
HisKeyField varchar2(50) ,
HisKeyField2 varchar2(50) ,
HisKeyField3 varchar2(50) ,
VisitNumber number(10,0) ,
ImageID varchar2(20) ,
ImageSent number(1,0) ,
IfPrint number(1,0) default ((0)),
IsJiZhen number(1,0) ,
IsQuBaoGao number(1,0) ,
IfTurnEarlier number(1,0) ,
TurnEarlierReason varchar2(30) ,
CallBackReason varchar2(30) ,
issendled number(10,0) default 0,
issound number(10,0) default 0,
soundsinfo varchar2(200) ,
SickType varchar2(30) ,
CallerIP varchar2(100) ,
ZyMzBz number(10,0) ,
IfVisited number(10,0) default 0,
IsPush number(10,0) default 0,
custsfz varchar2(100) ,
WorkType number(10,0),
ditime2 number(10,0),
ditime number(10,0),
statid NUMBER(10) not null,
waitid VARCHAR2(50),
callid VARCHAR2(50),
room VARCHAR2(50),
ledinfo VARCHAR2(200),
curtime2 varchar2(50),
fyjp_ip varchar2(10),
QueueName varchar2(50),
flag number(3)
)
/
-- Create table
create table WAITLIST_MZ
(
wlid NUMBER(10) not null,
queueid VARCHAR2(50),
qno VARCHAR2(30),
orderid NUMBER(10),
custid VARCHAR2(50),
custname VARCHAR2(100),
itemcode VARCHAR2(30),
itemname VARCHAR2(100),
entertime VARCHAR2(50),
newentertime DATE,
curtime VARCHAR2(50) default sysdate,
period NUMBER(10),
callerid VARCHAR2(10),
priorid NUMBER(10),
curstatus NUMBER(10) default 0,
history VARCHAR2(200),
source NUMBER(10),
smsno VARCHAR2(100),
userid VARCHAR2(30) default ('0'),
remark1 VARCHAR2(100),
remark2 VARCHAR2(100),
ctrllerid VARCHAR2(10),
callnum NUMBER(10),
hissort VARCHAR2(30),
nextqno VARCHAR2(30),
isremark NUMBER(10) default 0,
hiskeyfield VARCHAR2(50),
hiskeyfield2 VARCHAR2(50),
hiskeyfield3 VARCHAR2(50),
visitnumber NUMBER(10),
imageid VARCHAR2(20),
imagesent NUMBER(1),
ifprint NUMBER(1) default ((0)),
isjizhen NUMBER(1),
isqubaogao NUMBER(1),
ifturnearlier NUMBER(1),
turnearlierreason VARCHAR2(30),
callbackreason VARCHAR2(30),
issendled NUMBER(10) default 0,
issound NUMBER(10) default 0,
soundsinfo VARCHAR2(200),
worktype VARCHAR2(30),
sicktype VARCHAR2(30),
callerip VARCHAR2(100),
zymzbz NUMBER(10),
custsex VARCHAR2(100),
custage VARCHAR2(50),
ifvisited NUMBER(10) default 0,
ispush NUMBER(10) default 0,
custsfz VARCHAR2(100),
roomname VARCHAR2(100)
)
/
-- Create table
create table STAT_MZ
(
statid NUMBER(10) not null,
queueid VARCHAR2(20),
queuename VARCHAR2(20),
qno VARCHAR2(30),
custid VARCHAR2(50),
curstatus NUMBER(10) default 0,
custname VARCHAR2(100),
entertime VARCHAR2(30),
curtime VARCHAR2(30) default sysdate,
userid VARCHAR2(10),
source NUMBER(10),
priorid NUMBER(10),
waitid VARCHAR2(50),
callid VARCHAR2(50),
ctrllerid VARCHAR2(50),
room VARCHAR2(50),
memo VARCHAR2(50),
issendled NUMBER(10) default 0,
issound NUMBER(10) default 0,
ledinfo VARCHAR2(200),
soundsinfo VARCHAR2(200),
worktype VARCHAR2(30),
memo2 VARCHAR2(100),
custsex VARCHAR2(100),
callerip VARCHAR2(100),
soundstime VARCHAR2(30)
)
需要调试，尤其是错误处理，你改成Oracle通用错误处理即可
create or replace procedure P_SetPosition_Waitlist_mz (
p_sys_id varchar2, --子系统ID
p_ctrllerid varchar2, --诊区ID
p_queueid varchar2, --队列ID
p_userid varchar2, --用户
p_callerid varchar2, --诊室
p_worktype varchar2, --班别
p_smsno varchar2, --ID唯一值
p_ReasonName varchar2, --备注说明
p_BeLateForMin number, --迟到分钟
p_neworderid number, --调整位置
p_Flag number, --标志
p_Error Out number, --错误标志
p_ErrMsg Out varchar2), cur out sys_refcursor --错误提示
as
begin
SET p_Error=0
p_ErrMsg:='';
BEGIN TRY
--处理记录
if nvl(p_worktype,'')=''
then
p_worktype:=(case when SUBSTR(To_char(SYSTIMESTAMP,120),12,8)<'13'+CHAR(58)+'30'+CHAR(58)+'00' then 0 else 1 end);
end if;
if (nvl(p_sys_id,'')='')
then
p_Error:=2;
p_ErrMsg:='子系统ID为空值';
end if;
if (nvl(p_ctrllerid,'')='')
then
p_Error:=2;
p_ErrMsg:='诊区ID为空值';
end if;
if (nvl(p_queueid,'')='')
then
p_Error:=3;
p_ErrMsg:='队列ID为空值';
end if;
if nvl(p_smsno,'')=''
then
p_smsno:='ID唯一值为空值';
p_Error:=4;
end if;
if nvl(p_neworderid,0)<1
then
p_Error:=5;
p_ErrMsg:='调整位置必须大于0';
end if;
Declare tmp_WaitList Table(wlId number(10),
isremark number(10),orderid number(10),
Entertime timestamp(3), NewEntertime timestamp(3),
ConcludeEntertime timestamp(3),
ReasonName VARCHAR2(100),
UpdateFlag NUMBER(3))
insert tmp_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,Convert(datetime,EnterTime,121),NewEnterTime,ConcludeEnterTime,qno,remark1,nvl(PriorId,0) as PriorId into EnterTime
From v_JH_waitlist_mz where CtrllerID=p_ctrllerid and QueueID=p_queueid
and Remark1<>'未报到'
and worktype=p_worktype
and datediff(d,EnterTime,systimestamp)=0 and curstatus=0 and wlID<>p_smsno
union all
select wlID,isremark,Convert(datetime,EnterTime,121),NewEnterTime,ConcludeEnterTime,qno,remark1,nvl(PriorId,0) as PriorId into EnterTime
From v_JH_waitlist_mz where wlID=p_smsno and worktype=p_worktype
)v;
v_tmporderId number(10);
v_TempwlId number(10);
v_tmporderId2 number(10);
v_countOrderId2 number(10);
v_EnterTime varchar2(30);
-- SQLINES LICENSE FOR EVALUATION USE ONLY
select Orderid,EnterTime into v_tmporderId2, v_EnterTime from tmp_WaitList where wlID=p_smsno;
if p_Flag=1
then
if datediff(m,v_EnterTime,systimestamp)<p_BeLateForMin
then
p_neworderid:=1; -- SQLINES DEMO *** �在第一位
end if;
end if;
if v_tmporderId2>p_neworderid
then
--设置新序号
update tmp_WaitList set orderid=p_neworderid where wlID=p_smsno;
IF @@ROWCOUNT>0
THEN
p_ErrMsg:=to_char(v_tmporderId2(30))||'>'||to_char(p_neworderid(30));
ELSE
p_ErrMsg:=to_char(v_tmporderId2(30))||'!>'||to_char(p_neworderid(30));
END IF;
cursor Order_Cursor is select WlId,orderid from tmp_WaitList where wlID<>p_smsno;
open Order_Cursor;
fetch next from; Order_Cursor into v_TempwlId,v_tmporderId
while next%FOUND
Loop
if (v_tmporderId<v_tmporderId2) and (v_tmporderId>=p_neworderid)
then
update tmp_WaitList set orderid=v_tmporderId+1 where wlID=v_TempwlId;
end if;
fetch next from; Order_Cursor into v_TempwlId,v_tmporderId
End Loop;
CLOSE Order_Cursor;
else if v_tmporderId2<p_neworderid
then
update tmp_WaitList set orderid=p_neworderid where wlID=p_smsno;
IF @@ROWCOUNT>0
THEN
p_ErrMsg:=to_char(v_tmporderId2(30))||'<'||to_char(p_neworderid(30));
ELSE
p_ErrMsg:=to_char(v_tmporderId2(30))||'!<'||to_char(p_neworderid(30));
END IF;
cursor Order_Cursor2 is select WlId,orderid from tmp_WaitList where wlID<>p_smsno;
open Order_Cursor2;
fetch next from; Order_Cursor2 into v_TempwlId,v_tmporderId
while next%FOUND
Loop
if (v_tmporderId>v_tmporderId2) and (v_tmporderId<=p_neworderid)
then
update tmp_WaitList set orderid=v_tmporderId-1 where wlID=v_TempwlId;
end if;
fetch next from; Order_Cursor2 into v_TempwlId,v_tmporderId
End Loop;
CLOSE Order_Cursor2;
else
p_ErrMsg:=to_char(v_tmporderId2(30))||'='||to_char(p_neworderid(30));
end if;
end if;
v_isremark2 number(10); v_newEntertime2 timestamp(3);v_ReasonName2 varchar2(50);
if (v_tmporderId2=p_neworderid)
then
-- SQLINES LICENSE FOR EVALUATION USE ONLY
select isremark, INTERVAL '-1' s +ConcludeEnterTime,ReasonName into v_isremark2, v_newEntertime2, v_ReasonName2 from tmp_WaitList where orderid=p_neworderid;
update WaitList_mz set isremark=v_isremark2,newentertime=v_newEntertime2,remark1=p_ReasonName,CallbackReason=v_ReasonName2,curstatus=0,issendled=0,issound=0,IfTurnEarlier=1 where wlID=p_smsno;
p_Error := p_Error + SQLERRM();
else if (p_neworderid=1) and (v_tmporderId2<>p_neworderid)
then
select isremark, INTERVAL '-1' s +ConcludeEnterTime,ReasonName into v_isremark2, v_newEntertime2, v_ReasonName2 from tmp_WaitList where orderid=2;
update WaitList_mz set isremark=v_isremark2,newentertime=v_newEntertime2,remark1=p_ReasonName,CallbackReason=v_ReasonName2,curstatus=0,issendled=0,issound=0,IfTurnEarlier=1 where wlID=p_smsno;
update stat_mz set memo=p_ReasonName where waitid=p_smsno;
p_Error := p_Error + SQLERRM();
else if (p_neworderid>1) and (v_tmporderId2<>p_neworderid)
then
-- SQLINES LICENSE FOR EVALUATION USE ONLY
select COUNT(orderid) into v_countOrderId2 from tmp_WaitList;
IF v_countOrderId2>=p_neworderid
THEN
-- SQLINES LICENSE FOR EVALUATION USE ONLY
select isremark, INTERVAL '1' s +ConcludeEnterTime,ReasonName into v_isremark2, v_newEntertime2, v_ReasonName2 from tmp_WaitList where orderid=p_neworderid-1;
ELSE
-- SQLINES LICENSE FOR EVALUATION USE ONLY
select isremark, INTERVAL '1' s +ConcludeEnterTime,ReasonName into v_isremark2, v_newEntertime2, v_ReasonName2 from tmp_WaitList where orderid=v_countOrderId2-1;
END IF;
update WaitList_mz set isremark=v_isremark2,newentertime=v_newEntertime2,remark1=p_ReasonName,CallbackReason=v_ReasonName2,curstatus=0,issendled=0,issound=0,IfTurnEarlier=1 where wlID=p_smsno;
update stat_mz set memo=p_ReasonName where waitid=p_smsno;
p_Error := p_Error + SQLERRM();
end if;
if p_Flag<2
then
-- SQLINES LICENSE FOR EVALUATION USE ONLY
open cur for select p_Error as Error,p_ErrMsg as ErrMsg from dual;
end if;
END TRY
BEGIN CATCH
v__error_number number(10);
v__error_message nvarchar2(2048);
v__error_severity number(10);
v__error_state number(10);
v__error_line number(10);
v__error_procedure nvarchar2(126);
v__user_name nvarchar2(128);
v__host_name nvarchar2(128);
v__error_number := ERROR_NUMBER(),
v__error_message := ERROR_MESSAGE()+p_ErrMsg,
v__error_severity := ERROR_SEVERITY(),
v__error_state := ERROR_STATE(),
v__error_line := ERROR_LINE(),
v__error_procedure := ERROR_PROCEDURE(),
v__user_name := SUSER_SNAME(),
v__host_name := SYS_CONTEXT('USERENV', 'SERVER_HOST') FROM dual;
IF XACT_STATE() = 0 THEN
INSERT Mx_ErrorLog(
ferror_number,
ferror_message,
ferror_severity,
ferror_state,
ferror_line,
ferror_procedure,
fuser_name,
fhost_name,
Findate)
VALUES(
v__error_number,
v__error_message,
v__error_severity,
v__error_state,
v__error_line,
v__error_procedure,
v__user_name,
v__host_name,
SYSTIMESTAMP);
END IF;
RAISERROR(
N'User: %s, Host: %s, Procedure: %s, Error %d, Level %d, State %d, Line %d, Message: %s ',
v__error_severity,
1,
v__user_name,
v__host_name,
v__error_procedure,
v__error_number,
v__error_severity,
v__error_state,
v__error_line,
v__error_message)
END CATCH
end;