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_jyje),sum(t.cl_jysl),XMID,to_date(concat(concat(stryear,strmonth),'11'),'yyyy-mm-dd'),substr(DEPTCODE,0,3) from tops_h_yhgl t
where to_char(t.cl_jysj,'yyyymm')=concat(stryear,strmonth) and
-- t.cl_clbh in
-- ( select distinct cl.cl_code 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)
-- )
-- or
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;麻烦大神能给个具体写法嘛 谢谢
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_jyje),sum(t.cl_jysl),XMID,to_date(concat(concat(stryear,strmonth),'11'),'yyyy-mm-dd'),substr(DEPTCODE,0,3) from tops_h_yhgl t
where to_char(t.cl_jysj,'yyyymm')=concat(stryear,strmonth) and
-- t.cl_clbh in
-- ( select distinct cl.cl_code 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)
-- )
-- or
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;麻烦大神能给个具体写法嘛 谢谢
解决方案 »
- red hat5下安装oracle实例,数据库创建到85%时停滞,没有错误提示
- 关于to_datet的使用我如果不指定格式的话,它的默认格式是什么呢?用我如果不指定格式的话,它的默认格式是什么呢?
- SQL优化一下。。。
- 该表空间中已存在一个或多个数据文件,是否重复使用已存在的数据文件?
- 帮忙看看这个触发器那里有问题
- 求一SQL语句
- oracle 的连接突然失去
- 特急求助!请多个有经验大侠支援,不必全部回答,针对里面的其中一两个问题尽可能回答,分数不够的开多个相同帖给分
- 如何在客户端不用安装oracle程序就可以连上oracle数据库,我用的是(pb9.0+oracle8.0.5),说的越详细越好,谢谢
- 如何让where中不存在的值显示为NULL
- 关于oracle时间问题?
- 关于C#要返回Oracel数据集 Oracel中的包
create global temporary table tops_h_temp_yyxm_group(.........) on commit delete rows;
2、看你这个逻辑也可以不用建立临时表也可以搞定,在事务不太大的情况下最好不要建立临时表。
3、建议不要使用拼音+英文单词混合命名方式,看了有点晕~~~~
select cl.cl_code,temp.je,temp.sl,
temp.xm_code,temp.xm_date,xm.hsxm_kjkm,gs.dept_id,cd.dept_id,
gs.dept_name as jbsjb_gs_name ,cd.dept_name as jbsjb_cd_name
from (select t.cl_clbh as ch,sum(t.cl_jyje) as je,sum(t.cl_jysl) as sl,XMID as xm_code,to_date(concat(concat(stryear,strmonth),'11'),'yyyy-mm-dd') as xm_date from tops_h_yhgl t
where to_char(t.cl_jysj,'yyyymm')=concat(stryear,strmonth)
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) as temp,tops_h_hsxm xm,tops_h_cl cl,tops_a_department gs,tops_a_department cd
where xm.hsxm_code=temp.xm_code
and temp.xm_code=XMID
and cl.cl_no=temp.ch
and cd.father_dept_id = gs.uuid
and cd.dept_id=cl.dept_id;
rec_baseInfo cur_baseInfo%rowtype;
begin
open cur_baseInfo;
fetch cur_baseInfo into rec_baseInfo;
loop
exit when cur_baseInfo%notfound;
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)values('4','0',rec_baseInfo.cl_code,rec_baseInfo.je,rec_baseInfo.sl,
rec_baseInfo.xm_code,rec_baseInfo.xm_date,rec_baseInfo.hsxm_kjkm,rec_baseInfo.dept_jd,
rec_baseInfo.dept_id,rec_baseInfo.jbsjb_gs_name,rec_baseInfo.jbsjb_cd_name); end loop;
close cur_baseInfo;
commit;
end;
而且你也没有说什么情况,是你哪部分操作造成你的存储过程慢?还是其他什么状况。。
1.select cl.cl_code,temp.je,temp.sl, 提示错误SQL statemengt ignored
2.group by t.cl_clbh) as temp,tops_h_hsxm xm,tops_h_cl cl,tops_a_department gs,tops_a_department cd 提示错误 SQL 命令未正确结束