Oracle PL/SQL中EXCEPTION的用法
这篇文章主要讲解了"Oracle PL/SQL中EXCEPTION的用法"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"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的用法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！
