解决方案 »
- 菜鸟初始jdbc的问题
- oracle9i导出的.dmp导入oracle11g
- 上海 急急急
- mysql中create table可以自定义表的类型为InnoDB或MyISAM,那么其他数据库如orocle是否也有类似的表类型?
- 用Trigger实现表字段的自动更新!!!!求助
- 一个sql语句
- 数据库表导出导入
- oracle如何导出table的c语言结构?
- oracle里用什么命令格式从数据库A里把某一表连表结构和数据一起倒成一个文件,然后用什么命令格式把该文件倒到数据库B里(当然是倒文件的
- 超级简单问题1(100分)
- 关于Oracle10安装过程的问题~急救
- oracle expdp与exp 的速度问题
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 ;改成这样可以了