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