我想做一个存储过程把一个表的数据汇总入另一个表,我是这样想的,每读出一条记录,查找另一个表人员编号相同的记录,如有则分数相加,没有则新插入一条记录。CREATE OR REPLACE PROCEDURE JXKH."HUIZONG2" is v_djibie j_huizongjb.djibie%type; v_bjibie j_huizongjb.bjibie%type; v_bbianhao j_huizongjb.bbianhao%type; v_hangming j_huizongjb.hangming%type; v_bumen j_huizongjb.bumen%type; v_yjxiangmu j_huizongjb.yjxiangmu%type; v_ejxiangmu j_huizongjb.ejxiangmu%type; v_xiangmu j_huizongjb.xiangmu%type; v_fenzhi j_huizongjb.fenzhi%type; v_fenshu j_huizongjb.fenshu%type; v_shijian j_huizongjb.shijian%type; v_quanzhong j_huizongjb.quanzhong%type; v_zongfen j_huizongmx.zongfen%type; v_renshu j_huizongmx.renshu%type; v_pingjun j_huizongmx.pingjun%type; m_djibie j_huizongmx.djibie%type; m_bianhao j_huizongmx.bianhao%type; m_xiangmu j_huizongmx.xiangmu%type; cursor c_huizongjb is select djibie,bjibie,bbianhao,hangming,bumen,yjxiangmu,ejxiangmu,xiangmu,fenzhi,fenshu,shijian,quanzhong from j_huizongjb; cursor c_huizongmx is select bianhao,djibie,xiangmu,zongfen,renshu from j_huizongmx; begin open c_huizongjb; loop fetch c_huizongjb into v_djibie,v_bjibie,v_bbianhao,v_hangming,v_bumen,v_yjxiangmu,v_ejxiangmu,v_xiangmu,v_fenzhi,v_fenshu,v_shijian,v_quanzhong; exit when c_huizongjb%notfound; update j_huizongmx set zongfen=zongfen+v_fenshu,renshu=renshu+1 where bianhao=v_bbianhao and djibie=v_djibie and xiangmu=v_xiangmu;
if sql%notfound then insert into j_huizongmx(djibie,jibie,bianhao,hangming,bumen,yjxiangmu,ejxiangmu,xiangmu,fenzhi,zongfen,shiajin,quanzhong,renshu) values(v_djibie,v_bjibie,v_bbianhao,v_hangming,v_bumen,v_yjxiangmu,v_ejxiangmu,v_xiangmu,v_fenzhi,v_fenshu,v_shijian,v_quanzhong,1); end if;
end loop; close c_huizongjb; commit; open c_huizongmx; loop fetch c_huizongmx into m_bianhao,m_djibie,m_xiangmu,v_zongfen,v_renshu; v_pingjun:=v_zongfen/v_renshu; exit when c_huizongmx%notfound; update j_huizongmx set pingjun=v_pingjun where bianhao=m_bianhao and djibie=m_djibie and xiangmu=m_xiangmu; end loop; close c_huizongmx; commit;
end huizong2; 单执行完毕只有分数为空的人数累加了,分数不为空的总分和人数都没有累加! 大家帮我看看update语句有什么错误吗?
你这样 v_exist_flag integer在那个cursor里面,读取表a的员工编号 v_exist_flag := 0; select 1 into v_exist_flag from dual where exists(select * from 要汇入的表 where 员工编号= 表a的员工编号); 然后根据v_exist_flag 是不是1 来处理插入还是更新。
v_djibie j_huizongjb.djibie%type;
v_bjibie j_huizongjb.bjibie%type;
v_bbianhao j_huizongjb.bbianhao%type;
v_hangming j_huizongjb.hangming%type;
v_bumen j_huizongjb.bumen%type;
v_yjxiangmu j_huizongjb.yjxiangmu%type;
v_ejxiangmu j_huizongjb.ejxiangmu%type;
v_xiangmu j_huizongjb.xiangmu%type;
v_fenzhi j_huizongjb.fenzhi%type;
v_fenshu j_huizongjb.fenshu%type;
v_shijian j_huizongjb.shijian%type;
v_quanzhong j_huizongjb.quanzhong%type;
v_zongfen j_huizongmx.zongfen%type;
v_renshu j_huizongmx.renshu%type;
v_pingjun j_huizongmx.pingjun%type;
m_djibie j_huizongmx.djibie%type;
m_bianhao j_huizongmx.bianhao%type;
m_xiangmu j_huizongmx.xiangmu%type;
cursor c_huizongjb is
select djibie,bjibie,bbianhao,hangming,bumen,yjxiangmu,ejxiangmu,xiangmu,fenzhi,fenshu,shijian,quanzhong
from j_huizongjb;
cursor c_huizongmx is
select bianhao,djibie,xiangmu,zongfen,renshu from j_huizongmx;
begin
open c_huizongjb;
loop
fetch c_huizongjb into v_djibie,v_bjibie,v_bbianhao,v_hangming,v_bumen,v_yjxiangmu,v_ejxiangmu,v_xiangmu,v_fenzhi,v_fenshu,v_shijian,v_quanzhong;
exit when c_huizongjb%notfound;
update j_huizongmx set zongfen=zongfen+v_fenshu,renshu=renshu+1 where bianhao=v_bbianhao and djibie=v_djibie and xiangmu=v_xiangmu;
if sql%notfound then
insert into j_huizongmx(djibie,jibie,bianhao,hangming,bumen,yjxiangmu,ejxiangmu,xiangmu,fenzhi,zongfen,shiajin,quanzhong,renshu)
values(v_djibie,v_bjibie,v_bbianhao,v_hangming,v_bumen,v_yjxiangmu,v_ejxiangmu,v_xiangmu,v_fenzhi,v_fenshu,v_shijian,v_quanzhong,1);
end if;
end loop;
close c_huizongjb;
commit;
open c_huizongmx;
loop
fetch c_huizongmx into m_bianhao,m_djibie,m_xiangmu,v_zongfen,v_renshu;
v_pingjun:=v_zongfen/v_renshu;
exit when c_huizongmx%notfound;
update j_huizongmx set pingjun=v_pingjun where bianhao=m_bianhao and djibie=m_djibie and xiangmu=m_xiangmu;
end loop;
close c_huizongmx;
commit;
end huizong2;
单执行完毕只有分数为空的人数累加了,分数不为空的总分和人数都没有累加!
大家帮我看看update语句有什么错误吗?
v_exist_flag integer在那个cursor里面,读取表a的员工编号
v_exist_flag := 0;
select 1 into v_exist_flag from dual where exists(select * from 要汇入的表 where 员工编号= 表a的员工编号);
然后根据v_exist_flag 是不是1 来处理插入还是更新。
udatate语句应该没问题呀!
请问上面语句的1 dual是什么意思?
dual是特殊的对象,一般用来显示
比如 select 1 from dual;
就会显示 1 出来了select 1 into v_exist_flag from dual就是将l赋给v_exist_flag,在将他显示出来