DB2 functions, stored procedure Examples

1. Functions

--drop function getmaxdate;Create FUNCTIONGetmaxdate (yintMint )returnsDatebegin     DECLAREmaxdate date; Select Max(DateID) intoMaxDate fromD_timewhereYears=Y andMonths=m; returnmaxdate;End;Values Getmaxdate (2013, 3);

2. Stored Procedures

--drop procedure test;CREATE  PROCEDURETEST (inchbegindate DATE,inchENDDATE DATE, out NUMint ) begin  DeclareCurrdate date;--   DeclareCyint ; DeclareCmint ; DECLAREAt_endINT  DEFAULT  0;--   DECLARENot_found CONDITION forSQLSTATE'02000';--     DeclareC1cursor  for    SelectDateID, years, months fromD_timewhereDateID>Begindate andDateID<=EndDate+ 1DaysOrder  byDateID;--   DECLARE  CONTINUEHANDLER forNot_foundSETAt_end=  1;--       SetNum= 0 ; OpenC1;--         FetchC1 intoCurrdate, CY, CM;--                              whileAt_end= 0 DoSETAt_end=0;--           FetchC1 intoCurrdate, CY, CM;--               Insert  intoTest (DateID, Y, M)Values(currdate, CY, CM); SetNum=Num+ 1 ; END   while;--       CLOSEC1;-- END; Call Test (date'2014-01-01', date'2014-02-23', ?);

DB2 functions, stored procedure Examples