db2 函数、存储过程示例
1、函数--drop function getMaxDate;
create FUNCTION getMaxDate (y int, m int )
returns date
begin
DECLARE maxDate date ;
select max(dateid ) into maxDate from d_time where years = y and months = m;
return maxDate;
end;
values getMaxDate(2013 ,3);
2、存储过程
--drop procedure test ;
CREATEPROCEDURE TEST
( IN BEGINDATE DATE ,
IN ENDDATE DATE ,
OUT NUM int
)
begin
declare currDate date ; --
declare cy int ;
declare cm int ;
DECLAREat_end INTDEFAULT0 ; --
DECLAREnot_found CONDITIONFORSQLSTATE'02000' ; --
declare C1 cursor for
select dateid , years ,months from d_time where dateid >beginDate and dateid <=endDate + 1days order by dateid ; --
DECLARECONTINUEHANDLERFORnot_found
SETat_end =1 ; --
set num = 0 ;
open C1 ; --
fetchC1 into currDate ,cy ,cm ; --
WHILEat_end = 0 DO
SET at_end =0 ; --
fetchC1 into currDate ,cy ,cm ; --
insert into test (dateid ,y , m ) values ( currDate ,cy ,cm );
set num = num + 1 ;
ENDWHILE ; --
CLOSEC1 ; --
END ;
call test (date '2014-01-01' ,date '2014-02-23' , ?);
页:
[1]