v_sqlstr := 'select ';
v_sqlstr := v_sqlstr ||' reportdata_sum_view.reportid ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.reportcode ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.cropid ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.cropn ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.i_gsxz ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.i_gsfl ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.i_xh ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.year ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.month ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.vsignid ';
v_sqlstr := v_sqlstr ||' ,10053 hsignid ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.signname ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.isdata ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.unitname ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.numerator ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.denominator ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.coefficient ';
v_sqlstr := v_sqlstr ||' ,(case when sum(case when reportdata_sum_view.hsignid = 10053 then reportdata_sum_view.data else 0 end) > 0 ';
v_sqlstr := v_sqlstr ||' then sum(case when reportdata_sum_view.hsignid = 10053 then reportdata_sum_view.data else 0 end) ';
v_sqlstr := v_sqlstr ||' else 0 end) data ';
v_sqlstr := v_sqlstr ||' ,(case when sum(case when reportdata_sum_view.hsignid = 10053 then reportdata_sum_view.cumulative else 0 end) > 0 ';
v_sqlstr := v_sqlstr ||' then sum(case when reportdata_sum_view.hsignid = 10053 then reportdata_sum_view.cumulative else 0 end) ';
v_sqlstr := v_sqlstr ||' else 0 end) cumulative ';
v_sqlstr := v_sqlstr ||' from reportdata_sum_view ';
v_sqlstr := v_sqlstr ||' where reportdata_sum_view.REPORTID=108 ';
v_sqlstr := v_sqlstr ||' and reportdata_sum_view.I_GSXZ in (0,1) ';
v_sqlstr := v_sqlstr ||' and reportdata_sum_view.HSIGNID =10053 ';
v_sqlstr := v_sqlstr ||' and ((reportdata_sum_view.year between '|| to_char(p_year - 1)||' and '|| to_char(p_year)||') and (reportdata_sum_view.month between '|| to_char(p_month-1)||' and '|| to_char(p_month)||')) ';
v_sqlstr := v_sqlstr ||' group by ';
v_sqlstr := v_sqlstr ||' reportdata_sum_view.reportid ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.reportcode ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.cropid ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.cropn ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.i_gsxz ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.i_gsfl ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.i_xh ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.year ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.month ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.vsignid ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.signname ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.parentid ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.isdata ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.unitname ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.numerator ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.denominator ';
v_sqlstr := v_sqlstr ||' ,reportdata_sum_view.coefficient ';
/**********
以下获取产值表数据
**************/
v_sqlstr := v_sqlstr ||'union all ';
v_sqlstr := v_sqlstr ||'(select ';
....../**********综合能耗***********/
v_sqlstr := v_sqlstr ||'union all ';
v_sqlstr := v_sqlstr ||'(select ';
......
我通过上面的SQL语句查询得到我所需要的数据,由于时间是变化的,所以,采用了动态的语句,我想请教下如何把这个SQL语句执行,并储存到临时表里面?
取出数据再进行二次加工,加工后使用游标讲数据结果返回,我对oracle不是很熟悉,请大家多多指点,谢谢,请附例子代码
for cur in v_sqlstr loop
dbms_output.put_line(cur.reportid);
end loop;
2、oracle临时表需要事先建立,然后再使用;
如:create temporary table mytemp(col1 number, col2 varchar2(20)) on commit reserved rows ;
然后可以insert into mytemp select ....from 来插入你的数据。
当然也可以insert into mytemp(col1,col2) values (.....);
临时表的使用
3、如果你通过一条语然能完成,可以直接 open ... for 来返回数据集,如:
create or replace procedure p_out(o out sys_refcursor) is
v_sql varchar2(2000);
begin
v_sql := ''select ....... from ..... where ..........';
open o for v_sql;
end;
再在过程里
execute immediate 'insert into tmp '||v_sqlstr
select * from reportdata_source_view
where reportdata_source_view.year=2009 and reportdata_source_view.month=11
可以完成,可是当把视图和自己本身进行拼接的时候,却不行
select
rtv.rn rn
,rdv.reportid reportid
,rdv.i_gsxz i_gsxz
,rdv.I_GSFL I_GSFL
,case when (rdv.cumulative + rdv.data) > 0 then 1 else 0 end isoutput
,rdv.year year
,rdv.month month
,rdv.vsignid month
,rdv.hsignid hsignid
,rdv.signname signname
,rdv.isdata isdata
,rdv.unitname unitname
,rdv.numerator numerator
,rdv.denominator denominator
,rdv.coefficient coefficient
,rdv.coefficient_zb coefficient_zb
,0 planis --计划
,case
when rdy.HSIGNID = 10053 and rdy.data + rdvyb.DATA > 0 then (rdy.data - rdvyb.data)
when rdvb.HSIGNID <> 10053 then rdy.DATA
else 0 end c0nb --总公司上月
,case when rdy.HSIGNID = 10053 then rdy.data else rdy.cumulative end c0nbc --总公司上月累计
,case when rdv.HSIGNID = 10053 then rdv.data else rdv.DATA end c0n --总公司当月数据
,case when rdv.HSIGNID = 10053 then rdv.data else rdv.cumulative end c0nc --总公司当月累计
,case when rdvy.HSIGNID = 10053 then rdvy.DATA else rdvy.cumulative end c0nyc --总公司去年同期累计数据
,round(nvl(case
when rdv.HSIGNID = 10053 and rdvy.DATA - rdvyb.DATA > 0 then ( (rdv.DATA - rdvb.DATA)/(rdvy.DATA - rdvyb.DATA) - 1 )*100
when rdv.HSIGNID <> 10053 and rdvy.DATA > 0 then (rdv.DATA/rdvy.DATA -1 )*100
else 0 end,0),3) c0nyb --比去年(±%)
,0 c0np --完成计划(±%)
,round(nvl(case
when rdv.HSIGNID = 10053 and rdy.DATA - rdvyb.DATA > 0 then ((rdv.DATA - rdvb.DATA)/(rdy.DATA - rdvyb.DATA) - 1 )*100
when rdv.HSIGNID <> 10053 and (rdy.DATA - rdvyb.DATA) > 0 then (rdv.DATA/(rdy.DATA - rdvyb.DATA) -1 )*100
else 0 end,0),3) c0nbb --比上月(±%)
from reportdata_source_view rdv
JOIN reportsign_tree_view rtv
ON rtv.reportid=109
AND rdv.vsignid = rtv.signid
/*去年年底数据*/
LEFT JOIN reportdata_source_view rdy
on rdy.p_companyid=rdv.p_companyid
AND rdv.reportid = rdy.reportid
AND rdv.vsignid = rdy.vsignid
AND rdv.hsignid = rdy.hsignid
AND rdy.year = 2009
AND rdy.month = 12
/*前两个月数据*/
LEFT JOIN reportdata_source_view rdvbb
on rdvbb.p_companyid=rdv.p_companyid
AND rdv.reportid = rdvbb.reportid
AND rdv.vsignid = rdvbb.vsignid
AND rdv.hsignid = rdvbb.hsignid
AND rdvbb.year = 2010
AND rdvbb.month = -1
/*上个月数据*/
LEFT JOIN reportdata_source_view rdvb
on rdvb.p_companyid = rdv.p_companyid
AND rdv.reportid = rdvb.reportid
AND rdv.vsignid = rdvb.vsignid
AND rdv.hsignid = rdvb.hsignid
AND rdvb.year = 2010
AND rdvb.month = 0
/*去年前一个月数据*/
LEFT JOIN reportdata_source_view rdvyb
on rdvyb.p_companyid=rdv.p_companyid
AND rdv.reportid = rdvyb.reportid
AND rdv.vsignid = rdvyb.vsignid
AND rdv.hsignid = rdvyb.hsignid
AND rdvyb.year = 2009
AND rdvyb.month = 11
/*去年同期数据*/
LEFT JOIN reportdata_source_view rdvy
on rdvy.p_companyid=rdv.p_companyid
AND rdv.reportid = rdvy.reportid
AND rdv.vsignid = rdvy.vsignid
AND rdv.hsignid = rdvy.hsignid
AND rdvy.year = 2009
AND rdvy.month = 1
where rdv.p_companyid = 29
and rdv.year = 2010
and rdv.month = 1
order by rtv.rn
提示错误ORA-00600 internal error code,arguments:[qctcte1],[0],[],[],[]...