当然会错,若你能保证查出只有一条记录。 楼主意思是否要返回记录.(用游标实现吧) CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; END pkg_test; /create or replace procedure hp_mtdqtjsj(p_rc out pkg_test.myrctype) is v_sj1 varchar2(20);--开始时间 v_sj2 varchar2(20);--结束时间 str varchar2(4000); begin v_sj1:=to_char(sysdate-1,'yyyy-mm-dd')||' 20:00'; v_sj2:=to_char(sysdate,'yyyy-mm-dd')||' 20:00'; str:='select dq,zcrs,dzrs,dttze,xsze from (select a.dq as dq,count(*) as zcrs from gsmcard a where st=1 and to_char(a.indt,''yyyy-mm-dd hh24:mm'')>'''||v_sj1||''' and to_char(a.indt,''yyyy-mm-dd hh24:mm'')<='''||v_sj2||''' group by dq) , (select a.dq as dq4,count(*) as dzrs from gsmcard a where dz_f=1 and to_char(a.indt,''yyyy-mm-dd hh24:mm'')>'''||v_sj1||''' and to_char(a.indt,''yyyy-mm-dd hh24:mm'')<='''||v_sj2||''' group by dq) , (select dq as dq2,nvl(sum(n)*2,0) as dttze from (select * from ltystr) where to_char(dt,''yyyy-mm-dd hh24:mm'')>'''||v_sj1||''' and to_char(dt,''yyyy-mm-dd hh24:mm'')<='''||v_sj2||''' group by dq ) , (select dq as dq9,nvl(sum(n)*2,0) as xsze from (select * from ltystr union select * from ltystrold ) where to_char(dt,'yyyy-mm-dd hh24:mm')<='''||v_sj2||''' group by dq ) where dq=dq4(+) and dq=dq2(+) and dq=dq9(+)';open p_rc for str; end hp_mtdqtjsj;
楼主意思是否要返回记录.(用游标实现吧)
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/create or replace procedure hp_mtdqtjsj(p_rc out pkg_test.myrctype)
is
v_sj1 varchar2(20);--开始时间
v_sj2 varchar2(20);--结束时间
str varchar2(4000);
begin v_sj1:=to_char(sysdate-1,'yyyy-mm-dd')||' 20:00';
v_sj2:=to_char(sysdate,'yyyy-mm-dd')||' 20:00';
str:='select dq,zcrs,dzrs,dttze,xsze from
(select a.dq as dq,count(*) as zcrs from gsmcard a where st=1 and to_char(a.indt,''yyyy-mm-dd hh24:mm'')>'''||v_sj1||'''
and to_char(a.indt,''yyyy-mm-dd hh24:mm'')<='''||v_sj2||''' group by dq) ,
(select a.dq as dq4,count(*) as dzrs from gsmcard a where dz_f=1 and to_char(a.indt,''yyyy-mm-dd hh24:mm'')>'''||v_sj1||'''
and to_char(a.indt,''yyyy-mm-dd hh24:mm'')<='''||v_sj2||''' group by dq) ,
(select dq as dq2,nvl(sum(n)*2,0) as dttze from
(select * from ltystr) where to_char(dt,''yyyy-mm-dd hh24:mm'')>'''||v_sj1||'''
and to_char(dt,''yyyy-mm-dd hh24:mm'')<='''||v_sj2||''' group by dq ) ,
(select dq as dq9,nvl(sum(n)*2,0) as xsze from
(select * from ltystr union select * from ltystrold ) where to_char(dt,'yyyy-mm-dd hh24:mm')<='''||v_sj2||''' group by dq )
where dq=dq4(+)
and dq=dq2(+)
and dq=dq9(+)';open p_rc for str;
end hp_mtdqtjsj;