关于某个人的工作平均分配其他人的存储过程

显示全部楼层 · 2009-3-4 17:06:25

现在要写一个存储过程，将某个人的工作平均分配其他人
输入参数1：即将被分配的工号staffid（例如：1001）
输入参数2：分配的对象staffidstr（这是工号拼装的字符串，例如：101,102,103）
输入参数3：分配人
staffidadmin（例如：120）
工作量的表：
create table t_custom
(
CUSTOMINFOID VARCHAR2(20) ,--主键
ACCEPTPHONEVARCHAR2(20) , --电话号码
CUSTOMMGRIDVARCHAR2(20),--此字段就是工号
TOWNID
VARCHAR2(20)--城市ID
)
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582015', '13912345678', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582016', '13912345679', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582017', '13912345680', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582018', '13912345681', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582019', '13912345682', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582020', '13912345683', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582022', '13912345684', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582023', '13912345685', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040730583222', '13912345686', '3338', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040730583223', '13912345687', '3338', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040725581401', '13912345688', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040725581402', '13912345689', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040725581406', '13912345690', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040725581407', '13912345691', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726581997', '13912345692', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726581998', '13912345693', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582000', '13912345694', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582001', '13912345695', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582002', '13912345696', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582004', '13912345697', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582005', '13912345698', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582006', '13912345699', '3524', null);
insert into T_CUSTOM (CUSTOMINFOID, ACCEPTPHONE, CUSTOMMGRID, TOWNID)
values ('20091229040726582007', '13912345700', '3524', null);
commit;
需要将修改的记录记日志
create table T_MODIFYLOG
(
LOGID VARCHAR2(20), --日志ID
ACCEPTPHONE VARCHAR2(20),--电话号码
OLDMGRSTAFFIDVARCHAR2(20), --老的所属工号
NEWMGRSTAFFIDVARCHAR2(20),--新的所属工号
MODIFYTIME
VARCHAR2(50),--修改时间
MODIFYSTAFFIDVARCHAR2(20) --修改人
)

请各位大侠指点下,例如将：3524的工作量平均分给:101,102,103,104,105
希望的结果：
101 5条
102 4条
103 4条
104 4条
105 4条
记录分配是随机的，主键也是随机的
[ 本帖最后由 suqibin 于 2010-8-27 14:21 编辑 ]

千问 · 2009-3-4 17:06:25

有没有什么好的思路哦？我写了个非常慢，导致前台调存储过程超时

千问 · 2009-3-4 17:06:25

WITH t AS (SELECT 100+LEVEL nid, LEVEL rn1,COUNT(*)over() cnt FROM dual CONNECT BY LEVEL >
if instr(v_staffidstr, ',')0 then
v_tb_staffid.extend();
v_tb_staffid(i) := substr(v_staffidstr, 1, instr(v_staffidstr, ',') - 1);
v_staffidstr1 := substr(v_staffidstr, instr(v_staffidstr, ',') + 1);
i := i + 1;
v_staffidstr := v_staffidstr1;
goto out_01;
end if;
v_tb_staffid.extend();
v_tb_staffid(i) := v_staffidstr;
v_cnt2 := v_tb_staffid.count;
select t.acceptphone BULK COLLECT
into v_tb_tel
from t_esl_highcustom t
where t.townid= v_townid and t.custommgrid is null
order by t.acceptphone;
select count(*)
into v_count
from t_esl_highcustom t
where t.townid = v_townid and t.custommgrid is null;
select to_char(sysdate, 'yyyy-mm-dd hh24:mm:ss') into v_date from dual;
v_cnt:= floor(v_count / v_tb_staffid.count);
v_cnt1 := mod(v_count, v_tb_staffid.count);
if v_cnt - 10 and v_cnt1 = 0 then
for m in 1 .. v_cnt - 1 loop
for n in 1 .. v_cnt2 loop
v_tb_staffid.extend();
v_tb_staffid(v_cnt2 * m + n) := v_tb_staffid(n);
end loop;
end loop;
end if;
if v_cnt - 10 and v_cnt10 then
for m in 1 .. v_cnt - 1 loop
for n in 1 .. v_cnt2 loop
v_tb_staffid.extend();
v_tb_staffid(v_cnt2 * m + n) := v_tb_staffid(n);
end loop;
end loop;
for j in 1 .. v_cnt1 loop
v_tb_staffid.extend;
v_tb_staffid(v_cnt2 * v_cnt + j) := v_tb_staffid(j);
end loop;
end if;
if v_cnt - 1 = 0 and v_cnt10 then
for j in 1 .. v_cnt1 loop
v_tb_staffid.extend;
v_tb_staffid(v_cnt2 + j) := v_tb_staffid(j);
end loop;
end if;
for i in 1 .. v_tb_staffid.count loop
DBMS_OUTPUT.PUT_LINE(v_tb_staffid(i));
end loop;
DBMS_OUTPUT.PUT_LINE(v_tb_staffid.count);
for i in 1 .. v_tb_staffid.COUNT loop
update t_esl_highcustom a
set a.custommgrid = v_tb_staffid(i)
WHERE a.acceptphone = (SELECT acceptphone

FROM (SELECT t.acceptphone, ROWNUM rn

from t_esl_highcustom t

where t.townid = v_townid and t.custommgrid is null

ORDER BY acceptphone)

WHERE rn = 1);
end loop;
commit;
FOR i in 1 .. v_tb_staffid.COUNT loop
v_tb_sampleid.extend;
select '0000' || to_char(sysdate, 'YYYYMMDD') ||
lpad(to_char(SEQ_C_Custom.nextval), 8, '0')
into v_tb_sampleid(i)
from dual;
end loop;
FORALL i in 1 .. v_tb_staffid.COUNT
insert into t_c_esl_modifymgrlog
values
(v_tb_sampleid(i),
v_tb_tel(i),
null,
v_tb_staffid(i),
v_date,
i_staffidadmin);
commit;
end;
/
这个是我的方法，太慢了

千问 · 2009-3-4 17:06:25

一共 m 条数据，分配给 n个人：
mod(m, n) = 1 给人1
mod(m, n) = 2 给人2
。。。
mod(m, n) = 0 给人n

with t_to as
(
select 101 id from dual
union
select 102 id from dual
union
select 103 id from dual
union
select 104 id from dual
union
select 105 id from dual
),
t_to_id as
(select id, rownum row_no from t_to),
t_custom_id as
(select t_custom.*, rownum row_no from t_custom)
select t_custom_id.*, t_to_id.id
from t_custom_id, t_to_id
where mod(t_custom_id.row_no, 5) = mod(t_to_id.row_no, 5);

千问 · 2009-3-4 17:06:25

吉庆的方法很好，你只需稍微改一下就可以UPDATE原表，INSERT日志，大不了用游标。
日志表里竟然没有CUSTOMINFOID，只有电话号码，怎么知道修改的是哪条？

千问 · 2009-3-4 17:06:25

CUSTOMINFOID是应该有的，现在的业务中电话号码唯一，所以就没要这个ID