Oracle PL/SQL Tutorial/PL SQL Programming/Your Exception

Материал из SQL эксперт
Перейти к: навигация, поиск

An example of using PRAGMA EXCEPTION_INIT

   <source lang="sql">

SQL> SQL> create table org_level(

 2    company_id number(8)      not null,
 3    org_level varchar2(1) not null
 4  );

Table created. SQL> SQL> DECLARE

 2    invalid_org_level EXCEPTION;
 3    PRAGMA EXCEPTION_INIT(invalid_org_level,-2290);
 4  BEGIN
 5    INSERT INTO org_level VALUES (1001,"P");
 6    COMMIT;
 7  EXCEPTION WHEN invalid_org_level THEN
 8    dbms_output.put_line("Organization Level");
 9  END;
10  /

PL/SQL procedure successfully completed. SQL> SQL> drop table org_level; Table dropped. SQL></source>


An example showing handling of pre-defined exceptions

   <source lang="sql">

SQL> SQL> create table product(

 2     product_id number(4)     not null,
 3     product_description varchar2(20) not null
 4  );

Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> DECLARE

 2    v_descr VARCHAR2(20);
 3  BEGIN
 4    SELECT product_description
 5    INTO v_descr
 6    FROM product
 7    WHERE product_id =10;
 8    dbms_output.put_line(v_descr);
 9  EXCEPTION WHEN NO_DATA_FOUND THEN
10    dbms_output.put_line("ERR:Invalid Hierarchy Code 10");
11  END;
12  /

ERR:Invalid Hierarchy Code 10 PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table product; Table dropped.</source>


Assign custom exception a number

   <source lang="sql">

SQL> SQL> DECLARE

 2     bad_date EXCEPTION;
 3     PRAGMA EXCEPTION_INIT (bad_date, -1843);
 4  BEGIN
 5     DBMS_OUTPUT.PUT_LINE (TO_DATE ("13/13/99", "MM/DD/YY"));
 6  EXCEPTION
 7    WHEN bad_date
 8    THEN
 9      DBMS_OUTPUT.PUT_LINE("Just twelve months in a year...");
10  END;
11  /

Just twelve months in a year... PL/SQL procedure successfully completed.</source>


Catch "cannot get lock exception"

   <source lang="sql">

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> SQL> DECLARE

 2    no_lock EXCEPTION;
 3    pragma EXCEPTION_INIT(no_lock, -00054);
 4  BEGIN
 5    FOR my_rec IN (SELECT * from EMP FOR UPDATE NOWAIT)
 6    LOOP
 7      DBMS_OUTPUT.PUT_LINE ("Employee: " || my_rec.ename);
 8    END LOOP;
 9  EXCEPTION
10    WHEN no_lock THEN
11      DBMS_OUTPUT.PUT_LINE ("Could not get lock.  Try again later");
12  END;
13  /

Employee: SMITH Employee: ALLEN Employee: WARD Employee: JONES Employee: MARTIN Employee: BLAKE Employee: CLARK Employee: SCOTT Employee: KING Employee: TURNER Employee: ADAMS Employee: JAMES Employee: FORD Employee: MILLER PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped. SQL></source>


Create your own no_data_found EXCEPTION

   <source lang="sql">

SQL> SQL> SQL> DECLARE

 2     d VARCHAR2(1);
 3
 4     no_data_found EXCEPTION;
 5
 6  BEGIN
 7
 8     SELECT "dummy" INTO d FROM dual WHERE 1=2;
 9
10     IF d IS NULL
11
12     THEN
13
14        RAISE no_data_found;
15
16     END IF;
17
18  EXCEPTION
19
20     WHEN no_data_found
21
22     THEN
23
24        DBMS_OUTPUT.PUT_LINE ("Trapped the error!?");
25  END;
26  /

DECLARE

ERROR at line 1: ORA-01403: no data found ORA-06512: at line 8

SQL></source>


Handling user-defined exceptions with a WHEN clause

   <source lang="sql">

SQL> SQL> SQL> create table company(

 2     product_id        number(4)    not null,
 3     company_id          NUMBER(8)    not null,
 4     company_short_name  varchar2(30) not null,
 5     company_long_name   varchar2(60)
 6  );

Table created. SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc."); 1 row created. SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc."); 1 row created. SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc."); 1 row created. SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc."); 1 row created. SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc."); 1 row created. SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc."); 1 row created. SQL> SQL> SQL> create table org_company_site(

 2     company_id number(8) not null,
 3     site_no number(4) not null
 4  );

Table created. SQL> insert into org_company_site values (1001,1); 1 row created. SQL> insert into org_company_site values (1002,2); 1 row created. SQL> insert into org_company_site values (1003,3); 1 row created. SQL> insert into org_company_site values (1004,1); 1 row created. SQL> insert into org_company_site values (1004,2); 1 row created. SQL> insert into org_company_site values (1004,3); 1 row created. SQL> insert into org_company_site values (1005,1); 1 row created. SQL> insert into org_company_site values (1005,4); 1 row created. SQL> insert into org_company_site values (1005,5); 1 row created. SQL> insert into org_company_site values (1006,1); 1 row created. SQL> SQL> SQL> BEGIN

 2  insert into company values (3,1007,"O Inc.","O Inc.");
 3  COMMIT;
 4  END;
 5  /

PL/SQL procedure successfully completed. SQL> DECLARE

 2    sites_undefined_for_org EXCEPTION;
 3    v_cnt NUMBER;
 4  BEGIN
 5    SELECT COUNT(*)
 6    INTO v_cnt
 7    FROM org_company_site
 8    WHERE company_id =1007;
 9    IF (v_cnt=0)THEN
10      --explicitly raising the user-defined exception
11      RAISE sites_undefined_for_org;
12    END IF;
13  EXCEPTION
14    --handling the raised user-defined exception
15    WHEN sites_undefined_for_org THEN
16      dbms_output.put_line("There are no sites defined for organization 1007");
17    WHEN OTHERS THEN
18      dbms_output.put_line("ERR:An error occurred with info :"||
19      TO_CHAR(SQLCODE)||" "||SQLERRM);
20  END;
21  /

There are no sites defined for organization 1007 PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table company; Table dropped. SQL> SQL> drop table org_company_site; Table dropped. SQL></source>