Oracle PL/SQL Tutorial/PL SQL Programming/Your Exception
Содержание
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>