这篇文章主要讲解了“Oracle PL/SQL中EXCEPTION的用法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Oracle PL/SQL中EXCEPTION的用法”吧！
创新互联公司专注于企业营销型网站建设、网站重做改版、泽州网站定制设计、自适应品牌网站建设、html5、电子商务商城网站建设、集团公司官网建设、外贸网站制作、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为泽州等各大城市提供网站开发制作服务。
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的用法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
网站名称：OraclePL/SQL中EXCEPTION的用法
网站路径：
http://www.cdkjz.cn/article/pdshjp.html
