Oracle PL/SQL/PL SQL/Raise
Содержание
- 1 Print out an error message
- 2 RAISE_APPLICATION_ERROR
- 3 Raise different exception depends on value input
- 4 Raise exceptions for wrong parameters
- 5 Raises and manages a standard anonymous block PL/SQL program error.
- 6 Raise your own exception
- 7 Raising an exception
- 8 Raising NO_DATA_FOUND Exception
- 9 Setting the message dynamically
- 10 This procedure demonstrates the use of RAISE_APPLICATION_ERROR.
Print out an error message
<source lang="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>
</source>
RAISE_APPLICATION_ERROR
<source lang="sql">
SQL> SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL, 3 department CHAR(3) NOT NULL, 4 course NUMBER(3) NOT NULL, 5 grade CHAR(1) 6 );
Table created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created. SQL> CREATE OR REPLACE FUNCTION AverageGrade (
2 p_Department IN myStudent.department%TYPE, 3 p_Course IN myStudent.course%TYPE) RETURN CHAR AS 4 5 v_AverageGrade CHAR(1); 6 v_NumericGrade NUMBER; 7 v_Numberlecturer NUMBER; 8 9 CURSOR c_Grades IS 10 SELECT grade 11 FROM myStudent 12 WHERE department = p_Department 13 AND course = p_Course; 14 BEGIN 15 SELECT COUNT(*) 16 INTO v_Numberlecturer 17 FROM myStudent 18 WHERE department = p_Department 19 AND course = p_Course; 20 21 IF v_Numberlecturer = 0 THEN 22 RAISE_APPLICATION_ERROR(-20001, "No lecturer registered for " || 23 p_Department || " " || p_Course); 24 END IF; 25 26 SELECT AVG(DECODE(grade, "A", 5, 27 "B", 4, 28 "C", 3, 29 "D", 2, 30 "E", 1)) 31 INTO v_NumericGrade 32 FROM myStudent 33 WHERE department = p_Department 34 AND course = p_Course; 35 36 SELECT DECODE(ROUND(v_NumericGrade), 5, "A", 37 4, "B", 38 3, "C", 39 2, "D", 40 1, "E") 41 INTO v_AverageGrade 42 FROM dual; 43 44 RETURN v_AverageGrade; 45 END AverageGrade; 46 /
Function created. SQL> SQL> drop table myStudent; Table dropped. SQL>
</source>
Raise different exception depends on value input
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PROCEDURE RaiseIt(p_Exception IN NUMBER) AS
2 e_MyException EXCEPTION; 3 BEGIN 4 IF p_Exception = 0 THEN 5 NULL; 6 ELSIF p_Exception < 0 THEN 7 RAISE e_MyException; 8 ELSIF p_Exception = 1001 THEN 9 RAISE INVALID_CURSOR; 10 ELSIF p_Exception = 1403 THEN 11 RAISE NO_DATA_FOUND; 12 ELSIF p_Exception = 6502 THEN 13 RAISE VALUE_ERROR; 14 ELSE 15 RAISE_APPLICATION_ERROR(-20001, "Exception " || p_Exception); 16 END IF; 17 END RaiseIt; 18 /
Procedure created. SQL> show errors No errors. SQL>
</source>
Raise exceptions for wrong parameters
<source lang="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,"An error has occurred inserting an employee."); 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> SQL>
</source>
Raises and manages a standard anonymous block PL/SQL program error.
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> SQL> DECLARE
2 3 4 my_error EXCEPTION; 5 6 BEGIN 7 8 9 RAISE my_error; 10 11 EXCEPTION 12 13 WHEN others THEN 14 dbms_output.put_line("RAISE my_error"||CHR(10)||"SQLCODE ["||SQLCODE||"]"||CHR(10)||"SQLERRM ["||SQLERRM||"]"); 15 16 END; 17 /
RAISE my_error SQLCODE [1] SQLERRM [User-Defined Exception] PL/SQL procedure successfully completed. SQL>
</source>
Raise your own exception
<source lang="sql">
SQL> SQL> SQL> set echo off SQL> set verify off SQL> set define "&" SQL> SQL> prompt "degrees F?:" "degrees F?:" SQL> accept temp default "100" SQL> declare
2 porridge_too_hot exception; 3 porridge_too_cold exception; 4 begin 5 case 6 when "&temp" < 90.00 then raise porridge_too_cold; 7 when "&temp" > 140.00 then raise porridge_too_hot; 8 else null; 9 end case; 10 11 dbms_output.put_line("just right"); 12 13 exception 14 when VALUE_ERROR then 15 dbms_output.put_line("Please enter a numeric temperature (like 100)"); 16 17 when porridge_too_hot then 18 dbms_output.put_line("way too hot..."); 19 20 when porridge_too_cold then 21 dbms_output.put_line("way too cold..."); 22 end; 23 /
just right PL/SQL procedure successfully completed. SQL>
</source>
Raising an exception
<source lang="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> SQL> DECLARE
2 myException EXCEPTION; 3 studentCount NUMBER(3); 4 studentMax NUMBER(3); 5 BEGIN 6 SELECT current_lecturer, max_lecturer 7 INTO studentCount, studentMax 8 FROM session 9 WHERE department = "HIS" AND course = 101; 10 IF studentCount > studentMax THEN 11 RAISE myException; 12 END IF; 13 END; 14 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table session; Table dropped. SQL> SQL>
</source>
Raising NO_DATA_FOUND Exception
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE t_NumberTableType IS TABLE OF NUMBER 3 INDEX BY BINARY_INTEGER; 4 v_NumberTable t_NumberTableType; 5 v_TempVar NUMBER; 6 BEGIN 7 v_TempVar := v_NumberTable(1); 8 END; 9 /
DECLARE
ERROR at line 1: ORA-01403: no data found ORA-06512: at line 7
SQL>
</source>
Setting the message dynamically
<source lang="sql">
SQL> SQL> DECLARE
2 e EXCEPTION; 3 PRAGMA EXCEPTION_INIT(e,-20001); 4 BEGIN 5 RAISE_APPLICATION_ERROR(-20001,"A less than original message."); 6 EXCEPTION 7 WHEN e THEN 8 dbms_output.put_line(SQLERRM); 9 END; 10 /
ORA-20001: A less than original message. PL/SQL procedure successfully completed. SQL>
</source>
This procedure demonstrates the use of RAISE_APPLICATION_ERROR.
<source lang="sql">
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 );
Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H"); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z"); 1 row created. 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> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("4", "Database", "SQL", 535, 39.99, 2002, 4, 5, 9);
1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("5", "Database", "Java", 487, 39.99, 2002, 10, 11);
1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ("6", "Database", "JDBC", 592, 39.99, 2002, 12, 13);
1 row created. SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ("7", "Database", "XML", 500, 39.99, 2002, 1, 2, 3);
1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE Verifyemp(p_emp1 IN book.emp1%TYPE,p_emp2 IN book.emp2%TYPE,p_emp3 IN book.emp3%TYPE) AS
2 3 v_empCount NUMBER; 4 BEGIN 5 IF p_emp1 IS NULL THEN 6 RAISE_APPLICATION_ERROR(-20000, "emp1 cannot be null"); 7 ELSE 8 SELECT COUNT(*) INTO v_empCount FROM emp WHERE id = p_emp1; 9 IF v_empCount = 0 THEN 10 RAISE_APPLICATION_ERROR(-20001,"emp1 " || p_emp1 || " does not exist"); 11 END IF; 12 END IF; 13 14 IF p_emp1 = p_emp2 THEN 15 RAISE_APPLICATION_ERROR (-20002,"emp1 " || p_emp1 || " and emp2 " || p_emp2 ||" are duplicates"); 16 ELSIF p_emp1 = p_emp3 THEN 17 RAISE_APPLICATION_ERROR (-20002,"emp1 " || p_emp1 || " and emp3 " || p_emp3 ||" are duplicates"); 18 ELSIF p_emp2 = p_emp3 THEN 19 RAISE_APPLICATION_ERROR (-20002,"emp2 " || p_emp2 || " and emp3 " || p_emp3 ||" are duplicates"); 20 END IF; 21 END Verifyemp; 22 /
Procedure created.
SQL> SQL> drop table book; Table dropped. SQL> drop table emp; Table dropped. SQL>
</source>