How to Use the Loop Structure in SQL

How to Use the Loop Structure in SQL
The for, loop, while, repeat is an internal loop control of UDB/400, used to traverse each row of records that meet the conditions in the table.

For example:
Objective: To update the employee database and increase the salary of all Beijing employees by 10%

Example 1: Use a For Loop
--------------------------------------------
Create procedure qgpl/test_for
Language SQL
Begin
For each_record
--- Cur01 cursor
------ Select * from code, salary, city from employee where city = "Beijing"
--------- Do
------------ Update employee
------------ Set salary = salary * 1.1
------------ Where current of cur01;
Endfor;
End;

Example 2: 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
--- 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;

Example 3: Use A WHILE LOOP
---------------------------------------
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
--- 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;

Example 4: repeat loop
------------------------------------------------
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
--- 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;

conclusion: The functions of the four loop structures are basically the same. You can choose to use them according to your own habits.