db2普通循环结构

while循环

1. while 条件        
2.  
3. do  
4.  
5.          循环体  
6.  
7. end while;  

LOOP循环

1. SET V_INDEX = 0;  
2.  
3. AUTHLOOP:  
4. LOOP  
5.  
6. V_INDEXV_INDEX = V_INDEX + 1;  
7.              IF V_INDEX >=100 THEN  
8.                      LEAVE AUTHLOOP;--相当于break  
9.              END IF;  
10.               ....  
11.              IF 条件　THEN  
12.                    ITERATE AUTHLOOP;-- 相当于continue  
13.              END IF;       
14.  
15. SET V_INDEXV_INDEX = V_INDEX + 1;  
16. END LOOP;  

REPEAT循环

1. REPEAT   
2.  
3.    SQL statements;   
4.  
5.    UNTIL condition – 退出条件  
6.  
7. END REPEAT  

FOR 循环

1. FOR loop_name AS   
2.  
3.    SELECT … FROM   
4.  
5. DO   
6.  
7.    SQL statements;   
8.  
9. 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;

db2循环