本来是打算用游标的 结果发现还是不行
麻烦会的高手请帮帮忙 游标 临时表 别的什么都可以
请帮忙改下代码提高查询速度 谢谢
代码如下:
create or replace procedure HS_CLYY_WRITE_JBSJB(XMID in VARCHAR2, DEPTCODE in VARCHAR2, STRYEAR IN VARCHAR2, STRMONTH in VARCHAR2) is
strGS varchar2(32);
strGlmk varchar2(32);
strXmname varchar2(32);
begin
strGS:=substr(DEPTCODE,0,3);
--清除原有数据
delete from tops_h_jbsjb t where to_char(t.jbsjb_date,'yyyymm') = concat(stryear,strmonth)
and t.jbsjb_xm_id =XMID
and t.jbsjb_gs_id=substr(DEPTCODE,0,3)
;
commit;
--删除临时表
delete from tops_h_temp_yyxm_group
where to_char(xm_date,'yyyymm')=concat(stryear,strmonth) and xm_code=XMID;
commit; select xm.hsxm_name into strGlmk from tops_h_hsxm xm where xm.hsxm_code=XMID;
select xm.hsxm_kjkm into strXmname from tops_h_hsxm xm where xm.hsxm_code=XMID;
case
when strGlmk='车辆检验' then
--从车辆检验表进行group插入到临时表
insert into tops_h_temp_yyxm_group(CH,JE,SL,Xm_Code,Xm_Date,DEPT_CODE)
select t.cl_clbh,sum(t.cl_fsfy),0,XMID,to_date(concat(concat(stryear,strmonth),'11'),'yyyy-mm-dd'),substr(DEPTCODE,0,3) from tops_h_cljy t
where to_char(t.cl_wcrq,'yyyymm')=concat(stryear,strmonth) and
--t.cl_jylb=strXmname and
t.cl_clbh in
( select distinct cl.cl_no from tops_h_cl cl, tops_a_department cd,tops_a_department gs
where cl.dept_id=cd.dept_id and cd.father_dept_id = gs.uuid
and (gs.dept_id= substr(DEPTCODE,0,3) and length(DEPTCODE)<4)
)
group by t.cl_clbh;
commit;
--插入到基本数据表
insert into tops_h_jbsjb
(jbsjb_sjsx,jbsjb_sjly,jbsjb_ch_id,jbsjb_je,jbsjb_sl,
jbsjb_xm_id,jbsjb_date,jbsjb_xm_name,jbsjb_gs_id,jbsjb_cd_id,
jbsjb_gs_name,jbsjb_cd_name)
select '4' ,'0',cl.cl_code,t.je,t.sl,
t.xm_code,t.xm_date,xm.hsxm_kjkm,gs.dept_id,cd.dept_id,
gs.dept_name,cd.dept_name
from tops_h_temp_yyxm_group t,tops_h_hsxm xm,
tops_h_cl cl,tops_a_department gs,tops_a_department cd
where xm.hsxm_code=XMID and t.xm_code=XMID and cl.cl_no=t.ch and cd.father_dept_id = gs.uuid and
cd.dept_id=cl.dept_id and to_char(t.xm_date,'yyyymm')=concat(stryear,strmonth)
and
t.ch in
( select distinct cl.cl_no from tops_h_cl cl, tops_a_department cd,tops_a_department gs
where cl.dept_id=cd.dept_id and cd.father_dept_id = gs.uuid
and (gs.dept_id= substr(DEPTCODE,0,3) and length(DEPTCODE)<4)
);
commit;
-- 车辆检验完成
麻烦会的高手请帮帮忙 游标 临时表 别的什么都可以
请帮忙改下代码提高查询速度 谢谢
代码如下:
create or replace procedure HS_CLYY_WRITE_JBSJB(XMID in VARCHAR2, DEPTCODE in VARCHAR2, STRYEAR IN VARCHAR2, STRMONTH in VARCHAR2) is
strGS varchar2(32);
strGlmk varchar2(32);
strXmname varchar2(32);
begin
strGS:=substr(DEPTCODE,0,3);
--清除原有数据
delete from tops_h_jbsjb t where to_char(t.jbsjb_date,'yyyymm') = concat(stryear,strmonth)
and t.jbsjb_xm_id =XMID
and t.jbsjb_gs_id=substr(DEPTCODE,0,3)
;
commit;
--删除临时表
delete from tops_h_temp_yyxm_group
where to_char(xm_date,'yyyymm')=concat(stryear,strmonth) and xm_code=XMID;
commit; select xm.hsxm_name into strGlmk from tops_h_hsxm xm where xm.hsxm_code=XMID;
select xm.hsxm_kjkm into strXmname from tops_h_hsxm xm where xm.hsxm_code=XMID;
case
when strGlmk='车辆检验' then
--从车辆检验表进行group插入到临时表
insert into tops_h_temp_yyxm_group(CH,JE,SL,Xm_Code,Xm_Date,DEPT_CODE)
select t.cl_clbh,sum(t.cl_fsfy),0,XMID,to_date(concat(concat(stryear,strmonth),'11'),'yyyy-mm-dd'),substr(DEPTCODE,0,3) from tops_h_cljy t
where to_char(t.cl_wcrq,'yyyymm')=concat(stryear,strmonth) and
--t.cl_jylb=strXmname and
t.cl_clbh in
( select distinct cl.cl_no from tops_h_cl cl, tops_a_department cd,tops_a_department gs
where cl.dept_id=cd.dept_id and cd.father_dept_id = gs.uuid
and (gs.dept_id= substr(DEPTCODE,0,3) and length(DEPTCODE)<4)
)
group by t.cl_clbh;
commit;
--插入到基本数据表
insert into tops_h_jbsjb
(jbsjb_sjsx,jbsjb_sjly,jbsjb_ch_id,jbsjb_je,jbsjb_sl,
jbsjb_xm_id,jbsjb_date,jbsjb_xm_name,jbsjb_gs_id,jbsjb_cd_id,
jbsjb_gs_name,jbsjb_cd_name)
select '4' ,'0',cl.cl_code,t.je,t.sl,
t.xm_code,t.xm_date,xm.hsxm_kjkm,gs.dept_id,cd.dept_id,
gs.dept_name,cd.dept_name
from tops_h_temp_yyxm_group t,tops_h_hsxm xm,
tops_h_cl cl,tops_a_department gs,tops_a_department cd
where xm.hsxm_code=XMID and t.xm_code=XMID and cl.cl_no=t.ch and cd.father_dept_id = gs.uuid and
cd.dept_id=cl.dept_id and to_char(t.xm_date,'yyyymm')=concat(stryear,strmonth)
and
t.ch in
( select distinct cl.cl_no from tops_h_cl cl, tops_a_department cd,tops_a_department gs
where cl.dept_id=cd.dept_id and cd.father_dept_id = gs.uuid
and (gs.dept_id= substr(DEPTCODE,0,3) and length(DEPTCODE)<4)
);
commit;
-- 车辆检验完成
解决方案 »
- case..when..then..else..end问题
- 平均薪水最高的部门名称 问题》?》》
- 如何创建一个新的oracle数据库?
- oracle sql how to write?
- 连接字符串过长
- 求一SQL
- 如何备份服务器上的数据库的到客户端(最好能提供多种方法!)
- 登录Enterprise Manager Console时为什么不能登录到Oracle Management Server?
- 关于使用索引的疑惑!?!?!?!?!?!?!?!?
- Windows环境下oracle 如何把数据存放在raw分区上;求大神指点
- oracle 把一个字段的几个值连接起来
- oracle ebs reports如何調整表身行間距
( select distinct cl.cl_no from tops_h_cl cl, tops_a_department cd,tops_a_department gs
where cl.dept_id=cd.dept_id and cd.father_dept_id = gs.uuid
and (gs.dept_id= substr(DEPTCODE,0,3) and length(DEPTCODE)<4)
)
1.这里就有两个地方可以优化,首先可以使用exist代替in,其次distinct也可以去掉这两个可能导致性能很慢
对的,可以改成exists
and exists
( select 1 from tops_h_cl cl, tops_a_department cd,tops_a_department gs
where t.ch = cl.cl_no and cl.dept_id=cd.dept_id and cd.father_dept_id = gs.uuid
and (gs.dept_id= substr(DEPTCODE,0,3) and length(DEPTCODE)<4)
);