Table created SQL> select *from test2; N_SUM0 CZDW ------ ---- 50 440300010000 11 440304010000 100 440302010000 ----------------------------------------- create or replace procedure p_khb_test is cursor A_temp is select n_sum,tzfw from test1; v_emp A_temp%rowtype; cursor B_temp is select n_sum0,czdw from test2; v_emp_1 B_temp%rowtype;
v_czl_sj integer default NUll; v_czl_lg integer default NUll; v_czl_lh integer default NUll; v_czl_ft integer default NUll; v_czl_ns integer default NUll; v_czl_yt integer default NUll; v_czl_ba integer default NUll; v_czl_gm integer default NUll; v_czl_ps integer default NUll; v_czl_sj_1 integer default 0; v_czl_lg_1 integer default 0; v_czl_lh_1 integer default 0; v_czl_ft_1 integer default 0; v_czl_ns_1 integer default 0; v_czl_yt_1 integer default 0; v_czl_ba_1 integer default 0; v_czl_gm_1 integer default 0; v_czl_ps_1 integer default 0; begin open A_temp; fetch A_temp into v_emp; while A_temp%found loop if v_emp.tzfw ='1'then v_czl_sj:= v_emp.n_sum; elsif v_emp.tzfw ='2' then v_czl_lg:= v_emp.n_sum; elsif v_emp.tzfw ='3' then v_czl_lh:= v_emp.n_sum; elsif v_emp.tzfw ='4' then v_czl_ft:= v_emp.n_sum; elsif v_emp.tzfw ='5' then v_czl_ns:= v_emp.n_sum; elsif v_emp.tzfw ='6' then v_czl_yt:= v_emp.n_sum; elsif v_emp.tzfw ='7' then v_czl_ba:= v_emp.n_sum; elsif v_emp.tzfw ='8' then v_czl_gm:= v_emp.n_sum; elsif v_emp.tzfw ='9' then v_czl_ps:= v_emp.n_sum; end if; --fetch A_temp into v_emp; end loop; close A_temp; open B_temp; fetch B_temp into v_emp_1; while B_temp%found loop if v_emp_1.czdw ='440300010000'then v_czl_sj_1:= v_emp_1.n_sum0; elsif v_emp_1.czdw ='440307010000' then v_czl_lg_1:= v_emp_1.n_sum0; elsif v_emp_1.czdw ='440303010000' then v_czl_lh_1:= v_emp_1.n_sum0; elsif v_emp_1.czdw ='440304010000' then v_czl_ft_1:= v_emp_1.n_sum0; elsif v_emp_1.czdw ='440305010000' then v_czl_ns_1:= v_emp_1.n_sum0; elsif v_emp_1.czdw ='440308010000' then v_czl_yt_1:= v_emp_1.n_sum0; elsif v_emp_1.czdw ='440306010000' then v_czl_ba_1:= v_emp_1.n_sum0; elsif v_emp_1.czdw ='440309010000' then v_czl_gm_1:= v_emp_1.n_sum0; elsif v_emp_1.czdw ='440310010000' then v_czl_ps_1:= v_emp_1.n_sum0; end if; --fetch B_temp into v_emp_1; end loop; close B_temp; insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440300010000',v_czl_sj_1/nvl(v_czl_sj,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440307010000',v_czl_lg_1/nvl(v_czl_lg,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440303010000',v_czl_lh_1/nvl(v_czl_lh,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440304010000',v_czl_ft_1/nvl(v_czl_ft,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440305010000',v_czl_ns_1/nvl(v_czl_ns,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440308010000',v_czl_yt_1/nvl(v_czl_yt,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440306010000',v_czl_ba_1/nvl(v_czl_ba,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440309010000',v_czl_gm_1/nvl(v_czl_gm,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440310010000',v_czl_ps_1/nvl(v_czl_ps,1000000),'1','1','1','0'); commit; end p_khb_test; --不知道哪儿有问题,执行效果怎么样呢?
create or replace procedure p_khb_tj is cursor tj_czl_temp is select count(1) n_sum,tzfw from Table_1 group by tzfw; --至多有9条记录,也可能少于9条(有的单位没有) v_emp tj_czl_temp%rowtype; cursor tj_czl_temp1 is select count(1) n_sum0,czdw from Table_2 group by czdw;--至多有9条记录,也可能少于9条(有的单位没有) v_emp_1 tj_czl_temp1%rowtype; ----------记录各单位记录总数---------------------- v_czl_sj integer default NUll;--1 v_czl_lg integer default NUll;--2 v_czl_lh integer default NUll;--3 v_czl_ft integer default NUll;--4 v_czl_ns integer default NUll;--5 v_czl_yt integer default NUll;--6 v_czl_ba integer default NUll;--7 v_czl_gm integer default NUll;--8 v_czl_ps integer default NUll;--9 -----------记录各单位签收数------------------------ v_czl_sj_1 integer default 0;--1 v_czl_lg_1 integer default 0;--2 v_czl_lh_1 integer default 0;--3 v_czl_ft_1 integer default 0;--4 v_czl_ns_1 integer default 0;--5 v_czl_yt_1 integer default 0;--6 v_czl_ba_1 integer default 0;--7 v_czl_gm_1 integer default 0;--8 v_czl_ps_1 integer default 0;--9begin open tj_czl_temp; fetch tj_czl_temp into v_emp; --依据单位不同,记录总数 if v_emp.tzfw ='1'then v_czl_sj:= v_emp.n_sum;--v_dw:='440300010000';--1 else if v_emp.tzfw ='2' then v_czl_lg:= v_emp.n_sum;-- v_dw:='440307010000';--2 elsif v_emp.tzfw ='3' then v_czl_lh:= v_emp.n_sum; --v_dw:='440303010000';--3 elsif v_emp.tzfw ='4' then v_czl_ft:= v_emp.n_sum; --v_dw:='440304010000';--4 elsif v_emp.tzfw ='5' then v_czl_ns:= v_emp.n_sum; --v_dw:='440305010000';--5 elsif v_emp.tzfw ='6' then v_czl_yt:= v_emp.n_sum; --v_dw:='440308010000';--6 elsif v_emp.tzfw ='7' then v_czl_ba:= v_emp.n_sum; --v_dw:='440306010000';--7 elsif v_emp.tzfw ='8' then v_czl_gm:= v_emp.n_sum; --v_dw:='440309010000';--8 elsif v_emp.tzfw ='9' then v_czl_ps:= v_emp.n_sum; --v_dw:='440310010000';--9
end if; exit when tj_czl_temp%notfound; end loop;close tj_czl_temp;open tj_czl_temp1; fetch tj_czl_temp1 into v_emp_1; --依据单位不同,记录签收总数 if v_emp_1.czdw ='440300010000'then v_czl_sj_1:= v_emp_1.n_sum0;--1 elsif v_emp_1.czdw ='440307010000' then v_czl_lg_1:= v_emp_1.n_sum0;--2 elsif v_emp_1.czdw ='440303010000' then v_czl_lh_1:= v_emp.n_sum0;--3 elsif v_emp_1.czdw ='440304010000' then v_czl_ft_1:= v_emp.n_sum0;--4 elsif v_emp_1.czdw ='440305010000' then v_czl_ns_1:= v_emp.n_sum0;--5 elsif v_emp_1.czdw ='440308010000' then v_czl_yt:= v_emp.n_sum0; --6 elsif v_emp_1.czdw ='440306010000' then v_czl_ba_1:= v_emp.n_sum0;--7 elsif v_emp_1.czdw ='440309010000' then v_czl_gm_1:= v_emp.n_sum0;--8 elsif v_emp_1.czdw ='440310010000' then v_czl_ps_1:= v_emp.n_sum0;--9 end if; exit when tj_czl_temp1%notfound; end loop; close tj_czl_temp1; insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440300010000',v_czl_sj_1/nvl(v_czl_sj,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440307010000',v_czl_lg_1/nvl(v_czl_lg,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440303010000',v_czl_lh_1/nvl(v_czl_lh,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440304010000',v_czl_ft_1/nvl(v_czl_ft,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440305010000',v_czl_ns_1/nvl(v_czl_ns,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440308010000',v_czl_yt_1/nvl(v_czl_yt,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440306010000',v_czl_ba_1/nvl(v_czl_ba,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440309010000',v_czl_gm_1/nvl(v_czl_gm,1000000),'1','1','1','0'); insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440310010000',v_czl_ps_1/nvl(v_czl_ps,1000000),'1','1','1','0'); commit; end ;改成这样可以了
create table dwkhb
(id number,
lrsj date,
dwid char(12),
czl number(6,5),
qsl number(6,5),
fkl number(6,5),
khl number(6,5),
clf char(1))
是, 不是;--改了几个细节的问题 在我这边报视图不存在也就是说语法不存在问题
create or replace procedure p_khb_tj
is
cursor tj_czl_temp is select count(1) n_sum,tzfw from Table_1 group by tzfw;
v_emp tj_czl_temp%rowtype;
cursor tj_czl_temp1 is select count(1) n_sum0,czdw from Table_2 group by czdw;
v_emp_1 tj_czl_temp1%rowtype;
v_czl_sj integer default NUll;
v_czl_lg integer default NUll;
v_czl_lh integer default NUll;
v_czl_ft integer default NUll;
v_czl_ns integer default NUll;
v_czl_yt integer default NUll;
v_czl_ba integer default NUll;
v_czl_gm integer default NUll;
v_czl_ps integer default NUll;
v_czl_sj_1 integer default 0;
v_czl_lg_1 integer default 0;
v_czl_lh_1 integer default 0;
v_czl_ft_1 integer default 0;
v_czl_ns_1 integer default 0;
v_czl_yt_1 integer default 0;
v_czl_ba_1 integer default 0;
v_czl_gm_1 integer default 0;
v_czl_ps_1 integer default 0;
begin
open tj_czl_temp;
fetch tj_czl_temp into v_emp;
while tj_czl_temp%found
loop
if v_emp.tzfw ='1'then
v_czl_sj:= v_emp.n_sum;
elsif v_emp.tzfw ='2' then
v_czl_lg:= v_emp.n_sum;
elsif v_emp.tzfw ='3' then
v_czl_lh:= v_emp.n_sum;
elsif v_emp.tzfw ='4' then
v_czl_ft:= v_emp.n_sum;
elsif v_emp.tzfw ='5' then
v_czl_ns:= v_emp.n_sum;
elsif v_emp.tzfw ='6' then
v_czl_yt:= v_emp.n_sum;
elsif v_emp.tzfw ='7' then
v_czl_ba:= v_emp.n_sum;
elsif v_emp.tzfw ='8' then
v_czl_gm:= v_emp.n_sum;
elsif v_emp.tzfw ='9' then
v_czl_ps:= v_emp.n_sum;
end if;
fetch tj_czl_temp into v_emp;
end loop;
close tj_czl_temp;
open tj_czl_temp1;
fetch tj_czl_temp1 into v_emp_1;
while tj_czl_temp%found loop
if v_emp_1.czdw ='440300010000'then
v_czl_sj_1:= v_emp_1.n_sum0;
elsif v_emp_1.czdw ='440307010000' then
v_czl_lg_1:= v_emp_1.n_sum0;
elsif v_emp_1.czdw ='440303010000' then
v_czl_lh_1:= v_emp.n_sum0;
elsif v_emp_1.czdw ='440304010000' then
v_czl_ft_1:= v_emp.n_sum0;
elsif v_emp_1.czdw ='440305010000' then
v_czl_ns_1:= v_emp.n_sum0;
elsif v_emp_1.czdw ='440308010000' then
v_czl_yt:= v_emp.n_sum0;
elsif v_emp_1.czdw ='440306010000' then
v_czl_ba_1:= v_emp.n_sum0;
elsif v_emp_1.czdw ='440309010000' then
v_czl_gm_1:= v_emp.n_sum0;
elsif v_emp_1.czdw ='440310010000' then
v_czl_ps_1:= v_emp.n_sum0;
end if;
fetch tj_czl_temp1 into v_emp_1;
end loop;
close tj_czl_temp;
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440300010000',v_czl_sj_1/nvl(v_czl_sj,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440307010000',v_czl_lg_1/nvl(v_czl_lg,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440303010000',v_czl_lh_1/nvl(v_czl_lh,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440304010000',v_czl_ft_1/nvl(v_czl_ft,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440305010000',v_czl_ns_1/nvl(v_czl_ns,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440308010000',v_czl_yt_1/nvl(v_czl_yt,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440306010000',v_czl_ba_1/nvl(v_czl_ba,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440309010000',v_czl_gm_1/nvl(v_czl_gm,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440310010000',v_czl_ps_1/nvl(v_czl_ps,1000000),'1','1','1','0');
commit;
end ;
/
2、贴出存储过程前,里面引用的表的创建脚本要给出来。
3、一般来说,能够使用SQL语句本身来完成的,不要使用游标,游标循环较慢。
4、你最好给出原始创建表和数据脚本和想要的结果,这样可能会得到最佳的路径。
insert into 。。select语句就可以搞定了。,而且还支持以后9个万一要变成10个100个,也不用改程序。
cursor tj_czl_temp is....
--表字段、数据
n_sum tzfw
--------------
6 1
5 2
7 6
--------------第二个cursor tj_czl_temp1 is....
--表字段、数据
n_sum0 czdw
--------------------
6 440301000000
5 440302010000
7 440303010000
16 440307010000
---------------------这里关键问题是我不太了解游标像我这样写下去,还有变量这样赋值,是否妥当?
还有就是 end loop; end if;之类的结束标识是我那样的顺序吗?编译的时候有误。
cursor tj_czl_temp is....
--表字段、数据
n_sum tzfw
--------------
6 1
5 2
7 6
--------------第二个cursor tj_czl_temp1 is....
--表字段、数据
n_sum0 czdw
--------------------
6 440301000000
5 440302010000
7 440303010000
16 440307010000
---------------------
(
ID NUMBER(8) not null,
KHSJ DATE,
DWBH VARCHAR2(12),
QSCZL NUMBER(6,5),
FKL NUMBER(6,5),
KKJRL NUMBER(6,5),
CGLJL NUMBER(6,5),
CSF CHAR(1) default 0
)
SQL> select *from dwkhb;
ID KHSJ DWBH QSCZL FKL KKJRL CGLJL CSF
--------- ----------- ------------ -------- -------- -------- -------- ---
1 2010-10-14 440306010000 0.96000 0.95000 0.92000 0.93000 1
2 2010-10-19 440300010000 0.99000 0.96000 0.96000 0.94000 0
SQL> create sequence S_KHB
minvalue 1
maxvalue 9999999
start with 5
increment by 1
cache 10;SQL> create table test1(
2 n_sum number(5),
3 tzfw char(1))
4 /
Table created
SQL> insert into test1 values(12,'2');
1 row inserted
SQL> insert into test1 values(112,'6');
1 row inserted
SQL> select *from test1;
N_SUM TZFW
------ ----
60 1
12 2
112 6
SQL> commit;
Commit complete
SQL> insert into test1 values(12,'2');
1 row inserted
SQL> insert into test1 values(112,'6');
1 row inserted
SQL> create table test2(
2 n_sum0 number(5),
3 czdw char(12))
4 /
Table created
SQL> select *from test2; N_SUM0 CZDW
------ ----
50 440300010000
11 440304010000
100 440302010000
----------------------------------------- create or replace procedure p_khb_test is
cursor A_temp is
select n_sum,tzfw from test1;
v_emp A_temp%rowtype;
cursor B_temp is
select n_sum0,czdw from test2;
v_emp_1 B_temp%rowtype;
v_czl_sj integer default NUll;
v_czl_lg integer default NUll;
v_czl_lh integer default NUll;
v_czl_ft integer default NUll;
v_czl_ns integer default NUll;
v_czl_yt integer default NUll;
v_czl_ba integer default NUll;
v_czl_gm integer default NUll;
v_czl_ps integer default NUll;
v_czl_sj_1 integer default 0;
v_czl_lg_1 integer default 0;
v_czl_lh_1 integer default 0;
v_czl_ft_1 integer default 0;
v_czl_ns_1 integer default 0;
v_czl_yt_1 integer default 0;
v_czl_ba_1 integer default 0;
v_czl_gm_1 integer default 0;
v_czl_ps_1 integer default 0;
begin
open A_temp;
fetch A_temp into v_emp;
while A_temp%found
loop
if v_emp.tzfw ='1'then
v_czl_sj:= v_emp.n_sum;
elsif v_emp.tzfw ='2' then
v_czl_lg:= v_emp.n_sum;
elsif v_emp.tzfw ='3' then
v_czl_lh:= v_emp.n_sum;
elsif v_emp.tzfw ='4' then
v_czl_ft:= v_emp.n_sum;
elsif v_emp.tzfw ='5' then
v_czl_ns:= v_emp.n_sum;
elsif v_emp.tzfw ='6' then
v_czl_yt:= v_emp.n_sum;
elsif v_emp.tzfw ='7' then
v_czl_ba:= v_emp.n_sum;
elsif v_emp.tzfw ='8' then
v_czl_gm:= v_emp.n_sum;
elsif v_emp.tzfw ='9' then
v_czl_ps:= v_emp.n_sum;
end if;
--fetch A_temp into v_emp;
end loop;
close A_temp;
open B_temp;
fetch B_temp into v_emp_1;
while B_temp%found loop
if v_emp_1.czdw ='440300010000'then
v_czl_sj_1:= v_emp_1.n_sum0;
elsif v_emp_1.czdw ='440307010000' then
v_czl_lg_1:= v_emp_1.n_sum0;
elsif v_emp_1.czdw ='440303010000' then
v_czl_lh_1:= v_emp_1.n_sum0;
elsif v_emp_1.czdw ='440304010000' then
v_czl_ft_1:= v_emp_1.n_sum0;
elsif v_emp_1.czdw ='440305010000' then
v_czl_ns_1:= v_emp_1.n_sum0;
elsif v_emp_1.czdw ='440308010000' then
v_czl_yt_1:= v_emp_1.n_sum0;
elsif v_emp_1.czdw ='440306010000' then
v_czl_ba_1:= v_emp_1.n_sum0;
elsif v_emp_1.czdw ='440309010000' then
v_czl_gm_1:= v_emp_1.n_sum0;
elsif v_emp_1.czdw ='440310010000' then
v_czl_ps_1:= v_emp_1.n_sum0;
end if;
--fetch B_temp into v_emp_1;
end loop;
close B_temp;
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440300010000',v_czl_sj_1/nvl(v_czl_sj,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440307010000',v_czl_lg_1/nvl(v_czl_lg,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440303010000',v_czl_lh_1/nvl(v_czl_lh,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440304010000',v_czl_ft_1/nvl(v_czl_ft,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440305010000',v_czl_ns_1/nvl(v_czl_ns,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440308010000',v_czl_yt_1/nvl(v_czl_yt,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440306010000',v_czl_ba_1/nvl(v_czl_ba,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440309010000',v_czl_gm_1/nvl(v_czl_gm,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440310010000',v_czl_ps_1/nvl(v_czl_ps,1000000),'1','1','1','0');
commit;
end p_khb_test;
--不知道哪儿有问题,执行效果怎么样呢?
cursor tj_czl_temp is
select count(1) n_sum,tzfw from Table_1 group by tzfw; --至多有9条记录,也可能少于9条(有的单位没有)
v_emp tj_czl_temp%rowtype;
cursor tj_czl_temp1 is
select count(1) n_sum0,czdw from Table_2 group by czdw;--至多有9条记录,也可能少于9条(有的单位没有)
v_emp_1 tj_czl_temp1%rowtype;
----------记录各单位记录总数----------------------
v_czl_sj integer default NUll;--1
v_czl_lg integer default NUll;--2
v_czl_lh integer default NUll;--3
v_czl_ft integer default NUll;--4
v_czl_ns integer default NUll;--5
v_czl_yt integer default NUll;--6
v_czl_ba integer default NUll;--7
v_czl_gm integer default NUll;--8
v_czl_ps integer default NUll;--9
-----------记录各单位签收数------------------------
v_czl_sj_1 integer default 0;--1
v_czl_lg_1 integer default 0;--2
v_czl_lh_1 integer default 0;--3
v_czl_ft_1 integer default 0;--4
v_czl_ns_1 integer default 0;--5
v_czl_yt_1 integer default 0;--6
v_czl_ba_1 integer default 0;--7
v_czl_gm_1 integer default 0;--8
v_czl_ps_1 integer default 0;--9begin
open tj_czl_temp;
fetch tj_czl_temp into v_emp;
--依据单位不同,记录总数
if v_emp.tzfw ='1'then
v_czl_sj:= v_emp.n_sum;--v_dw:='440300010000';--1
else if v_emp.tzfw ='2' then
v_czl_lg:= v_emp.n_sum;-- v_dw:='440307010000';--2
elsif v_emp.tzfw ='3' then
v_czl_lh:= v_emp.n_sum; --v_dw:='440303010000';--3
elsif v_emp.tzfw ='4' then
v_czl_ft:= v_emp.n_sum; --v_dw:='440304010000';--4
elsif v_emp.tzfw ='5' then
v_czl_ns:= v_emp.n_sum; --v_dw:='440305010000';--5
elsif v_emp.tzfw ='6' then
v_czl_yt:= v_emp.n_sum; --v_dw:='440308010000';--6
elsif v_emp.tzfw ='7' then
v_czl_ba:= v_emp.n_sum; --v_dw:='440306010000';--7
elsif v_emp.tzfw ='8' then
v_czl_gm:= v_emp.n_sum; --v_dw:='440309010000';--8
elsif v_emp.tzfw ='9' then
v_czl_ps:= v_emp.n_sum; --v_dw:='440310010000';--9
end if;
exit when tj_czl_temp%notfound;
end loop;close tj_czl_temp;open tj_czl_temp1;
fetch tj_czl_temp1 into v_emp_1;
--依据单位不同,记录签收总数
if v_emp_1.czdw ='440300010000'then
v_czl_sj_1:= v_emp_1.n_sum0;--1
elsif v_emp_1.czdw ='440307010000' then
v_czl_lg_1:= v_emp_1.n_sum0;--2
elsif v_emp_1.czdw ='440303010000' then
v_czl_lh_1:= v_emp.n_sum0;--3
elsif v_emp_1.czdw ='440304010000' then
v_czl_ft_1:= v_emp.n_sum0;--4
elsif v_emp_1.czdw ='440305010000' then
v_czl_ns_1:= v_emp.n_sum0;--5
elsif v_emp_1.czdw ='440308010000' then
v_czl_yt:= v_emp.n_sum0; --6
elsif v_emp_1.czdw ='440306010000' then
v_czl_ba_1:= v_emp.n_sum0;--7
elsif v_emp_1.czdw ='440309010000' then
v_czl_gm_1:= v_emp.n_sum0;--8
elsif v_emp_1.czdw ='440310010000' then
v_czl_ps_1:= v_emp.n_sum0;--9
end if;
exit when tj_czl_temp1%notfound;
end loop;
close tj_czl_temp1;
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440300010000',v_czl_sj_1/nvl(v_czl_sj,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440307010000',v_czl_lg_1/nvl(v_czl_lg,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440303010000',v_czl_lh_1/nvl(v_czl_lh,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440304010000',v_czl_ft_1/nvl(v_czl_ft,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440305010000',v_czl_ns_1/nvl(v_czl_ns,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440308010000',v_czl_yt_1/nvl(v_czl_yt,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440306010000',v_czl_ba_1/nvl(v_czl_ba,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440309010000',v_czl_gm_1/nvl(v_czl_gm,1000000),'1','1','1','0');
insert into zhzx110.dwkhb values(s_khb.nextval,sysdate,'440310010000',v_czl_ps_1/nvl(v_czl_ps,1000000),'1','1','1','0');
commit;
end ;改成这样可以了