Oracle PL/SQL/PL SQL/Exception Range — различия между версиями

Текущая версия на 09:59, 26 мая 2010

Содержание

1 Add a WHEN OTHERS handler to catch all runtime exceptions.
2 A WHEN OTHERS handler that records which runtime error occurred.
3 Exception Range in action
4 Inner exception and outter exception
5 Raising a dynamic exception without previously declaring a user-defined EXCEPTION variable:

Add a WHEN OTHERS handler to catch all runtime exceptions.

  
SQL>
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> CREATE TABLE myLogTable (
  2    code             NUMBER,
  3    message          VARCHAR2(200),
  4    info             VARCHAR2(100)
  5    );
Table created.
SQL>
SQL> DECLARE
  2    myException EXCEPTION;
  3
  4    studentCount NUMBER(3);
  5
  6    studentMax NUMBER(3);
  7  BEGIN
  8    SELECT current_lecturer, max_lecturer
  9      INTO studentCount, studentMax
 10      FROM session
 11      WHERE department = "HIS" AND course = 101;
 12
 13    IF studentCount > studentMax THEN
 14      RAISE myException;
 15    END IF;
 16  EXCEPTION
 17    WHEN myException THEN
 18      INSERT INTO myLogTable (info)
 19        VALUES ("History 101 has " || studentCount ||
 20                "lecturer: max allowed is " || studentMax);
 21    WHEN OTHERS THEN
 22      INSERT INTO myLogTable (info) VALUES ("Another error occurred");
 23  END;
 24  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from myLogTable;
no rows selected
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL> drop table session;
Table dropped.

A WHEN OTHERS handler that records which runtime error occurred.

  
SQL>
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> CREATE TABLE myLogTable (
  2    code             NUMBER,
  3    message          VARCHAR2(200),
  4    info             VARCHAR2(100)
  5    );
Table created.
SQL>
SQL> DECLARE
  2    myException EXCEPTION;
  3
  4    studentCount NUMBER(3);
  5
  6    studentMax NUMBER(3);
  7
  8    v_ErrorCode myLogTable.code%TYPE;
  9    v_ErrorText myLogTable.message%TYPE;
 10  BEGIN
 11    SELECT current_lecturer, max_lecturer
 12      INTO studentCount, studentMax
 13      FROM session
 14      WHERE department = "HIS" AND course = 101;
 15
 16    IF studentCount > studentMax THEN
 17      RAISE myException;
 18    END IF;
 19  EXCEPTION
 20    WHEN myException THEN
 21      INSERT INTO myLogTable (info)
 22        VALUES ("History 101 has " || studentCount ||
 23                "lecturer: max allowed is " || studentMax);
 24    WHEN OTHERS THEN
 25      v_ErrorCode := SQLCODE;
 26      v_ErrorText := SUBSTR(SQLERRM, 1, 200);
 27      INSERT INTO myLogTable (code, message, info) VALUES
 28        (v_ErrorCode, v_ErrorText, "Oracle error occurred");
 29  END;
 30  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> select * from myLogTable;
no rows selected
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL> drop table session;
Table dropped.

Exception Range in action

 
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> begin
  2    begin
  3      begin
  4        begin
  5          begin
  6            declare
  7              myFirstName employee.first_name%type;
  8            begin
  9              select first_name into myFirstName from employee where 1=2;
 10  --          exception
 11  --            when NO_DATA_FOUND then
 12  --              dbms_output.put_line("block #6");
 13            end;
 14          exception
 15            when NO_DATA_FOUND then
 16              dbms_output.put_line("block #5");
 17          end;
 18        exception
 19          when NO_DATA_FOUND then
 20            dbms_output.put_line("block #4");
 21        end;
 22      exception
 23        when NO_DATA_FOUND then
 24          dbms_output.put_line("block #3");
 25      end;
 26    exception
 27      when NO_DATA_FOUND then
 28        dbms_output.put_line("block #2");
 29    end;
 30  exception
 31    when NO_DATA_FOUND then
 32      dbms_output.put_line("block #1");
 33  end;
 34  /
block #5
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>

Inner exception and outter exception

 

SQL>
SQL> -- Inner exception and outter exception
SQL>
SQL>
SQL>  begin
  2      declare
  3        l_number number default "MY NUMBER";
  4      begin
  5        null;
  6      exception
  7        when OTHERS then
  8          dbms_output.put_line("Exception caught in inner block");
  9      end;
 10    exception
 11      when others then
 12        dbms_output.put_line("Exception caught in outer block");
 13        raise;
 14    end;
 15    /
Exception caught in outer block
 begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 13

SQL>

Raising a dynamic exception without previously declaring a user-defined EXCEPTION variable:

   
SQL>
SQL> BEGIN
  2    RAISE_APPLICATION_ERROR(-20001,"A not too original message.");
  3  EXCEPTION
  4    WHEN others THEN
  5      dbms_output.put_line(SQLERRM);
  6  END;
  7  /
ORA-20001: A not too original message.
PL/SQL procedure successfully completed.

Oracle PL/SQL/PL SQL/Exception Range — различия между версиями

Текущая версия на 09:59, 26 мая 2010

Содержание

Add a WHEN OTHERS handler to catch all runtime exceptions.

A WHEN OTHERS handler that records which runtime error occurred.

Exception Range in action

Inner exception and outter exception

Raising a dynamic exception without previously declaring a user-defined EXCEPTION variable:

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты