Oracle PL/SQL Tutorial/Object Oriented/type body

Материал из SQL эксперт
Перейти к: навигация, поиск

Create toString function for a type

   <source lang="sql">

SQL> SQL> SQL> create or replace type Address_Type

 2  as object
 3  (  street_addr1   varchar2(25),
 4     street_addr2   varchar2(25),
 5     city           varchar2(30),
 6     state          varchar2(2),
 7     zip_code       number,
 8     member function toString return varchar2
 9  )
10  /

Type created. SQL> SQL> SQL> SQL> create or replace type body Address_Type

 2  as
 3      member function toString return varchar2
 4      is
 5      begin
 6          if ( street_addr2 is not NULL )
 7          then
 8              return street_addr1 || " " ||
 9                     street_addr2 || " " ||
10                     city || ", " || state || " " || zip_code;
11          else
12              return street_addr1 || " " ||
13                     city || ", " || state || " " || zip_code;
14          end if;
15      end;
16  end;
17  /

Type body created. SQL> SQL> SQL> create table people

 2  ( name           varchar2(10),
 3    home_address   address_type,
 4    work_address   address_type
 5  )
 6  /

Table created. SQL> SQL> declare

 2      l_home_address address_type;
 3      l_work_address address_type;
 4  begin
 5      l_home_address := Address_Type( "1 Street", null,"R", "VA", 45678 );
 6      l_work_address := Address_Type( "1 Way", null,"R", "CA", 23456 );
 7
 8      insert into people( name, home_address, work_address )values ( "Tom Kyte", l_home_address, l_work_address );
 9  end;
10  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> select name, p.home_address.toString()

 2    from people P
 3  /

NAME


P.HOME_ADDRESS.TOSTRING()


Tom Kyte 1 Street R, VA 45678

SQL> drop type Address_Type; drop type Address_Type

ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents

SQL> SQL> drop table people; Table dropped. SQL> SQL> SQL></source>


Create type body

   <source lang="sql">

SQL> SQL> create or replace type Address_Type

 2  as object
 3  (  street_addr1   varchar2(25),
 4     street_addr2   varchar2(25),
 5     city           varchar2(30),
 6     state          varchar2(2),
 7     zip_code       number,
 8     member function toString return varchar2,
 9     map member function mapping_function return varchar2
10  )
11  /

Type created. SQL> create or replace type body Address_Type as

 2      member function toString return varchar2
 3      is
 4      begin
 5          if ( street_addr2 is not NULL )
 6          then
 7              return street_addr1 || " " ||
 8                     street_addr2 || " " ||
 9                     city || ", " || state || " " || zip_code;
10          else
11              return street_addr1 || " " ||
12                     city || ", " || state || " " || zip_code;
13          end if;
14      end;
15
16      map member function mapping_function return varchar2
17      is
18      begin
19          return to_char( nvl(zip_code,0), "fm00000" ) ||
20                 lpad( nvl(city," "), 30 ) ||
21                 lpad( nvl(street_addr1," "), 25 ) ||
22                 lpad( nvl(street_addr2," "), 25 );
23      end;
24  end;
25  /

Type body created. SQL> SQL> create table people

 2  ( name           varchar2(10),
 3    home_address   address_type,
 4    work_address   address_type
 5  )
 6  /

Table created. SQL> SQL> set echo on SQL> set linesize 73 SQL> SQL> create or replace type Address_Array_Type as varray(25) of Address_Type

 2  /

Type created. SQL> alter table people add previous_addresses Address_Array_Type

 2  /

Table altered. SQL> set describe depth all SQL> SQL> desc people

Name                                  Null?    Type
------------------------------------- -------- -------------------------
NAME                                           VARCHAR2(10)
HOME_ADDRESS                                   ADDRESS_TYPE
  STREET_ADDR1                                 VARCHAR2(25)
  STREET_ADDR2                                 VARCHAR2(25)
  CITY                                         VARCHAR2(30)
  STATE                                        VARCHAR2(2)
  ZIP_CODE                                     NUMBER

METHOD


MEMBER FUNCTION TOSTRING RETURNS VARCHAR2

METHOD


MAP MEMBER FUNCTION MAPPING_FUNCTION RETURNS VARCHAR2
WORK_ADDRESS                                   ADDRESS_TYPE
  STREET_ADDR1                                 VARCHAR2(25)
  STREET_ADDR2                                 VARCHAR2(25)
  CITY                                         VARCHAR2(30)
  STATE                                        VARCHAR2(2)
  ZIP_CODE                                     NUMBER

METHOD


MEMBER FUNCTION TOSTRING RETURNS VARCHAR2

METHOD


MAP MEMBER FUNCTION MAPPING_FUNCTION RETURNS VARCHAR2
PREVIOUS_ADDRESSES                             ADDRESS_ARRAY_TYPE
  STREET_ADDR1                                 VARCHAR2(25)
  STREET_ADDR2                                 VARCHAR2(25)
  CITY                                         VARCHAR2(30)
  STATE                                        VARCHAR2(2)
  ZIP_CODE                                     NUMBER

METHOD


MEMBER FUNCTION TOSTRING RETURNS VARCHAR2

METHOD


MAP MEMBER FUNCTION MAPPING_FUNCTION RETURNS VARCHAR2

SQL> SQL> select name, length from sys.col$

 2  where obj# = ( select object_id from user_objects where object_name = "PEOPLE" )
 3  /

NAME LENGTH


----------

NAME 10 HOME_ADDRE 1 SS SYS_NC0000 25 3$ SYS_NC0000 25 4$ SYS_NC0000 30 5$ SYS_NC0000 2 6$ SYS_NC0000 22 7$ WORK_ADDRE 1 SS SYS_NC0000 25 9$ SYS_NC0001 25 0$ drop table people; NAME LENGTH


----------

SYS_NC0001 30 1$ SYS_NC0001 2 2$ SYS_NC0001 22 3$ PREVIOUS_A 3042 DDRESSES

14 rows selected. SQL> drop type Address_Array_Type; SQL> drop type address_type; drop type address_type</source>


Implement a function in type body

   <source lang="sql">

SQL> SQL> create or replace type Address_Type

 2  as object
 3  (  street_addr1   varchar2(25),
 4     street_addr2   varchar2(25),
 5     city           varchar2(30),
 6     state          varchar2(2),
 7     zip_code       number,
 8     member function toString return varchar2
 9  )
10  /

Type created. SQL> show error No errors. SQL> create or replace type body Address_Type

 2  as
 3      member function toString return varchar2
 4      is
 5      begin
 6          if ( street_addr2 is not NULL )
 7          then
 8              return street_addr1 || " " ||
 9                     street_addr2 || " " ||
10                     city || ", " || state || " " || zip_code;
11          else
12              return street_addr1 || " " ||
13                     city || ", " || state || " " || zip_code;
14          end if;
15      end;
16  end;
17  /

Type body created. SQL> show error No errors. SQL> SQL> SQL> drop type Address_Type; Type dropped.</source>


Object table

   <source lang="sql">

SQL> SQL> create or replace type Address_Type

 2  as object
 3  (  street_addr1   varchar2(25),
 4     street_addr2   varchar2(25),
 5     city           varchar2(30),
 6     state          varchar2(2),
 7     zip_code       number
 8  )
 9  /

Type created. SQL> SQL> create table people

 2  ( name           varchar2(10),
 3    home_address   address_type,
 4    work_address   address_type
 5  )
 6  /

Table created. SQL> SQL> declare

 2      l_home_address address_type;
 3      l_work_address address_type;
 4  begin
 5      l_home_address := Address_Type( "1 Street", null,"R", "VA", 45678 );
 6      l_work_address := Address_Type( "1 Way", null,"R", "CA", 23456 );
 7
 8      insert into people ( name, home_address, work_address )values ( "T K", l_home_address, l_work_address );
 9  end;
10  /

PL/SQL procedure successfully completed. SQL> SQL> select * from people order by home_address; select * from people order by home_address

      *

ERROR at line 1: ORA-22950: cannot ORDER objects without MAP or ORDER method

SQL> SQL> select * from people where home_address > work_address; select * from people where home_address > work_address

                          *

ERROR at line 1: ORA-22950: cannot ORDER objects without MAP or ORDER method

SQL> SQL> select * from people where home_address = work_address; no rows selected SQL> SQL> drop table people; Table dropped. SQL> SQL> drop type Address_Type; Type dropped.</source>


Static Method

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE zip_code AS OBJECT

 2  (five_digit_code NUMBER,
 3   four_digit_code NUMBER,
 4   STATIC FUNCTION getZip(zip_in zip_code) RETURN zip_Code
 5  );
 6  /

Type created. SQL> CREATE OR REPLACE TYPE BODY zip_code AS

 2    STATIC FUNCTION getZip(zip_in zip_code) RETURN zip_code
 3    IS
 4      v_zip zip_code;
 5    BEGIN
 6      v_zip :=zip_code(null,null);
 7      v_zip.five_digit_code :=zip_in.five_digit_code;
 8      v_zip.four_digit_code :=zip_in.four_digit_code;
 9      RETURN (v_zip);
10     END;
11  END;
12  /

Type body created. SQL></source>


Type with member procedure

   <source lang="sql">

SQL> SQL> CREATE TABLE emp

 2     (id         VARCHAR2(10) NOT NULL,
 3      course     VARCHAR2(10),
 4      year       VARCHAR2(4),
 5      PRIMARY KEY (id));

Table created. SQL> CREATE OR REPLACE TYPE empType AS OBJECT

 2     (id                     VARCHAR2(10),
 3      course                 VARCHAR2(20),
 4      year                   VARCHAR2(4),
 5      MEMBER PROCEDURE       Delete_emp )
 6  /

Type created. SQL> SQL> CREATE OR REPLACE TYPE BODY empType AS

 2     MEMBER PROCEDURE
 3     Delete_emp IS
 4     BEGIN
 5           DELETE FROM emp
 6           WHERE emp.id = self.id;
 7     END Delete_emp;
 8  END;
 9  /

Type body created. SQL> drop table emp; Table dropped.</source>


Type with method

   <source lang="sql">

SQL> SQL> -- Methods SQL> CREATE OR REPLACE TYPE address AS OBJECT

 2              (line1 VARCHAR2(20),
 3               line2 VARCHAR2(20),
 4               city VARCHAR2(20),
 5               state_code VARCHAR2(2),
 6               zip VARCHAR2(13),
 7    MEMBER FUNCTION get_address RETURN VARCHAR2,
 8    MEMBER PROCEDURE set_address
 9              (addressLine1 VARCHAR2,
10               addressLine2 VARCHAR2,
11               address_city VARCHAR2,
12               address_state VARCHAR2,
13               address_zip VARCHAR2)
14  );
15  /

Type created. SQL> CREATE OR REPLACE TYPE BODY address AS

 2    MEMBER FUNCTION get_address RETURN VARCHAR2
 3    IS
 4    BEGIN
 5
 6      RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "|| SELF.state_code||" "||SELF.zip);
 7
 8    END get_address;
 9
10    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
11                  addressLine2 VARCHAR2,
12                  address_city VARCHAR2,
13                  address_state VARCHAR2,
14                  address_zip VARCHAR2)
15    IS
16
17    BEGIN
18      line1 :=addressLine1;
19      line2 :=addressLine2;
20      city :=address_city;
21      state_code :=address_state;
22      zip :=address_zip;
23    END set_address;
24  END;
25  /

Type body created. SQL></source>


Type with order function

   <source lang="sql">

SQL> SQL> create or replace type Address_Type

 2  as object
 3  (  street_addr1   varchar2(25),
 4     street_addr2   varchar2(25),
 5     city           varchar2(30),
 6     state          varchar2(2),
 7     zip_code       number,
 8     member function toString return varchar2,
 9     order member function order_function( compare2 in Address_type )
10     return number
11  )
12  /

Type created. SQL> SQL> create or replace type body Address_Type

 2  as
 3      member function toString return varchar2
 4      is
 5      begin
 6          if ( street_addr2 is not NULL )
 7          then
 8              return street_addr1 || " " ||
 9                     street_addr2 || " " ||
10                     city || ", " || state || " " || zip_code;
11          else
12              return street_addr1 || " " ||
13                     city || ", " || state || " " || zip_code;
14          end if;
15      end;
16
17      order member function order_function(compare2 in Address_type)
18      return number
19      is
20      begin
21          if (nvl(self.zip_code,-99999) <> nvl(compare2.zip_code,-99999))
22          then
23              return sign(nvl(self.zip_code,-99999) - nvl(compare2.zip_code,-99999));
24          end if;
25          if (nvl(self.city,chr(0)) > nvl(compare2.city,chr(0)))
26          then
27              return 1;
28          elsif (nvl(self.city,chr(0)) < nvl(compare2.city,chr(0)))
29          then
30              return -1;
31          end if;
32          if ( nvl(self.street_addr1,chr(0)) > nvl(compare2.street_addr1,chr(0))  )
33          then
34              return 1;
35          elsif ( nvl(self.street_addr1,chr(0)) < nvl(compare2.street_addr1,chr(0)) )
36          then
37              return -1;
38          end if;
39          if ( nvl(self.street_addr2,chr(0)) > nvl(compare2.street_addr2,chr(0))  )
40          then
41              return 1;
42          elsif ( nvl(self.street_addr2,chr(0)) < nvl(compare2.street_addr2,chr(0)) )
43          then
44              return -1;
45          end if;
46          return 0;
47      end;
48  end;
49  /

Type body created. SQL> SQL> SQL> drop type Address_Type; Type dropped. SQL></source>