в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:02:37Z

1 версия

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

==Compare the appartment objects and display the results==



<source lang="sql">

SQL>
SQL> set echo on
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT (
2 street_1 VARCHAR2(40),
3 street_2 VARCHAR2(40),
4 city VARCHAR2(40),
5 state_abbr VARCHAR2(2),
6 zip_code VARCHAR2(5),
7 phone_number VARCHAR2(10),
8 MEMBER PROCEDURE ChangeAddress (
9 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
10 state IN VARCHAR2, zip IN VARCHAR2),
11 MEMBER FUNCTION getStreet (line_no IN number) RETURN VARCHAR2,
12 MEMBER FUNCTION getCity RETURN VARCHAR2,
13 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2,
14 MEMBER FUNCTION getPostalCode RETURN VARCHAR2,
15 MEMBER FUNCTION getPhone RETURN VARCHAR2,
16 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2)
17 );
18 /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY address AS
2 MEMBER PROCEDURE ChangeAddress (
3 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
4 state IN VARCHAR2, zip IN VARCHAR2) IS
5 BEGIN
6 IF (st_1 IS NULL) THEN
18 RAISE_application_error(-20001,"The new Address is invalid.");
19 ELSE
20 street_1 := st_1;
21 street_2 := st_2;
22 city := cty;
23 state_abbr := upper(state);
24 zip_code := zip;
25 END IF;
26 END;
27
28 MEMBER FUNCTION getStreet (line_no IN number)
29 RETURN VARCHAR2 IS
30 BEGIN
31 IF line_no = 1 THEN
32 RETURN street_1;
33 ELSIF line_no = 2 THEN
34 RETURN street_2;
35 ELSE
36 RETURN " ";
37 END IF;
38 END;
39
40 MEMBER FUNCTION getCity RETURN VARCHAR2 IS
41 BEGIN
42 RETURN city;
43 END;
44
45 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2 IS
46 BEGIN
47 RETURN state_abbr;
48 END;
49
50 MEMBER FUNCTION getPostalCode RETURN VARCHAR2 IS
51 BEGIN
52 RETURN zip_code;
53 END;
54
55 MEMBER FUNCTION getPhone RETURN VARCHAR2 IS
56 BEGIN
57 RETURN phone_number;
58 END;
59
60 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2) IS
61 BEGIN
62 phone_number := newPhone;
63 END;
64 END;
65 /
Type body created.
SQL> CREATE OR REPLACE TYPE appartment AS OBJECT (
2 BldgName VARCHAR2(40),
3 BldgAddress address,
4 BldgMgr INTEGER,
5 MEMBER PROCEDURE ChangeMgr (SELF IN OUT appartment,NewMgr IN INTEGER),
6 ORDER MEMBER FUNCTION Compare (SELF IN appartment,Otherappartment IN appartment)
7 RETURN INTEGER
8 );
9 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY appartment AS
2 MEMBER PROCEDURE ChangeMgr(SELF IN OUT appartment, NewMgr IN INTEGER) IS
3 BEGIN
4 SELF.BldgMgr := NewMgr;
5 END;
6
7 ORDER MEMBER FUNCTION Compare (SELF IN appartment,
8 Otherappartment IN appartment)
9 RETURN INTEGER IS
10 BldgName1 VARCHAR2(40);
11 BldgName2 VARCHAR2(40);
12 BEGIN
13 BldgName1 := upper(ltrim(rtrim(SELF.BldgName)));
14 BldgName2 := upper(ltrim(rtrim(Otherappartment.BldgName)));
15
16 IF BldgName1 = BldgName2 THEN
17 RETURN 0;
18 ELSIF BldgName1 < BldgName2 THEN
19 RETURN -1;
20 ELSE
21 RETURN 1;
22 END IF;
23 END;
24 END;
25 /
Type body created.
SQL> DECLARE
2 appA appartment;
3 appB appartment;
4 appB2 appartment;
5 appC appartment;
6 BEGIN
7
8 appA := appartment("An appartment",null,null);
9 appB := appartment("Another appartment",null,null);
10 appB2 := appartment("Another appartment",null,null);
11 appC := appartment("Cosmotology Research Lab",null,null);
12
13 IF appA < appB THEN
14 dbms_output.put_line("appA < appB");
15 END IF;
16
17
18 IF appB = appB2 THEN
19 dbms_output.put_line("appB = appB2");
20 END IF;
21
22 IF appC > appB2 THEN
23 dbms_output.put_line("appC > appB2");
24 END IF;
25 END;
26 /
appA < appB
appB = appB2
appC > appB2
PL/SQL procedure successfully completed.
SQL>
SQL> drop type appartment;
Type dropped.
SQL>
SQL> --
</source>

==Compare user-defined type==



<source lang="sql">

SQL>
SQL> create or replace
2 type person as object(
3 first_name varchar2(100),
4 last_name varchar2(100) )
5 /
Type created.
SQL>
SQL> alter type person
2 add attribute dob date
3 cascade not including table data
4 /
Type altered.
SQL>
SQL> create or replace
2 type employee as object(
3 name person,
4 empno number,
5 hiredate date,
6 sal number,
7 commission number,
8 order member function match ( p_employee employee ) return integer )
9 /
Type created.
SQL>
SQL> create or replace
2 type body employee as
3 order member function match ( p_employee employee ) return integer is
4 begin
5 if self.empno > p_employee.empno then
6 return 1;
7 elsif self.empno < p_employee.empno then
8 return -1;
9 else
10 return 0;
11 end if;
12 end;
13 end;
14 /
Type body created.
SQL>
SQL>
SQL> declare
2 emp1 employee;
3 emp2 employee;
4 begin
5 emp1 := employee( null, 12345, "01-JAN-01", 100, 100 );
6 emp2 := employee( null, 67890, "01-JAN-01", 100, 100 );
7 if emp1 > emp2 then
8 dbms_output.put_line( "Employee 1 is greater" );
9 end if;
10 if emp1 < emp2 then
11 dbms_output.put_line( "Employee 2 is greater" );
12 end if;
13 if emp1 = emp2 then
14 dbms_output.put_line( "Employees are equal" );
15 end if;
16 end;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop type employee;
Type dropped.
SQL>
SQL> drop type person;
Type dropped.
SQL>
SQL>
</source>

==Create user type with order==



<source lang="sql">

SQL>
SQL> create or replace
2 type person as object(
3 first_name varchar2(100),
4 last_name varchar2(100) )
5 /
Type created.
SQL>
SQL>
SQL>
SQL> alter type person
2 add attribute dob date
3 cascade not including table data
4 /
Type altered.
SQL>
SQL> create or replace
2 type employee as object(
3 name person,
4 empno number,
5 hiredate date,
6 sal number,
7 commission number,
8 order member function match ( p_employee employee ) return integer )
9 /
Type created.
SQL>
SQL> create or replace
2 type body employee as
3 order member function match ( p_employee employee ) return integer is
4 begin
5 if self.empno > p_employee.empno then
6 return 1;
7 elsif self.empno < p_employee.empno then
8 return -1;
9 else
10 return 0;
11 end if;
12 end;
13 end;
14 /
Type body created.
SQL>
SQL> drop type employee;
Type dropped.
SQL>
SQL> drop type person;
Type dropped.
SQL>
SQL>
</source>

==Define "Compare" method and use it in order by clause==



<source lang="sql">

SQL>
SQL>
SQL> set echo on
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT (
2 street_1 VARCHAR2(40),
3 street_2 VARCHAR2(40),
4 city VARCHAR2(40),
5 state_abbr VARCHAR2(2),
6 zip_code VARCHAR2(5),
7 phone_number VARCHAR2(10),
8 MEMBER PROCEDURE ChangeAddress (
9 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
10 state IN VARCHAR2, zip IN VARCHAR2),
11 MEMBER FUNCTION getStreet (line_no IN number) RETURN VARCHAR2,
12 MEMBER FUNCTION getCity RETURN VARCHAR2,
13 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2,
14 MEMBER FUNCTION getPostalCode RETURN VARCHAR2,
15 MEMBER FUNCTION getPhone RETURN VARCHAR2,
16 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2)
17 );
18 /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY address AS
2 MEMBER PROCEDURE ChangeAddress (
3 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
4 state IN VARCHAR2, zip IN VARCHAR2) IS
5 BEGIN
6 IF (st_1 IS NULL) THEN
18 RAISE_application_error(-20001,"The new Address is invalid.");
19 ELSE
20 street_1 := st_1;
21 street_2 := st_2;
22 city := cty;
23 state_abbr := upper(state);
24 zip_code := zip;
25 END IF;
26 END;
27
28 MEMBER FUNCTION getStreet (line_no IN number)
29 RETURN VARCHAR2 IS
30 BEGIN
31 IF line_no = 1 THEN
32 RETURN street_1;
33 ELSIF line_no = 2 THEN
34 RETURN street_2;
35 ELSE
36 RETURN " ";
37 END IF;
38 END;
39
40 MEMBER FUNCTION getCity RETURN VARCHAR2 IS
41 BEGIN
42 RETURN city;
43 END;
44
45 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2 IS
46 BEGIN
47 RETURN state_abbr;
48 END;
49
50 MEMBER FUNCTION getPostalCode RETURN VARCHAR2 IS
51 BEGIN
52 RETURN zip_code;
53 END;
54
55 MEMBER FUNCTION getPhone RETURN VARCHAR2 IS
56 BEGIN
57 RETURN phone_number;
58 END;
59
60 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2) IS
61 BEGIN
62 phone_number := newPhone;
63 END;
64 END;
65 /
Type body created.
SQL>
SQL> CREATE OR REPLACE TYPE appartment AS OBJECT (
2 BldgName VARCHAR2(40),
3 BldgAddress address,
4 BldgMgr INTEGER,
5 MEMBER PROCEDURE ChangeMgr (NewMgr IN INTEGER),
6 ORDER MEMBER FUNCTION Compare (Otherappartment IN appartment)
7 RETURN INTEGER
8 );
9 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY appartment AS
2 MEMBER PROCEDURE ChangeMgr(NewMgr IN INTEGER) IS
3 BEGIN
4 BldgMgr := NewMgr;
5 END;
6
7 ORDER MEMBER FUNCTION Compare (Otherappartment IN appartment)
8 RETURN INTEGER IS
9 BldgName1 VARCHAR2(40);
10 BldgName2 VARCHAR2(40);
11 BEGIN
12 BldgName1 := upper(ltrim(rtrim(BldgName)));
13 BldgName2 := upper(ltrim(rtrim(Otherappartment.BldgName)));
14
15 IF BldgName1 = BldgName2 THEN
16 RETURN 0;
17 ELSIF BldgName1 < BldgName2 THEN
18 RETURN -1;
19 ELSE
20 RETURN 1;
21 END IF;
22 END;
23 END;
24 /
Type body created.
SQL>
SQL> CREATE TABLE appartments OF appartment;
Table created.
SQL>
SQL> set echo on
SQL>
SQL> INSERT INTO appartments values (appartment("appartment",address("2 Square"," ","Lansing","MI","48823"," "),597));
1 row created.
SQL> INSERT INTO appartments values (appartment("East Shed",address("1 Rd","","Lansing","MI","48823",""),598));
1 row created.
SQL> INSERT INTO appartments values (appartment("Headquarters appartment",address("1 Jeff","","Detroit","MI","48226",""),599));
1 row created.
SQL>
SQL> SELECT * from appartments;
BLDGNAME
----------------------------------------
BLDGADDRESS(STREET_1, STREET_2, CITY, STATE_ABBR,
--------------------------------------------------
BLDGMGR
----------
appartment
ADDRESS("2 Square", " ", "Lansing", "MI", "48823",
" ")
597
East Shed
ADDRESS("1 Rd", NULL, "Lansing", "MI", "48823", NU
LL)
598
Headquarters appartment
ADDRESS("1 Jeff", NULL, "Detroit", "MI", "48226",
NULL)
599

3 rows selected.
SQL>
SQL> SELECT BldgName FROM appartments order BY BldgName;
BLDGNAME
----------------------------------------
appartment
East Shed
Headquarters appartment
3 rows selected.
SQL>
SQL> drop table appartments;
Table dropped.
SQL>
SQL> drop type appartment;
Type dropped.
SQL> --
</source>

==Raise error in object memthod==



<source lang="sql">

SQL>
SQL>
SQL> create or replace
2 type employee as object(
3 name varchar2(100),
4 empno number,
5 hiredate date,
6 vacation number,
7 final member procedure vacation( p_days number ),
8 not instantiable member procedure give_raise( p_increase number ),
9 not instantiable member function yearly_compensation return number
10 )
11 not instantiable
12 not final
13 /
Type created.
SQL>
SQL> create or replace
2 type body employee as
3 final member procedure vacation( p_days number ) is
4 begin
5 if p_days + self.vacation <= 10 then
6 self.vacation := self.vacation + p_days;
7 else
8 raise_application_error(
9 -20001,
10 "You are " || to_char(p_days + self.vacation - 10) ||
11 " days over your vacation limit." );
12 end if;
13 end;
14 end;
15 /
Type body created.
SQL>
SQL>
SQL> drop type employee;
Type dropped.
SQL>
SQL>
</source>

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

Oracle PL/SQL/Object Oriented Database/Comparable - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия