共计 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 小编将为大家推送更多相关知识点的文章，欢迎关注！
