DB2循環的用途在DB2數據庫中是很廣泛的,下面就為您介紹四種常見的DB2循環用法,希望可以在您學習DB2循環方面有所幫助。
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_INDEXV_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;
DB2創建數據庫的實現
DB2目錄視圖說明
DB2創建表--帶有自增列的表
DB2重命名表和查看表信息
DB2列轉行的實現