《Oracle PL/SQL实例精讲》学习笔记24——ORACLE对象类型（第二部分）-CSDN博客

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/84037035

本章内容：

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