Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Object Oriented

 

Alter type to add member function

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  / SQL> SQL> alter type Address_Type   2  REPLACE   3  as object   4  (  street_addr1   varchar2(25),   5     street_addr2   varchar2(25),   6     city           varchar2(30),   7     state          varchar2(2),   8     zip_code       number,   9     member function toString return varchar2,  10     map member function mapping_function return varchar2  11  )  12  / Type altered. 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      map member function mapping_function return varchar2  18      is  19      begin  20          return to_char( nvl(zip_code,0), 'fm00000' ) ||  21                 lpad( nvl(city,' '), 30 ) ||  22                 lpad( nvl(street_addr1,' '), 25 ) ||  23                 lpad( nvl(street_addr2,' '), 25 );  24      end;  25  end;  26  / Type body created. SQL> create table people   2  ( name           varchar2(10),   3    home_address   address_type,   4    work_address   address_type   5  )   6  / SQL> create or replace type Address_Array_Type as varray(50) of Address_Type   2  / SQL> alter table people add previous_addresses Address_Array_Type   2  / SQL> SQL> select object_type, object_name, decode(status,'INVALID','*','') status, tablespace_name   2  from user_objects a, user_segments b   3  where a.object_name = b.segment_name (+)   4  and   rownum < 50   5  order by object_type, object_name   6  / OBJECT_TYPE ------------------- OBJECT_NAME ------------------------------------------------------------------------- S TABLESPACE_NAME - ------------------------------ FUNCTION CHITIME FUNCTION IS_OVERDUE FUNCTION RECEIVE_MESSAGE * FUNCTION RULERSTR FUNCTION TABCOUNT FUNCTION TO_MMSDDSYYYY_OR_NULL drop table people; OBJECT_TYPE ------------------- OBJECT_NAME ------------------------------------------------------------------------- S TABLESPACE_NAME - ------------------------------ FUNCTION TO_NUMBER_OR_NULL FUNCTION TRACEIT * FUNCTION VALUE_IN PACKAGE CLASSPACKAGE * PACKAGE DATES PACKAGE GENDER_TS OBJECT_TYPE ------------------- OBJECT_NAME ------------------------------------------------------------------------- S TABLESPACE_NAME - ------------------------------ * PACKAGE PARAMETERS PACKAGE SCOPES PACKAGE WORKER_TS * PACKAGE WORKER_TYPE_TS * PACKAGE WORKPLACE_TYPE_TS * PACKAGE BODY OBJECT_TYPE ------------------- OBJECT_NAME ------------------------------------------------------------------------- S TABLESPACE_NAME - ------------------------------ CLASSPACKAGE * PACKAGE BODY DATES PACKAGE BODY GENDER_TS * PACKAGE BODY PARAMETERS PACKAGE BODY SCOPES PACKAGE BODY WORKER_TS * OBJECT_TYPE ------------------- OBJECT_NAME ------------------------------------------------------------------------- S TABLESPACE_NAME - ------------------------------ PACKAGE BODY WORKER_TYPE_TS * PACKAGE BODY WORKPLACE_TYPE_TS * PROCEDURE BULK_TEST * PROCEDURE CLOSECUR * PROCEDURE CREINDX * PROCEDURE DELETE_CUST * OBJECT_TYPE ------------------- OBJECT_NAME ------------------------------------------------------------------------- S TABLESPACE_NAME - ------------------------------ PROCEDURE DROPIT PROCEDURE DROP_IF_EXISTS PROCEDURE FCREATE * PROCEDURE FLUSH PROCEDURE GET_LINE * PROCEDURE P1 OBJECT_TYPE ------------------- OBJECT_NAME ------------------------------------------------------------------------- S TABLESPACE_NAME - ------------------------------ PROCEDURE PRINTLN PROCEDURE PUT_BOOLEAN * PROCEDURE P_RUN_INSERT * PROCEDURE ROW_AT_A_TIME_TEST * PROCEDURE RUNDDL PROCEDURE OBJECT_TYPE ------------------- OBJECT_NAME ------------------------------------------------------------------------- S TABLESPACE_NAME - ------------------------------ SAVESTRINGTOFILE * PROCEDURE SEND_MESSAGE * PROCEDURE WAIT * TABLE HAZARD_LEVEL_T   SYSTEM TYPE DEBUG_O TYPE BODY DEBUG_O * OBJECT_TYPE ------------------- OBJECT_NAME ------------------------------------------------------------------------- S TABLESPACE_NAME - ------------------------------ VIEW DEPT20_V * VIEW SALES_BY_ATLAS_V * VIEW SALES_PER_PERSON_V *