本章内容:
1. 对象类型
2. 对象类型的方法
代码如下:
1. 创建类型规范
SQL> -- For Example ch23_8a.sql
SQL> CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT
2 (zip VARCHAR2(5)
3 ,city VARCHAR2(25)
4 ,state VARCHAR2(2)
5 ,created_by VARCHAR2(30)
6 ,created_date DATE
7 ,modified_by VARCHAR2(30)
8 ,modified_date DATE
9
10 ,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
11 ,zip VARCHAR2)
12 RETURN SELF AS RESULT
13
14 ,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
15 ,zip VARCHAR2
16 ,city VARCHAR2
17 ,state VARCHAR2)
18 RETURN SELF AS RESULT);
19 /
Type created.
2. 创建对象体
SQL> CREATE OR REPLACE TYPE BODY zipcode_obj_type AS
2
3 CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
4 ,zip VARCHAR2)
5 RETURN SELF AS RESULT
6 IS
7 BEGIN
8 SELF.zip := zip;
9
10 SELECT city, state
11 INTO SELF.city, SELF.state
12 FROM zipcode
13 WHERE zip = SELF.zip;
14
15 RETURN;
16 EXCEPTION
17 WHEN NO_DATA_FOUND THEN
18 RETURN;
19 END;
20
21 CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
22 ,zip VARCHAR2
23 ,city VARCHAR2
24 ,state VARCHAR2)
25 RETURN SELF AS RESULT
26 IS
27 BEGIN
28 SELF.zip := zip;
29 SELF.city := city;
30 SELF.state := state;
31
32 RETURN;
33 END;
34 END;
35 /
Type body created.
3. 成员方法
SQL> -- For Example ch23_8b.sql
SQL> CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT
2 (zip VARCHAR2(5)
3 ,city VARCHAR2(25)
4 ,state VARCHAR2(2)
5 ,created_by VARCHAR2(30)
6 ,created_date DATE
7 ,modified_by VARCHAR2(30)
8 ,modified_date DATE
9
10 ,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
11 ,zip VARCHAR2)
12 RETURN SELF AS RESULT
13
14 ,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
15 ,zip VARCHAR2
16 ,city VARCHAR2
17 ,state VARCHAR2)
18 RETURN SELF AS RESULT
19
20 ,MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2
21 ,out_city OUT VARCHAR2
22 ,out_state OUT VARCHAR2));
23 /
Type created.
4. 静态方法
SQL> -- For Example ch23_8c.sql
SQL> CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT
2 (zip VARCHAR2(5)
3 ,city VARCHAR2(25)
4 ,state VARCHAR2(2)
5 ,created_by VARCHAR2(30)
6 ,created_date DATE
7 ,modified_by VARCHAR2(30)
8 ,modified_date DATE
9
10 ,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
11 ,zip VARCHAR2)
12 RETURN SELF AS RESULT
13
14 ,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
15 ,zip VARCHAR2
16 ,city VARCHAR2
17 ,state VARCHAR2)
18 RETURN SELF AS RESULT
19
20 ,MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2
21 ,out_city OUT VARCHAR2
22 ,out_state OUT VARCHAR2)
23
24 ,STATIC PROCEDURE display_zipcode_info (in_zip_obj IN zipcode_obj_type));
25 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY zipcode_obj_type AS
2
3 CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
4 ,zip VARCHAR2)
5 RETURN SELF AS RESULT
6 IS
7 BEGIN
8 SELF.zip := zip;
9
10 SELECT city, state
11 INTO SELF.city, SELF.state
12 FROM zipcode
13 WHERE zip = SELF.zip;
14
15 RETURN;
16 EXCEPTION
17 WHEN NO_DATA_FOUND THEN
18 RETURN;
19 END;
20
21 CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
22 ,zip VARCHAR2
23 ,city VARCHAR2
24 ,state VARCHAR2)
25 RETURN SELF AS RESULT
26 IS
27 BEGIN
28 SELF.zip := zip;
29 SELF.city := city;
30 SELF.state := state;
31
32 RETURN;
33 END;
34
35 MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2
36 ,out_city OUT VARCHAR2
37 ,out_state OUT VARCHAR2)
38 IS
39 BEGIN
40 out_zip := SELF.zip;
41 out_city := SELF.city;
42 out_state := SELF.state;
43 END;
44
45 STATIC PROCEDURE display_zipcode_info (in_zip_obj IN zipcode_obj_type)
46 IS
47 BEGIN
48 DBMS_OUTPUT.PUT_LINE ('Zip: ' ||in_zip_obj.zip);
49 DBMS_OUTPUT.PUT_LINE ('City: ' ||in_zip_obj.city);
50 DBMS_OUTPUT.PUT_LINE ('State: '||in_zip_obj.state);
51 END;
52 END;
53 /
5. 比较对象
1) 映射方法
SQL> -- For Example ch23_8d.sql
SQL> CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT
2 (zip VARCHAR2(5)
3 ,city VARCHAR2(25)
4 ,state VARCHAR2(2)
5 ,created_by VARCHAR2(30)
6 ,created_date DATE
7 ,modified_by VARCHAR2(30)
8 ,modified_date DATE
9
10 ,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
11 ,zip VARCHAR2)
12 RETURN SELF AS RESULT
13
14 ,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
15 ,zip VARCHAR2
16 ,city VARCHAR2
17 ,state VARCHAR2)
18 RETURN SELF AS RESULT
19
20 ,MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2
21 ,out_city OUT VARCHAR2
22 ,out_state OUT VARCHAR2)
23
24 ,STATIC PROCEDURE display_zipcode_info (in_zip_obj IN zipcode_obj_type)
25
26 ,MAP MEMBER FUNCTION zipcode RETURN VARCHAR2);
27 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY zipcode_obj_type AS
2
3 CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
4 ,zip VARCHAR2)
5 RETURN SELF AS RESULT
6 IS
7 BEGIN
8 SELF.zip := zip;
9
10 SELECT city, state
11 INTO SELF.city, SELF.state
12 FROM zipcode
13 WHERE zip = SELF.zip;
14
15 RETURN;
16 EXCEPTION
17 WHEN NO_DATA_FOUND THEN
18 RETURN;
19 END;
20
21 CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
22 ,zip VARCHAR2
23 ,city VARCHAR2
24 ,state VARCHAR2)
25 RETURN SELF AS RESULT
26 IS
27 BEGIN
28 SELF.zip := zip;
29 SELF.city := city;
30 SELF.state := state;
31
32 RETURN;
33 END;
34
35 MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2
36 ,out_city OUT VARCHAR2
37 ,out_state OUT VARCHAR2)
38 IS
39 BEGIN
40 out_zip := SELF.zip;
41 out_city := SELF.city;
42 out_state := SELF.state;
43 END;
44
45 STATIC PROCEDURE display_zipcode_info (in_zip_obj IN zipcode_obj_type)
46 IS
47 BEGIN
48 DBMS_OUTPUT.PUT_LINE ('Zip: ' ||in_zip_obj.zip);
49 DBMS_OUTPUT.PUT_LINE ('City: ' ||in_zip_obj.city);
50 DBMS_OUTPUT.PUT_LINE ('State: '||in_zip_obj.state);
51 END;
52
53 MAP MEMBER FUNCTION zipcode RETURN VARCHAR2
54 IS
55 BEGIN
56 RETURN (zip);
57 END;
58 END;
59 /
Type body created.
测试:
SQL> -- For Example ch23_9a.sql
SQL> DECLARE
2 zip_obj1 zipcode_obj_type;
3 zip_obj2 zipcode_obj_type;
4 BEGIN
5 -- Initialize object instances with user-defined constructor methods
6 zip_obj1 := zipcode_obj_type (zip => '12345'
7 ,city => 'Some City'
8 ,state => 'AB');
9
10 zip_obj2 := zipcode_obj_type (zip => '48104');
11
12 -- Compare object instances via map methods
13 IF zip_obj1 > zip_obj2
14 THEN
15 DBMS_OUTPUT.PUT_LINE ('zip_obj1 is greater than zip_obj2');
16 ELSE
17 DBMS_OUTPUT.PUT_LINE
18 ('zip_obj1 is not greater than zip_obj2');
19 END IF;
20 END;
21 /
zip_obj1 is not greater than zip_obj2
2)排序方法
SQL> -- For Example ch23_8e.sql
SQL> CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT
2 (zip VARCHAR2(5)
3 ,city VARCHAR2(25)
4 ,state VARCHAR2(2)
5 ,created_by VARCHAR2(30)
6 ,created_date DATE
7 ,modified_by VARCHAR2(30)
8 ,modified_date DATE
9
10 ,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
11 ,zip VARCHAR2)
12 RETURN SELF AS RESULT
13
14 ,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
15 ,zip VARCHAR2
16 ,city VARCHAR2
17 ,state VARCHAR2)
18 RETURN SELF AS RESULT
19
20 ,MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2
21 ,out_city OUT VARCHAR2
22 ,out_state OUT VARCHAR2)
23
24 ,STATIC PROCEDURE display_zipcode_info (in_zip_obj IN zipcode_obj_type)
25
26 ,ORDER MEMBER FUNCTION zipcode (zip_obj zipcode_obj_type) RETURN INTEGER);
27 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY zipcode_obj_type AS
2
3 CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
4 ,zip VARCHAR2)
5 RETURN SELF AS RESULT
6 IS
7 BEGIN
8 SELF.zip := zip;
9
10 SELECT city, state
11 INTO SELF.city, SELF.state
12 FROM zipcode
13 WHERE zip = SELF.zip;
14
15 RETURN;
16 EXCEPTION
17 WHEN NO_DATA_FOUND THEN
18 RETURN;
19 END;
20
21 CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
22 ,zip VARCHAR2
23 ,city VARCHAR2
24 ,state VARCHAR2)
25 RETURN SELF AS RESULT
26 IS
27 BEGIN
28 SELF.zip := zip;
29 SELF.city := city;
30 SELF.state := state;
31
32 RETURN;
33 END;
34
35 MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2
36 ,out_city OUT VARCHAR2
37 ,out_state OUT VARCHAR2)
38 IS
39 BEGIN
40 out_zip := SELF.zip;
41 out_city := SELF.city;
42 out_state := SELF.state;
43 END;
44
45 STATIC PROCEDURE display_zipcode_info (in_zip_obj IN zipcode_obj_type)
46 IS
47 BEGIN
48 DBMS_OUTPUT.PUT_LINE ('Zip: ' ||in_zip_obj.zip);
49 DBMS_OUTPUT.PUT_LINE ('City: ' ||in_zip_obj.city);
50 DBMS_OUTPUT.PUT_LINE ('State: '||in_zip_obj.state);
51 END;
52
53 ORDER MEMBER FUNCTION zipcode (zip_obj zipcode_obj_type) RETURN INTEGER
54 IS
55 BEGIN
56 IF zip < zip_obj.zip THEN RETURN -1;
57 ELSIF zip = zip_obj.zip THEN RETURN 0;
58 ELSIF zip > zip_obj.zip THEN RETURN 1;
59 END IF;
60 END;
61 END;
62 /
Type body created.
测试:
SQL> -- For Example ch23_10a.sql
SQL> DECLARE
2 zip_obj1 zipcode_obj_type;
3 zip_obj2 zipcode_obj_type;
4
5 v_result INTEGER;
6 BEGIN
7 -- Initialize object instances with user-defined constructor methods
8 zip_obj1 := zipcode_obj_type ('12345', 'Some City', 'AB');
9 zip_obj2 := zipcode_obj_type ('48104');
10
11 -- Compare objects instances via ORDER method
12 v_result := zip_obj1.zipcode(zip_obj2);
13 DBMS_OUTPUT.PUT_LINE ('The result of comparison is '||v_result);
14
15 IF v_result = 1
16 THEN
17 DBMS_OUTPUT.PUT_LINE ('zip_obj1 is greater than zip_obj2');
18
19 ELSIF v_result = 0
20 THEN
21 DBMS_OUTPUT.PUT_LINE ('zip_obj1 is equal to zip_obj2');
22
23 ELSIF v_result = -1
24 THEN
25 DBMS_OUTPUT.PUT_LINE ('zip_obj1 is less than zip_obj2');
26 END IF;
27 END;
28 /
The result of comparison is -1
zip_obj1 is less than zip_obj2