в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:59:22Z

1 версия

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

==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>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия