PROCEDURE之循环

--  DROP PROCEDURE WI.SP_NG_TEST;
CREATE PROCEDURE WI.SP_NG_TEST(
IN DATEIN DATE
)
LANGUAGE SQL

BEGIN

  DECLARE I INTEGER;
  SET I=0;
   WHILE I<5 DO
   UPDATE WI.NG_TEST SET USER_NAME=USER_NAME||'>'||CHAR(I);
   SET I = I + 1;
   END WHILE ;
END;

1.利用得到的遊標在儲存過程中循環:
DECLARE CUR_FEESET CURSOR WITH RETURN TO CALLER FOR (
              SELECT
                     --
              FROM 表
              WHERE  条件
              );
       OPEN CUR_FEESET;--得到遊標
       --得到遊標記錄數
       SELECT
       count(CIF_CSTNO)
       into v_count
       FROM CB_CSTINF A ,CB_CSTBSNINF B
       WHERE A.CIF_STT<>'3' AND A.CIF_CSTNO = B.CBI_CSTNO ;   --
       FETCH CUR_FEESET INTO V_CSTNO,V_FEECODE,V_CSTLEVEL,V_FEEMODE;--
       WHILE V_COUNT>0 DO
     ……..
         FETCH CUR_FEESET INTO V_CSTNO,V_FEECODE,V_CSTLEVEL,V_FEEMODE;--
       END WHILE; --

2.另一種db2標準循環格式(leave、iterate 用法):
SET V_COUNT = LENGTH(V_VALIDAUTHCOMBOS);--
SET V_INDEX = 0;--
AUTHLOOP:
LOOP
             IF V_INDEX >= V_COUNT THEN
                     LEAVE AUTHLOOP;--相當於break
             END IF;--
              ....
             SET V_INDEX = V_INDEX + 1;--
             .......
             IF 條件 THEN
                   ITERATE AUTHLOOP;-- 相當於continue
             END IF;--      
END LOOP;--
 

3.截取字符串的循环(设V_TEMP=‘CB1001|CB1002|CB1003|’):
  SET V_LENGTH = LENGTH(V_TEMP);
   WHILE V_LENGTH>0 DO
        SET V_POS = POSSTR(V_TEMP,'|');
        SET V_CURRENT_BSN = SUBSTR( V_TEMP, 1, V_POS-1 );
        SET V_TEMP = SUBSTR( V_TEMP, V_POS+1 );
        SET V_LENGTH = LENGTH(V_TEMP);
        --最后一个字段,不再截取
        SET V_BSNTYPE = V_CURRENT_BSN;
   END WHILE;  
 
4.游標循環(不用open 游標):
DROP PROCEDURE TESTFOR;
    CREATE PROCEDURE TESTFOR()
    LANGUAGE SQL
    BEGIN
         DECLARE V_TEMP1 VARCHAR(2);
         DECLARE V_TEMP2 VARCHAR(70);
         FOR V1 AS CURSOR1 CURSOR FOR
              SELECT STUDENT_ID AS TEMP1,STUDENT_NAME AS TEMP2 FROM STUDENT
         DO
              DELETE FROM STUDENT WHERE STUDENT_ID = TEMP1;
              SET V_TEMP1 = TEMP1;
              SET V_TEMP2 = TEMP2;
         END FOR;
         COMMIT;
    END;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值