в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:28Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/PL SQL Programming/Your Exception - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия