в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:27Z

1 версия

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

== Because OTHERS handles all exceptions, you must list it after any specific exceptions in your EXCEPTION block.==



<source lang="sql">
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(1 / 0);
3 EXCEPTION
4 WHEN OTHERS THEN
5 DBMS_OUTPUT.PUT_LINE("An exception occurred");
6 WHEN ZERO_DIVIDE THEN
7 DBMS_OUTPUT.PUT_LINE("Division by zero");
8 END;
9 /
WHEN OTHERS THEN
*
ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

SQL></source>

== Catch "divide by zero" exception==



<source lang="sql">
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 Num_a NUMBER := 6;
3 Num_b NUMBER;
4 BEGIN
5 Num_b := 0;
6 Num_a := Num_a / Num_b;
7 Num_b := 7;
8 dbms_output.put_line(" Value of Num_b " || Num_b);
9 EXCEPTION
10 WHEN ZERO_DIVIDE
11 THEN
12 dbms_output.put_line("Trying to divide by zero");
13 dbms_output.put_line(" Value of Num_a " || Num_a);
14 dbms_output.put_line(" Value of Num_b " || Num_b);
15 END;
16 /
Trying to divide by zero
Value of Num_a 6
Value of Num_b 0
PL/SQL procedure successfully completed.
SQL></source>

== Data not found exception==



<source lang="sql">
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
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> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> DECLARE
2 v_employee EMPLOYEE.FIRST_NAME%TYPE;
3 BEGIN
4
5 -- the first nested block
6 BEGIN
7 SELECT first_name
8 INTO v_employee
9 FROM EMPLOYEE
10 WHERE UPPER(last_name) = "CAT";
11 EXCEPTION
12 WHEN NO_DATA_FOUND
13 THEN
14 DBMS_OUTPUT.PUT_LINE("EXCEPTION HANDLER for nested block 1");
15 DBMS_OUTPUT.PUT_LINE(" ");
16 NULL;
17 END;
18
19 -- the second nested block
20 BEGIN
21 SELECT first_name
22 INTO v_employee
23 FROM EMPLOYEE
24 WHERE UPPER(last_name) = "HARDMAN";
25
26 EXCEPTION
27 WHEN TOO_MANY_ROWS
28 THEN
29 DBMS_OUTPUT.PUT_LINE(" ");
30 DBMS_OUTPUT.PUT_LINE("EXCEPTION HANDLER for nested block 2");
31 DBMS_OUTPUT.PUT_LINE("If this is printing, then the both nested");
32 DBMS_OUTPUT.PUT_LINE("blocks"" exception handler worked!");
33 END;
34 END;
35 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 21

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.</source>

== Declare variables in exception handler==



<source lang="sql">
SQL> set serveroutput on
SQL> DECLARE
2 Num_a NUMBER := 6;
3 Num_b NUMBER;
4 BEGIN
5 Num_b := 0;
6 Num_a := Num_a / Num_b;
7 Num_b := 7;
8 dbms_output.put_line(" Value of Num_b " || Num_b);
9 EXCEPTION
10 WHEN ZERO_DIVIDE THEN
11 DECLARE
12 err_num NUMBER := SQLCODE;
13 err_msg VARCHAR2(512) := SQLERRM;
14 BEGIN
15 dbms_output.put_line("ORA Error Number " || err_num );
16 dbms_output.put_line("ORA Error message " || err_msg);
17 dbms_output.put_line(" Value of Num_a is " || Num_a);
18 dbms_output.put_line(" Value of Num_b is " || Num_b);
19 END;
20 END;
21 /
ORA Error Number -1476
ORA Error message ORA-01476: divisor is equal to zero
Value of Num_a is 6
Value of Num_b is 0
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== DUP_VAL_ON_INDEX Exception==

The DUP_VAL_ON_INDEX exception is raised when an attempt is made to store duplicate values in a column that is constrained by a unique index.



<source lang="sql">
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
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> BEGIN
2 INSERT INTO employee (id)VALUES(1);
3 EXCEPTION
4 WHEN DUP_VAL_ON_INDEX THEN
5 DBMS_OUTPUT.PUT_LINE("Duplicate value on an index");
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL></source>

== Exception Handling==

The Syntax for Exceptions



<source lang="sql">
EXCEPTION
WHEN OTHERS THEN
<statements></source>

statements is one or more statements that will be processed when the exception occurs.
You could always code a NULL statement if no action is to be taken.

== Exceptions==

In PL/SQL, the user can catch certain runtime errors.
Exceptions can be internally defined by Oracle or the user.
Exceptions are used to handle errors that occur in your PL/SQL code.
A PL/SQL block contains an EXCEPTION block to handle exception.
There are three types of exceptions:
<OL><LI>Predefined Oracle errors</LI><LI>Undefined Oracle errors</LI><LI>User-defined errors</LI></OL>
The different parts of the exception.
<OL><LI>Declare the exception.</LI><LI>Raise an exception.</LI><LI>Handle the exception.</LI></OL>
An exception has four attributes:
<OL><LI>Name provides a short description of the problem.</LI><LI>Type identifies the area of the error.</LI><LI>Exception Code gives a numeric representation of the exception.</LI><LI>Error message provides additional information about the exception.</LI></OL>
The predefined divide-by-zero exception has the following values for the attributes:
<OL><LI>Name = ZERO_DIVIDE</LI><LI>Type = ORA (from the Oracle engine)</LI><LI>Exception Code = C01476</LI><LI>Error message = divisor is equal to zero</LI></OL>
The following is the typical syntax of an exception-handling PL/SQL block.



<source lang="sql">
exception
when exception_1 THEN
statements
when exception_2 THEN
statements
...</source>

exception_1 and exception_2 are the names of the predefined exceptions.
statements is the PL/SQL code that will be executed if the exception name is satisfied.

== INVALID_NUMBER Exception==

The INVALID_NUMBER exception is raised when an attempt is made to convert an invalid character string into a number.



<source lang="sql">
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE),
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> BEGIN
2 INSERT INTO employee(salary) VALUES("123X");
3 EXCEPTION
4 WHEN INVALID_NUMBER THEN
5 DBMS_OUTPUT.PUT_LINE("Conversion of string to number failed");
6 END;
7 /
Conversion of string to number failed
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL></source>

== OTHERS Exception==

You can use the OTHERS exception to handle all exceptions.



<source lang="sql">
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(1 / 0);
3 EXCEPTION
4 WHEN OTHERS THEN
5 DBMS_OUTPUT.PUT_LINE("An exception occurred");
6 END;
7 /
An exception occurred
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== The NULL Statement==

The NULL statement is simply a statement that does nothing.
Its format is simply



<source lang="sql">
NULL;</source>

== Types of more commonly used Exceptions==

The following are the more commonly used predefined exceptions:
no_data_found
Singleton SELECT statement returned no data.
too_many_rows
Singleton SELECT statement returned more than one row of data.
invalid_cursor
Illegal cursor operation occurred.
value_error
Arithmetic, conversion, or truncation error occurred.
invalid_number
Conversion of a number to a character string failed.
zero_divide
Attempted to divide by zero.
dup_val_on_index
Attempted to insert a duplicate value into a column that has a unique index.
cursor_already_open
Attempted to open a cursor that was previously opened.
not_logged_on
A database call was made without being logged into Oracle.
transaction_backed_out
Usually raised when a remote portion of a transaction is rolled back.
login_denied
Login to Oracle failed.
program_error
If PL/SQL encounters an internal problem.
storage_error
If PL/SQL runs out of memory or if memory is corrupted.
timeout_on_resource
Timeout occurred while Oracle was waiting for a resource.
others
For all of the rest.

== Understanding Different Exception Types==

Identifying Exception Types
Error Code Prefix
Indicates This Exception Type of Error
ORA
Core RDBMS errors
PLS
PL/SQL errors
FRM
Oracle Forms errors
REP
Oracle Reports errors
Oracle function SQLCODE returns the type and code of the most recently raised exception.
Exception names are usually associated with exceptions that you create yourself.
The exception message is a text string that describes the exception.
The function SQLERRM returns the text of the error message for the most recently raised exception.

== When the exception occurs, program control passes to the EXCEPTION block where the WHEN clause is examined for a matching exception.==

If no matching exception is found, the exception is propagated to the enclosing block.



<source lang="sql">
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(1 / 0);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 2</source>

== ZERO_DIVIDE Exception==

The ZERO_DIVIDE exception is raised when an attempt is made to divide a number by zero.



<source lang="sql">
BEGIN
2 DBMS_OUTPUT.PUT_LINE(1 / 0);
3 EXCEPTION
4 WHEN ZERO_DIVIDE THEN
5 DBMS_OUTPUT.PUT_LINE("Division by zero");
6 END;
7 /
Division by zero
PL/SQL procedure successfully completed.
SQL>
SQL></source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия