DB2存储过程一些技巧 博客分类： 数据库 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);
    
注意
做除法时，除数和被除数都不能为零，要做判断