PLSQL三种循环语句
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1.Loop循环
loop
exit when x < 1;
end loop;
2.While循环
while x>1 loop
end loop;
3. for循环
for x in REVERSE 1 .. 10 loop
end loop;
举例
--1.Loop循环
declare
x number(10, 2) := 10;
begin
loop
x := x - 1;
dbms_output.put_line('x的值 ' || x);
exit when x < 1;
end loop;
dbms_output.put_line('x的值 ' || x);
end;
--2.while循环
declare
x number(10, 2) := 10;
begin
while x>1 loop
x := x - 1;
dbms_output.put_line('x的值 ' || x);
end loop;
dbms_output.put_line('x的值 ' || x);
end;
--3.for循环
declare
-- x number(10, 2);
begin
for x in REVERSE 1 .. 10 loop
dbms_output.put_line('x的值 ' || x);
end loop;
end;
深化练习
/*三种循环的练习*/
select * from locations l where country_id='CA' order by l.location_id desc ;
DECLARE
v_country_id locations.country_id%TYPE := 'CA';
v_location_id locations.location_id%TYPE;
v_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id)
INTO v_location_id
FROM locations
WHERE country_id = v_country_id;
FOR i IN 1 .. 3 LOOP
dbms_output.put_line(v_location_id);
INSERT INTO locations
(location_id, city, country_id)
VALUES
((v_location_id + i), v_city, v_country_id);
END LOOP;
END;
--改为while循环
DECLARE
v_country_id locations.country_id%TYPE := 'CA';
v_location_id locations.location_id%TYPE;
v_city locations.city%TYPE := 'Montreal';
x number(9, 2) :=0;
BEGIN
SELECT MAX(location_id)
INTO v_location_id
FROM locations
WHERE country_id = v_country_id;
while x <3 LOOP
x := x + 1;
INSERT INTO locations
(location_id, city, country_id)
VALUES
((v_location_id + x), v_city, v_country_id);
END LOOP;
END;
--改为标准循环
DECLARE
v_country_id locations.country_id%TYPE := 'CA';
v_location_id locations.location_id%TYPE;
v_city locations.city%TYPE := 'Montreal';
x number(9, 2) := 0;
BEGIN
SELECT MAX(location_id)
INTO v_location_id
FROM locations
WHERE country_id = v_country_id;
LOOP
x:=x+1;
dbms_output.put_line(v_location_id);
INSERT INTO locations
(location_id, city, country_id)
VALUES
((v_location_id + x), v_city, v_country_id);
exit when x>3;
END LOOP;
END;