感觉自己写的很棒的一个Oracle存储过程,(其实想说很叼^,^).
集成了一堆操作数据的功能(至少几十), 包括存储过程执行异常信息输出帮助诊断.
亮点很多, 比如`over(partition by t.u_id order by t.u_id)`, 哈哈.
是个很多客户都在用的稳定的关键存储过程!（客户没有一千也有三五百，并且还在不停的增加）
每天都要运行 无数次。因为每个客户的坐席每次打电话都要用到，包括导入客户资料，几万，几十万的批量导入，速度都不错，每次执行都有日志记录。
create or replace procedure NAG_CUSTOMER_TOPOOLW(
--客户ID集(如:"10001,10002,10003,") (最后一位逗号,)
--或者是 客户资料表的where条件(and xx=xxx and yyy=yy) (无最后一位识别)
--或者是 数据池数据ID集(如:"10001,10002,10003,;") (最后一位是分号;)
--或者是 数据池和 客户资料的条件("and xc=xc and dpd_dp_id=id!") (最后一位是感叹号!)
--或者是 select 语句,只有一个字段DPD_ID) (最后一位是井号#)
--或者是 自动外拨的,从临时表取数据,最后一位是 符号@ (如'来自自动外拨(预测外拨)@')
V_IDW in clob, V_FromDPID in varchar2,--从原来哪个池转出
V_ToDPID in varchar2,--转到哪个池 V_KH in number,--保留归属人
V_DF in number,--从原数据池中删除
V_KCS in number,--保留拨打状态 V_ToUID in number:=0,--转给谁
V_ActUID in number,--谁操作的 OutRow out number, --影响的行数; -999999表示满了,数据量已达上限,未做分配;
V_GID in number:=null
) Authid Current_User is
/*批量的数据流转
Auth: Lzpong 2014/05/28
+ 几乎完全重新写 2014/10/24
已经在这个池的数据不会被操作
兼容传入ID集 或者 客户表的where的条件 +或者 DPD_ID集(分号结尾) 2014/10/24
如果传入的是ID集,末尾必须有逗号(,),如果是池数据ID集再加个分号(;)
+实现了平均分配 2014/10/24 +2014/11/12修正重写
+实现了数据限制 2014/10/24 +2014/11/12修正重写
+修正流转数据不齐全 2014/12/01
+修正了 临时表去重导致的 慢问题,并目标池标记删除的不删除重新插入了 2014/12/19
+insert/update 使用merge 大大提高速度 2014/12/23
+自动外拨转数据到数据池,用到了临时表 "NAG_FIXTABLE_TEMP" 2015/01/29
+客户资料在数据池中唯一检查 2015/04/20
+数据池转移检查 转入和转出池的指定转移规则属性 2015/04/20
+删除原池数据前前加入确认检查已转入目标池 2015/05/06
+共享,关注 流转后删除/更新 ;更改分配前在临时表,最后一次性操作数据表,增强效率 2015/05/18
+修复 低权限人可操作所有数据bug; 全局数据唯一设置使数据汇总流转不能流转 2015/05/22
+修复 条件转移 原池不删除 2015/05/22
+修复 数据限制判断部分 慢(部分改了)
+限制 审核状态的不能流转 2015/06/08
+日志 输出完整的调用日志和调试输出信息 2015/06/09
+重大修改整理 完全整理操作流程 ~2015/06/17
+为了给其他功能(导入,未接来电转移等)通用,为where条件时,客户资料操作转移,完全由查询条件限定的(如数据规则限制) 2015/07/07
+客户资料到数据池 和 自动外拨 到数据池 也检查 数据唯一性 2015/07/07
+修复剩余量小于0的不限制情况 2015/07/08
+(池流转)删除源数据时,改为仅更新DPD_DP_ID 2015/07/13
+更新/修复/优化 改删除原池数据为更新池ID等 2015/07/24
+限制 非超管 需要业务规则允许才能单池间流转多条池数据 2015/08/05
+所有入池转移均根据唯一设置检查或不检查数据唯一性 2015/08/11
+数据转移时清除组ID
+加快表(datapooldata)数据量大时更新数据执行速度,原merge慢 2015/11/17
+指定到人的转移,超过限制的不转移 2015/12/24
+修改第一次进池 update 很慢的问题 xy 2016/3/1
+除去客户姓名入池,避免客户姓名太长导致出错 2016/04/16
+修复V_ToUID==null时不能平均分配; 优化日志总是全部输出 2016/04/26
+增加入池到组功能,只有不保留归属且不指定到人才能到指定组;否则组ID为空(包括不正确的组ID,保留归属,指定到人) lzp 2016/04/29
+加入功能: 在启用同类型检查唯一性的时候,(字典)池类型代码 小于1才检查 lzp 2016/06/12-
+不保留归属,转到人的,清除组ID; 返回不同的错误代码 lzpong 2016/09/09
+不保留拨打状态入池后,非未拨打的改为callBack lzpong 2016/12/23
*/
/*指定要使用的临时表
NAG_IDS_TEMP
NAG_IDS1_TEMP
NAG_ALLIDS_TEMP
NAG_FIXTABLE_TEMP (自动外拨转数据到数据池时)
返回的错误代码:
-900001 : 参数错误
-900002 : 目标池已结束, 或设置不允许转入
-900099 : 可操作流转的数据量为0
-900012 : 没有任何的比例用来分配
-900013 : 没有可按比例分配的用户
-999999 : 目标池用户数据量达到限制, 或用户数据总量达到限制; 超级管理员在池的上限也受限
-999888 : 不允许流转多条到池数据,没有流转多条的权限
*/
tType number:=0; --转数据的类型 1:客户表到池 1.5:自动外拨到池 2:单池到单池 3:多池到单池 4:来自数据池(池查询)(混合查询) 5:来自数据池(池查询结果)(池查询)
fenp number; --目标池的分配类型:1自动抽取,2平均分配,3自定义分配
--isLimit number; --目标池是否有数据限制
str1 varchar2(5000);
znum number;
xnum number;--数据状态 0:未启动 1:已启动 2:已结束,不能操作数据了
ynum number;--是否允许转入 0:不允许 1:允许
tnum number;
pnum number;
unum number;
cursor dusers is select * from nag_allids_temp; --用户
cursor newdatas is select * from nag_ids_temp where flag=0 and u_id is null; --未分配数据
strFrom varchar2(200);
dpname varchar2(200):=0; --数据池名
pici varchar(22):=to_char(systimestamp,'yyyymmdd_hh24missxff3'); --分配批次
onlyOne number; --客户资料在数据池中唯一?
d_DF number:=V_DF; --数据池转出是否删除数据
d_ToUID number;--指定的转给谁,V_ToUID 检查
GM number;--操作人是否是超管(>0),(流转多条池数据限制)
gid number:=V_GID;
--调试信息输出日志
bgDate date:=sysdate;
--调试信息记录输出
v_strMsg long;
---------------------------------比例分配新增的参数------------------------------------------
hastemp number;--临时变量
SUM_DPL_RATIOCOUNT number;--目标池比例总和
TYPE ref_cursor_type IS REF CURSOR;
Prorete_Cursor ref_cursor_type;
/*V_DPU_U_ID_TEMP varchar(50);
V_U_LEVEL_TEMP varchar(50);
V_DPL_RATIOCOUNT_TEMP varchar(50);*/
V_DPL_RATIOCOUNT_TEMP number;--用于计算比例基数 比例
V_U_COUNT_TEMP number;----用于计算比例基数 人数
V_SUMRATIONCOUNT number;--用于计算比例基数 总量
V_BASECOUNT number;--基数
V_DPU_U_ID_TEMP number;--数据池用户
V_U_ID_GETCOUNT number;--分配量
V_U_ID_KFP number;--用户可分配量(临时变量)
---------------------------------------------------------------------------------------------
begin
OutRow:=0;
if((V_ToDPID is null) or (V_FromDPID is null and V_ToDPID is null) or (V_ToDPID=V_FromDPID)) then --执行前置条件检查
OutRow:=-900001;
null;
else --前置条件满足,开始执行
/*begin
insert into T_CUSTOPOOL_PARAM( t_idw, t_fromdpid, t_todpid, t_kh, t_df, t_kcs, t_touid, t_actuid, t_outrow, t_date,t_Times,t_msg)
values(substr(V_IDW,4000), V_FromDPID, V_ToDPID, V_KH, V_DF, V_KCS, d_ToUID, V_ActUID, OutRow, sysdate, tnum, substr(v_str,1,3999));
commit;
end;*/
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 84 begin Exec ');
select to_number(sp_param1) into onlyOne from NAG_SYSPARAM where sp_id=29;--唯一性检查开关
--select dp_pooltype , nvl(d_datapool,0) into xnum,ynum from nag_datapool left join nag_dictionary on d_id=dp_pooltype where dp_id=V_ToDPID;
if(onlyOne=1 /*and ynum<1*/)then --是否删除原池数据
d_DF:=1;
end if;
select DP_START,DP_TRANSINT into xnum,ynum from nag_datapool where dp_id=V_ToDPID;
if(xnum=2 or ynum<1)then --目标池 已结束 或 不允许转入
OutRow:=-900002;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 128 目标池 已结束 或 不允许转入');
return ;
end if; --如果是单池转移,--暂做记录来源池ID
if(V_FromDPID is not null and V_FromDPID>10)then
dpname:=V_FromDPID;
end if;
--确保转到人存在
select count(u_id) into d_ToUID from nag_user where u_id=V_ToUID;
if(d_ToUID>0)then
d_ToUID:=V_ToUID;
end if;
--操作人是否是超管(>0)
select count(gu_g_id) into GM from nag_groupuser where gu_u_id=V_ActUID and gu_g_id=10000; --清理临时表
execute immediate 'truncate table NAG_IDS_TEMP';--实际(去重)数据操作表
execute immediate 'truncate table NAG_IDS1_TEMP';--临时表
--要操作的ID集合,放到临时表(NAG_IDS_TEMP),flag='0' 的可用, 并 group by 去重
strFrom:=substr(V_IDW,length(V_IDW),1);--判断数据来源
CASE strFrom
WHEN chr(44) THEN --',' 为CM_ID集
insert into NAG_IDS1_TEMP(CM_ID,DP_ID,FLAG)--CM_ID 客户ID,数据池ID,0
select COLUMN_VALUE,dpname,0 from table(splitstr(V_IDW))
where COLUMN_VALUE!=';' and COLUMN_VALUE is not null;
insert into nag_ids_temp(cm_id,flag)--CM_ID 客户ID,0
select cm_id,0 from nag_ids1_temp group by cm_id; if(V_FromDPID is not null and V_FromDPID>10)then
update nag_ids_temp t set dp_id=V_FromDPID;
end if; WHEN chr(59) THEN --';' 是数据池数据ID(dpd_id),多个池的数据
--为DPD_ID集
insert into NAG_IDS1_TEMP(DPD_ID,FLAG)--数据池数据ID,0
select COLUMN_VALUE,0 from table(splitstr(V_IDW))
where COLUMN_VALUE!=';' and COLUMN_VALUE is not null;
--根据dpd_id 更新pd_id,cm_id
merge into nag_ids1_temp t
using (select d.dpd_cm_id,d.dpd_dp_id,d.dpd_id from nag_datapooldata d where d.dpd_actstate=1) tt on (t.dpd_id=tt.dpd_id)
when matched then
update set cm_id=tt.dpd_cm_id, dp_id= tt.dpd_dp_id, t.flag=0;
--CM_ID去重插入
--insert into nag_ids_temp(cm_id,dp_id,flag) --使用merge 貌似耗时差的不多
--select cm_id,dp_id,0 from (select cm_id,dp_id,row_number() over(partition by t.cm_id order by t.cm_id) rnum from nag_ids1_temp t) where rnum<2 ;
merge into nag_ids_temp t
using(select cm_id,dp_id from (
select dpd_id,cm_id,dp_id,row_number() over(partition by t.cm_id order by t.cm_id) rnum from nag_ids1_temp t) where rnum<2 )Q on (1=2)
when not matched then insert (t.cm_id,t.dp_id,t.flag) values(Q.cm_id,Q.dp_id,0);
tType:=3; --多池到单池
if(V_FromDPID is not null and V_FromDPID>10)then
strFrom:='来自数据池('||V_FromDPID||')/勾选';
else
strFrom:='来自数据池(数据汇总/勾选)';
end if; WHEN chr(33) THEN --'!' 混合条件
str1:='insert into NAG_IDS1_TEMP(cm_id,dp_id,flag) select cm_id,dpd_dp_id,0 from nag_customer c,nag_datapooldata d where c.cm_id=d.dpd_cm_id and d.dpd_actstate=1 '||substr(V_IDW,0,length(V_IDW)-1)||' group by cm_id,dpd_dp_id';
execute immediate str1;
--CMID去重
merge into nag_ids_temp t using(select cm_id,dp_id from (
select dpd_id,cm_id,dp_id,row_number() over(partition by t.cm_id order by t.cm_id) rnum from nag_ids1_temp t) where rnum<2 )Q on (1=2)
when not matched then insert (t.cm_id,t.dp_id,t.flag) values(Q.cm_id,Q.dp_id,0); tType:=4; --池查询到单池
if(V_FromDPID is not null and V_FromDPID>10)then
strFrom:='来自数据池('||V_FromDPID||')/查询';
else
strFrom:='来自数据池(数据汇总/查询)';
end if; WHEN chr(35) THEN --'#' select语句
execute immediate 'insert into NAG_IDS1_TEMP(DPD_ID) '||substr(V_IDW,0,length(V_IDW)-1);
--根据dpd_id 更新pd_id,cm_id
merge into nag_ids1_temp t
using (select d.dpd_cm_id,d.dpd_dp_id,d.dpd_id from nag_datapooldata d where d.dpd_actstate=1) tt on (t.dpd_id=tt.dpd_id)
when matched then
update set cm_id=tt.dpd_cm_id, dp_id= tt.dpd_dp_id, t.flag=0;
--CM_ID去重插入
merge into nag_ids_temp t using(select cm_id,dp_id from (
select dpd_id,cm_id,dp_id,row_number() over(partition by t.cm_id order by t.cm_id) rnum from nag_ids1_temp t) where rnum<2 )Q on (1=2)
when not matched then insert (t.cm_id,t.dp_id,t.flag) values(Q.cm_id,Q.dp_id,0); tType:=5; --池查询到单池
if(V_FromDPID is not null and V_FromDPID>10)then
strFrom:='来自数据池('||V_FromDPID||')/查询结果';
else
strFrom:='来自数据池(数据汇总/查询结果)';
end if;
d_DF:=1; WHEN chr(64) THEN --'@' 来自自动外拨
--CMID去重
merge into nag_ids_temp t using(select all_id,flag from (
select all_id,flag,row_number() over(partition by t.all_id order by t.all_id) rnum from nag_fixtable_temp t) where rnum<2 )Q on (1=2)
when not matched then insert (t.cm_id,t.u_id,t.flag) values(Q.all_id,Q.flag,0);
tType:=1.5;
strFrom:=substr(V_IDW,0,length(V_IDW)-1); ELSE --为where的条件
str1:='';
--修复普通人员可以流转所有数据(不选择时) 2015/05/22
--为了给其他功能(导入,未接来电转移等)通用,客户资料操作转移 完全由查询条件限定的(如数据规则限制) 2015/07/07
execute immediate 'insert into NAG_IDS1_TEMP(cm_id,dp_id,flag) select cm_id,'||dpname||',0 from nag_customer left join nag_contact ct on ct_cm_id=cm_id where 1=1 '||V_IDW||str1||' group by cm_id';
insert into nag_ids_temp(cm_id,flag) select cm_id,0 from nag_ids1_temp group by cm_id;
update nag_customer c set c.CM_HOLDERID=null where c.CM_HOLDERID=0 and exists(select 1 from NAG_IDS1_TEMP t where t.cm_id=c.cm_id);
if(V_FromDPID is not null and V_FromDPID>10)then
update nag_ids_temp set dp_id=V_FromDPID;
end if; END CASE; --来路分析结束
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 236 into '||sql%rowcount);
--没有数据转移
select count(cm_id) into OutRow from nag_ids_temp;
if(OutRow=0)then
OutRow:=-900099;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 243 可操作数量为0');
return;
elsif(OutRow>1 and tType>=2 and GM<1 and V_FromDPID>10)then --非超管,流转多条池数据,检查流转(数量)规则
select count(ps_g_id) into xnum from nag_permission,nag_groupuser where gu_g_id=ps_g_id and ps_br_id=900 and gu_u_id=V_ActUID;
if(xnum<1)then
OutRow:=-999888;--没有流转多条的权限
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 250 没有流转多条的权限 ');
return;
end if;
end if;
--客户表到池 / 单池到单池
if(tType=0)then --来自多池 V_FromDPID 也为空,排除
if(V_FromDPID is null or V_FromDPID <100)then
tType:=1;
strFrom:='来自客户资料';
else
tType:=2;
strFrom:='来自数据池('||V_FromDPID||')/查询';
end if;
end if; --所有不需要执行(与目标池客户重复)的打上标识:'1'
if(tType>=2)then
--除去在本身在目标池的
delete from nag_ids_temp where dp_id=V_ToDPID;
delete from nag_ids1_temp where dp_id=V_ToDPID;
--审核状态的不能流转 标记:1
update nag_ids_temp t set flag=1
where exists(select dpd_id from nag_datapooldata d where d.dpd_cm_id=t.cm_id and d.dpd_dp_id=t.dp_id and d.dpd_actstate=1 and d.dpd_auditstate=1);
end if;
--数据唯一检查:数据本身在目标池 不需要执行的 标记:1
update nag_ids_temp set flag=1 where dp_id=V_ToDPID ;
update nag_ids1_temp set flag=1 where dp_id=V_ToDPID ;
--数据唯一检查:与目标池数据重复 标记:1
update nag_ids_temp set flag=1
where exists (select dpd_cm_id from nag_datapooldata where dpd_dp_id=V_ToDPID and dpd_cm_id=cm_id and dpd_actstate=1);
--数据池中唯一检查:与目标池同类型池唯一(排除原池) 重复标记:1
if(onlyOne>0)then
--select dp_pooltype into xnum from nag_datapool where dp_id=V_ToDPID;
select dp_pooltype , nvl(d_datapool,0) into xnum,ynum from nag_datapool left join nag_dictionary on d_id=dp_pooltype where dp_id=V_ToDPID;
--数据唯一检查:此类型的池是否检查唯一性
if(ynum<1)then
if(tType>=2)then--来自数据池
update nag_ids_temp t set flag=1
where exists (select dpd_cm_id from nag_datapooldata d,nag_datapool p where p.dp_id!=t.dp_id and p.dp_id=d.dpd_dp_id and p.dp_pooltype=xnum and d.dpd_cm_id=t.cm_id and d.dpd_actstate=1);
else --来自客户资料(没有池ID)
update nag_ids_temp t set flag=1
where exists (select dpd_cm_id from nag_datapooldata d,nag_datapool p where p.dp_id!=V_ToDPID and p.dp_id=d.dpd_dp_id and p.dp_pooltype=xnum and d.dpd_cm_id=t.cm_id and d.dpd_actstate=1);
end if;
end if;
end if;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 285 update '||sql%rowcount);
--===========================================如果没有可转移数据,跳到结束执行====================================================--
select count(cm_id) into OutRow from nag_ids_temp where flag=0;
if(OutRow=0)then
OutRow:=-900099;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 290 可操作数量为0');
goto endExc;
end if; --数据池类型:1自动抽取,2平均分配,3自定义分配,4资源分配,5自定义抽取
select dp_dataassign into fenp from nag_datapool where dp_id=V_ToDPID;
select dp_name into dpname from nag_datapool where dp_id=V_ToDPID; execute immediate 'truncate table nag_allids_temp';
--用户的可分配数量 (uid,可分配数,已分配数) --v_userdatadistribute 代替
insert into nag_allids_temp(all_id,tag5,tag6)
select u_id,leftnum, 0 from v_userdatadistribute where dp_id=V_ToDPID;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 299 into '||sql%rowcount); --=============================================上面数据已准备,下面开始分配======================================================-- --需要平均分配: 目标池为平均分配,且 不保留归属并且没有指定目标用户
if(fenp=2 and (tType<2 or (V_KH=0 and (V_ToUID<100 or V_ToUID is null)))) then --平均分配
begin
gid:=null;
--根据目标池成员删除临时表非目标池成员的数据,删除非池成员
delete from nag_allids_temp a where not exists(select pu.dpu_u_id from nag_datapooluser pu where pu.dpu_dp_id=V_ToDPID and pu.dpu_u_id=a.all_id);
--有归属的更新数量,防止超限
update nag_allids_temp a set tag5=(select a.tag5+count(cm_id) from nag_ids_temp t where t.u_id=a.all_id and flag=0);
--用户数
select count(*) into ynum from nag_allids_temp;
--可分配总量
select sum(tag5) into xnum from nag_allids_temp; --循环分配
znum:=0; --第几轮
for cur in newdatas loop
exit when xnum=0 and ynum<1;
unum:=0;
--找第znum轮还有没分配的,或者下一轮的
select count(*) into pnum from nag_allids_temp where tag6=znum and tag5>0; if(pnum>0)then --本轮有
select all_id into unum from nag_allids_temp where rownum<2 and tag6=znum and tag5>0; else --得进入下一轮
znum:=znum+1;
select count(*) into pnum from nag_allids_temp where tag6=znum and tag5>0; if(pnum<1)then exit; end if;--没人可分配了,退出
select all_id into unum from nag_allids_temp where rownum<2 and tag6=znum and tag5>0; end if; --没人要分配了,退出
if(unum=0)then exit; end if; --分配,在临时表,并记录
update nag_allids_temp set tag6=tag6+1,tag5=tag5-1 where all_id=unum; --记录,并进入下一轮待命
update nag_ids_temp set u_id=unum where cm_id=cur.cm_id; xnum:=xnum-1; end loop; --操作日志/消息
for ur in dusers loop
if(ur.tag6>0)then
--操作日志
insert into nag_system_log(sl_u_id,sl_type,sl_module,sl_content)
values(V_ActUID,'数据转移','平均分配('||dpname||'-池:ID-'||V_ToDPID||'),'||strFrom,'因平均分配规则,给用户['||ur.all_id||']分配了'||ur.tag6||'条数据'); --消息(系统消息)
insert into nag_messages(m_type,m_from,m_to,m_msg)
values(4,V_ActUID,ur.all_id,'你的平均分配数据池('||dpname||')新增了'||ur.tag6||'条数据<a href=''#'' onclick=\"parent.window.OpenTab('''||dpname||''',''/DataPool/DataPoolShow?FNID=3000'||'&'||'DPID='||V_ToDPID||'&'||'BATCH='||pici||''',true)\">查看</a>');
end if; end loop; --有剩余数据的操作日志/消息
select count(*) into xnum from nag_ids_temp where u_id is not null and flag=0;--已分配量
select count(*) into tnum from nag_ids_temp where u_id is null and flag=0;--剩余量
if(tnum>0)then
insert into nag_system_log(sl_u_id,sl_type,sl_module,sl_content)
values(V_ActUID,'数据转移','平均分配池('||dpname||' ID='||V_ToDPID||'),'||strFrom,'由平均分配规则,给('||dpname||')的'||ynum||'个用户分配了:'||xnum||'条数据,剩余:'||tnum||'条数据未分配,成为公共数据');
end if; --给执行者报告
insert into nag_messages(m_type,m_from,m_to,m_msg)
values(4,V_ActUID,V_ActUID,'你给平均分配池('||dpname||' ID='||V_ToDPID||')的'||ynum||'个用户分配了:'||xnum||'条数据,剩余:'||tnum||'条数据未分配,成为公共数据'); end;--平均分配块结束
----------------------------------------------------比例分配-------------------------------------
--需要比例分配: 目标池为比例分配,且 不保留归属并且没有指定目标用户
--数据池中-转入数据池-保留数据归属(不打勾),并且是清除数据数据归属是才满足比例分配
elsif (fenp=6 and (V_KH=0 and (V_ToUID<100 or V_ToUID is null))) then
begin
--比例分配
select count(*) into hastemp from user_tables where table_name=upper('TEMP_PRORATE_'||V_ActUID);
if(hastemp)>0 then
execute immediate 'drop table TEMP_PRORATE_'||V_ActUID||' purge ';
end if;
select count(*) into hastemp from user_tables where table_name=upper('TEMP_PRORATE_EXT'||V_ActUID);
if(hastemp>0) then
execute immediate 'drop table TEMP_PRORATE_EXT'||V_ActUID||' purge ';
end if;
--删除没有分配量的用户
delete from nag_allids_temp where tag5<1;
--用户,等级,比例
execute immediate 'create table TEMP_PRORATE_'||V_ActUID||'(DPU_U_ID,U_LEVEL,DPL_RATIOCOUNT)
as select DPU_U_ID,U_LEVEL,DPL_RATIOCOUNT from nag_datapooluser inner join nag_user on dpu_u_id=u_id
left join NAG_DATAPOOLLIMIT on dpl_dp_id=dpu_dp_id and dpl_level=U_LEVEL
where dpu_dp_id='||V_ToDPID||' and exists(select 1 from nag_allids_temp atp where atp.all_id=dpu_u_id) ';
--等级,比例,成员数
execute immediate 'create table TEMP_PRORATE_EXT'||V_ActUID||'(DPL_LEVEL,DPL_RATIOCOUNT,U_COUNT) as select DPL_LEVEL,DPL_RATIOCOUNT,(select count(1) from TEMP_PRORATE_'||V_ActUID||' tee where tee.U_LEVEL=nmt.DPL_LEVEL) as U_COUNT from NAG_DATAPOOLLIMIT nmt where dpl_dp_id='||V_ToDPID||'';
--查看有成员数的比例是不是大于0
execute immediate ' select sum(DPL_RATIOCOUNT) from TEMP_PRORATE_EXT'||V_ActUID||' where U_COUNT>0' into SUM_DPL_RATIOCOUNT;
if(SUM_DPL_RATIOCOUNT<1)then--没有任何的比例用来分配
OutRow:=-900012;
goto endExc;
end if;
--删除没有比例的用户
execute immediate 'delete from TEMP_PRORATE_'||V_ActUID||' where DPL_RATIOCOUNT<1';
execute immediate 'select count(1) from TEMP_PRORATE_'||V_ActUID||' ' into hastemp;
if hastemp<1 then
OutRow:=-900013;
goto endExc;
end if;
--删除没有比例的等级
execute immediate 'delete from TEMP_PRORATE_EXT'||V_ActUID||' where U_COUNT<1 or DPL_RATIOCOUNT<0.00000001 ';
V_SUMRATIONCOUNT:=0;
--开始计算总量
open Prorete_Cursor for 'select DPL_RATIOCOUNT,U_COUNT from TEMP_PRORATE_EXT'||V_ActUID||'';
loop
fetch Prorete_Cursor into V_DPL_RATIOCOUNT_TEMP,V_U_COUNT_TEMP;
exit when Prorete_Cursor%notfound;
V_SUMRATIONCOUNT:=V_SUMRATIONCOUNT+(V_DPL_RATIOCOUNT_TEMP*V_U_COUNT_TEMP);
end loop;
close Prorete_Cursor;
--临时表可分配的数量
select count(1) into hastemp from nag_ids_temp where flag=0;
--计算出比例最小基数
V_BASECOUNT:=hastemp/V_SUMRATIONCOUNT; open Prorete_Cursor for 'select DPU_U_ID,DPL_RATIOCOUNT from TEMP_PRORATE_'||V_ActUID||'';
loop
fetch Prorete_Cursor into V_DPU_U_ID_TEMP,V_DPL_RATIOCOUNT_TEMP;
exit when Prorete_Cursor%notfound or hastemp<1;
V_U_ID_GETCOUNT:=V_DPL_RATIOCOUNT_TEMP*V_BASECOUNT;
--判断是否小数点进1
if(V_U_ID_GETCOUNT>trunc(V_U_ID_GETCOUNT)) then
V_U_ID_GETCOUNT:=trunc(V_U_ID_GETCOUNT)+1;
end if;
--用户可分配量写入临时变量
select tag5 into V_U_ID_KFP from nag_allids_temp where all_id=V_DPU_U_ID_TEMP;
if V_U_ID_GETCOUNT>V_U_ID_KFP then
V_U_ID_GETCOUNT:=V_U_ID_KFP;
end if;
update nag_ids_temp set u_id=V_DPU_U_ID_TEMP where rownum<V_U_ID_GETCOUNT+1 and flag=0 and u_id is null;
hastemp:=hastemp-V_U_ID_GETCOUNT;
end loop;
close Prorete_Cursor; /*select * from v_userdatadistribute*/
/*insert into strtable values('比例分配'||(select count(1) from nag_ids_temp where flag=1) );*/
end;
--------------------------------------比例分配结束-----------------------------------------------
else --=================================非平均分配=============================================--
begin
if(V_KH=0)then --不保留归属
begin
update nag_ids_temp set u_id=null;--清除归属
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 365 update '||sql%rowcount);
--指定到人的话,数据限制适配
if(d_ToUID>100)then
select count(*) into tnum from nag_allids_temp t where t.tag5>0 and t.all_id=d_ToUID;
if(tnum=0)then
OutRow:=-999999; --数据量达到限额
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 370 数据量达到限额 ');
rollback;
return;
end if;
--分配,不超过剩余可分配数
select tag5 into tnum from nag_allids_temp where all_id=d_ToUID;
update nag_ids_temp set u_id=d_ToUID where flag=0 and rownum<=tnum;
tnum:=sql%rowcount;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 378 update '||sql%rowcount);
--超过的数据量,不转移
--delete from nag_ids_temp where u_id is null;
update nag_ids_temp set flag=2 where u_id is null;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 380 delete '||sql%rowcount);
--发消息(系统消息)
insert into nag_messages(m_type,m_from,m_to,m_msg)
values(4,V_ActUID,d_ToUID,'你的数据池('||dpname||')新增了'||tnum||'条数据<a href=''#'' onclick=\"parent.window.OpenTab('''||dpname||''',''/DataPool/DataPoolShow?FNID=3000'||'&'||'DPID='||V_ToDPID||'&'||'BATCH='||pici||''',true)\">查看</a>');
end if; --操作日志
insert into nag_system_log(sl_u_id,sl_type,sl_module,sl_content)
values(V_ActUID,'数据转移','指定分配('||dpname||'-池:ID='||V_ToDPID||'),'||strFrom,'给用户[ID='||d_ToUID||']分配了:'||tnum||'条数据,总数为:'||xnum||'条'); end;--不保留归属块结束
else --保留归属
begin
gid:=null;
if(tType<2)then --来自客户表(或自动外拨)
if(tType=1)then
--或许已经有归属了,就保留归属转入池
update nag_customer c set cm_holderid=(
select u.u_id from nag_user u where c.cm_holder = u.u_agentid
)where c.cm_holderid is null and exists(select 1 from nag_ids_temp t where c.cm_id=t.cm_id);
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 397 update '||sql%rowcount);
--客户归属有的,保留其归属
update (select t.u_id,c.cm_holderid from nag_ids_temp t,nag_customer c where t.cm_id=c.cm_id) set u_id=cm_holderid;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 400 update '||sql%rowcount);
end if;
--如果是第一次入池,记录
/* update nag_customer set CM_DATAPOOLID=V_ToDPID
where CM_DATAPOOLID is null and cm_id in (select cm_id from nag_ids_temp where flag=0);*/
--edit person xy 2016/3/1
update nag_customer nr set CM_DATAPOOLID=V_ToDPID
where CM_DATAPOOLID is null and exists(select 1 from nag_ids_temp where flag=0 and cm_id=nr.cm_id); else --来自数据池
update nag_ids_temp t set t.u_id=(select dpd_u_id from nag_datapooldata d where t.cm_id=d.dpd_cm_id and t.dp_id=d.dpd_dp_id and d.dpd_actstate=1 and rownum<2);
/*
merge into nag_ids_temp t
using nag_datapooldata d on(t.cm_id=d.dpd_cm_id and t.dp_id=d.dpd_dp_id and d.dpd_actstate=1)
when matched then
update set t.u_id=d.dpd_u_id;
*/
end if;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 413 insert pool '||sql%rowcount); end;--保留归属块结束
end if;--是否保留归属 end;--非平均分配块结束
end if; --是否需要平均分配 --超限的不转移 (修复剩余量小于0的不限制情况)
update nag_ids_temp tt set flag=2
where exists(--是否存在超限,或已超限
select cm_id from (
select t.cm_id,t.u_id,a.tag5,row_number() over(partition by t.u_id order by t.u_id) rnum
from nag_ids_temp t,nag_allids_temp a where t.u_id=a.all_id) A
where (A.rnum>A.tag5 or A.tag5<1) and A.u_id=tt.u_id and A.cm_id=tt.cm_id
) and tt.u_id is not null;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 429 update '||sql%rowcount); --对保留归属(且有归属)的单条转移进行特殊判断
select count(cm_id) into xnum from nag_ids_temp;
if(xnum=1 and V_KH=1)then
select count(cm_id) into xnum from nag_ids_temp where u_id is not null;--有归属数量
select count(cm_id) into ynum from nag_ids_temp where u_id is not null and flag=0;--有归属且能转移数量
if( xnum>0 and ynum=0)then
OutRow:=-999999;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 439 可操作数量为0');
goto endExc;
end if;
end if; select count(*) into ynum from nag_group where g_id=V_GID;
if(ynum<1)then --正确的组ID
gid:=null;
end if;
--=============================================上面数据已准备,下面开始转移======================================================-- --删除标记已删除的数据 , 不管删除状态(dpd_actstate=0)了会有问题
--delete from nag_datapooldata dd where dd.dpd_actstate=0 and dd.dpd_dp_id=V_ToDPID
-- and exists (select cm_id from nag_ids_temp where cm_id=dd.dpd_cm_id and flag=0);
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 447 delete '||sql%rowcount);
--已分配的数据进池,未分配/剩余的也一起进池
if(tType<2)then --来自客户表
merge into nag_datapooldata
using (select pici PICI, t.cm_id ,t.u_id,u.u_agentid,u.u_agentname,(select wm_concat(g_name) from nag_group,nag_groupuser where gu_g_id=g_id and gu_u_id=t.u_id and g_pid>-1 group by gu_u_id) u_agentpartment
from nag_customer cm, nag_user u, nag_ids_temp t
where cm.cm_id=t.cm_id and t.u_id=u.u_id(+) and t.flag=0
)tt on (1=0)
when not matched then
insert (dpd_batch,dpd_createdate,dpd_dp_id,dpd_cm_id,dpd_u_id,DPD_AGENTID,DPD_AGENTNAME,dpd_agentpartment,dpd_gid)
values (tt.PICI,sysdate,V_ToDPID,tt.cm_id ,tt.u_id,tt.u_agentid,tt.u_agentname,u_agentpartment, gid); else --来自数据池
if(d_DF>0)then --删除原池数据的
if(V_KH=1)then --保留归属的话
update nag_datapooldata d --2015/11/17 lzp 加快表数据量大时执行速度,原merge慢
set d.dpd_dp_id=V_ToDPID,d.dpd_batch=pici,d.dpd_createdate=sysdate
,d.dpd_dialstate=0,d.dpd_dialstatestr='未拨打',d.dpd_dialstarttime=null,d.dpd_dialendtime=null,d.dpd_callresult=null,d.dpd_dialtimes=0
,d.dpd_agentpartment=(select wm_concat(g_name) from nag_group,nag_groupuser where gu_g_id=g_id and gu_u_id=d.dpd_u_id and g_pid>-1 group by gu_u_id)
where DPD_ACTSTATE=1 and exists(select 1 from nag_ids_temp tt where tt.cm_id=d.dpd_cm_id and tt.flag=0 and d.dpd_dp_id=tt.dp_id); else --不保留归属
update nag_datapooldata d --2015/11/17 lzp 加快表数据量大时执行速度,原merge慢
set d.dpd_dp_id=V_ToDPID,d.dpd_batch=pici,d.dpd_createdate=sysdate,d.dpd_allocdate=sysdate,d.DPD_ALLOCDAYNUM=1,d.dpd_gid=gid
,d.dpd_dialstate=0,d.dpd_dialstatestr='未拨打',d.dpd_dialstarttime=null,d.dpd_dialendtime=null,d.dpd_callresult=null,d.dpd_dialtimes=0
,(d.dpd_u_id,d.dpd_agentid,d.dpd_agentname,d.dpd_agentpartment)
=(select u.u_id,u.u_agentid,u.u_agentname,wm_concat(g_name) from nag_group g,nag_groupuser,nag_ids_temp t
left join nag_user u on t.u_id=u.u_id
where gu_g_id=g.g_id and gu_u_id=t.u_id and t.cm_id=d.dpd_cm_id and d.dpd_dp_id=t.dp_id and g.g_pid>-1 group by u.u_id,u.u_agentid,u.u_agentname)
where DPD_ACTSTATE=1 and exists(select 1 from (select t.cm_id,t.dp_id,t.u_id,u.u_agentid,u.u_agentname from nag_ids_temp t
left join nag_user u on t.u_id=u.u_id where t.flag=0)tt where tt.cm_id=d.dpd_cm_id and d.dpd_dp_id=tt.dp_id);
end if; else --不删除原池数据
merge into nag_datapooldata
using (select pici PICI,t.cm_id ,t.u_id ,u.u_agentid ,u.u_agentname ,d.dpd_dialtel,(select wm_concat(g_name) from nag_group,nag_groupuser where gu_g_id=g_id and gu_u_id=t.u_id and g_pid>-1 group by gu_u_id) dpd_agentpartment,d.dpd_dialstate,d.dpd_dialstatestr,d.dpd_dialstarttime,d.dpd_dialendtime,d.dpd_recordfile,d.dpd_talklength,d.dpd_officiallength ,d.dpd_nextcalltime,d.dpd_callresult,d.dpd_summary,d.dpd_dialtimes,d.dpd_string1,d.dpd_string2,d.dpd_string3,d.dpd_string4,d.dpd_string5,d.dpd_string6,d.dpd_string7,d.dpd_string8,d.dpd_string9,d.dpd_string10,d.dpd_number1,d.dpd_number2,d.dpd_number3,d.dpd_number4,d.dpd_number5,d.dpd_number6,d.dpd_date1,d.dpd_date2,d.dpd_date3,d.dpd_date4,d.dpd_date5,d.dpd_comment1,d.dpd_comment2,d.dpd_comment3,d.dpd_comment4,d.dpd_comment5, d.dpd_actstate,d.dpd_act_u_id,d.dpd_actdate,d.dpd_allocdate,d.DPD_ALLOCDAYNUM
from nag_customer cm, nag_user u ,nag_datapooldata d, nag_ids_temp t
where cm.cm_id=t.cm_id and t.u_id=u.u_id(+) and t.flag=0 and t.dp_id=d.dpd_dp_id(+) and t.cm_id=d.dpd_cm_id(+)
)tt on (1=0)
when not matched then
insert (dpd_batch,dpd_dp_id,dpd_cm_id,dpd_u_id,DPD_AGENTID,DPD_AGENTNAME ,dpd_dialtel,dpd_agentpartment,dpd_dialstate,dpd_dialstatestr,dpd_dialstarttime,dpd_dialendtime,dpd_recordfile,dpd_talklength,dpd_officiallength ,dpd_nextcalltime,dpd_callresult,dpd_summary,dpd_dialtimes,dpd_string1,dpd_string2,dpd_string3,dpd_string4,dpd_string5,dpd_string6,dpd_string7,dpd_string8,dpd_string9,dpd_string10,dpd_number1,dpd_number2,dpd_number3,dpd_number4,dpd_number5,dpd_number6,dpd_date1,dpd_date2,dpd_date3,dpd_date4,dpd_date5,dpd_comment1,dpd_comment2,dpd_comment3,dpd_comment4,dpd_comment5,dpd_createdate,dpd_actstate,dpd_act_u_id,dpd_actdate,dpd_allocdate,DPD_ALLOCDAYNUM, dpd_gid)
values (tt.PICI,V_ToDPID,tt.cm_id ,tt.u_id ,tt.u_agentid ,tt.u_agentname ,tt.dpd_dialtel,tt.dpd_agentpartment,tt.dpd_dialstate,tt.dpd_dialstatestr,tt.dpd_dialstarttime,tt.dpd_dialendtime,tt.dpd_recordfile,tt.dpd_talklength,tt.dpd_officiallength ,tt.dpd_nextcalltime,tt.dpd_callresult,tt.dpd_summary,tt.dpd_dialtimes,tt.dpd_string1,tt.dpd_string2,tt.dpd_string3,tt.dpd_string4,tt.dpd_string5,tt.dpd_string6,tt.dpd_string7,tt.dpd_string8,tt.dpd_string9,tt.dpd_string10,tt.dpd_number1,tt.dpd_number2,tt.dpd_number3,tt.dpd_number4,tt.dpd_number5,tt.dpd_number6,tt.dpd_date1,tt.dpd_date2,tt.dpd_date3,tt.dpd_date4,tt.dpd_date5,tt.dpd_comment1,tt.dpd_comment2,tt.dpd_comment3,tt.dpd_comment4,tt.dpd_comment5,sysdate,tt.dpd_actstate,tt.dpd_act_u_id,tt.dpd_actdate,tt.dpd_allocdate,tt.DPD_ALLOCDAYNUM, gid);
end if;
--不保留归属,转到人的,清除组ID lzpong 2015/09/09
if (V_KH=0 and V_ToUID>100) then
update nag_datapooldata set dpd_gid=null
where dpd_dp_id=V_ToDPID and dpd_gid is not null and dpd_u_id is not null and dpd_actstate=1
and exists(select 1 from nag_ids_temp t where t.u_id=dpd_u_id and t.cm_id=dpd_cm_id);
end if;
end if;--数据流转
OutRow:=sql%rowcount;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 487 merge into pool '||sql%rowcount); --=============================================数据已转移成功,下面后续动作======================================================-- if(OutRow>0)then
--不保留拨打状态,非未拨打的改为callBack
if(V_KCS=0 and tType>1)then
update nag_datapooldata set DPD_DIALSTATE=2,DPD_DIALSTATESTR='callback',dpd_callresult=null where dpd_batch=pici and DPD_DIALSTATE>0;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3')||' 495 merge '||sql%rowcount);
end if;
--不保留归属 更新分配时间/持有天数
if(V_KH=0)then
update nag_datapooldata set dpd_allocdate=null,DPD_ALLOCDAYNUM=null where dpd_batch=pici and DPD_U_ID is null; --(无归属)
update nag_datapooldata set dpd_allocdate=sysdate,DPD_ALLOCDAYNUM=1 where dpd_batch=pici and dpd_u_id is not null;--(有归属)
end if;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 502 update '||sql%rowcount);
if(d_DF=1)then
--更新流转记录历史(删除)
update nag_dataallcationhistory set dah_isdelete=1
where exists (select /*+ ORDERED USE_NL(nag_ids1_temp)*/ t1.cm_id from nag_ids1_temp t1,nag_ids_temp t where t1.dp_id=DAH_OUTCALLID and DAH_CUSTOMERID=t1.cm_id and t1.cm_id=t.cm_id and t.flag=0);
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 507 update '||sql%rowcount);
end if;
--添加流转到记录(入池)
merge into NAG_DATAALLCATIONHISTORY
using(select t.cm_id,u.u_agentid,u.u_agentname,u.U_ID from nag_ids_temp t
left join nag_user u on t.u_id=u.u_id where t.flag=0
)tt on (1=0)
when not matched then
insert (DAH_OUTCALLID, DAH_OUTCALLNAME, DAH_CUSTOMERID, DAH_AGENTID, DAH_AGENTNAME,DAH_CREATEBY,DAH_TYPE)
values (V_ToDPID,dpname,tt.cm_id,tt.u_agentid,tt.u_agentname,V_ActUID,strFrom);
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 517 merge '||sql%rowcount); --操作日志/消息
if(fenp=1 and OutRow>1)then --是公共池,并防止拨打自动流转产生垃圾消息
insert into nag_system_log(sl_u_id,sl_type,sl_module,sl_content)
values(V_ActUID,'数据转移','自动抽取出池','给自动抽取池('||dpname||'),增加数据量:'||OutRow); insert into nag_messages(m_type,m_from,m_to,m_msg)
select 4,V_ActUID,dpu_u_id,dpname||',迎来了一波公共数据,<a href=''#'' onclick=\"parent.window.OpenTab('''||dpname||''',''/DataPool/DataPoolShow?FNID=3000'||'&'||'DPID='||V_ToDPID||''',true)\">立即前往抽取数据</a>' from nag_datapooluser where dpu_dp_id=V_ToDPID; --elsif(fenp=3)then --自定义池
-- null; --通知谁去分配数据
end if;
--操作日志/消息,分配到组的
if(gid is not null)then
select g_name into str1 from nag_group where g_id=gid;
select u_agentname||'转入数据池'||dpname||'['||OutRow||'] 条数据,并分配给组['||str1||']成为组公共数据; '||strFrom into str1 from nag_user where u_id=V_ActUID; insert into nag_system_log(sl_u_id,sl_type,sl_module,sl_content)
values( V_ActUID, '新组公共数据', '数据转移', str1); insert into nag_messages(m_type,m_from,m_to,m_title,m_msg)
select 5,V_ActUID, gm_u_id ,'新数据到组(组公共数据)',str1 from nag_groupmanger where gm_g_id=gid; insert into NAG_DATATOGROUPUSERHISTORY
values (V_ToDPID,gid,null,OutRow,sysdate,V_ActUID);
end if; --如果流转成功,更新关注共享?
if(tType>=2)then
--更新关注?用户在目标池内
select sp_param1 into xnum from nag_sysparam where sp_id=16;--数据流转时更新关注
if xnum is not null and xnum=1
then
merge into NAG_CUSTOMERATTENTION using nag_ids_temp on(flag=0 and ca_cm_id=cm_id and ca_dp_id=dp_id)
when matched then update set ca_u_id=0 delete where 1=1; merge into NAG_CUSTOMERATTENTIONHISTORY using nag_ids_temp on(flag=0 and cah_cm_id=cm_id and cah_dp_id=dp_id)
when matched then update set CAH_ENDDATETIME=sysdate;
end if;
--存在共享,更新共享?
select sp_param1 into xnum from nag_sysparam where sp_id=15;--池中已共享的数据流转时更新共享
if xnum is not null and xnum=1
then
merge into NAG_CUSTOMERSHARE using nag_ids_temp on(flag=0 and cs_cm_id=cm_id and cs_dp_id=dp_id)
when matched then update set cs_from_u_id=0 delete where 1=1; merge into NAG_CUSTOMERSHAREHISTORY using nag_ids_temp on(flag=0 and csh_cm_id=cm_id and csh_dp_id=dp_id)
when matched then update set CSH_ENDDATETIME=sysdate;
end if;
v_strMsg:=v_strMsg||chr(10)||(to_char(systimestamp,'hh24:mi:ssxff3') ||' 562 merge '||sql%rowcount);
end if; --更新关注共享 end if; --if(OutRow>0)
commit;
-----------------最后,提交更改-----------------
<<endExc>>
--记录执行日志
tnum:=(sysdate-bgDate)*48600;
--if ((tnum>0 and OutRow>1)) then
v_strMsg:=strFrom||chr(10)||v_strMsg;
insert into T_CUSTOPOOL_PARAM( t_idw, t_fromdpid, t_todpid, t_kh, t_df, t_kcs, t_touid, t_actuid, t_outrow, t_date,t_Times,t_msg)
values(substr(V_IDW,1,3985)||'...', V_FromDPID, V_ToDPID, V_KH, V_DF, V_KCS, d_ToUID, V_ActUID, OutRow, sysdate, tnum, substr(v_strMsg,1,3999));
commit;
dbms_output.put_line(v_strMsg);
--end if; end if; exception
when others then
OutRow:=SQLCODE;
IF(OutRow>0)THEN OutRow:=-OutRow;END IF;
dbms_output.put_line(SQLCODE||'---'||SQLERRM);
rollback;
--调用日志记录
v_strMsg:=strFrom||chr(10)||'SQL ERRCODE:'||SQLERRM||chr(10)||v_strMsg||chr(10)||dbms_utility.format_error_backtrace;
insert into T_CUSTOPOOL_PARAM( t_idw, t_fromdpid, t_todpid, t_kh, t_df, t_kcs, t_touid, t_actuid, t_outrow, t_date,t_Msg,t_Times)
values(substr(V_IDW,1,3985)||'...', V_FromDPID, V_ToDPID, V_KH, V_DF, V_KCS, d_ToUID, V_ActUID, OutRow,sysdate,substr(v_strMsg,1,3999),(sysdate-bgDate)*48600);
commit;
dbms_output.put_line(v_strMsg); end NAG_CUSTOMER_TOPOOLW;
当然了 , 也很大!!
不过速度还是很快的啦!!!
其中一个视图 v_userdatadistribute:
create or replace view v_userdatadistribute as
with Q1 as(
--U_ID,U_AGENTID,U_AGENTNAME,DP_ID,DP_NAME --用户,数据池(已结束除外)
select u_id,u_agentid,u_agentname,dp_id,dp_name,u_level,dp_islimit,u_maxdatacount U_MAXDATANUM from nag_user,nag_datapool where dp_start<2
),Q2 as(
--DP_ID,UID,DP_TOTALCOUNT --用户各池数据总量
select dpd_dp_id dp_id,dpd_u_id u_id,count(dpd_id) DP_TOTALCOUNT from nag_datapooldata,nag_datapool where dp_id=dpd_dp_id and dpd_u_id>0 and dpd_actstate>0 group by dpd_dp_id,dpd_u_id
),Q3 as(
--UID,U_LEVEL,DPID,DP_TOTALCOUNT --用户受限数据池数量限制量
select u_id,dpu_dp_id dp_id,dpl_totalcout DPL_DATANUM from nag_user,nag_datapooluser,nag_datapoollimit,nag_datapool where u_level=dpl_level and u_id=dpu_u_id and dpl_dp_id=dpu_dp_id and dpl_dp_id=dp_id and dp_islimit>0
),Q4 as(
--U_ID,U_DATACOUNT --用户受限池的总数据量
--select u_id,sum(DP_TOTALCOUNT) U_TOTALCOUNT from Q2 group by u_id --貌似这个更慢?
select dpd_u_id u_id, count(dpd_id) U_TOTALCOUNT from nag_datapooldata,nag_datapool where dp_id=dpd_dp_id and dp_islimit>0 and dpd_actstate>0 and dpd_u_id>0 group by dpd_u_id
),Q as(
select Q1.u_id,Q1.u_agentid,Q1.u_agentname,Q1.dp_id,Q1.dp_name,Q1.u_level,Q1.dp_islimit
,(case when Q1.U_MAXDATANUM is null or Q1.dp_islimit=0 then 9999999999 else Q1.U_MAXDATANUM end) U_MAXDATANUM --非池成员或是没有相应等级(包括是/否池成员)或非限制池的不限制
,nvl(Q3.DPL_DATANUM,9999999999) DPL_DATANUM --没有等级的不限制
,nvl(Q4.U_TOTALCOUNT,0) U_TOTALCOUNT --用户拥有数据量(不包括受限池)
,nvl(Q2.DP_TOTALCOUNT,0) DP_TOTALCOUNT --池拥有数据量
from Q1
left join Q2 on Q1.u_id=Q2.u_id and Q1.dp_id=Q2.dp_id
left join Q3 on Q1.u_id=Q3.u_id and Q1.dp_id=Q3.dp_id
left join Q4 on Q1.u_id=Q4.u_id
)
select Q.U_ID,Q.U_AGENTID,Q.U_AGENTNAME,Q.DP_ID,Q.DP_NAME,Q.U_LEVEL,Q.dp_islimit,Q.U_MAXDATANUM,Q.DPL_DATANUM,Q.U_TOTALCOUNT,Q.DP_TOTALCOUNT
,case when Q.dp_islimit=0 then 9999999999 else Q.U_MAXDATANUM-Q.U_TOTALCOUNT end u_leftnum --用户剩余量
,case when Q.dp_islimit=0 then 9999999999 else Q.DPL_DATANUM-Q.DP_TOTALCOUNT end dp_leftnum --池剩余量
,case when Q.dp_islimit=0 then 9999999999 else least((Q.U_MAXDATANUM-Q.U_TOTALCOUNT),(Q.DPL_DATANUM-Q.DP_TOTALCOUNT)) end leftnum --可用量
from Q--这些情况数据不受限: 池不参与数据限制; 不是池成员; 是池成员但是没有对应的等级
;
执行日志表:
create table T_CUSTOPOOL_PARAM
(
t_idw VARCHAR2(4000), --条件参数(以下如同)
t_fromdpid VARCHAR2(40),
t_todpid VARCHAR2(40),
t_kh NUMBER,
t_df NUMBER,
t_kcs NUMBER,
t_touid NUMBER,
t_actuid NUMBER,
t_outrow NUMBER, --结果
t_date DATE default sysdate, --时间
t_msg VARCHAR2(4000), --异常等消息
t_times NUMBER --耗时,毫秒
);