本人写了一个存储过程,由于在这个存储过程中处理了相当多的逻辑,所以比较复杂,现在有个问题困扰着我,就是如果客户端同时调用此存储过程时会造成服务器死机,客户端无法返回,不同时调用则一切正常,我在这个存储过程里用递归的方式处理了一个图形的拓扑.我想有什么办法能控制这个存储过程在同一时刻只有一个调用,意思也就是说如何锁定这个存储过程,等一次调用完成后再解除锁定?代码如下:
CREATE OR REPLACE PROCEDURE "SP_NODETRANSFER" (
v_UserID Number,
v_NodeID Number,
v_TargetNodeID Number default -1,
v_ManualHandleInfo varchar2 default '没有手工处理信息',
v_ProjectID Number,
v_IsCanTransfer OUT Number,
v_ReturnInfo OUT varchar2
)
as
v_ProjectTypeID Number;
v_VersionID date;
v_IsFinished Number;
v_HandlingNodeIDArray varchar2(100);
v_HandleMethod Number;
v_TempChildNodeIDArray varchar2(50);
v_TempChildNodeID Number; v_ConditionSQL nvarchar2(500);
v_HandleCondition varchar2(250);
v_ConditionHandleInfo varchar2(250);
v_IsSLorBRootNode Number;
v_SQLString nvarchar2(500);
v_define Nvarchar2(500);
v_IsMeetCondition Number;
v_ExceptNodeID Number;
v_BranchNodeIDArray varchar2(250);
v_TempNodeID Number;
v_TempNodeState Number;
v_tempNodeIDIndex Number;
v_ResultNodeIDArray varchar2(100);
v_EndDate varchar2(10);
v_STATUS_NAME Number;
v_tempHandlingNodeIDArray varchar2(50);
v_TempIndex Number;
v_IsHave Number;
v_ActualUserNodes varchar2(100);
v_ComputerIP varchar2(15);
v_AllAfterNodeIDArray varchar2(500);
v_NoActualUserNodes varchar2(100);
v_RecCount Number;
begin
select ProjectTypeID, VersionID into v_ProjectTypeID, v_VersionID from tbl_Project where ProjectID= v_ProjectID;
select to_char(sysdate,'yyyy-mm-dd') into v_EndDate from dual;
v_TempChildNodeIDArray := '';
v_HandlingNodeIDArray :='';
v_BranchNodeIDArray :='';
v_ActualUserNodes :='';
v_NoActualUserNodes :='';
v_ComputerIP :='127.0.0.1'; select HandleMethod into v_HandleMethod from tbl_NodeHandleMethod where (NodeID = v_NodeID) and (ProjectTypeID = v_ProjectTypeID) and (VersionID = v_VersionID); if v_HandleMethod = 0 then
begin select ChildNodeIDArray into v_TempChildNodeIDArray from tbl_NodeRelationAidInfo where NodeID=v_NodeID and ProjectTypeID=v_ProjectTypeID and VersionID=v_VersionID; if (v_TempChildNodeIDArray is not Null) and (length(trim(v_TempChildNodeIDArray))>0) then Update tbl_Project set HandlingNodeIDArray = v_TempChildNodeIDArray where ProjectID=v_ProjectID; sp_IsNodesHaveActualUser(v_ProjectID, v_IsHave, v_ActualUserNodes, v_NoActualUserNodes); if v_IsHave=1 then
sp_SetActualUserAndStatus(v_ProjectID,v_ActualUserNodes); sp_NodeSetStatus(3,v_NoActualUserNodes,v_ProjectID);
end if;
Update tbl_NodeStatus set NodeState = 2, SubmiterID = v_UserID,HandlingUserIDArray='',NodeHolderID=0 , EndDate =to_date(v_EndDate,'yyyy-mm-dd') where ProjectID = v_ProjectID and NodeID = v_NodeID; sp_NodeSetBegintime(to_date(v_EndDate,'yyyy-mm-dd'), v_TempChildNodeIDArray, v_ProjectID); sp_CreatLogWorkflow(v_UserID, 5, v_ComputerIP, v_ProjectID,v_NodeID, 1); v_IsCanTransfer := 1;
v_ReturnInfo := '节点传送成功!';
commit;
Return;
else
Update tbl_NodeStatus set NodeState = 2, SubmiterID = v_UserID,HandlingUserIDArray='',NodeHolderID=0 , EndDate = v_EndDate where ProjectID = v_ProjectID and NodeID = v_NodeID; Update tbl_Project set IsFinished = 1, ProjectEndTime = v_EndDate where ProjectID = v_ProjectID; sp_CreatLogWorkflow(v_UserID, 5, v_ComputerIP, v_ProjectID,0, 1); v_IsCanTransfer := 1;
v_ReturnInfo := '此项目到此已完成办理!';
commit;
Return; end if;
end;
end if;if v_HandleMethod = 1 then
sp_IsHandlingNodesFinished(v_ProjectID,v_NodeID,v_IsFinished);
if v_IsFinished=0 then
begin
Update tbl_NodeStatus set NodeState = 2, SubmiterID = v_UserID,HandlingUserIDArray='',NodeHolderID=0 , EndDate = to_date(v_EndDate,'yyyy-mm-dd') where ProjectID = v_ProjectID and NodeID = v_NodeID; sp_CreatLogWorkflow(v_UserID, 5, v_ComputerIP, v_ProjectID,v_NodeID, 1); v_IsCanTransfer := 1;
v_ReturnInfo := '节点传送成功!';
commit;
Return;
end;
else
if v_IsFinished=1 then
select ChildNodeIDArray into v_TempChildNodeIDArray from tbl_NodeRelationAidInfo where ProjectTypeID = v_ProjectTypeID and VersionID = v_VersionID and NodeID = v_NodeID;
Update tbl_Project set HandlingNodeIDArray = v_TempChildNodeIDArray where ProjectID=v_ProjectID and VersionID=v_VersionID; sp_IsNodesHaveActualUser(v_ProjectID, v_IsHave, v_ActualUserNodes, v_NoActualUserNodes); if v_IsHave=1 then
begin sp_SetActualUserAndStatus(v_ProjectID,v_ActualUserNodes); sp_NodeSetStatus(3,v_NoActualUserNodes,v_ProjectID);
end;
end if; Update tbl_NodeStatus set NodeState = 2, SubmiterID = v_UserID,HandlingUserIDArray='',NodeHolderID=0 , EndDate = to_date(v_EndDate,'yyyy-mm-dd') where ProjectID = v_ProjectID and NodeID = v_NodeID; sp_NodeSetBegintime(to_date(v_EndDate,'yyyy-mm-dd'), v_TempChildNodeIDArray, v_ProjectID); sp_CreatLogWorkflow(v_UserID, 5, v_ComputerIP, v_ProjectID,v_NodeID, 1); v_IsCanTransfer := 1;
v_ReturnInfo := '节点传送成功!';
commit;
Return;
end if;
end if;end if;
.
.
.
end if;end;
CREATE OR REPLACE PROCEDURE "SP_NODETRANSFER" (
v_UserID Number,
v_NodeID Number,
v_TargetNodeID Number default -1,
v_ManualHandleInfo varchar2 default '没有手工处理信息',
v_ProjectID Number,
v_IsCanTransfer OUT Number,
v_ReturnInfo OUT varchar2
)
as
v_ProjectTypeID Number;
v_VersionID date;
v_IsFinished Number;
v_HandlingNodeIDArray varchar2(100);
v_HandleMethod Number;
v_TempChildNodeIDArray varchar2(50);
v_TempChildNodeID Number; v_ConditionSQL nvarchar2(500);
v_HandleCondition varchar2(250);
v_ConditionHandleInfo varchar2(250);
v_IsSLorBRootNode Number;
v_SQLString nvarchar2(500);
v_define Nvarchar2(500);
v_IsMeetCondition Number;
v_ExceptNodeID Number;
v_BranchNodeIDArray varchar2(250);
v_TempNodeID Number;
v_TempNodeState Number;
v_tempNodeIDIndex Number;
v_ResultNodeIDArray varchar2(100);
v_EndDate varchar2(10);
v_STATUS_NAME Number;
v_tempHandlingNodeIDArray varchar2(50);
v_TempIndex Number;
v_IsHave Number;
v_ActualUserNodes varchar2(100);
v_ComputerIP varchar2(15);
v_AllAfterNodeIDArray varchar2(500);
v_NoActualUserNodes varchar2(100);
v_RecCount Number;
begin
select ProjectTypeID, VersionID into v_ProjectTypeID, v_VersionID from tbl_Project where ProjectID= v_ProjectID;
select to_char(sysdate,'yyyy-mm-dd') into v_EndDate from dual;
v_TempChildNodeIDArray := '';
v_HandlingNodeIDArray :='';
v_BranchNodeIDArray :='';
v_ActualUserNodes :='';
v_NoActualUserNodes :='';
v_ComputerIP :='127.0.0.1'; select HandleMethod into v_HandleMethod from tbl_NodeHandleMethod where (NodeID = v_NodeID) and (ProjectTypeID = v_ProjectTypeID) and (VersionID = v_VersionID); if v_HandleMethod = 0 then
begin select ChildNodeIDArray into v_TempChildNodeIDArray from tbl_NodeRelationAidInfo where NodeID=v_NodeID and ProjectTypeID=v_ProjectTypeID and VersionID=v_VersionID; if (v_TempChildNodeIDArray is not Null) and (length(trim(v_TempChildNodeIDArray))>0) then Update tbl_Project set HandlingNodeIDArray = v_TempChildNodeIDArray where ProjectID=v_ProjectID; sp_IsNodesHaveActualUser(v_ProjectID, v_IsHave, v_ActualUserNodes, v_NoActualUserNodes); if v_IsHave=1 then
sp_SetActualUserAndStatus(v_ProjectID,v_ActualUserNodes); sp_NodeSetStatus(3,v_NoActualUserNodes,v_ProjectID);
end if;
Update tbl_NodeStatus set NodeState = 2, SubmiterID = v_UserID,HandlingUserIDArray='',NodeHolderID=0 , EndDate =to_date(v_EndDate,'yyyy-mm-dd') where ProjectID = v_ProjectID and NodeID = v_NodeID; sp_NodeSetBegintime(to_date(v_EndDate,'yyyy-mm-dd'), v_TempChildNodeIDArray, v_ProjectID); sp_CreatLogWorkflow(v_UserID, 5, v_ComputerIP, v_ProjectID,v_NodeID, 1); v_IsCanTransfer := 1;
v_ReturnInfo := '节点传送成功!';
commit;
Return;
else
Update tbl_NodeStatus set NodeState = 2, SubmiterID = v_UserID,HandlingUserIDArray='',NodeHolderID=0 , EndDate = v_EndDate where ProjectID = v_ProjectID and NodeID = v_NodeID; Update tbl_Project set IsFinished = 1, ProjectEndTime = v_EndDate where ProjectID = v_ProjectID; sp_CreatLogWorkflow(v_UserID, 5, v_ComputerIP, v_ProjectID,0, 1); v_IsCanTransfer := 1;
v_ReturnInfo := '此项目到此已完成办理!';
commit;
Return; end if;
end;
end if;if v_HandleMethod = 1 then
sp_IsHandlingNodesFinished(v_ProjectID,v_NodeID,v_IsFinished);
if v_IsFinished=0 then
begin
Update tbl_NodeStatus set NodeState = 2, SubmiterID = v_UserID,HandlingUserIDArray='',NodeHolderID=0 , EndDate = to_date(v_EndDate,'yyyy-mm-dd') where ProjectID = v_ProjectID and NodeID = v_NodeID; sp_CreatLogWorkflow(v_UserID, 5, v_ComputerIP, v_ProjectID,v_NodeID, 1); v_IsCanTransfer := 1;
v_ReturnInfo := '节点传送成功!';
commit;
Return;
end;
else
if v_IsFinished=1 then
select ChildNodeIDArray into v_TempChildNodeIDArray from tbl_NodeRelationAidInfo where ProjectTypeID = v_ProjectTypeID and VersionID = v_VersionID and NodeID = v_NodeID;
Update tbl_Project set HandlingNodeIDArray = v_TempChildNodeIDArray where ProjectID=v_ProjectID and VersionID=v_VersionID; sp_IsNodesHaveActualUser(v_ProjectID, v_IsHave, v_ActualUserNodes, v_NoActualUserNodes); if v_IsHave=1 then
begin sp_SetActualUserAndStatus(v_ProjectID,v_ActualUserNodes); sp_NodeSetStatus(3,v_NoActualUserNodes,v_ProjectID);
end;
end if; Update tbl_NodeStatus set NodeState = 2, SubmiterID = v_UserID,HandlingUserIDArray='',NodeHolderID=0 , EndDate = to_date(v_EndDate,'yyyy-mm-dd') where ProjectID = v_ProjectID and NodeID = v_NodeID; sp_NodeSetBegintime(to_date(v_EndDate,'yyyy-mm-dd'), v_TempChildNodeIDArray, v_ProjectID); sp_CreatLogWorkflow(v_UserID, 5, v_ComputerIP, v_ProjectID,v_NodeID, 1); v_IsCanTransfer := 1;
v_ReturnInfo := '节点传送成功!';
commit;
Return;
end if;
end if;end if;
.
.
.
end if;end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货