-- 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;