Oracle PL/SQL中EXCEPTION的用法

共计 3817 个字符，预计需要花费 10 分钟才能阅读完成。

这篇文章主要讲解了“Oracle PL/SQL 中 EXCEPTION 的用法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“Oracle PL/SQL 中 EXCEPTION 的用法”吧！

1. 自定義 EXCEPTION

DECLARE

  past_due EXCEPTION;

  acct_num NUMBER := 2;

BEGIN

  DECLARE 
———-
sub-block begins

  past_due
EXCEPTION; 
this declaration prevails

  acct_num
NUMBER :=3;

  due_date
DATE := SYSDATE – 1;

 
todays_date DATE := SYSDATE;

  BEGIN

  IF due_date
todays_date THEN

  RAISE past_due; 
this is not handled

  END IF;

  EXCEPTION

  WHEN past_due THEN 
does not handle raised EXCEPTION

 
DBMS_OUTPUT.PUT_LINE(Handling
PAST_DUE exception. ||acct_num);

  WHEN OTHERS THEN

 
DBMS_OUTPUT.PUT_LINE(Could not
recognize PAST_DUE_EXCEPTION in this scope. ||acct_num); 

  END; 
————-
sub-block ends 

EXCEPTION

  WHEN past_due THEN 
does not handle raised exception

 
DBMS_OUTPUT.PUT_LINE(Handling
PAST_DUE exception. ||acct_num);

  WHEN OTHERS THEN

 
DBMS_OUTPUT.PUT_LINE(Could not
recognize PAST_DUE_EXCEPTION in this scope. ||acct_num);

END;

DECLARE

 
out_of_stock  EXCEPTION;

 
number_on_hand NUMBER := 0;

BEGIN

  IF
number_on_hand 1 THEN

  RAISE
out_of_stock;
— raise
an exception that we defined

  END IF;

EXCEPTION

  WHEN
out_of_stock THEN

 
— handle the error

 
DBMS_OUTPUT.PUT_LINE(Encountered
out-of-stock error.

END;

2. 使用 oracle 自帶的 error 返回

DECLARE

  acct_type INTEGER := 7;

BEGIN

  IF acct_type
NOT IN (1, 2, 3) THEN

  RAISE INVALID_NUMBER; 
raise predefined exception

  END IF;

EXCEPTION

  WHEN INVALID_NUMBER THEN

 
DBMS_OUTPUT.PUT_LINE(HANDLING
INVALID INPUT BY ROLLING BACK.

  ROLLBACK;

END;

3.Retrieving the Error Code and
Error Message: SQLCODE and SQLERRM

CREATE TABLE errors (code NUMBER, message VARCHAR2(64), happened TIMESTAMP);

DECLARE

  names employee.name%TYPE;

  v_code NUMBER;

  v_errm VARCHAR2(64);

BEGIN

  SELECT name INTO names FROM employee WHERE id = -1;

  EXCEPTION

  WHEN OTHERS THEN

 
v_code := SQLCODE;

 
v_errm := SUBSTR(SQLERRM, 1 , 64);

 
DBMS_OUTPUT.PUT_LINE(Error
code || v_code || : || v_errm);

— Normally we would call another procedure,
declared with PRAGMA

— AUTONOMOUS_TRANSACTION, to insert information
about errors.

  INSERT INTO errors VALUES (v_code, v_errm, SYSTIMESTAMP);

  commit;

END;

4. raise_application_error

DECLARE

  num_tables
NUMBER;

BEGIN

  SELECT COUNT(*) INTO
num_tables FROM USER_TABLES;

  IF
num_tables 1000 THEN

 
/* Issue your own error code (ORA-20101) with your
own error message.

  Note
that you do not need to qualify raise_application_error with

 
DBMS_STANDARD */

 
raise_application_error(-20101, Expecting
at least 1000 tables

  ELSE

  NULL;
— Do the rest of the processing (for the non-error
case).

  END IF;

END;

5. 指定 PRAGMA EXCEPTION_INIT(COMPILATION_ERROR,ERROR_CODE)

CREATE OR REPLACE PROCEDURE
SFIS1.execute_immediate(
p_sql_text VARCHAR2 ) IS

 
COMPILATION_ERROR EXCEPTION;

  PRAGMA EXCEPTION_INIT(COMPILATION_ERROR,-24344);

  l_cursor INTEGER DEFAULT 0;

  rc  INTEGER DEFAULT 0;

  stmt  VARCHAR2(1000);

BEGIN

  l_cursor :=
DBMS_SQL.OPEN_CURSOR;

 
DBMS_SQL.PARSE(l_cursor,
p_sql_text,
DBMS_SQL.NATIVE);

  rc :=
DBMS_SQL.EXECUTE(l_cursor);

 
DBMS_SQL.CLOSE_CURSOR(l_cursor);

—

— Ignore compilation errors because these
sometimes happen due to

— dependencies between views AND procedures

—

EXCEPTION

WHEN
COMPILATION_ERROR THEN

 
DBMS_SQL.CLOSE_CURSOR(l_cursor);

WHEN OTHERS THEN

  BEGIN

 
DBMS_SQL.CLOSE_CURSOR(l_cursor);

 
raise_application_error(-20101,sqlerrm ||   when executing || p_sql_text ||  

  END;

END;

CREATE UNIQUE INDEX GC.EMP_NO_ ON GC.EMP(EMP_NAME)

DECLARE

 
EMP_NAME_UNIQUE EXCEPTION;

  PRAGMA EXCEPTION_INIT(EMP_NAME_UNIQUE, -00001);

BEGIN

  INSERT INTO GC.EMP SELECT * FROM GC.EMP;

EXCEPTION

  WHEN
EMP_NAME_UNIQUE THEN

 
DBMS_OUTPUT.PUT_LINE(違反一致性

  WHEN OTHERS THEN

 
DBMS_OUTPUT.PUT_LINE(SQLCODE|| — ||SQLERRM);

END;

6.DBMS_UTILITY.format_error_backtrace,DBMS_UTILITY.format_error_stack 返回错误行和错误

DECLARE
  V_TABLE_NAME  VARCHAR2 (500);
BEGIN
  SELECT TABLE_NAME INTO V_TABLE_NAME FROM DBA_TABLES;
EXCEPTION
  WHEN OTHERS
  THEN
  DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace|| — || DBMS_UTILITY.format_error_stack);
      –DBMS_OUTPUT.put_line (error line: || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE () || SQLCODE: || SQLCODE||   SQLERRM: || SQLERRM);
END;

感谢各位的阅读，以上就是“Oracle PL/SQL 中 EXCEPTION 的用法”的内容了，经过本文的学习后，相信大家对 Oracle PL/SQL 中 EXCEPTION 的用法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！