Oracle PL/SQL/PL SQL/Predefined Exceptions
Содержание
- 1 If an ordering is applied, it occurs after the WHERE has been executed
- 2 NO_DATA_FOUND Exception
- 3 Predefined exceptions: WHEN ZERO_DIVIDE
- 4 This block illustrates the behavior of a predefined exception
- 5 This script demonstrates the scope of exceptions.
- 6 This script demonstrates user defined exceptions
If an ordering is applied, it occurs after the WHERE has been executed
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID 3 ename VARCHAR2(10 BYTE), -- Employee Name 4 hireDate DATE, -- Date Employee Hired 5 orig_salary Number(8,2), -- Orignal Salary 6 curr_salary Number(8,2), -- Current Salary 7 region VARCHAR2(1 BYTE) -- Region where employeed 8 ) 9 /
Table created. SQL> SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID 3 jobtitle VARCHAR2(10 BYTE) -- Employee job title 4 ) 5 /
Table created. SQL> -- prepare data for employee table SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000, 48000, "E") 3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, "James",to_date("19781212","YYYYMMDD"), 23000, 32000, "W") 3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,"Celia",to_date("19821024","YYYYMMDD"), 53000, 58000, "E") 3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000, 36000, "W") 3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,"Linda", to_date("19870730","YYYYMMDD"), 43000, 53000, "E") 3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,"David", to_date("19901231","YYYYMMDD"), 78000, 85000, "W") 3 /
1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,"Jode", to_date("19960917","YYYYMMDD"), 21000, 29000, "E") 3 /
1 row created. SQL> SQL> -- prepare data for job table SQL> SQL> insert into job(empno, jobtitle)
2 values(101, "Painter");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(122, "Tester");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(123, "Dediator");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(104, "Chemist");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(105, "Accountant");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(116, "Manager");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(117, "Programmer");
1 row created. SQL> SQL> insert into job(empno, jobtitle)
2 values(108, "Developer");
1 row created. SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
Hit a key to continue
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E 123 James 12-DEC-78 23000 32000 W 104 Celia 24-OCT-82 53000 58000 E 105 Robert 15-JAN-84 31000 36000 W 116 Linda 30-JUL-87 43000 53000 E 117 David 31-DEC-90 78000 85000 W 108 Jode 17-SEP-96 21000 29000 E
7 rows selected. SQL> select * from job; Hit a key to continue
EMPNO JOBTITLE
----------
101 Painter 122 Tester 123 Dediator 104 Chemist 105 Accountant 116 Manager 117 Programmer 108 Developer
8 rows selected. SQL> SQL> SQL> SQL> -- If an ordering is applied, it occurs after the WHERE has been executed: SQL> SQL> SELECT e.empno, e.ename, j.jobtitle, e.orig_salary
2 FROM employee e, job j 3 WHERE e.orig_salary < 43000 4 AND e.empno = j.empno 5 ORDER BY orig_salary desc; EMPNO ENAME JOBTITLE ORIG_SALARY
---------- ---------- -----------
105 Robert Accountant 31000 123 James Dediator 23000 108 Jode Developer 21000
SQL> SQL> SQL> SQL> SQL> drop table job; Table dropped. SQL> SQL> -- clean the table SQL> drop table Employee; Table dropped. SQL> SQL> SQL>
</source>
NO_DATA_FOUND Exception
<source lang="sql">
SQL> 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> 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 #6 PL/SQL procedure successfully completed. SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL>
</source>
Predefined exceptions: WHEN ZERO_DIVIDE
<source lang="sql">
SQL> SQL> --Predefined exceptions SQL> set serverout on; SQL> SQL> DECLARE
2 v_num1 NUMBER := 5; 3 v_num2 NUMBER := 0; 4 myResult NUMBER; 5 BEGIN 6 myResult := v_num1/v_num2; 7 DBMS_OUTPUT.PUT_LINE("The quotient is "||TO_CHAR(myResult)); 8 EXCEPTION 9 WHEN ZERO_DIVIDE THEN 10 DBMS_OUTPUT.PUT_LINE("You cannot divide by zero"); 11 END; 12 13 /
You cannot divide by zero PL/SQL procedure successfully completed. SQL>
</source>
This block illustrates the behavior of a predefined exception
<source lang="sql">
SQL> BEGIN
2 RAISE NO_DATA_FOUND; 3 END; 4 /
BEGIN
ERROR at line 1: ORA-01403: no data found ORA-06512: at line 2
SQL> SQL>
</source>
This script demonstrates the scope of exceptions.
<source lang="sql">
SQL> SQL> BEGIN
2 DECLARE 3 myException EXCEPTION; 4 BEGIN 5 RAISE myException; 6 END; 7 EXCEPTION 8 WHEN OTHERS THEN 9 RAISE; 10 END; 11 /
BEGIN
ERROR at line 1: ORA-04045: errors during recompilation/revalidation of sqle.LOG_ERRORS ORA-01031: insufficient privileges ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 9
SQL> SQL> CREATE OR REPLACE PACKAGE Globals AS
2 myException EXCEPTION; 3 END Globals; 4 /
Package created. SQL> SQL> BEGIN
2 BEGIN 3 RAISE Globals.myException; 4 END; 5 EXCEPTION 6 WHEN Globals.myException THEN 7 RAISE; 8 END; 9 /
BEGIN
ERROR at line 1: ORA-04045: errors during recompilation/revalidation of sqle.LOG_ERRORS ORA-01031: insufficient privileges ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 7
SQL>
</source>
This script demonstrates user defined exceptions
<source lang="sql">
SQL> CREATE TABLE book (
2 isbn CHAR(10) PRIMARY KEY, 3 category VARCHAR2(20), 4 title VARCHAR2(100), 5 num_pages NUMBER, 6 price NUMBER, 7 copyright NUMBER(4), 8 emp1 NUMBER, 9 emp2 NUMBER, 10 emp3 NUMBER 11 );
Table created. SQL> SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("1", "Database", "Oracle", 563, 39.99, 1999, 1, 2, 3);
1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("2", "Database", "MySQL", 765, 44.99, 1999, 4, 5);
1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("3", "Database", "SQL Server", 404, 39.99, 2001, 6, 7, 8);
1 row created. SQL> SQL> SQL> DECLARE
2 e_Duplicateemp EXCEPTION; 3 4 v_emp1 book.emp1%TYPE; 5 v_emp2 book.emp2%TYPE; 6 v_emp3 book.emp3%TYPE; 7 BEGIN 8 SELECT emp1, emp2, emp3 INTO v_emp1, v_emp2, v_emp3 FROM book WHERE title = "XML"; 9 10 IF (v_emp1 = v_emp2) OR (v_emp1 = v_emp3) OR (v_emp2 = v_emp3) THEN 11 RAISE e_Duplicateemp; 12 END IF; 13 END; 14 /
DECLARE
ERROR at line 1: ORA-01403: no data found ORA-06512: at line 8
SQL> SQL> drop table book; Table dropped.
</source>