OraclePL/SQL中EXCEPTION的用法

这篇文章主要讲解了“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的用法
文章URL:https://www.cdcxhl.com/article28/pdshjp.html

成都网站建设公司_创新互联,为您提供关键词优化建站公司企业建站网站收录品牌网站制作商城网站

广告

声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联

成都做网站