[大数据]Oracle异常处理语法与实例

0 总结

oracle异常总结

• Oracle内部异常统一在《Database Error Messages》中，例如ORA-00910等等，其中-00910就是SQLCODE。
• Oracle内部异常有系统内部识别抛出，在Catch时只能使用Other关键字接受（… when others …）。
• 预定义异常将一些常用SQLCODE绑定名称，所以在Catch是可以使用名字接收(… when ZERO_DIVIDE …)。
• 用户自定异常有两种使用方式：
  • 定义·xxx EXCEPTION;变量，然后直接raise xxx;，sqlcode永远为1。
  • 触发指定sqlcode的异常：3.2 与 3.3中介绍。sqlcode为定义值，范围：-20000到-20999。

sqlcode总结

• 内部异常、预定义异常（=内部异常号绑定名字），sqlcode通常为负数，只有"no data found"是100。
• 用户定义异常
  • 直接定义使用的sqlcode永远为1。
  • 用RAISE_APPLICATION_ERROR的范围：-20000到-20999。

1 Oracle内部异常

• 不能用名字catch，必须间接使用EXCEPTION_INIT定义名字来catch
• 预定义异常相当于给内部异常一个名字

1.1 实例

create table t12(info varchar(2));

BEGIN
  INSERT INTO t12 VALUES('11111111111');
EXCEPTION
  WHEN others THEN
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);
END;
/

-- -12899
-- ORA-12899: value too large for column "SYS"."T12"."INFO" (actual: 11, maximum: 2)

1.2 取值范围

从文档中看

sqlcode可以使用范围：-00001到-99999，五位负数。

实例：

...
ORA-12872: First slave parse gave different plan
ORA-12899: value too large for column string (actual: string, maximum: string)
ORA-12899: value too large for column string (actual: string, maximum: string)
ORA-12901: default temporary tablespace must be of TEMPORARY type
...

1.3 Raise与Catch方法

触发方式：

• 系统系统触发，只能用others接收。
• 用户自定义触发，可以用自定义标识符接受系统内部异常，注意SQLCODE必须为系统内部异常的CODE才能实现对应功能。

create table t12(info varchar(2));

DECLARE
  vvv EXCEPTION;
  PRAGMA EXCEPTION_INIT(vvv, -12899);
BEGIN
  INSERT INTO t12 VALUES('11111111111');
EXCEPTION
  WHEN vvv THEN
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);
END;
/

2 Oracle预定义异常

• 一共22个预定义异常

• 注意所有预定义异常可以理解为Oracle内部异常加上STANDARD包中使用EXCEPTION_INIT定义了一些异常名，类似于1.3中的实例用法。

• NO_DATA_FOUND 100是比较特殊的语法，唯一SQLCODE为正数的用法。

内部异常与预定义异常等价实例

内部异常

DECLARE
  stock_price   NUMBER := 9.73;
  net_earnings  NUMBER := 0;
  pe_ratio      NUMBER;
BEGIN
  pe_ratio := stock_price / net_earnings;  -- raises ZERO_DIVIDE exception
  DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION
  WHEN others THEN
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);
END;
/

-- -1476
-- ORA-01476: divisor is equal to zero

预定义异常

DECLARE
  stock_price   NUMBER := 9.73;
  net_earnings  NUMBER := 0;
  pe_ratio      NUMBER;
BEGIN
  pe_ratio := stock_price / net_earnings;  -- raises ZERO_DIVIDE exception
  DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);
END;
/

-- -1476
-- ORA-01476: divisor is equal to zero

3 用户定义异常

3.1 用户自定义异常：sqlcode=1

DECLARE
  xxx  EXCEPTION;
BEGIN
  raise xxx;
EXCEPTION
  WHEN xxx THEN
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);
END;
/

-- 1
-- User-Defined Exception

3.2 others接收实例：sqlcode=-20222

sqlcode范围：-20000到-20999

BEGIN
  RAISE_APPLICATION_ERROR(-20222, 'raise my zero devide!');
EXCEPTION
  WHEN others THEN
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);
END;
/

-- -20222
-- ORA-20222: raise my zero devide!

3.3 名字接收实例：sqlcode=-20222

sqlcode范围：-20000到-20999

DECLARE
  zzz EXCEPTION;
  PRAGMA EXCEPTION_INIT (zzz, -20222);
BEGIN
  RAISE_APPLICATION_ERROR(-20222, 'raise my zero devide!');
EXCEPTION
  WHEN zzz THEN
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);
END;
/

-- -20222
-- ORA-20222: raise my zero devide!