Using the looping structure in SQL

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.