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);