Oracle PL/SQL Tutorial/Object Oriented/type body
Содержание
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>