DECLARE CUR_FEESET CURSORWITHRETURNTO 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;-- ENDWHILE; --
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 ENDIF;-- .... SET V_INDEX = V_INDEX +1;-- ....... IF 條件 THEN ITERATE AUTHLOOP;-- 相當於continue ENDIF;-- 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; ENDWHILE;
4.游標循環(不用open 游標):
DROPPROCEDURE TESTFOR; CREATEPROCEDURE TESTFOR() LANGUAGE SQL BEGIN DECLARE V_TEMP1 VARCHAR(2); DECLARE V_TEMP2 VARCHAR(70); FOR V1 AS CURSOR1 CURSORFOR SELECT STUDENT_ID AS TEMP1,STUDENT_NAME AS TEMP2 FROM STUDENT DO DELETEFROM STUDENT WHERE STUDENT_ID = TEMP1; SET V_TEMP1 = TEMP1; SET V_TEMP2 = TEMP2; ENDFOR; COMMIT; END;