使用Oracle Exception

Question

我想使用Oracle exception来处理以下代码中可能发生的错误。如果用户提供的图书ID和/或员工ID在数据库中不存在，则将引发NO_DATA_FOUND异常。因此，我如何知道是哪条语句引发了这个异常。

CREATE OR REPLACE PROCEDURE TEST_EXCEPTION
(
    book_id_in IN book.book_id%TYPE,
    emp_id_in IN emp.emp_id%TYPE
)
IS

v_book_desc book.description%TYPE;
v_emp_name emp.emp_name%TYPE;

BEGIN

SELECT description into v_book_desc FROM book WHERE book_id = book_id_in;

...

SELECT emp_name into v_emp_name FROM emp WHERE emp_id = emp_id_in;

...

EXCEPTION
     WHEN NO_DATA_FOUND THEN
         /* Do something */


END TEST_EXCEPTION;

我很感谢任何建议或指导。谢谢。

Answer 1

您可以在给定语句之前设置变量。喜欢；

CREATE OR REPLACE PROCEDURE TEST_EXCEPTION ( book_id_in IN book.book_id%TYPE, emp_id_in IN emp.emp_id%TYPE ) IS

v_book_desc book.description%TYPE; v_emp_name emp.emp_name%TYPE;
statementIndex number(1, 0);

BEGIN

statementIndex := 1;
SELECT description into v_book_desc FROM book WHERE book_id = book_id_in;

...

statementIndex := 2;
SELECT emp_name into v_emp_name FROM emp WHERE emp_id = emp_id_in;

...

EXCEPTION WHEN NO_DATA_FOUND THEN 
if statementIndex = 1
then 
/* Do something */
else
/* Do something */
endif;

END TEST_EXCEPTION;

Answer 2

有多种方法可以做到这一点：

选项1

另一个选项是创建多个begin / end块

CREATE OR REPLACE PROCEDURE TEST_EXCEPTION (
     book_id_in IN book.book_id%TYPE,
     emp_id_in IN emp.emp_id%TYPE )
IS

v_book_desc book.description%TYPE; v_emp_name emp.emp_name%TYPE;
statementIndex number(1, 0);

BEGIN

     BEGIN

     SELECT description into v_book_desc FROM book WHERE book_id = book_id_in;

     EXCEPTION WHEN NO_DATA_FOUND THEN 
      -- do your handling or raise a custom exception to be handled at end
     END; 

     BEGIN

     SELECT emp_name into v_emp_name FROM emp WHERE emp_id = emp_id_in;

      EXCEPTION WHEN NO_DATA_FOUND THEN 
      -- do your handling or raise a custom exception to be handled at end
     END; 

EXCEPTION WHEN_OTHERS THEN 

END TEST_EXCEPTION;

选项2

另一种选择是使用游标；

使用光标%NOTFOUND

有关游标的信息，请参见(http://www.unix.com.ua/orelly/oracle/langpkt/ch01_09.htm)

选项3:从Yapiskan复制

CREATE OR REPLACE PROCEDURE TEST_EXCEPTION ( book_id_in IN book.book_id%TYPE, emp_id_in IN emp.emp_id%TYPE ) IS

v_book_desc book.description%TYPE; v_emp_name emp.emp_name%TYPE;
statementIndex number(1, 0);

BEGIN

statementIndex := 1;
SELECT description into v_book_desc FROM book WHERE book_id = book_id_in;

...

statementIndex := 2;
SELECT emp_name into v_emp_name FROM emp WHERE emp_id = emp_id_in;

...

EXCEPTION WHEN NO_DATA_FOUND THEN 
if statementIndex = 1
then 
/* Do something */
else
/* Do something */
endif;

END TEST_EXCEPTION;