в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:59:01Z

1 версия

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

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

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

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

в 13:45, 26 мая 2010

Admin: 1 версия