CREATE TABLE AREAS
(
RADIUS NUMBER(5),
AREA NUMBER(14,2)
);
(
RADIUS NUMBER(5),
AREA NUMBER(14,2)
);
方法一:使用 goto
DECLARE
pi CONSTANT NUMBER (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;
pi CONSTANT NUMBER (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;
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 CONSTANT NUMBER (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;
DECLARE
pi CONSTANT NUMBER (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;
如果CONTINUE只有一两处,而且可以轻易变成IF..ELSE.., 那么就用IF ELSE, 这也是TOM推荐的方法。
但有时候CONTINUE藏在其他复杂的控制结构里面,IF ELSE 就不容易搞定了。我会用这种方法:
FOR 或者 WHILE 主循环 LOOP
FOR v_foo IN 1..1 LOOP ---- 第二层假循环
……代码……
IF …… THEN
EXIT; ---- 退出假循环,等效于CONTINUE主循环
END IF;
……代码……
IF …… THEN
EXIT main_loop; ---- 如果需要退出主循环,用这个写法
END IF;
……代码……
END LOOP v_foo; ---- 第二层假循环结束
END LOOP main_loop; ---- 主循环结束
初看有些古怪,因为用EXIT代替了CONTINUE. 但习惯了就会发现很灵活。
但有时候CONTINUE藏在其他复杂的控制结构里面,IF ELSE 就不容易搞定了。我会用这种方法:
FOR 或者 WHILE 主循环 LOOP
FOR v_foo IN 1..1 LOOP ---- 第二层假循环
……代码……
IF …… THEN
EXIT; ---- 退出假循环,等效于CONTINUE主循环
END IF;
……代码……
IF …… THEN
EXIT main_loop; ---- 如果需要退出主循环,用这个写法
END IF;
……代码……
END LOOP v_foo; ---- 第二层假循环结束
END LOOP main_loop; ---- 主循环结束
初看有些古怪,因为用EXIT代替了CONTINUE. 但习惯了就会发现很灵活。
相关推荐
Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, ...
《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...
《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...
Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, ...
Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, ...
The PL/SQL features for 11g Release 1 (11.1) are: ■ Enhancements to Regular Expression Built-In SQL Functions ■ SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data Types ■ CONTINUE Statement ■ ...
1.6.4 sql环境中对象的命名规则 18 第2章 主要的关系数据库与sql 21 2.1 sql server 21 2.1.1 sql server的结构 21 2.1.2 数据库访问标准化接口—odbc 22 2.1.3 使用查询分析器执行sql语句 22 2.2 transact-...
《精通SQ:结构化查询语言详解》全面讲解SQL语言,提供317个典型应用,读者可以随查随用,针对SQL Server和Oracle进行讲解,很有代表性。 全书共包括大小实例317个,突出了速学速查的特色。《精通SQ:结构化查询语言...
Starting with the 10g release, Oracle now offers a new interface, known as JDWP, for debugging PL/SQL and stored Java procedures. Toad fully supports both interfaces, but we recommend you continue to ...
sql> create user juncky identified by oracle default tablespace users sql> temporary tablespace temp quota 10m on data password expire sql> [account lock|unlock] [profile profilename|default]; ...