create or replace procedure SOLVE_STEP_SELECT(StartTime in varchar2,
EndTime in varchar2) is cursor cur_step_solve is
select * from manager.cd_step_solve where rownum<0;
step_solve cur_step_solve%rowtype;
begin
open cur_step_solve for
select *
into step_solve
from manager.cd_step_solve
where (to_char(RecvDateTime, 'yyyy"/"mm"/"dd')) >= StartTime and
to_char(RecvDateTime, 'yyyy"/"mm"/"dd') < EndTime;close cur_step_solve;
end SOLVE_STEP_SELECT;
EndTime in varchar2) is cursor cur_step_solve is
select * from manager.cd_step_solve where rownum<0;
step_solve cur_step_solve%rowtype;
begin
open cur_step_solve for
select *
into step_solve
from manager.cd_step_solve
where (to_char(RecvDateTime, 'yyyy"/"mm"/"dd')) >= StartTime and
to_char(RecvDateTime, 'yyyy"/"mm"/"dd') < EndTime;close cur_step_solve;
end SOLVE_STEP_SELECT;
into step_solve
from manager.cd_step_solve
where (to_char(RecvDateTime, 'yyyy"/"mm"/"dd')) >= StartTime and
to_char(RecvDateTime, 'yyyy"/"mm"/"dd') < EndTime;
返回行数超过一条.
你可以写一个测试表.
inset into table as
select *
from manager.cd_step_solve
where (to_char(RecvDateTime, 'yyyy"/"mm"/"dd')) >= StartTime and
to_char(RecvDateTime, 'yyyy"/"mm"/"dd') < EndTime;
之后看一下这个测试表就行了
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure SOLVE_STEP_SELECT(StartTime in varchar2,
EndTime in varchar2,
cur_step_solve out pkg_test.cur_test) is begin
open cur_step_solve for
select *
into step_solve
from manager.cd_step_solve
where (to_char(RecvDateTime, 'yyyy"/"mm"/"dd')) >= StartTime and
to_char(RecvDateTime, 'yyyy"/"mm"/"dd') < EndTime;close cur_step_solve;
end SOLVE_STEP_SELECT;
谢谢你!
我是想用这个cur_step_solve返回上面select 语句返回的结果集给前台程序。
对于我上面的存储过程该怎么该呢?
select *
into step_solve
from manager.cd_step_solve
where (to_char(RecvDateTime, 'yyyy"/"mm"/"dd')) >= StartTime and
to_char(RecvDateTime, 'yyyy"/"mm"/"dd') < EndTime;
其中 step_solve在何处定义呢?
select *
from manager.cd_step_solve
where (to_char(RecvDateTime, 'yyyy"/"mm"/"dd')) >= StartTime and
to_char(RecvDateTime, 'yyyy"/"mm"/"dd') < EndTime;
cur_test out pkg_test.cur_test
execute SOLVE_STEP_SELECT('2003/09/01',2003/09/30',cur_test);
对吗?