CREATE
TABLE AREAS
(
RADIUS NUMBER(5),
AREA NUMBER(14,2)
); 方法一:使用 goto
DECLARE
pi CONSTANTNUMBER (9,7) :=
3.1415927;
radius INTEGER (5);
area NUMBER (14,2);
BEGIN
radius :=
0;
LOOP
<<here>>
radius := radius+
1;
IF radius=
4
THEN
GOTO here;
ELSE
area :=
pi
*
POWER (radius, 2);
INSERT
INTO areas
VALUES (radius, area);
EXIT
WHEN area >
100;
END
IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END;
-- 方法二:使用 exception
-- 利用系统 exception
DECLARE
pi CONSTANT NUMBER (9,7) :=
3.1415927;
radius INTEGER (5);
area NUMBER (14,2);
some_variable NUMBER (14,2);
BEGIN
radius :=
0;
LOOP
BEGIN
radius := radius+
1;
some_variable :=
1
/ (radius -
4);
area := pi* POWER (radius,2);
INSERT INTO areas
VALUES (radius, area);
EXIT WHEN area>
100;
EXCEPTION
WHEN ZERO_DIVIDE
THEN
NULL;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END;
- 自定义 exception
DECLARE
pi CONSTANTNUMBER (9,7) :=
3.1415927;
radius INTEGER (5);
area NUMBER (14,2);
e_userdefinedexception EXCEPTION;
PRAGMA EXCEPTION_INIT (e_userdefinedexception,-1401);
BEGIN
radius :=
0;
LOOP
radius := radius+
1;
BEGIN
IF radius=
4
THEN
RAISE e_userdefinedexception;
ELSE
area :=
pi
*
POWER (radius, 2);
INSERT
INTO areas
VALUES (radius, area);
EXIT
WHEN area >
100;
END
IF;
EXCEPTION
WHEN e_userdefinedexception
THEN
NULL;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END;
方法三、
Loop
...
if(true) then
goto nextloop;
end if;
...
<<next_loop>>
null;
end loop;
在oracle11g中增加CONTINUE关键字
SQL> set serveroutput on
SQL> DECLARE
2 x number :=0;
3 BEGIN
4 dbms_output.put_line('开始计数');
5 loop
6 x:=x+1;
7 if x<=3 then
8 dbms_output.put_line('计数值:'||x);
9 continue;
10 end if;
11 dbms_output.put_line('大于3的值:'||x);
12 exit when x>=8;
13 end loop;
14 END;
15 /
开始计数
计数值:1
计数值:2
计数值:3
大于3的值:4
大于3的值:5
大于3的值:6
大于3的值:7
大于3的值:8PL/SQL 过程已成功完成
TABLE AREAS
(
RADIUS NUMBER(5),
AREA NUMBER(14,2)
); 方法一:使用 goto
DECLARE
pi CONSTANTNUMBER (9,7) :=
3.1415927;
radius INTEGER (5);
area NUMBER (14,2);
BEGIN
radius :=
0;
LOOP
<<here>>
radius := radius+
1;
IF radius=
4
THEN
GOTO here;
ELSE
area :=
pi
*
POWER (radius, 2);
INSERT
INTO areas
VALUES (radius, area);
EXIT
WHEN area >
100;
END
IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END;
-- 方法二:使用 exception
-- 利用系统 exception
DECLARE
pi CONSTANT NUMBER (9,7) :=
3.1415927;
radius INTEGER (5);
area NUMBER (14,2);
some_variable NUMBER (14,2);
BEGIN
radius :=
0;
LOOP
BEGIN
radius := radius+
1;
some_variable :=
1
/ (radius -
4);
area := pi* POWER (radius,2);
INSERT INTO areas
VALUES (radius, area);
EXIT WHEN area>
100;
EXCEPTION
WHEN ZERO_DIVIDE
THEN
NULL;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END;
- 自定义 exception
DECLARE
pi CONSTANTNUMBER (9,7) :=
3.1415927;
radius INTEGER (5);
area NUMBER (14,2);
e_userdefinedexception EXCEPTION;
PRAGMA EXCEPTION_INIT (e_userdefinedexception,-1401);
BEGIN
radius :=
0;
LOOP
radius := radius+
1;
BEGIN
IF radius=
4
THEN
RAISE e_userdefinedexception;
ELSE
area :=
pi
*
POWER (radius, 2);
INSERT
INTO areas
VALUES (radius, area);
EXIT
WHEN area >
100;
END
IF;
EXCEPTION
WHEN e_userdefinedexception
THEN
NULL;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END;
方法三、
Loop
...
if(true) then
goto nextloop;
end if;
...
<<next_loop>>
null;
end loop;
在oracle11g中增加CONTINUE关键字
SQL> set serveroutput on
SQL> DECLARE
2 x number :=0;
3 BEGIN
4 dbms_output.put_line('开始计数');
5 loop
6 x:=x+1;
7 if x<=3 then
8 dbms_output.put_line('计数值:'||x);
9 continue;
10 end if;
11 dbms_output.put_line('大于3的值:'||x);
12 exit when x>=8;
13 end loop;
14 END;
15 /
开始计数
计数值:1
计数值:2
计数值:3
大于3的值:4
大于3的值:5
大于3的值:6
大于3的值:7
大于3的值:8PL/SQL 过程已成功完成