我的代码如下,其中季累计的不知道怎么写了 strYearMonth 是传递过来的变量----------------------------------------------------------- strYearFirstMonth t_a.Yearmonth%type := substr(strYearMonth,1,4) || '01'; type Cur_RefHS is ref cursor ; CurrefHS cur_refhs; begin if accumulationTypeID = 1 then --月累计 open currefhs for Select * From t_b b, t_a a Where a.ID=b.ID and b.yearmonth = strYearMonth; elsif accumulationTypeID = 3 then --年累计 open currefhs for open currefhs for Select * From t_b b, t_a a Where a.ID=b.ID and b.YearMonth >= stryearFirstMonth and ; elsif accumulationTypeID = 2 then --季累计 null; end if;
如果,如果参数为1,表示1季度 则查询1、2、3月得数据 create or replace procedure test(mypara number) as type ref_cursor is ref cursor; your_cursor ref_cursor; var1 varchar2(8); begin if mypara=1 then open your_cursor for select name from aa where month in(1,2,3); else open your_cursor for select name from aa where month='1'; end if; loop fetch your_cursor into var1; exit when your_cursor%notfound; dbms_output.put_line(var1); end loop; close your_cursor; end test; /
哪要12个开关,你可以设两个参数 test(mORq number,mypara number) 第一个参数如果为1,表示第二个参数为季度, 第一个参数如果为2,表示第二个参数为月份, 如test(1,1) 表示1季度 如test(2,1) 表示1月份所以只要两个open语句就可以了create or replace procedure test(qORm number,mypara varchar2) as type ref_cursor is ref cursor; your_cursor ref_cursor; var1 aa.name%type; var2 varchar2(20); m_beg aa.month%type; m_end aa.month%type; var3 varchar2(20); begin var2:=mypara; select decode(var2,1,1,2,4,3,7,10) into m_beg from dual; select decode(var2,1,3,2,6,3,9,12) into m_end from dual; if qORm=1 then open your_cursor for select name from aa where month>=m_beg and month<=m_end; else open your_cursor for select name from aa where month='1'; end if; loop fetch your_cursor into var1; exit when your_cursor%notfound; dbms_output.put_line(var1); end loop; close your_cursor; end test; /
strYearFirstMonth t_a.Yearmonth%type := substr(strYearMonth,1,4) || '01';
type Cur_RefHS is ref cursor ;
CurrefHS cur_refhs;
begin if accumulationTypeID = 1 then
--月累计
open currefhs for
Select *
From t_b b,
t_a a
Where a.ID=b.ID
and b.yearmonth = strYearMonth;
elsif accumulationTypeID = 3 then
--年累计
open currefhs for
open currefhs for
Select *
From t_b b,
t_a a
Where a.ID=b.ID
and b.YearMonth >= stryearFirstMonth and ;
elsif accumulationTypeID = 2 then
--季累计
null;
end if;
则查询1、2、3月得数据
create or replace procedure test(mypara number)
as
type ref_cursor is ref cursor;
your_cursor ref_cursor;
var1 varchar2(8);
begin
if mypara=1 then
open your_cursor for select name from aa where month in(1,2,3);
else
open your_cursor for select name from aa where month='1';
end if;
loop
fetch your_cursor into var1;
exit when your_cursor%notfound;
dbms_output.put_line(var1);
end loop;
close your_cursor;
end test;
/
test(mORq number,mypara number)
第一个参数如果为1,表示第二个参数为季度,
第一个参数如果为2,表示第二个参数为月份,
如test(1,1)
表示1季度
如test(2,1)
表示1月份所以只要两个open语句就可以了create or replace procedure test(qORm number,mypara varchar2)
as
type ref_cursor is ref cursor;
your_cursor ref_cursor;
var1 aa.name%type;
var2 varchar2(20);
m_beg aa.month%type;
m_end aa.month%type;
var3 varchar2(20);
begin
var2:=mypara;
select decode(var2,1,1,2,4,3,7,10) into m_beg from dual;
select decode(var2,1,3,2,6,3,9,12) into m_end from dual;
if qORm=1 then
open your_cursor for select name from aa where month>=m_beg and month<=m_end;
else
open your_cursor for select name from aa where month='1';
end if;
loop
fetch your_cursor into var1;
exit when your_cursor%notfound;
dbms_output.put_line(var1);
end loop;
close your_cursor;
end test;
/