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;