2025年sql的循环语句怎么用（sql里循环怎么用）

科技前沿 • 2025-05-21 16:52 • 阅读 40

大家好，我是讯享网，很高兴认识大家。

如何在SQL中使用循环结构
解答 FOR,LOOP,WHILE,REPEAT是UDB/400的一种内部循环控制，用于遍历表中符合条件的每一行记录。

例一：使用FOR循环
——————————————–
CREATE PROCEDURE QGPL/TEST_FOR
LANGUAGE SQL
BEGIN
FOR each_record AS
—cur01 CURSOR FOR
——SELECT * FROM code,salary,city from employee where city=“Beijing”
———DO
————UPDATE employee
————SET salary=salary * 1.1
————WHERE CURRENT OF cur01;
ENDFOR;
END;

例二：使用LOOP循环
—————————————-
CREATE PROCEDURE QGPL/TEST_LOOP
LANGUAGE SQL
BEGIN
DECLARE code_v char(10);
DECLARE salary_v integer;
DECLARE city_v char(20);

DECLARE C1 CURSOR FOR
—SELECT code,salary,city FROM employee WHERE city=“Beijing”;
OPEN C1;
loop_label:
LOOP
- FETCH C1 INTO code_v,salary_v,city_v;
–IF SQLCODE=0 THEN
——SET salary_v=salary_v*1.1;
——UPDATE employee SET salary=salary_v
———WHERE CURRENT OF C1;
–ELSE
——LEAVE loop_label;
–END IF;
END LOOP loop_label;
CLOSE C1;
END;

例三：使用WHILE循环
—————————————
CREATE PROCEDURE QGPL/TEST_WHILE
LANGUAGE SQL
BEGIN
DECLARE code_v char(10);
DECLARE salary_v integer;
DECLARE city_v char(20);
DECLARE at_end integer;

DECLARE C1 CURSOR FOR
—SELECT code,salary,city FROM employee WHERE city=“Beijing”;
OPEN C1;

SET at_end=0;
WHILE at_end = 0 DO
–FETCH C1 INTO code_v,salary_v,city_v;
–IF SQLCODE=0 THEN
——SET salary_v=salary_v*1.1;
——UPDATE employee SET salary=salary_v
———WHERE CURRENT OF C1;
–ELSE
——SET at_end=1;
–END IF;
END WHILE;
CLOSE C1;
END;

例四：使用REPEAT循环
————————————————
CREATE PROCEDURE QGPL/TEST_REPEAT
LANGUAGE SQL
BEGIN
DECLARE code_v char(10);
DECLARE salary_v integer;
DECLARE city_v char(20);

DECLARE C1 CURSOR FOR
—SELECT code,salary,city FROM employee WHERE city=“Beijing”;
OPEN C1;

repeat_label:
REPEAT
–FETCH C1 INTO code_v,salary_v,city_v;
–IF SQLCODE=0 THEN
——SET salary_v=salary_v*1.1;
——UPDATE employee SET salary=salary_v
———WHERE CURRENT OF C1;
–END IF;
–UNTIL SQLCODE<>0;
END REPEAT repeat_loop;
CLOSE C1;
END;

讯享网

2025年sql的循环语句怎么用（sql里循环怎么用）

相关推荐