Solution For,loop,while,repeat is an internal loop control of udb/400 that iterates through each row of records in a table that meets the criteria.
For example:
Objective: To update the employee library to raise the wages of all Beijing employees by 10%
Example one: Using a For loop
--------------------------------------------
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;
Example two: Using Loop loops
----------------------------------------
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;
Example three: Using 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 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;
Example four: Using repeat loops
------------------------------------------------
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;
Using the looping structure in SQL