db2普通循环结构

while循环

while 条件

do

         循环体

end while;

LOOP循环

SET V_INDEX = 0;

AUTHLOOP:
LOOP

V_INDEXV_INDEX = V_INDEX + 1;
             IF V_INDEX >=100 THEN
                     LEAVE AUTHLOOP;--相当于break
             END IF;
              ....
             IF 条件　THEN
                   ITERATE AUTHLOOP;-- 相当于continue
             END IF;

SET V_INDEXV_INDEX = V_INDEX + 1;
END LOOP;

REPEAT循环

REPEAT

   SQL statements;

   UNTIL condition – 退出条件

END REPEAT

FOR 循环

FOR loop_name AS

   SELECT … FROM

DO

   SQL statements;

END FOR;

循环实例：

loop循环：

create or replace procedure pro_test_loop is

i number;

begin

i:=0;

loop

  i:=i+1;

  dbms_output.put_line(i);

  if i>5 then

    exit;

  end if;

end loop;

end pro_test_loop;

while循环：

create or replace procedure pro_test_while is

i number;

begin

i:=0;

while i<5 loop

  i:=i+1;

  dbms_output.put_line(i);

end loop;

end pro_test_while;

for循环1：

create or replace procedure pro_test_for is

i number;

begin

i:=0;

for i in 1..5 loop

  dbms_output.put_line(i);

end loop;

end pro_test_for;

for循环2：

create or replace procedure pro_test_cursor is

userRow t_user%rowtype;

cursor userRows is

select * from t_user;

begin

for userRow in userRows loop

    dbms_output.put_line(userRow.Id||','||userRow.Name||','||userRows%rowcount);

end loop;

end pro_test_cursor;

游标循环使用：

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

２．另一种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;