//----------------------------------------------
create or replace procedure Pr_DelIfAffair(
I_IfileNo numeric,
S_CurUserID varchar,
S_TreatContent varchar)as--删除待办事务表中的外部资料阅览人
TempTreatType varchar(1);
TempSendUserID varchar(20);
TempTreatCount int;
TempStr1 varchar(200);
TempStr2 varchar(10);
TempDate Date;
TempTreatContent varchar(1000);
TempFileTitle varchar(200);
TempDeptNo varchar(5);
TempMessageId int;
TempCount int;begin TempTreatContent:='';
TempCount := 0;
select Count(UserID) into TempCount from UndoAffair
where (RTrim(AffairType)='外部资料')and (AffairFileID=I_IfileNo)and(UserID=S_CurUserID);
--if EXISTS(select UserID from UndoAffair where (AffairType='外部资料')and (AffairFileID=I_IfileNo)and(UserID=S_CurUserID)) --取得外部资料标题
select RTrim(AffairTitle) into TempFileTitle from UndoAffair where
(RTrim(AffairType)='外部资料')and (AffairFileID=I_IfileNo)and(UserID=S_CurUserID); --删除待办事务中的相关记录
delete from UndoAffair where (AffairType='外部资料')and
(AffairFileID=I_IfileNo)and(UserID=S_CurUserID);
if SQLCODE<>0 then
rollback;
return;
end if; --获得办理类型和发送人(1:领导、2:部门)
select TreatType,SendUserID into TempTreatType,TempSendUserID from If_OutTempTreat where
(IfileNo=I_IfileNo)and(TreatUserID=S_CurUserID); --向外部资料临时办理表中存入办理意见和办理状态
update If_OutTempTreat set TreatContent=S_TreatContent,TreatDate=SYSDate(),
TreatState='1' where (IfileNo=I_IfileNo)and(TreatUserID=S_CurUserID);
if SQLCODE<>0 then
rollback;
return;
end if; --如果是领导办理,检查是否最后一个领导办理,如果是最后一个领导,向外部资料管理员发送个人消息
if TempTreatType='1' then
Select Count(TreatUserID) into TempTreatCount from If_OutTempTreat
where (IfileNo=I_IfileNo)and(RTrim(TreatType)='1')and(RTrim(TreatState)='0');
if TempTreatCount=0 then
--1.2.1、定义游标,从外部资料临时办理表中查找相关记录
declare Cursor Cur_Temp is select RTrim(A.TreatContent),
(select RTrim(B.UserName) from Bas_UserBasic B where B.UserID=A.TreatUserID),
A.TreatDate from If_OutTempTreat A where (A.IfileNo=I_IfileNo)and(RTrim(A.TreatType)='1');
begin
open Cur_Temp;
fetch Cur_Temp into TempStr1,TempStr2,TempDate;
while Cur_Temp%found loop --如果返回值等于0 ,则游标查找成功
--累加办理意见
--TempTreatContent:=TempTreatContent+TempStr2+':'+RTrim(TempStr1)
-- +'^M'+TO_CHAR(TempDate,'yyyy-mm-dd')+';'+'^M';
TempTreatContent:=concat(concat(concat(concat(concat(concat(concat(TempTreatContent,TempStr2),':'),RTrim(TempStr1)),
'^M'),TO_CHAR(TempDate,'yyyy-mm-dd')),';'),'^M');
fetch Cur_Temp into TempStr1,TempStr2,TempDate;
end loop;
CLOSE cur_Temp; --关闭游标
end ;--游标结束
-- end if; --2.7.10 从用户属性表中取得消息发布人的部门编号
select DeptNo into TempDeptNo from Bas_UserBasic where UserID=TempSendUserID; insert into IF_MsgPerson (Title,TEXTCONT,SenderID,DeptNo,SendDate,AvailedTime,IsAttached)
Values (concat(concat('外部资料《',TempFileTitle),'》领导批阅意见:'),TempTreatContent,TempSendUserID,TempDeptNo,
SYSdate(),SYSdate()+3,'0'); if SQLCODE<>0 then
rollback ;
return;
end if; --2.7.9 取得个人消息ID
select Max(MessageId) into TempMessageId from IF_MsgPerson ; --2.7.11.向个人消息权限表中插入记录
insert into IF_MsgPersonBorw (MessageId,BorwserID) Values (TempMessageId,TempSendUserID);
if SQLCODE<>0 then
rollback ;
return;
end if;
--2.7.13.向消息提示表中插入记录
--给外部资料传办人
insert into MsgHint Values (TempSendUserID,'个人消息--外部资料领导批阅意见',S_CurUserID,SYSdate());
if SQLCODE<>0 then
rollback ;
return;
end if;
end if; --最后一个领导批阅结束TempTreatCount=0
if SQLCODE = 0 then
commit work;
return;
end if; end if; --TempTreatType='1'结束
--如果是部门阅办,到最后一个时,删除外部资料临时办理表中的记录
if TempTreatType='2' then Select Count(TreatUserID) into TempTreatCount from If_OutTempTreat
where (IfileNo=I_IfileNo)and(RTrim(TreatType)='2')and(RTrim(TreatState)='0');
if TempTreatCount=0 then
delete from If_OutTempTreat where IfileNo=I_IfileNo;
if SQLCODE<>0 then
rollback work;
return;
end if;
if SQLCODE=0 then
commit work;
return;
end if;
end if;
end if;--TempTreatType='2'结束
if SQLCODE = 0 then
commit work;
return;
end if;
return;
end ;
//--------------------------------------------------------
create or replace procedure Pr_DelIfAffair(
I_IfileNo numeric,
S_CurUserID varchar,
S_TreatContent varchar)as--删除待办事务表中的外部资料阅览人
TempTreatType varchar(1);
TempSendUserID varchar(20);
TempTreatCount int;
TempStr1 varchar(200);
TempStr2 varchar(10);
TempDate Date;
TempTreatContent varchar(1000);
TempFileTitle varchar(200);
TempDeptNo varchar(5);
TempMessageId int;
TempCount int;begin TempTreatContent:='';
TempCount := 0;
select Count(UserID) into TempCount from UndoAffair
where (RTrim(AffairType)='外部资料')and (AffairFileID=I_IfileNo)and(UserID=S_CurUserID);
--if EXISTS(select UserID from UndoAffair where (AffairType='外部资料')and (AffairFileID=I_IfileNo)and(UserID=S_CurUserID)) --取得外部资料标题
select RTrim(AffairTitle) into TempFileTitle from UndoAffair where
(RTrim(AffairType)='外部资料')and (AffairFileID=I_IfileNo)and(UserID=S_CurUserID); --删除待办事务中的相关记录
delete from UndoAffair where (AffairType='外部资料')and
(AffairFileID=I_IfileNo)and(UserID=S_CurUserID);
if SQLCODE<>0 then
rollback;
return;
end if; --获得办理类型和发送人(1:领导、2:部门)
select TreatType,SendUserID into TempTreatType,TempSendUserID from If_OutTempTreat where
(IfileNo=I_IfileNo)and(TreatUserID=S_CurUserID); --向外部资料临时办理表中存入办理意见和办理状态
update If_OutTempTreat set TreatContent=S_TreatContent,TreatDate=SYSDate(),
TreatState='1' where (IfileNo=I_IfileNo)and(TreatUserID=S_CurUserID);
if SQLCODE<>0 then
rollback;
return;
end if; --如果是领导办理,检查是否最后一个领导办理,如果是最后一个领导,向外部资料管理员发送个人消息
if TempTreatType='1' then
Select Count(TreatUserID) into TempTreatCount from If_OutTempTreat
where (IfileNo=I_IfileNo)and(RTrim(TreatType)='1')and(RTrim(TreatState)='0');
if TempTreatCount=0 then
--1.2.1、定义游标,从外部资料临时办理表中查找相关记录
declare Cursor Cur_Temp is select RTrim(A.TreatContent),
(select RTrim(B.UserName) from Bas_UserBasic B where B.UserID=A.TreatUserID),
A.TreatDate from If_OutTempTreat A where (A.IfileNo=I_IfileNo)and(RTrim(A.TreatType)='1');
begin
open Cur_Temp;
fetch Cur_Temp into TempStr1,TempStr2,TempDate;
while Cur_Temp%found loop --如果返回值等于0 ,则游标查找成功
--累加办理意见
--TempTreatContent:=TempTreatContent+TempStr2+':'+RTrim(TempStr1)
-- +'^M'+TO_CHAR(TempDate,'yyyy-mm-dd')+';'+'^M';
TempTreatContent:=concat(concat(concat(concat(concat(concat(concat(TempTreatContent,TempStr2),':'),RTrim(TempStr1)),
'^M'),TO_CHAR(TempDate,'yyyy-mm-dd')),';'),'^M');
fetch Cur_Temp into TempStr1,TempStr2,TempDate;
end loop;
CLOSE cur_Temp; --关闭游标
end ;--游标结束
-- end if; --2.7.10 从用户属性表中取得消息发布人的部门编号
select DeptNo into TempDeptNo from Bas_UserBasic where UserID=TempSendUserID; insert into IF_MsgPerson (Title,TEXTCONT,SenderID,DeptNo,SendDate,AvailedTime,IsAttached)
Values (concat(concat('外部资料《',TempFileTitle),'》领导批阅意见:'),TempTreatContent,TempSendUserID,TempDeptNo,
SYSdate(),SYSdate()+3,'0'); if SQLCODE<>0 then
rollback ;
return;
end if; --2.7.9 取得个人消息ID
select Max(MessageId) into TempMessageId from IF_MsgPerson ; --2.7.11.向个人消息权限表中插入记录
insert into IF_MsgPersonBorw (MessageId,BorwserID) Values (TempMessageId,TempSendUserID);
if SQLCODE<>0 then
rollback ;
return;
end if;
--2.7.13.向消息提示表中插入记录
--给外部资料传办人
insert into MsgHint Values (TempSendUserID,'个人消息--外部资料领导批阅意见',S_CurUserID,SYSdate());
if SQLCODE<>0 then
rollback ;
return;
end if;
end if; --最后一个领导批阅结束TempTreatCount=0
if SQLCODE = 0 then
commit work;
return;
end if; end if; --TempTreatType='1'结束
--如果是部门阅办,到最后一个时,删除外部资料临时办理表中的记录
if TempTreatType='2' then Select Count(TreatUserID) into TempTreatCount from If_OutTempTreat
where (IfileNo=I_IfileNo)and(RTrim(TreatType)='2')and(RTrim(TreatState)='0');
if TempTreatCount=0 then
delete from If_OutTempTreat where IfileNo=I_IfileNo;
if SQLCODE<>0 then
rollback work;
return;
end if;
if SQLCODE=0 then
commit work;
return;
end if;
end if;
end if;--TempTreatType='2'结束
if SQLCODE = 0 then
commit work;
return;
end if;
return;
end ;
//--------------------------------------------------------
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货