Oracle PL/SQL/PL SQL/Handle Exception
Содержание
A procedure that uses a WHEN OTHERS clause
SQL> -- Here is an example of a procedure that uses a WHEN OTHERS clause:
SQL>
SQL> -- create demo table
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> CREATE OR REPLACE PROCEDURE add_new_employee
2 (employee_id_in IN NUMBER, salary_in IN NUMBER)
3 IS
4 no_salary EXCEPTION;
5
6 BEGIN
7 IF salary_in = 0 THEN
8 RAISE no_salary;
9 ELSE
10 INSERT INTO employee (id, salary ) VALUES ( employee_id_in, salary_in );
11 END IF;
12
13 EXCEPTION
14 WHEN no_salary THEN
15 raise_application_error (-20001,"You must have salary in order to insert the employee.");
16
17 WHEN OTHERS THEN
18 raise_application_error (-20002,"When other.");
19
20 END;
21 /
Procedure created.
SQL>
SQL> exec add_new_employee("99",0);
BEGIN add_new_employee("99",0); END;
*
ERROR at line 1:
ORA-20001: You must have salary in order to insert the employee.
ORA-06512: at "sqle.ADD_NEW_EMPLOYEE", line 15
ORA-06512: at line 1
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Check and output sqlcode sqlerrm
SQL>
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> declare
2 l_emp emp%rowtype;
3 begin
4 select *
5 into l_emp
6 from emp;
7 dbms_output.put_line("EMPNO: " || l_emp.empno);
8 dbms_output.put_line("ENAME: " || l_emp.ename);
9 exception
10 when others then
11 dbms_output.put("Exception encountered! (");
12 dbms_output.put_line(sqlcode || "): " || sqlerrm);
13 raise;
14 end;
15 /
Exception encountered! (-1422): ORA-01422: exact fetch returns more than requested number of rows
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 13
SQL>
SQL> drop table emp;
Table dropped.
Generic error handling to handle any type of error
SQL>
SQL> declare
2 str varchar2(50);
3 begin
4 str := "CAT";
5 exception
6 when others then
7 raise_application_error (-20100, "error#" || sqlcode || " desc: " || sqlerrm);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
noroom exception
SQL> CREATE TABLE myHotel(
2 room_id INTEGER,
3 resident_count INTEGER,
4 room_capacity INTEGER,
5 name VARCHAR2(20)
6 );
Table created.
SQL>
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(101, 20, 20, "First Room");
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(102, 19, 20, "Second Room");
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(103, 10, 20, "Third Room");
1 row created.
SQL> INSERT INTO myHotel(room_id, resident_count, room_capacity, name) VALUES(104, 0, 20, "Fourth Room");
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE addstudent (roomin IN INTEGER)
2 IS
3 roomname VARCHAR2 (20);
4 residentcount PLS_INTEGER;
5 capacity PLS_INTEGER;
6 noroom EXCEPTION;
7 BEGIN
8 SELECT resident_count, room_capacity, name
9 INTO residentcount, capacity, roomname
10 FROM myHotel
11 WHERE room_id = roomin;
12
13 IF residentcount > capacity - 1
14 THEN
15 RAISE noroom;
16 ELSE
17 UPDATE myHotel
18 SET resident_count = residentcount + 1
19 WHERE room_id = roomin;
20 COMMIT;
21 DBMS_OUTPUT.put_line ("Student count:"||residentcount||" in "|| roomname);
22 END IF;
23 EXCEPTION
24 WHEN noroom
25 THEN
26 DBMS_OUTPUT.put_line ("There is no room in " || roomname);
27 WHEN OTHERS
28 THEN
29 DBMS_OUTPUT.put_line ("Error " || SQLERRM || " occurred.");
30 END;
31 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> drop table myHotel;
Table dropped.
SQL>
SQL>
SQL>
SQL>
Use exception handler to mark success flag
SQL> CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL> DECLARE
2 success_flag BOOLEAN;
3 BEGIN
4 BEGIN
5 UPDATE emp
6 SET rate = rate * 1.10;
7 success_flag := TRUE;
8 EXCEPTION
9 WHEN OTHERS THEN
10 success_flag := false;
11 END;
12
13 IF success_flag THEN
14 COMMIT;
15 ELSE
16 ROLLBACK;
17 DBMS_OUTPUT.PUT_LINE("The UPDATE failed.");
18 RAISE_APPLICATION_ERROR (-20000,
19 "The UPDATE of emp billing rates failed.");
20 END IF;
21 END;
22 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table emp;
Table dropped.