--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 ;
CREATE PROCEDURE TEST
( IN BEGINDATE DATE ,
IN ENDDATE DATE ,
OUT NUM int
)
begin
declare currDate date ; --
declare cy int ;
declare cm int ;
DECLARE at_end INT DEFAULT 0 ; --
DECLARE not_found CONDITION FOR SQLSTATE '02000' ; --
declare C1 cursor for
select dateid , years ,months from d_time where dateid >beginDate and dateid <=endDate + 1 days order by dateid ; --
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1 ; --
set num = 0 ;
open C1 ; --
fetch C1 into currDate ,cy ,cm ; --
WHILE at_end = 0 DO
SET at_end =0 ; --
fetch C1 into currDate ,cy ,cm ; --
insert into test (dateid ,y , m ) values ( currDate ,cy ,cm );
set num = num + 1 ;
END WHILE ; --
CLOSE C1 ; --
END ;
call test (date '2014-01-01' ,date '2014-02-23' , ?);