DB2存储过程一些技巧

1 循环
1.1 while
示例：
SET vCallTime = DATE('2011-05-01');
WHILE (vCallTime <= Date('2011-06-10')) do
-- PLSQL
SET vCallTime = vCallTime + 1 days;
END WHILE;
1.2 Loop
示例:
DECLARE AT_END INTEGER DEFAULT 1;

DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET AT_END=0;
END;

OPEN curInsuranceKind;
kindLoop:
LOOP
FETCH curInsuranceKind INTO vInsuranceClassName;
IF AT_END=0 THEN
LEAVE kindLoop;
END IF;
-- PLSQL
END LOOP;
2 时间
2.1 某月有多少天
例如，2011年2月份有多少天
SELECT DAYS('2011-03-01') - DAYS('2011-02-01') FROM sysibm.sysdummy1
2.2 今天是今年的第几天
SELECT DAYS('今天日期') - DAYS('2011-01-01') + 1 FROM sysibm.sysdummy1

3 获得执行命令影响的行数
示例：
DECLARE SEQ_COL INTEGER;
SELECT COUNT(1) INTO SEQ_COL
FROM FINAL TABLE(
UPDATE DB2ADMIN.DW_BIZ2006
SET PAIDNO_SQ = vPaidno,
PAIDLOSS_SQM = vPaidloss,
SUMOSNO_SQM = vSumossno,
SUMOSLOSS_SQM = vSumosloss,
POLICYCOUNT_SQM = vPolicycount,
PREMIUM_SQM = vPremium
WHERE DESKDATE = vCollectNextMonthYear AND
COMCODE = vComCode AND
KINDCODE = vKindCode);

注意
做除法时，除数和被除数都不能为零，要做判断